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: