Calculate Difference From Previous Row with NULLs (Fill Null Gap)

You will find two options to write queries to get the result. The Outer Apply method looks very compact but it is not performing well. Instead the LEFT JOIN way is a lot quicker.

Create Table MyTable (ID int, RecordDtTm Datetime, TotalScore int, DeltaScore int)
--Sample data
insert into MyTable (ID, RecordDtTm, TotalScore, DeltaScore)
Values 
(2, '2012-01-01 15:15', 15, NULL),
(2, '2012-01-01 15:25', NULL, NULL),
(2, '2012-01-01 15:55', NULL, NULL),
(2, '2012-01-01 16:10', 12, NULL),
(2, '2012-01-01 16:30', 12, NULL),
(2, '2012-01-01 16:45', 9, NULL),
(2, '2012-01-01 16:55', NULL, NULL),
(2, '2012-01-01 17:12', 10, NULL)

--Expected result
/*
ID RecordDtTm TotalScore DeltaScore
2 2012-01-01 15:15 15 NULL
2 2012-01-01 15:25 NULL 0
2 2012-01-01 15:55 NULL 0
2 2012-01-01 16:10 12 -3
2 2012-01-01 16:30 12 0
2 2012-01-01 16:45 09 -3
2 2012-01-01 16:55 NULL 0
2 2012-01-01 17:12 10 1
*/

--Solution 1
;With mycte as
(select *, row_number() over(order by RecordDtTm asc) rn from MyTable where TotalScore is not null)

Select m.Id, m.RecordDtTm, m.TotalScore, (m1.TotalScore-m2.TotalScore) as DeltaScore
From MyTable m
Left join mycte m1 on m.id=m1.id And m.RecordDtTm=m1.RecordDtTm
Left join mycte m2 on m1.rn=m2.rn+1
Order by m.Id, m.RecordDtTm

--Solution2 Noami’s
select T.Id, T.RecordDtTm, T.TotalScore,
case when T.TotalScore IS NULL and T1.TotalScore IS NULL then 0 else T.TotalScore - T1.TotalScore END as DeltaScore
FROM myTable T
OUTER APPLY (select top (1) TotalScore from myTable T1 where T1.Id = T.Id
and T1.RecordDtTm < T.RecordDtTm and T1.TotalScore IS NOT NULL
ORDER BY RecordDtTm DESC) T1


drop table myTable
--Thread at MSDN:

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/4b8efff4-bb98-43da-92fb-13c05d83bc61

T-SQL Sample with Sequence 1,2,3,6,12,24,36…

;WITH mycte AS
(
SELECT 1 as i
UNION ALL
SELECT Case WHen i>=3 Then m.i*2 Else m.i+1 End as i
FROM mycte m
WHERE i<36
)

SELECT
CAST(i AS VARCHAR(2)) +
CASE WHEN i = 1 THEN ' Month' ELSE ' Months' END AS NumPreviousMonth
,CAST(DATEADD(month, -i, Current_Timestamp) AS DATE) PreviousDate
FROM mycte

Create Fifteen Minutes Intervals For Events (T-SQL)

create table tmp2 (Activity varchar(50),	StartTime time(0),	EndTime time(0))
insert into tmp2 values ('Act. A',	'8:00',	'8:15'),
('Act. B',	'8:15',	'8:45'),
('Act. C',	'8:45',	'9:45'),
('Act. D',	'9:45',	'12:00')

;WITH mycte AS
(
SELECT Activity,Starttime,DATEADD(MINUTE,15,Starttime) EndTime
FROM tmp2

UNION ALL
SELECT m.Activity,m.EndTime Starttime,DATEADD(MINUTE,15,m.EndTime) EndTime
FROM mycte m
INNER JOIN tmp2 t2 ON m.Activity = t2.Activity
WHERE t2.EndTime > m.Starttime

)

SELECT m.Activity,m.Starttime,m.EndTime
FROM mycte m
INNER JOIN tmp2 t2
ON m.Activity = t2.Activity AND m.Starttime < t2.EndTime
ORDER BY m.Activity,m.Starttime

drop table tmp2

Rotate Matrix with T-SQL

--Rotate with a key
CREATE TABLE #Test(id int identity(1,1), col1 INT,col2 INT,col3 INT)
INSERT INTO #Test VALUES (1,2,3),(4,5,6),(7,8,9)

