Feeds:
Posts
Comments

Posts Tagged ‘to’


-- 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

Advertisements

Read Full Post »


-- The objective is to find the second occurence of space and insert a carriage return. 

-- First Method:
----------------

DECLARE @passed VARCHAR(max)
SET @passed = 'First Second Third Fourth'

-- Extract all text until the second occurence 
select substring(@passed, 1,
		charindex(' ', @passed, charindex(' ', @passed) + 1)-1
		) [Space Occured First]


union  all 
select CHAR(13) + CHAR(10)
union 

-- Extract all text after the second occurence 
select substring(@passed
			, charindex(' ', @passed, charindex(' ', @passed) + 1)
			, len(@passed)
		) [Space Occured Second]
		
		
-- Second Method:
--------------------

declare @tbl_Foo table 
(
	FiledToChange varchar(50)
)

insert into @tbl_Foo values -- inserting this way can only be from SQL server 2008 and above 
('a b c')
,('1 2 3 4')
,('Single')

select FiledToChange as [Before] from @tbl_Foo

update @tbl_Foo
	set FiledToChange = ISNULL(
		case when charindex(' ', FiledToChange, charindex(' ', FiledToChange) + 1)-1 > 0 then -- Look for a second space 
			substring(FiledToChange, 1, -- This will get two words 
				charindex(' ', FiledToChange, charindex(' ', FiledToChange) + 1)-1)
			+ 
			CHAR(13) + CHAR(10) -- add the carriage return and line feed 
			+ 
			substring(FiledToChange -- add the rest of the text 
				, charindex(' ', FiledToChange, charindex(' ', FiledToChange) + 1)
				, len(FiledToChange))
			end 
		,FiledToChange)
			
select FiledToChange as [After] from @tbl_Foo
-- Since SQL does not support displaying multiple lines, you will notice that instead there is an extra space. Applications supporting multiple lines can read this and conver it to multiple lines

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 »

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 »

-- Returns the Week Day Names and its correponding Week Number (taking Sunday as 1)
--==================================================
--alter proc dbo.usp_getWeekDayNames
--as
SET DATEFIRST 1 -- Monday is set to be the first day of the week

DECLARE @c INT

SET @c = 1

DECLARE @WeekDays TABLE (
	WeekDayNumber INT
	,WeekDayName VARCHAR(12)
	)

WHILE (@c & lt;= 7)
BEGIN
	INSERT @WeekDays
	SELECT datepart(weekday, dateadd(day, @c, getdate())) WeekDayNumber
		,datename(weekday, dateadd(day, @c, getdate())) WeekDayName

	SET @c = @c + 1
END

SELECT *
FROM @WeekDays
ORDER BY 1

Read Full Post »

Older Posts »