Feeds:
Posts
Comments

Posts Tagged ‘table’


--Please note section 3 nneds to be run each time an Insert, Update or Delete is performed. 
--If section 3 is not run after each DML then only the last DML can be garaunteed.

/*  Section 1: Run to reset. This recreates the tables and makes it ready from scratch

if object_id('tempdb..##org') is not null drop table ##org

if object_id('tempdb..##hist') is not null drop table ##hist

create table ##org
(
	id int identity 
	, name varchar (50)
	, age int 
)

create table ##hist
(
	sur int identity 
	, id int 
	, name varchar (50)
	, age int 
	, isCurrent bit 
	, EffectiveFrom datetime
	, EffectiveTo datetime
)

insert into ##org values 
	('tom','34')
	, ('dick','29')
	, ('harry','40')

insert into ##hist
select * , 1, GETDATE(), '9999-1-1'
from ##org

select * from ##org 
select * from ##hist

*/


/*		Section 2: Perform an opertion. Perform an Insert, Update or Delete 

-- insert 
insert into ##org values ('ron','19')

-- update 
update ##org set name = 'jerry' where id =1

-- delete 
delete from ##org where id = 3

select * from ##org  
select * from ##hist


*/


--Section 3: Run this each time an above DML is performed and the changes will be saved in the history table (##hist)

insert into ##hist (id, name, age, isCurrent, effectiveFrom, effectiveTo) 
select id, name, age, 0 as  isCurrent, effectiveFrom, effectiveTo
from 
	(
	merge ##hist as target 
		using ##org as source 
			on		source.id = target.id 

	when MATCHED  
				and isCurrent = 1
				and exists -- UPDATE - when record exists but some other fields changed (the bleow also handles nulls)
						(select source.id, source.name, source. age
						except 
						select target.id, target.name, target. age)

	then  -- UPDATE - when record exists but some other fields changed
		UPDATE set
			Target.name = Source.name
			, Target.age = Source.age
			, Target.isCurrent = 1 
			, Target.EffectiveFrom = getdate()
			, Target.EffectiveTo = '9999-1-1' 

	when NOT MATCHED BY TARGET -- INSERT -- new records exits in the source 
	then 
		INSERT --(id, name, age, isCurrent, EffectiveFrom, EffectiveTo) 
		values (source.id, source.name, source.age, 1, getdate(), '9999-1-1')
	
	when NOT MATCHED BY SOURCE -- DELETE -- if a record is not found then UPDATE EffectiveTo date as getdate() adn set isCurrent to 0
	then 
		UPDATE set
			Target.isCurrent = 0
			, Target.EffectiveTo = getdate()
	------------------------------
	-- Getting the previous row before the update is comminted 
	output $ACTION ActioinOut
			,  deleted.id, deleted.name, deleted.age, inserted.isCurrent as IsCurrent, deleted.effectiveFrom, getdate() as effectiveTo
	------------------------------
	) RowBeforeMerge
where RowBeforeMerge.ActioinOut = 'Update'
	and RowBeforeMerge.isCurrent = 1 -- this helps not to write the the row that is deleted twice
									-- when DELETED inserted.isCurrent is equal to 0
	;

select * from ##org  
select * from ##hist



Read Full Post »

declare @t table ( f1 int identity , f2 varchar(100))
insert into @tselect 'hello'unionselect 'world'

select * from @t 

-- Table to XML 
select  ( select 1 as f1r1 , 2 as f1r2 for XML path ('f1'), type ),
 ( select 'hello' as f2r1 , 'world' as f2r2 for XML path ('f2'), type )for XML path (''), root('sample')

-- XML to table declare @myXML xml
set @myXML = '<sample>  <f1>    <f1r1>1</f1r1>    <f1r2>2</f1r2>  </f1>  <f2>    <f2r1>hello</f2r1>    <f2r2>world</f2r2>  </f2></sample>'

select  a.b.value('f1[1]/f1r1[1]','int') as f1 , a.b.value('f1[1]/f1r2[1]','int') as f2
 , a.b.value('f2[1]/f2r1[1]','varchar(10)') as f3 , a.b.value('f2[1]/f2r2[1]','varchar(10)') as f4from @myXML.nodes('sample')a(b) --read this as @myXML.nodes('[root]') [table name]([column name])

Read Full Post »

In this example we will be using a Tablix as the objects but really it could be a Matrix, Graphs, Textbox etc.

Assume a scenario. In Tablix A (on the left hand side) we have a list of fruits :
Melons and Apples

If we click on Melons then on a second Tablix B (on the right hand side) we will display the colours Yellow and Green. If apples is selected then we will show Red and Green.

Create a Parameter called DrillThrough of type Text and make the visibility Hidden.

Goto the textbox property of the cell holding Tablix A’s values.
This is where the magic happens:
Select Action.
From there select “Go to report”
Under the Specify a Report select the name of the report you are currently on (this is so that it refer’s back to itself).
Then click the “Add” parameter button.
Select the parameter DrillThrough and under value select the field from Tablix A.

You can now test if you wanted using a text box by creating a text box and in the expression put in =Parameters!DrillThrough.Value
Delete the text box if yout testing is complete.

Finally create a Dataset where the parameter DrillThrough is used so that depending on what is clicked the list of Fruits in Tablix A that will be sent through to display the list of relevant colours in Tablix B.

Read Full Post »


/*
    There are many uses of date tables and it could becomes essential at other times.
    The below script creates a function that produces a daily date table with the passed start and end dates.

    There are also examples at ehe ver bottom
*/

create function [dbo].[udf_DailyDateTable]
      (@StartDate datetime, @EndDate datetime)         
returns @DailyDates table 
(
    DailyDatesID int identity,
    DailyDate date, 
    DayNumber int,
    DayName varchar(50),
    WeekNumber int,
    QuaterNumber int,
    MonthNumber int,
    MonthCalled varchar(50),
    YearNumber int
)
AS 
BEGIN
 --   ------------ Test -------------

	---- select * from [dbo].[udf_DailyDateTable] ('1 jan 2016', '5 jan 2016')

 --   declare @DailyDates table
 --   (
 --       DailyDatesID int identity,
	--   DailyDate date, 
	--   DayNumber int,
 --       DayName varchar(50),
 --       WeekNumber int,
	--   QuaterNumber int,
	--   MonthNumber int,
	--   MonthCalled varchar(50),
 --       YearNumber int
 --   )
     
 --   declare @StartDate date, @EndDate date
 --   set @StartDate = '15 sep 2010' -- GETDATE()
 --   set @EndDate = '19 nov 2011'
	
 --  ------------------------ 
 
    while (@StartDate <= @EndDate)
    begin
        insert into @DailyDates
        select	@StartDate DailyDate
			 , datename(day, @StartDate) DayName 
				, datename(dw, @StartDate) DayName 
                , datepart(week, @StartDate) WeekNumber
			 , DATEPART(qq , @StartDate) as QuarterValue 
                , datepart(month,@StartDate) MonthNumber
				, datename(month,@StartDate) MonthCalled
				, YEAR(@StartDate) YearNumber
         
        set @StartDate = dateadd(day, 1 ,@StartDate)
        --print @StartDate -- test ` 
    end
     
    --select * from @DailyDates -- test ` 
    return 
END


------------------------ Examples below ------------------------------ 

DECLARE @DateToProcess date = '28 jul 2016' -- getdate() -- this is generally todays date 

-- it is important to initialse @EndOfLastMonth first because that in turn is used in @YTDDateFrom. 
-- if this is run in january then the last year's dates will be used  
DECLARE @EndOfLastMonth date = DATEADD(ms,-3,DATEADD(m, DATEDIFF(m,0,@DateToProcess) , 0)) -- End of previous month. All results should be upto this date not yesterday
DECLARE @YTDDateFrom date = cast('1 jan ' + str(year(@EndOfLastMonth)) as date) -- YTD stars from 1 Jan. Just add the requested year


-- MTD
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.MonthNumber = month(@EndOfLastMonth)
						  

-- QTD
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.QuaterNumber = datepart(q,@EndOfLastMonth) 

-- YTD
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.YearNumber = datepart(yy,@DateToProcess) 

-- Yesterday 
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.DailyDate = dateadd(day, -1, @DateToProcess)

-- Month Of Yesterday
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.MonthNumber = month(dateadd(day, -1, @DateToProcess))


---- e.g. ------
/*
    ---------- Filter for dates ----------------
    and (CASE 
		  WHEN ddt.DailyDate = dateadd(day, -1, @DateToProcess) THEN 'Yes'  --- this and the next ones need to be before the otheres 
		  WHEN ddt.MonthNumber = month(dateadd(day, -1, @DateToProcess)) THEN 'MoY' -- this may be becasue thiss is a derived table and @DateToProcess > @EndOfLastMonth
		  WHEN ddt.MonthNumber = month(@EndOfLastMonth) THEN 'MTD' 
		  WHEN ddt.QuaterNumber = datepart(q,@EndOfLastMonth) THEN 'QTD' 
		  WHEN ddt.YearNumber = datepart(yy,@DateToProcess) THEN 'YTD'   		  
    END ) = @XTD 
    --------------------------------------------
*/
------------ 


Read Full Post »


-- New Table Structure 
CREATE TABLE dbo.FirstTable(
	FirstField varchar(100) null
) 


--Table Rename -- 
exec sp_rename 'dbo.FirstTable', 'dbo.TableRenamed'

-- Columns Rename 
exec sp_rename  'dbo.FirstTable.FirstField', 'FieldRenamed'


/* as a note, instead of using sp_rename to rename COLUMNS an alternative method is to use the ALTER table command to change a column's data type or (to re-name it)a new column can be added and old one dropped */

alter table dbo.TableRenamed 
add FieldRenamedSecondTime varchar(100) NULL

alter table dbo.TableRenamed 
drop column FieldRenamed varchar(100) NULL

-- To just change the datatype:
alter table dbo.TableRenamed 
alter column FieldRenamed int NULL

Read Full Post »

The different options available to eliminate/handle decimals

declare @value as float = 123.9876
select ceiling(@value), floor(@value) -- eliminate decimals 
select round(@value,2)

Find triggers inside a database (Courtesy to Harry Lee)

select st.name, strig.name, st.name
from sys.triggers strig inner join sys.tables st
	on strig.parent_id = st.object_id inner join sys.schemas sch
	on st.schema_id = sch.schema_id

Format number as currency

SELECT CONVERT(varchar, CAST(987654321 AS money), 1) 

Find column names in a given table

SELECT OBJECT_NAME(c.OBJECT_ID) TableName, c.name ColumnName
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE t.name = 'dbo.Foo' --you can change text to other dataTypes
ORDER BY 1--,c.OBJECT_ID;

Note on – pivoting accuracy with numbers
————————————
I have seen from experience that when Pivoting its better to use float or decimal (if you results can contain fractional values) and not integers to get more accurate results. If integer is required then the numbers need to be converted after the Pivotting.

Check IF Table is empty
—————————

To chekc if a record or a certain record exists

if exists(select top 1 * from TableName) — use the where clause to check for a certain record

The if exists checks whether any rows were returned. If I use top 1 in the selected statement that supposedly sohuld be more optimized because it does not need to show the showl table.
The star is used so that it can just be copied and reused by just changing the table name.

Generate Scripts (Backup scripts)
———————————–
Right click on a database
> Task
> Generate Scripts

Select the type of objects
Click the Advances button to see what how you want the script to be produced.

This way you would have all or your selected object include tables and its data all scripted out in a file.

Read Full Post »

Below are three samples (from the same script) of how columns can be dynamically selected.

Simple :


/*
Dynamic column selection 

In Thing x number of columns need to be displayed and it change each time – a solution could be:
If the names of columns can be captured in a table then the below code could be used to build a dynamic sql:

store all the fields names in the variable:

*/

SELECT  @pivotedfields= COALESCE(@pivotedfields + ',[' + category + ']','[' + category + ']')


-- Sample:

set @pivotedfields = 'Select ' 
	SELECT  @pivotedfields= COALESCE(@pivotedfields + ',[' + category + ']','[' + category + ']')
						+ '')
	FROM    TableContainingColumnNames
	set @pivotedfields = @pivotedfields + ' from tableName'




-- Example of how to format any one type of column (taken from project_reports_cba)


			set @strSQL3 = ' select [Month],  dbo.udf_FormatToCurrency ([' + @Drill  
				+ '], ''%'', 2, ''false'', ''false'') [' +  @Drill
				+ '] from #tmpFinalnew order by closed_month' -- UMA (3/2/2012) Added square brackets 
			execute (@strSQL3 )

Complex:

	
	-------------- ================== UMA 17/4/2012  - Making columns dynamic ====================== ---------------- 
	 
	    -- select top 10 * from #tmpSavings_res1 -- test `  -- common table containing the fields 
	    
	    create table #distinctFields -- the table needed to be created becuase SQL doesn't allow select inot the same table in a if else 
	    (
			FieldName varchar(50) 
	    )
	    	    
	    --- Extract the distinct field names 
	    if @Bar <> 'Motor' -- for all and casualty 
	    begin 
			insert into #distinctFields
			select distinct Bar 
			from #tmpSavings_res1
		end 
		else 
		begin 
			insert into #distinctFields
			select distinct Drill  
			from #tmpSavings_res1
		end 
	    
	    --select * from #distinctFields -- test ` 
	    
		if (select count(1) from #distinctFields) = 2 -- When just one new/product type is selected 
		begin											-- delete the total row 
			delete from #distinctFields
			where FieldName = 'Total'
		end 

		DECLARE @listedFields NVARCHAR(2000) -- = ''
		
		-- get a pivot list 
		SELECT  -- @listedFields= @listedFields  + ', [' + isnull(FieldName, '')  + ']'
				@listedFields = COALESCE(@listedFields + ',[' + FieldName + ']',
										'[' + FieldName + ']') -- COALESCE helps to stat the list without. this line is first execute and for the rest the above is. 
		FROM    #distinctFields 
		ORDER BY FieldName 
		
		 select @listedFields -- test ` -- List of fields that are requested to be seen 

		declare @MySQL varchar(max) -- This is where I will hold all my dynamic SQL

	-------------- ======================================== ---------------- 		

Extensive:

-------------- ================== Making columns dynamic ====================== ---------------- 
	 
	    
	    create table #distinctFields -- the table needed to be created becuase SQL doesn't allow select inot the same table in a if else 
	    (
			FieldName varchar(50) 
	    )
	    	    
	    --- Extract the distinct field names 
	    if @Bar <> 'Apple' 
	    begin 
			insert into #distinctFields
			select distinct Bar 
			from #tmpSavings_res1
		end 
		else 
		begin 
			insert into #distinctFields
			select distinct Drill  
			from #tmpSavings_res1
		end 
	    
	    --select * from #distinctFields -- test ` 
	    
		if (select count(1) from #distinctFields) = 2 -- When just one new/product type is selected 
		begin											-- delete the total row 
			delete from #distinctFields
			where FieldName = 'Total'
		end 

		DECLARE @listedFields NVARCHAR(2000) -- = ''
		
		-- get a pivot list 
		SELECT  -- @listedFields= @listedFields  + ', [' + isnull(FieldName, '')  + ']'
				@listedFields = COALESCE(@listedFields + ',[' + FieldName + ']',
										'[' + FieldName + ']') -- COALESCE helps to stat the list without. this line is first execute and for the rest the above is. 
		FROM    #distinctFields 
		ORDER BY FieldName 
		
		 --select @listedFields -- test ` -- List of fields that are requested to be seen 

		declare @MySQL varchar(max) -- This is where I will hold all my dynamic SQL

	-------------- ======================================== ---------------- 		

						declare @totalPT float 
						set @totalPT = (select sum(value) from #tmpSavings_res1	 where Bar = 'Total')
						
						INSERT INTO #tmpSavings_res1 (Bar, closed_month_txt, value, closed_month) -- no Drills
						SELECT b.Bar	 [Fruit],
								'Percentage' [Month],
								(select (sum(a.value)/@totalPT) * 100 
									from #tmpSavings_res1 a	
									where a.Bar = b.Bar
									group by a.Bar 
								), 
								getdate() 
						FROM   #tmpSavings_res1 b 
									--where category <> 'Total'  -- Need to show the Total for this one 
						GROUP BY b.Bar	
						
						--select * from #tmpSavings_res1 -- test ` 
							
							-- insert into #tmpFinal excluding the above 
						set @MySQL = 
							'select [Month], ' + 
								@listedFields +
							' into ##tmpFinal_Revamped
							from 
							(		    
								select	Bar [Fruit],
										closed_month_txt [Month], 
										cast(round(sum([value]),0) as int) [Value],
										closed_month
								from #tmpSavings_res1
								group by	Bar ,
											closed_month,
											closed_month_txt 

										
							)	as sourcetable
							PIVOT
							( 
							sum([Value])
							FOR [Fruit] in (' + 
								@listedFields + 
								 ')
							) as Final' 
							
							--print @MySQL -- test `
							exec (@MySQL) 
							
							select * from ##tmpFinal_Revamped -- test ` 


Read Full Post »

Older Posts »