select * from #test
;with mycte
as
(
SELECT id,col,val
FROM (SELECT id,col1,col2,col3
FROM #Test) AS src1
UNPIVOT (val FOR col
IN ( [col1], [col2], [col3])) AS unpvt
)

SELECT [1],[2],[3]
FROM (SELECT id,col,val
FROM mycte) AS src2 PIVOT
( Max(val) FOR id IN ([1], [2], [3], [4])) AS pvt
drop table #Test

Sum Time Data Type Data with T-SQL

A sample for sum up minutes:

SELECT CAST(CAST(SUM(DATEDIFF(MINUTE, '0:00:00', DelayTime)) /60 AS VARCHAR(2))
            + ':'
            + CAST(SUM(DATEDIFF(MINUTE, '0:00:00', DelayTime)) %60 AS VARCHAR(2))
            + ':00' AS TIME(0)) as Delaytime, DATENAME(month,[date]) as CurrentMonth,Ename As Name
FROM   [dbo].[TbPassTime]
GROUP  BY DATENAME(month,[date]),Ename
--http://social.msdn.microsoft.com/Forums/en/transactsql/thread/2cd8d545-1c4f-4121-bd7b-50f598d18680

Data Collector (Management Data Warehouse) SQL Server 2008 –Resource Links

I started to use Data Collector for my SQL Instances recently. I came across to Sudarshan Narasimhan’s theSQLDude.com site. I found out he shared a lot of useful information you may not find easily in other places. I created this post to keep a link to his helpful tips related to Data Collector and other SQL Server issues.

Data Collector (Management Data Warehouse) – Issues, Tips and Solutions

Kalen Delaney has a good introduction paper for this:
http://msdn.microsoft.com/en-us/library/dd939169(v=sql.100).aspx

PIVOT with SSIS Package

I have written many queries with PIVOT in T-SQL. I may still write up T-SQL query to do the pivot even if I want to design an SSIS package. However, you can use PIVOT tool within BIDS to design a PIVOT package. Reza Rad has a detailed post for how to do it step by step to design a PIVOT SSIS package. Here is the link if you are interested:
http://www.rad.pasfu.com/index.php?/archives/14-PIVOT-Transformation-SSIS-Complete-Tutorial.html

Split String with Multiple Delimiters (T-SQL XML Method)

http://forums.asp.net/p/1852168/5184467.aspx/1?p=True&t=634861565202699866

--declare @Questions varchar(100)='1-32|26,42-41|32'
declare @Questions varchar(100)='1-32-26,42-41-32,1-46-1'

DECLARE @myXML AS XML  = N'<H><r>' + Replace(@Questions, ',', '</r><r>') + '</r></H>'

;WITH cte

AS (
SELECT CAST(N'<H><r>' + replace(Replace(Vals.id.value('.', 'NVARCHAR(50)'),'-','|'), '|', '</r><r>') + '</r></H>' as XML) AS val

FROM @myXML.nodes('/H/r') AS Vals(id) )

SELECT DISTINCT S.a.value('(/H/r)[1]', 'NVARCHAR(50)') AS QuestionId,
S.a.value('(/H/r)[2]', 'NVARCHAR(50)') AS [IndexNumber],
S.a.value('(/H/r)[3]', 'NVARCHAR(50)') AS Numbers
FROM cte CROSS APPLY val.nodes('/H/r') S(a);

Another sample:

declare @test table(id int identity(1,1), myString varchar(35) );
insert into @test values('\string1\string2\file0123.xml.xxx');

SELECT distinct  S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS splitVal
FROM
(
SELECT *,CAST (N'<H><r>' + Replace(REPLACE(myString, '\', '</r><r>'),'.','</r><r>') + '</r></H>' AS XML) AS [vals]
FROM @test) d

CROSS APPLY d.[vals].nodes('/H/r') S(a)

---Or

declare @myString varchar(100)='\string1\string2\file0123.xml.xxx'

;WITH cte
AS (
SELECT CAST(N'<H><r>' + Replace(REPLACE(@myString, '\', '</r><r>'),'.','</r><r>') + '</r></H>' AS XML) AS val
 )

SELECT DISTINCT S.a.value('(/H/r)[4]', 'NVARCHAR(50)') AS val
FROM cte CROSS APPLY val.nodes('/H/r') S(a);

One more example:

create table test(id int,resp varchar(100))
insert into test(Id,resp)
SELECT 1,'1^123,2^321,3^234,4^321' UNION
SELECT 2,'1^321,2^123,3^345,4^543'
--select * from test

 ;with mycte as
 ( SELECT  id,  S.a.value('.', 'VARCHAR(100)') AS splitVal1 FROM( SELECT *,CAST (N'<H><r>' + Replace( resp, ',','</r><r>') + '</r></H>' AS XML) AS [vals]
 FROM test) d   CROSS APPLY d.[vals].nodes('/H/r') S(a)   
 )
 Select DISTINCT id, S.a.value('(/H/r)[1]', 'VARCHAR(100)') qn,  S.a.value('(/H/r)[2]', 'VARCHAR(100)') ans  
 FROM (Select *, CAST (N'<H><r>' + Replace( splitVal1, '^','</r><r>') + '</r></H>' AS XML) [vals] from mycte) d   
 CROSS APPLY d.[vals].nodes('/H/r') S(a)   
drop table test
/*
id	qn	ans
1	1	123
1	2	321
1	3	234
1	4	321
2	1	321
2	2	123
2	3	345
2	4	543
*/

Save Data to Archive Table When Delete From Table With T-SQL OUTPUT INTO Clause

CREATE PROCEDURE [dbo].[sp_signup_DeletedRecord]
(
@UserName NVARCHAR(50)
)
AS

Begin
DELETE FROM signup
--Change col1, col2,col3… to column names from your signuptable
OUTPUT deleted.col1,
deleted.col2,
deleted.col3
INTO DeletedRecord
WHERE UserName = @UserName
End

Reference:
http://msdn.microsoft.com/en-us/library/ms177564(v=sql.90).aspx