Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    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. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    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. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by mishaalsy View Post
    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?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    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 ? )


    it is enough for me to re-read grade 1 maths

    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. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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. #7
    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. #8
    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. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Thumbs up

    Code:
    CREATE table DaTable(
    Name   CHAR(1)  NOT NULL,
    Id1   INT  NOT NULL,
    Id2   INT  NOT NULL,
    aDate DATETIME 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
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  10. #10
    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?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    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. #12
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This is what I came up with. I'm curious about other people's solutions.
    Code:
    WITH T as
    (SELECT Name, 
    	Id1, 
    	Id2, 
    	aDate,
    	ROW_NUMBER() OVER (PARTITION BY Name ORDER BY aDate) as RowNum
    FROM DaTable
    )
    SELECT Name, 
    	Id1, 
    	Id2, 
    	aDate,
    	day(aDate) as Dif
    FROM T
    WHERE RowNum = 1
    	UNION ALL
    SELECT TA.Name, 
    	TA.Id1, 
    	TA.Id2, 
    	TA.aDate,
    	Datediff(d, TB.aDate, TA.aDate) as Dif
    FROM T as TB
    	INNER JOIN T as TA ON
    		TB.Name = TA.Name AND
    		TB.RowNum = TA.RowNum - 1
    WHERE TA.RowNum > 1
    ORDER BY Name, aDate
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  13. #13
    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. #14
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by pootle flump View Post
    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?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Wim View Post
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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