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



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:
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/81a5c057-bc0c-4c56-a9fe-9be11eedb19d

I have included whatI have found from Sybase documentation:
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/39235;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) 
SELECT CASE @num
when 1 then 'New York'
when 2 then 'Los Angeles'
--..
ELSE
'Unknown'
END


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.
http://thesqldude.com/2011/08/03/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