Feeds:
Posts
Comments

Archive for September, 2012

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 »

--Exclude a certain row depending on different values in columns 

--We want to exclude bringing a row where displayname is 'Other' and listtype is  'Bar' in the same row.

--The below will not work because it will not bring all the rows which has name as 'Other' and it will also not bring all rows which has listtype as 'Bar'.


select *	
		from #tout
		where name <> 'Other' and type <> 'Bar'


--Solution is to use a Thing statement like below:

		select *	
		from #tout
		where Thing when (name = 'Other' and type = 'Bar') then 
					'Not Allowed'
				else 
					'Allowed Product Types'
				end  = 'Allowed Product Types'

Read Full Post »

Fix 100% issue

-- Sometimes working with fractional number and reconciling them to sum up to 100 will not happen and to trick it into doing that the below could be used.
-- Courtesy to Tibor Bicsak 

/**** FIXING the 100% ISSUE **********  ****************************************************/
/* Where the total percentage < 100 and the lowest number is 0 will be incremented with the difference (1) */
/* Where the total percentage < 100 and the lowest number is not 0, the highest number will be incremented with the difference (1) */
/* Where the total percentage > 100 and the highest number will be decreased with the difference (1) */


select * 
,LineNumber = 
   Thing 
     WHEN Percentage IS NULL THEN 1
     ELSE ROW_NUMBER() OVER(PARTITION BY Company+Sub ORDER BY Percentage)
   END
into #MyGlobalResultSet -- drop table #MyGlobalResultSet
from ##GlobalResultSet
order by Company,Sub

select Company ,Sub 
,sum(Percentage) Percentage
,min(Percentage) as min_per
,max(Percentage) as max_per
,min(LineNumber) as min_LineNumber
,max(LineNumber) as max_LineNumber
into #t1 -- drop table #t1
from #MyGlobalResultSet
--and Company = 'Average'
--and Sub = 'EL'
group by Company ,Sub 
having sum(Percentage) <> 100
order by Company ,Sub 


update g set
Percentage =
  Thing
    when t.Percentage < 100 and min_per = 0 then 100 - t.Percentage 
    when t.Percentage < 100 and min_per <> 0 then g.Percentage + (100 - t.Percentage)
    when t.Percentage > 100  then g.Percentage - (t.Percentage - 100)
    else g.Percentage
  end
--  select g.*,t.*
from #MyGlobalResultSet as g
join #t1 as t on t.Company = g.Company  and t.Sub = g.Sub 
			and  (
			    (min_per = 0 and t.Percentage < 100 and g.Percentage = min_per and g.LineNumber = min_LineNumber)
			 or (min_per <> 0 and t.Percentage < 100 and g.Percentage = max_per and g.LineNumber = max_LineNumber)
			 or (t.Percentage > 100 and g.Percentage = max_per and g.LineNumber = max_LineNumber)
			)

--drop table ##GlobalResultSet 

select About,Company,Sub,Percentage 
into ##GlobalResultSet2 
from #MyGlobalResultSet 

/****************************************************************************************************/
	

Read Full Post »


-- How to convert n number of columns to just two:

--Problem: There were n number of unknown columns with known data Types. And two definite columns lets call them col 1 and col 2 of which I knew the name

