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

drop table myTable
--Thread at MSDN:

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

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

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

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

Don’t Use Rand() function in CASE Expressions

When you use Rand function in CASE expression, you may have unexpected results. Here is a discussion for this issue:

I have included whatI have found from Sybase documentation:;pt=39091

My input for the thread:
The return value NULL is a design behavior when you use Rand() function in CASE Expression (it seems it is inherited from Sybase product and my guess is a standard compliance behavior). You will get some unexpected results, in your case it is the NULL value.

You can use a variable to pass the value to use in the CASE Expression:

Declare @num int=CAST((50  * RAND() + 1) as int) 
when 1 then 'New York'
when 2 then 'Los Angeles'

Sum Time Data Type Data with T-SQL

A sample for sum up minutes:

            + ':'
            + 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

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

Kalen Delaney has a good introduction paper for this:

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:

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

--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('.', '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\');

SELECT distinct  S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS splitVal
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)


declare @myString varchar(100)='\string1\string2\'

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

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