Archive for the ‘TSQL’ Category

-- List of days in the week
;WITH WeekNames
    SELECT datepart(weekday,getdate()) AS WeekNumber, getdate() AS ActualDate


    SELECT datepart(weekday, dateadd(day, 1, b.ActualDate)), dateadd(day, 1, b.ActualDate)
    FROM  WeekNames b
    WHERE  DATEDIFF(day,getdate(), dateadd(day, 1, b.ActualDate)) < 7    
SELECT WeekNumber, dateName(weekday, ActualDate) WeekName  
FROM WeekNames
ORDER BY WeekNumber

-- List of the months 
;WITH MonthNames
    SELECT datepart(month, getdate()) AS MonthNumber, getdate() as ActualDate 


    SELECT datepart(month, DATEADD(month, 1, mn.ActualDate)), dateadd(month, 1, mn.ActualDate) 
    FROM MonthNames mn
    WHERE datediff(month, getdate(), dateadd(month, 1, mn.actualDate)) < 12
SELECT MonthNumber, datename(month, ActualDate) as MonthName
FROM MonthNames
ORDER BY MonthNumber

--List of last 10 years 
;WITH Last10Years
    SELECT datepart(year, getdate()) AS YearNumber, getdate() as ActualDate 


    SELECT datepart(year, DATEADD(year, -1, l10y.ActualDate)), dateadd(year, -1, l10y.ActualDate) 
    FROM Last10Years l10y
    WHERE datediff(year, dateadd(year, -1, dateadd(year, -1, l10y.ActualDate)), getdate()) <= 10
SELECT YearNumber
FROM Last10Years

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
 --   ------------ 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)
        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 ` 
    --select * from @DailyDates -- test ` 

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

-- 1) Get your source data data. This is the result that contains data that you would like to pivot

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

select *
INTO ##t -- you will need to store it in a Global temp table
from TreeExample
-- ... some joins and conditoiins ...
-- 2) To dynamically populate columns create the variables for it

declare @pivotedfields varchar(max), @pivotedfieldsSummed varchar(max)

-- i) The below is to use when pivoting itself

SELECT @pivotedfields = COALESCE(@pivotedfields + ',[' + MonthYear + ']','[' + MonthYear+ ']')
FROM (select distinct MonthYear, min(PeriodStartDate) PeriodStartDate from ##t group by MonthYear) t
order by PeriodStartDate

-- ii) there are times when AFTER the pivot the whole result might need to be grouped again for each unique IDs (of some kind) and the NULLs across disappears because data are stacked on the same rows.

SELECT @pivotedfieldsSummed = COALESCE(@pivotedfieldsSummed + ',sum(isnull([' + MonthYear + '],0)) [' + MonthYear + ']','sum(isnull([' + MonthYear+ '],0)) [' + MonthYear +']')
FROM (select distinct MonthYear, min(PeriodStartDate) PeriodStartDate from ##t group by MonthYear) t
order by PeriodStartDate

--select @pivotedfields, @pivotedfieldsSummed -- test
declare @sql as varchar(max)
set @sql =
'SELECT convert(char(10),min(CountStartDate),103) as CountStartFrom, -- The first day the count was started
+ @pivotedfieldsSummed + --- we are using hte Summed varialbe here
select * from ##t -- this is the source data
) as s
FOR MonthYear IN (' + @pivotedfields + ') -- this is where the first pivot variable goes.
)AS p
group by IsTreeOrPlant, TreeNumber -- We specify what we want it grouped by
order by CountStartFrom' -- this is optional but we acn sort with any one of hte fields

--print @sql -- test
exec (@sql)

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

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

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

 Cross Join: 
 Each row in the first table is paired with all the rows in the second table. 
if OBJECT_ID('tempdb..#a') is not null 
	drop table #a 

if OBJECT_ID('tempdb..#b') is not null 
	drop table #b 

create table #a 
	acol varchar(10) 
insert into #a values 

create table #b 
	bcol varchar(10) 
insert into #b values 

select * from #a 
select * from #b 

select * from #a cross join #b 

Read Full Post »

Command like Sql can be used to run sql scripts saved in disks. Therefroe if there is a bunch of scripts

(files) they can all be added with ttheir path and file name in notepad and have it saved with anyname with a

cmd extension. Then running this file would execute all the lines in the file.

Use “sqlcmd” instead of osql because “osql” cannot handle alot of special characters like the “£” sign.

Here is an example:
sqlcmd -S MyServerName -E -d My_dbName -i “C:\MyTSQL\MyScript.sql”

The above if run in commandline will execute the script in the path.

This is usefull when we need to run batches of scripts.

To make scripts upload/commit to a server by using the above command make sure the SP/Function or other

objects are alwasy dropped and recreated e.i. starts with the create statement as below:

--For SP:
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mySP]') AND type in (N'P', N'PC'))


--For Function:
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myUDF]') AND type in (N'FN', N'IF', 

N'TF', N'FS', N'FT'))

create FUNCTION [dbo].[myUDF]
	@var AS VARCHAR(100)

Read Full Post »

Older Posts »