Compare a date to a previous group of records' max date

Question

Post reply

Compare a date to a previous group of records' max date

tim.oliver

Grasshopper

Points: 16
More actions
September 11, 2013 at 5:15 am

#277114

I want to reference a previous group's max date and compare it to the date on the current record.
My data looks like this:-
Grp - Date
00 - 28-10-2012
10 - 29-10-2012
10 - 28-10-2012
10 - 28-10-2012
20 - 30-10-2012
20 - 05-11-2012
20 - 10-11-2012
20 - 30-10-2012
30 - 01-11-2012
So we can that for Grp 10, the max date is 29-10-2012, for Grp 20, the max date is 10-11-2012, etc...
I want to reference the previous group's max date and compare it to the date on the current record.
For example, for Grp 20, dated 05-11-2012, it needs to be compared to the max date for Grp 10 (29-10-2012) to calculate the difference in dates.
Grp - Date - Previous Date - Delay
00 - 28-10-2012 -
10 - 29-10-2012 - 28-10-2012 - 1
10 - 28-10-2012 - 28-10-2012 - 0
10 - 28-10-2012 - 28-10-2012 - 0
20 - 30-10-2012 - 29-10-2012 - 1
20 - 05-11-2012 - 29-10-2012 - 7
20 - 10-11-2012 - 29-10-2012 - 12
20 - 30-10-2012 - 29-10-2012 - 1
30 - 12-11-2012 - 10-11-2012 - 2
Is this possible in TSQL or do i need to write Package/Function?
Any guidance would be appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

kapil_kk SSC-Insane Points: 21316 More actions · Answer 1

Your requirement is not clear to me...

On what basis you are doing rhis thing-

for Grp 20, dated 05-11-2012, it needs to be compared to the max date for Grp 10 (29-10-2012) to calculate the difference in dates.

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 2

SET DATEFORMAT DMY;

DECLARE @t TABLE(Grp CHAR(2), dt Date);

INSERT INTO @t(Grp,dt)

VALUES

('00' , '28-10-2012'),

('10' , '29-10-2012'),

('10' , '28-10-2012'),

('20' , '30-10-2012'),

('20' , '05-11-2012'),

('20' , '10-11-2012'),

('20' , '30-10-2012'),

('30' , '12-11-2012');

SELECT t.Grp,

t.dt AS [Date],

ca.PreviousDate,

DATEDIFF(Day,ca.PreviousDate,t.dt) AS Delay

FROM @t t

OUTER APPLY (SELECT TOP 1 t2.dt

FROM @t t2

WHERE t2.Grp < t.Grp

ORDER BY t2.Grp DESC,t2.dt DESC) ca(PreviousDate)

ORDER BY 1,2;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

good one Mark 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

tim.oliver Grasshopper Points: 16 More actions · Answer 4

Yay - thats great Mark! many Thanks, and apologies for not following the right way to set up the questionin the first place - I will definately try and follow your guidance in the future.

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 5

tim.oliver (9/11/2013)
Yay - thats great Mark! many Thanks, and apologies for not following the right way to set up the questionin the first place - I will definately try and follow your guidance in the future.

You're welcome!

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537