Results 1 to 6 of 6

Thread: Need query help

  1. #1
    Join Date
    Oct 2010
    Posts
    4

    Unanswered: Need query help

    Hi,

    I need some help to write a query. Consider a table "CardHistory"

    Create Table CardHistory
    (
    SerialNo INT IDENTITY(1,1),
    ModificationDatetime DATETIME,
    OldCardNo INT,
    NewCardNo INT
    )

    Some times "OldCardNo" and "NewCardNo" can be same.

    I need to find out very first CardNo, most recent (last) CardNo and ModificationsCount.

    Example:
    CardNo 100 changed to 101
    CardNo 101 changed to 102
    CardNo 102 changed to 103
    CardNo 103 changed to 104

    then query should return 100, 104, 4

    Please help as soon as possible.

    Thanks in advance ...

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT Min(OldCardNo), Max(NewCardNo), Count(*)
       FROM CardHistory
    Will do exactly what you've requested.

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

  3. #3
    Join Date
    Oct 2010
    Posts
    4

    Re:

    No, its not what I expected.

    This was just an example. There are multiple set of such data.
    Example:
    CardNo 100 changed to 101
    CardNo 101 changed to 102
    CardNo 102 changed to 103
    CardNo 103 changed to 104
    CardNo 200 changed to 201
    CardNo 201 changed to 202
    CardNo 202 changed to 203
    CardNo 203 changed to 204
    CardNo 1200 changed to 1201
    CardNo 1201 changed to 1202
    CardNo 1202 changed to 1203
    CardNo 1203 changed to 1204
    CardNo 1204 changed to 1206

    It should result:
    100, 104, 4
    200, 204, 4
    1200, 1206, 5

    Anyway, thanks for your reply ...

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Why do you get three rows of output? Based on your description with that sample data you ought to still get one row of output, with 100, 1206, and 13. Maybe your description is missing something.

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

  5. #5
    Join Date
    Oct 2010
    Posts
    4

    Re:

    We need to find out oldest and newest cards with modification count for each modified card.

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This will work.
    Code:
    WITH RPL (CardNoStarter, OldCardNo, NewCardNo, iter) AS
    (
    -- Insert all records from #CardHistory where OldCardNo is
    -- not preceded by another NewCardNo (i.e. it is the first 
    -- record of 1 or more consecutive cards).
    
    SELECT CH.OldCardNo, CH.OldCardNo, CH.NewCardNo, 1
    FROM #CardHistory CH
    WHERE NOT EXISTS (SELECT 1
                    FROM #CardHistory CH2
                    WHERE CH.OldCardNo = CH2.NewCardNo
                    )
    
    UNION ALL
    
    -- Create a new record with the consecutive OldCardNo-NewCardNo 
    -- combination for each card, until there is no consecutive 
    -- OldCardNo-NewCardNo combination to be found. 
    -- Iter keeps track on how many iterations were necessary.
    SELECT RPL.CardNoStarter, CH.OldCardNo, CH.NewCardNo, RPL.iter+1
    FROM RPL
    	INNER JOIN #CardHistory as CH ON
    		RPL.NewCardNo = CH.OldCardNo
    )
    -- Get those records per CardRangeStarter with the 
    -- highest iter
    
    SELECT CardNoStarter, max(NewCardNo), max(iter)
    FROM RPL
    GROUP BY CardNoStarter
    ORDER BY CardNoStarter
    ;
    I am not pleased with the two max() at the final SELECT. I'm thinking of adding a ROWCOUNT to RPL ODER BY Iter DESC, but it's far too late now for me to think clearly. Perhaps someone else is willing to give it a shot?
    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
  •