# Thread: Difference between dates in one column with grouping

1. Registered User
Join Date
Nov 2009
Posts
8

## Unanswered: Difference between dates in one column with grouping

Hello
It's my first post, so hi everybody
I have the following situation: in table I have five columns: 'name', 'id1', 'id2', 'dates', 'diff'. The records are grouping within 'name' colum. It looks:
Code:
```Name   Id1   Id2          Date         Diff
A       1      1       2009-11-06
A       5      1       2009-11-24
B       1      1       2008-03-15
B       2      3       2008-03-18
B       5      3       2008-03-24
C       2      5       2008-06-12
C       3      5       2008-06-15```
I'd like to obtain difference beetwen dates (in only days) in two records within one group (column 'name'). This table should look:

Code:
```Name   Id1   Id2          Date         Diff
A       1      1       2009-11-06     6
A       5      1       2009-11-24     18 (24-6)
B       1      1       2008-03-15     15
B       2      3       2008-03-18     3
B       5      3       2008-03-24     6
C       2      5       2008-06-12     12
C       3      5       2008-06-15     3```
I have no idea, how I should solve this problem.
Thanks awfully for help.
Regards

2. Registered User
Join Date
Nov 2004
Posts
1,428
Can you give the logic behind each and every Diff, like you did for A 18 (24 - 6)? I have no idea where the 6 after 2009-11-06 comes from. Same with most of the Diff's of the B and C groups.

1. What is the Diff when you have two records in a group? Which record gets what value?
2. What is the Diff when you have more than two records in a group? Which record gets what value?
3. What is the Diff of the "first" record in a group (assuming they are ordered by date).
4. What is the Diff if more dates in a group are equal?
Code:
```Name   Id1   Id2          Date         Diff
A       1      1       2009-11-06     6
A       5      1       2009-11-24     18 (24-6)
B       1      1       2008-03-15     15
B       2      3       2008-03-18     3
B       5      3       2008-03-24     6
C       2      5       2008-06-12     12
C       3      5       2008-06-15     3```

3. Registered User
Join Date
Aug 2009
Posts
262
explain the DIFF column . how the value for each row is calculated .
(24-6) = 18 correct ... but what is a measure of 24
what is 24

what are these values in 24 .

tell me this and i will give you your procedure .

4. Registered User
Join Date
Nov 2004
Posts
1,428
Originally Posted by mishaalsy
explain the DIFF column . how the value for each row is calculated .
(24-6) = 18 correct ... but what is a measure of 24
what is 24

what are these values in 24 .

tell me this and i will give you your procedure .
Based on his question
I'd like to obtain difference beetwen dates (in only days) in two records within one group (column 'name').
I guess the "24" comes from day part in the date column, and "6" from date column of the other record in the A group. Is that enough for you to make a procedure?

5. Registered User
Join Date
Aug 2009
Posts
262
Name Id1 Id2 Date Diff
A 1 1 2009-11-06 6
A 5 1 2009-11-24 18 (24-6)
B 1 1 2008-03-15 15
B 2 3 2008-03-18 3
B 5 3 2008-03-24 6
C 2 5 2008-06-12 12
C 3 5 2008-06-15 3

group a.
A 1 1 2009-11-06 6
A 5 1 2009-11-24 18 (24-6)

