Date computation between rows, cumulative total

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date computation between rows, cumulative total

Author

Topic

Rosembm1
Starting Member

15 Posts

Posted - 2010-02-03 : 12:30:41

Dear Gurus:
I wish my SQL skills were up to the following challenge! Thanks for any help I can get!

Two goals:
1- Compute a cumulative sum for mydate2 by each ID and its date1
For example, ID 6 would be recounted if date1 changes
2- Count every time the cumulative sum is a multiple of 14

Create table #mytable
(
ID int
, date1 datetime
, Mydate2 datetime
)

INSERT INTO #mytable VALUES(1, '11/14/2008', '11/19/2008' )
INSERT INTO #mytable VALUES(2, '12/11/2008', '12/14/2008' )
INSERT INTO #mytable VALUES(2, '12/11/2008', '12/18/2008' )
INSERT INTO #mytable VALUES(3, '1/27/2008', '2/1/2008' )
INSERT INTO #mytable VALUES(4, '8/2/2008', '10/13/2008' )
INSERT INTO #mytable VALUES(5, '6/30/2008', '7/5/2008' )
INSERT INTO #mytable VALUES(5, '6/30/2008', '7/10/2008' )

INSERT INTO #mytable VALUES(6, '11/2/2008', '11/16/2008' )
INSERT INTO #mytable VALUES(6, '11/2/2008', '11/23/2008' )
INSERT INTO #mytable VALUES(6, '11/2/2008', '11/27/2008' )
INSERT INTO #mytable VALUES(6, '11/2/2008', '11/16/2008' )
INSERT INTO #mytable VALUES(6, '11/2/2008', '12/3/2008' )

INSERT INTO #mytable VALUES(6, '12/10/2008', '12/15/2008' )
INSERT INTO #mytable VALUES(6, '12/10/2008', '12/22/2008' )
INSERT INTO #mytable VALUES(6, '12/10/2008', '12/30/2008' )
INSERT INTO #mytable VALUES(6, '12/10/2008', '1/15/2009' )
INSERT INTO #mytable VALUES(6, '12/10/2008', '1/25/2009' )
INSERT INTO #mytable VALUES(6, '12/10/2008', '1/31/2009' )
select * from
#mytable

Result:- see last 2 columns

ID Date1 MyDate2 Compute cumulative date2 countEvery14
1 11/14/2008 11/19/2008 1
2 12/11/2008 12/14/2008 1
2 12/11/2008 12/18/2008 4 1
3 1/27/2008 2/1/2008 1
4 8/2/2008 10/13/2008 1
5 6/30/2008 7/5/2008 1
5 6/30/2008 7/10/2008 5 1

6 11/2/2008 11/16/2008 1
6 11/2/2008 11/23/2008 7 1
6 11/2/2008 11/27/2008 11 1
6 11/2/2008 12/3/2008 17 2
6 12/10/2008 12/15/2008 1
6 12/10/2008 12/22/2008 7 1
6 12/10/2008 12/30/2008 15 2
6 12/10/2008 1/15/2009 31 3
6 12/10/2008 1/25/2009 41 3
6 12/10/2008 1/31/2009 47 3

MWRosenblatt
Spins Yak

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 12:38:27

[code]SELECT ID,
date1,
Mydate2,
COALESCE(DATEDIFF(dd,t1.Mydate2,t.Mydate),1) AS [Compute cumulative date2],
(COALESCE(DATEDIFF(dd,t1.Mydate2,t.Mydate),1)/14) + 1 AS [countEvery14]
FROM table t
OUTER APPLY (SELECT TOP 1 Mydate2
FROM table
WHERE ID=t.ID
AND date1 = t.date1
AND Mydate2 < t.Mydate2
ORDER BY MyDate2 DESC)t1
[/code]

Rosembm1
Starting Member

15 Posts

Posted - 2010-02-04 : 07:04:58

Thanks for the quick response but not quite right!
It's a little more complicated than that.

You have to compare mydate2 from the previous row for each mydate1 then compute its cumulative as per the result example.
I wish there were a lag function for this one -
Make sense?

MWRosenblatt
Spins Yak

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 08:13:23

quote:
Originally posted by Rosembm1

Thanks for the quick response but not quite right!
It's a little more complicated than that.

You have to compare mydate2 from the previous row for each mydate1 then compute its cumulative as per the result example.
I wish there were a lag function for this one -
Make sense?

MWRosenblatt
Spins Yak

I've done the same thing. why do you think its not right?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 08:18:42

ok i think i got you
what about this?

      
 SELECT ID,
date1,
Mydate2,
COALESCE(DATEDIFF(dd,t1.Mydate2,t.Mydate),1) AS [Compute cumulative date2],
(COALESCE(DATEDIFF(dd,t1.Mydate2,t.Mydate),1)/14) + 1 AS [countEvery14]
FROM table t
OUTER APPLY (SELECT TOP 1 Mydate2
             FROM table
             WHERE ID=t.ID
             AND date1 = t.date1
             AND Mydate2 < t.Mydate2
             ORDER BY MyDate2 DESC)t1

Rosembm1
Starting Member

15 Posts

Posted - 2010-02-04 : 09:13:59

Hi-
Just cleaned up the code a wee bit
and it works. Thanks so much! I have no doubt this will be useful for others.

SELECT
ID,
date1,
t1.Mydate2 as Intial_Date,
--t2.Mydate2 as Next_date,
COALESCE(DATEDIFF(dd,t1.Mydate2, t.Mydate2),1) AS [Compute cumulative date2],
(COALESCE(DATEDIFF(dd,t1.mydate2, t.Mydate2),1)/14) + 1 AS [countEvery14]
FROM #mytable t
OUTER APPLY (SELECT TOP 1 Mydate2
FROM #mytable
WHERE ID=t.ID
AND date1 = t.date1
AND Mydate2 < t.Mydate2
ORDER BY MyDate2 )t1

MWRosenblatt
Spins Yak

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 09:14:39

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources