Results 1 to 10 of 10

Thread: Find and print

  1. #1
    Join Date
    Mar 2012
    Posts
    37

    Unanswered: Find and print

    Hi All
    I am stuck with the problem and want your help, What I want
    to do is I have a table checks which looks like-:

    Items--Waitarea--Datecreated
    1-------A1-------2011-10-12
    1-------A1-------2011-10-13
    1-------A1-------2011-10-14
    1-------A1-------2011-10-15
    1-------A2-------2011-10-16
    1-------A2-------2011-10-18
    2-------A2-------2011-10-19
    2-------A2-------2011-11-17
    2-------A2-------2011-12-17
    3-------A2-------2012-01-17
    3-------A2-------2012-01-18
    3-------A3-------2012-01-19
    3-------A1-------2012-01-20
    4-------A2-------2012-01-21
    4-------A2-------2012-01-22
    4-------A2-------2012-01-31

    What i need to find is items and datediff from current date
    to created date with respect to wait area. You will get more clear with example

    For Eg. For items 1 the wait area changes from A1 to A2 at
    2011-10-16 and remains A2 so i need to find the date differeence from
    today date to 2011-10-16

    For item 2 that wait area remains same for entire period that is
    A2 so datediff from today date to 2011-10-19(last created date of that item)

    For item 3 that wait area A2 then it becomes A3 then becomes A1
    so need datediff from today to 2012-01-20

    For item 4 that wait area A2 remains for the entire period
    so need datediff from today-2012-01-21

    The expected output wanted
    items---daycount
    1-----datediff(day,checks.datecreated,getdate()--here checks.datecreated will be 2011-10-16
    2-----datediff(day,checks.datecreated,getdate()--here checks.datecreated will be 2011-10-19
    3-----datediff(day,checks.datecreated,getdate()--here checks.datecreated will be 2012-01-20
    4-----datediff(day,checks.datecreated,getdate()--here checks.datecreated will be 2012-01-21
    Last edited by goodman2253; 04-04-12 at 15:33.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Don't you want 2012-01-20 for item 3? That was its most recent modification date.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2012
    Posts
    37
    yes
    Sorry for the mistake

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    select 1 as Items, 'A1' as WaitArea, '2011-10-12' as DateCreated
    into #TestData
    union select 1, 'A1', '2011-10-13'
    union select 1, 'A1', '2011-10-14'
    union select 1, 'A1', '2011-10-15'
    union select 1, 'A2', '2011-10-16' --Target
    union select 1, 'A2', '2011-10-18'
    union select 2, 'A2', '2011-10-19' --Target
    union select 2, 'A2', '2011-11-17'
    union select 2, 'A2', '2011-12-17'
    union select 3, 'A2', '2012-01-17'
    union select 3, 'A2', '2012-01-18'
    union select 3, 'A3', '2012-01-19' --Target?
    union select 3, 'A1', '2012-01-20'
    union select 4, 'A2', '2012-01-21' --Target
    union select 4, 'A2', '2012-01-22'
    union select 4, 'A2', '2012-01-31'
    
    ;
    with OrderedData as
    		(select	Items,
    				WaitArea,
    				DateCreated,
    				ROW_NUMBER() over (Partition by Items order by DateCreated asc) as RowNum
    		from	#TestData),
    	DeltaRecords as
    		(select	CurrentRecords.Items,
    				CurrentRecords.WaitArea,
    				CurrentRecords.DateCreated,
    				ROW_NUMBER() over (Partition by CurrentRecords.Items order by CurrentRecords.DateCreated desc) as RowNum2
    		from	OrderedData as CurrentRecords
    				left outer join OrderedData as PriorRecords
    					on CurrentRecords.Items = PriorRecords.Items
    					and CurrentRecords.RowNum = PriorRecords.RowNum + 1
    		where	CurrentRecords.WaitArea <> PriorRecords.WaitArea
    				or PriorRecords.RowNum is null)
    select	*
    from	DeltaRecords
    where	RowNum2 = 1
    
    drop table #TestData
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    NZDF!
    Code:
    CREATE TABLE #foo (
       item         INT         NOT NULL
    ,  waitarea     CHAR(2)     NOT NULL
    ,  DateCreated  DATETIME    NOT NULL
    )
    
    INSERT INTO #foo (item, waitarea, DateCreated)
       VALUES (1, 'A1', '2011-10-12'), (1, 'A1', '2011-10-13')
    ,     (1, 'A1', '2011-10-14'),     (1, 'A1', '2011-10-15')
    ,     (1, 'A2', '2011-10-16'),     (1, 'A2', '2011-10-18')
    ,     (2, 'A2', '2011-10-19'),     (2, 'A2', '2011-11-17')
    ,     (2, 'A2', '2011-12-17'),     (3, 'A2', '2012-01-17')
    ,     (3, 'A2', '2012-01-18'),     (3, 'A3', '2012-01-19')
    ,     (3, 'A1', '2012-01-20'),     (4, 'A2', '2012-01-21')
    ,     (4, 'A2', '2012-01-22'),     (4, 'A2', '2012-01-31')
    
    SELECT this.item
    ,  DateDiff(day, Max(CASE 
          WHEN prev.waitarea IS NULL THEN this.DateCreated
          WHEN this.waitarea <> prev.waitarea THEN this.DateCreated
       END), GETDATE()) AS days
       FROM #foo AS this
       LEFT JOIN #foo AS prev
          ON (prev.item = this.item
          AND prev.DateCreated = (SELECT MAX(z1.DateCreated)
             FROM #foo AS z1
             WHERE  z1.item = this.item
                AND z1.DateCreated < this.DateCreated))
        GROUP BY this.item
    
    DROP TABLE #foo
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Me loves them CTEs.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I like CTEs too, but in this case the CTE seems to be guilding the lilly to me.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It neatly lays out the logic of the query into steps, plus it has the benefit of not requiring a correlated sub-query in the join clause!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by Pat Phelan View Post
    NZDF!
    ...
    NZDF - New Zealand Defence Force??
    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
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Exactly the same reasons why I like CTE's too.
    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

Posting Permissions

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