group b
B 1 1 2008-03-15 15
B 2 3 2008-03-18 3 (24-15 =9 ? then why 3
B 5 3 2008-03-24 6 (24-15-3 = 6 . correct)

group c
C 2 5 2008-06-12 12
C 3 5 2008-06-15 3 (24-12 =3 ? )

only if i know the calculation upon which the difference should be driven out, i will be able to post my existing with slight modification

6. Registered User
Join Date
May 2009
Posts
509
I think I 'decoded' the logic of what the OP is asking for:
Code:
```Name Id1 Id2          Date     Diff
A     1    1       2009-11-06   6         (The DAY if no previous row in group (I would think it should be a 0))
A     5    1       2009-11-24  18 (24- 6) (The current row DAY - previous row DAY in group)

B     1    1       2008-03-15  15         (the DAY if no previous row in group)
B     2    3       2008-03-18   3 (18-15) (the current row DAY - previous row DAY in group)
B     5    3       2008-03-24   6 (24-18) (the current row DAY - previous row DAY in group)

C     2    5       2008-06-12  12         (the DAY if no previous row in group)
C     3    5       2008-06-15   3 (15-12) (the current row DAY - previous row DAY in group)```

7. Registered User
Join Date
Nov 2009
Posts
8
I'm sorry for not much good explanation my problem. Yes, Stealth_DBA it just, what I want to obtain. You wrote: "I would think it should be a 0", but I have to get days, which pass. I put Stealth_DBA explanation:

Code:
```Name Id1 Id2          Date     Diff
A     1    1       2009-11-06   6         (The DAY if no previous row in group )
A     5    1       2009-11-24  18 (24- 6) (The current row DAY - previous row DAY in group)

B     1    1       2008-03-15  15         (the DAY if no previous row in group)
B     2    3       2008-03-18   3 (18-15) (the current row DAY - previous row DAY in group)
B     5    3       2008-03-24   6 (24-18) (the current row DAY - previous row DAY in group)

C     2    5       2008-06-12  12         (the DAY if no previous row in group)
C     3    5       2008-06-15   3 (15-12) (the current row DAY - previous row DAY in group)```
Thanks awfully for help.
Regards

8. Registered User
Join Date
Aug 2009
Posts
262
now kindly give me DDL of the table and 2 sample insert statement .

i will have your procedure ready in an hour as soon as i have it .

9. Registered User
Join Date
Nov 2004
Posts
1,428
Code:
```CREATE table DaTable(
Name   CHAR(1)  NOT NULL,
Id1   INT  NOT NULL,
Id2   INT  NOT NULL,
)

INSERT INTO DaTable (Name, Id1, Id2, aDate)
SELECT 'A', 1, 1, '2009.11.06'
UNION ALL
SELECT 'A', 5, 1, '2009.11.24'
UNION ALL
SELECT 'B', 1, 1, '2008.03.15'
UNION ALL
SELECT 'B', 2, 3, '2008.03.18'
UNION ALL
SELECT 'B', 5, 3, '2008.03.24'
UNION ALL
SELECT 'C', 2, 5, '2008.06.12'
UNION ALL
SELECT 'C', 3, 5, '2008.06.15'

select * from DaTable ORDER BY Name, aDate```
now kindly give me DDL of the table and 2 sample insert statement .

i will have your procedure ready in an hour as soon as i have it .
The clock is ticking

10. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
A brief read of this suggests there is a nice solution or two in 2005 and 2008, and a nasty one in 2000. OP - please can you tell us the version of SQL Server?

11. Registered User
Join Date
Nov 2009
Posts
8
I'm sorry that I haven't written reply earlier, but I haven't had access to computer. Thanks Wim, your code is very good. For pootle flump question, I use SQL Server 2005.

Thanks awfully for help.
Regards

12. Registered User
Join Date
Nov 2004
Posts
1,428
This is what I came up with. I'm curious about other people's solutions.
Code:
```WITH T as
(SELECT Name,
Id1,
Id2,
ROW_NUMBER() OVER (PARTITION BY Name ORDER BY aDate) as RowNum
FROM DaTable
)
SELECT Name,
Id1,
Id2,
FROM T
WHERE RowNum = 1
UNION ALL
SELECT TA.Name,
TA.Id1,
TA.Id2,
FROM T as TB
INNER JOIN T as TA ON
TB.Name = TA.Name AND
TB.RowNum = TA.RowNum - 1
WHERE TA.RowNum > 1
This value of Dif only works if you have another Name per month. Is it just a coincidence in the examples given?
If a Name can span different months you will have to specify the business rule that applies in those cases.

13. Registered User
Join Date
Nov 2009
Posts
8
Thanks for your solution. It's very good. I have another Name per months - it isn't problem.

Thanks awfully for help.
Regards

14. Registered User
Join Date
Nov 2004
Posts
1,428
Originally Posted by pootle flump
A brief read of this suggests there is a nice solution or two in 2005 and 2008, and a nasty one in 2000. OP - please can you tell us the version of SQL Server?
You care to share your solutions?

15. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Originally Posted by Wim
You care to share your solutions?
Well - they are half formed, and one used a CTE I'll have a stab at lunch see if what I have in mind works.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•