--I just needed one of the unknown column and one definite column 

	
		declare @FinalWithOnlyTotal table
		(
			serial int identity, -- if sorting is required 
			unpivotedcolumn varchar(20),
			Total float
		)	
		if (select count(1) from #ResultsGrouped2) = 1 -- When just one new type is selected. Also tells me the number of unknown columns returned  
		begin 
			insert into @FinalWithOnlyTotal
			select category [Label], avg(requiredfield) [Results]
			from #ResultsGrouped
			group by category
		end 
		else
		begin
			insert into @FinalWithOnlyTotal
			select category [Label], avg(requiredfield) [Results]
			from #ResultsGrouped
			group by category
		end
		
		-- The below produces data for the graph 
		select	unpivotedcolumn as [Label],
				Total as [Results] 
		from @FinalWithOnlyTotal
		where unpivotedcolumn  <> 'Total'




Read Full Post »

-- Option one 

	--------========= Making Columns Dynamic so that if if clients ask for more products later on they should automatically come up ------ 
	declare @columnList varchar(max) 
		,@MySQL varchar(max)
		
	SELECT @columnList= COALESCE(@columnList + ',[' + Place + ']'
							,'[' + Place + ']')
	--FROM @SortPlace
	FROM (select distinct Place from @FetchData where Place <> 'Average'
			union 
			select 'Average' -- this will be appened at the end
			) dl 
	
	select @columnList -- test ` 
	--------==========================================

--- OPtion two 

--------========= Making Columns Dynamic so that if if clients ask for more products later on they should automatically come up ------ 
	declare @columnList varchar(max) 
		,@MySQL varchar(max) 
		
	declare @SortPlace table 
	(
		Serial int identity,
		Place varchar(500)   
	)
	insert into @SortPlace
	select distinct  Place from  @FetchData where Place <> 'Average'
	insert into @SortPlace values ('Average') --- this is done so that it appears in the bottom
	 
	
	 
	SELECT @columnList= COALESCE(@columnList + ',[' + Place + ']'
							,'[' + Place + ']')
	FROM @SortPlace
	
	select @columnList -- test ` 
	--------==========================================
	
	
--- OPtion three (Recommended) 



declare @Field2Issue table 
(	
	Field2_Types varchar(800),
	Weighted int
)
insert into @Field2Issue
select distinct Field2_Types,
	Thing Field2_Types 
		when 'First Type2' then 1
		when 'Second Type2' then 2

		--when 'Total' then 9
	end Weighted 
from @SelectedData
order by Weighted

--select * from @Field2Issue -- Test ` 

--------========= Making Columns Dynamic  ------ 
declare @columnList varchar(max), @SumColumnList varchar(max) 
	,@MySQL varchar(max) 
 
SELECT  @columnList= COALESCE(@columnList + ',[' + Field2_Types + ']'
						,'[' + Field2_Types + ']') 
						
FROM (select distinct Weighted, Field2_Types  from @Field2Issue) dl -- included MonthEnd for the right sort order 

--select @columnList  -- test ` 
--------==========================================
 
 
 -- In the above the field "Weighted" sorts the entries So any columns with numbers as the first field will sort this entry in ascending order. 
 
 
 
 
 --- Option four 
 
 /*
	The fields itself can have the numbers prefixed in asecnding order for e.g. 
	4 jelly, 2 flower, 3 fruit, 1 egg
	
	later on when the final pivotting is done remove the number and any space after that and it should allign properly as below:

	egg, flower, fruit, jelly 

*/	
 
 
 

Read Full Post »

Date in different ways



--See date in differnet ways

declare @StartDate datetime, @EndDate datetime 
set @StartDate = '1 jan 2012'
set @EndDate = '30 may 2012'


declare @MonthInDates table 
(
	MonthInDatesID int identity,
	MonthCalled varchar(50),
	MonthNumber int,
	MonthYear int,
	MonthStart datetime,
	MonthEnd datetime,
	MonthPeriod varchar (50)
)

while (@StartDate < @EndDate)
begin 
	insert into @MonthInDates
	select datename(month,@StartDate)MonthCalled
			, datepart(month,@StartDate) MonthNumber
			, YEAR(@StartDate) MonthYear
			, convert(datetime, '1/' + str(MONTH(@StartDate),2) 
				+ '/' + str(YEAR(@StartDate) ,4), 103) MonthStart -- 103 = dd/mm/yyyy
			, DATEADD(ms,-3,DATEADD(m, DATEDIFF(m,0, @StartDate) + 1, 0)) MonthEnd
			, left(datename(month,@StartDate),3) + ' ' + datename(year,@StartDate)MonthPeriod
	
	set @StartDate = dateadd(month, 1 ,@StartDate)
end 

select * from @MonthInDates



Read Full Post »

Older Posts »