Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: SELECT only changes

    I'm currently looking at an existing interface that is run on a monthly basis; the result of which is sent to a 3rd party client for processing. The information itself is on benefits.

    Now the reason I'm looking at this interface is because the 3rd party have grumbled that we're giving them far too much information - they're only interested in changes.

    Each month we recieve extracts from payroll which we import into a holding table. This table is truncated before every upload. I am planning on changing this so we do not delete previous data - rather we datestamp our imports and keep a history so we can track changes.

    Tables and sample data
    Code:
    /* Employee */
    DECLARE @employee table (
        employee_number char(6) unique
      , name            char(10)
      , ni_number       char(9)
    )
    
    /* Benefit Lookup */
    DECLARE @xbenefit table (
        code        char(6) unique
      , description char(25)
    )
    
    /* Flex */
    DECLARE @flex table (
        employee_number char(6)
      , benefit_code    char(6)
      , benefit_value   money
      , date_loaded     datetime
    )
    
    INSERT INTO @employee (employee_number, name, ni_number)
    SELECT '000001', 'Abby'   , '00A0000AA' UNION
    SELECT '000002', 'Barry'  , '11B1111BB' UNION
    SELECT '000003', 'Chris'  , '22C2222CC' UNION
    SELECT '000004', 'Dorothy', '33D3333DD' UNION
    SELECT '000005', 'Edward' , '44E4444EE' UNION
    SELECT '000006', 'Francis', '55F5555FF'
    
    INSERT INTO @xbenefit (code, description)
    SELECT 'BONUS', 'Annual Bonus'  UNION
    SELECT 'BUPA' , 'BUPA Cover'    UNION
    SELECT 'CAR'  , 'Company Car'   UNION
    SELECT 'CARA' , 'Car Allowance' UNION
    SELECT 'DEN'  , 'Dental Care'
    
    INSERT INTO @flex (employee_number, benefit_code, benefit_value, date_loaded)
    SELECT '000001', 'BONUS', 60.00, '20070101' UNION
    SELECT '000001', 'BUPA' , 10.00, '20070101' UNION
    SELECT '000001', 'BUPA' , 10.00, '20070202' UNION
    SELECT '000001', 'DEN'  ,  5.00, '20070101' UNION
    SELECT '000001', 'DEN'  , 15.00, '20070202' UNION
    SELECT '000002', 'CAR'  , 15.00, '20070202' UNION
    SELECT '000002', 'DEN'  , 15.00, '20070202' UNION
    SELECT '000003', 'DEN'  ,  7.00, '20070101' UNION
    SELECT '000003', 'BONUS', 65.00, '20070101' UNION
    SELECT '000004', 'DEN'  ,  6.00, '20070101' UNION
    SELECT '000004', 'BUPA' , 10.00, '20070202' UNION
    SELECT '000004', 'DEN'  ,  6.00, '20070202' UNION
    SELECT '000005', 'BONUS', 80.00, '20070101'
    So here comes the tricky part - identifying changes. I've come up with 3 rules so far
    1. New Benefits: If there is a current value and no previous values, then select the current value.
    2. Changed Benefit Value: If the current value and previous value are different, select the current value
    3. End of benefit: If there is no current value but there is a previous value, then select zero

    Unfortunately I'm struggling to come up with the SQL needed to select the values that match the above rules.

    If we were run the extract for point in time of 20070202 the required output should look like this
    Code:
    +-----------------+-----------+--------------+---------------+
    | employee_number | ni_number | description  | benefit_value |
    +-----------------+-----------+--------------+---------------+
    | 000001          | 00A0000AA | Annual Bonus | 0.00          |
    | 000001          | 00A0000AA | Dental Care  | 15.00         |
    | 000002          | 00B0000BB | Company Car  | 15.00         |
    | 000002          | 00B0000BB | Dental Care  | 15.00         |
    | 000003          | 00C0000CC | Dental Care  | 0.00          |
    | 000003          | 00C0000CC | Annual Bonus | 0.00          |
    | 000004          | 00D0000DD | BUPA Cover   | 0.00          |
    | 000005          | 00E0000EE | Anual Bonus  | 0.00          |
    +-----------------+-----------+--------------+---------------+
    Any help you can offer is greatly appreciated

    If something's not clear - please let me know!

    Many Thanks,
    George
    George
    Home | Blog

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Very knacky but I've got new benefits and changed benefit values...
    Code:
    DECLARE @topDate datetime
    SET @topDate = (SELECT Max(date_loaded) FROM @flex)
    
    --New Benefits
    SELECT *
    FROM   @flex
    WHERE  date_loaded = @topDate
    AND    employee_number + benefit_code IN (
              SELECT employee_number + benefit_code
              FROM   @flex
              GROUP
                  BY employee_number, benefit_code
              HAVING Count(*) = 1
              )
    
    --Changed benefit value
    SELECT f1.*
    FROM   @flex f1
     INNER
      JOIN @flex f2
        ON f2.employee_number = f1.employee_number
       AND f2.benefit_code = f1.benefit_code
       AND f2.benefit_value <> f1.benefit_value
       AND f1.date_loaded > f2.date_loaded
    WHERE  f1.date_loaded = @topDate
    In need of vast improvement though!
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select curr.employee_number 
         , e.ni_number 
         , b.description
         , curr.benefit_value 
      from @flex as curr
    inner
      join @employee as e
        on e.employee_number = curr.employee_number
    inner
      join @xbenefit as b
        on b.code = curr.benefit_code
    left outer
      join @flex as prev
        on prev.employee_number = curr.employee_number
        on prev.date_loaded     < curr.date_loaded
     where curr.date_loaded = ( select max(date_loaded) from @flex )
       and prev.employee_number is null
        or prev.benefit_value <> curr.benefit_value
    union all
    select allprev.employee_number 
         , e.ni_number 
         , b.description
         , 0
      from ( select employee_number
                  , benefit_code
               from @flex 
              where date_loaded < ( select max(date_loaded) from @flex )
             group
                 by employee_number
                  , benefit_code
           ) as allprev
    inner
      join @employee as e
        on e.employee_number = allprev.employee_number
    inner
      join @xbenefit as b
        on b.code = allprev.benefit_code
    left outer
      join @flex as curr
        on curr.employee_number = allprev.employee_number
        on curr.date_loaded = ( select max(date_loaded) from @flex )
     where curr.employee_number is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you may need to arrange it so that only the previous set of rows is in the table when you load the current set

    otherwise my query will need tweaking so that comparisons to previous rows select only the most recent previous, not all previous
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    George - who do you work for? PM me if you'd rather not post publicly.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Cheers Rudy. It's taken 3 minor tweaks but I think it's there...
    I'm having abad SQL day

    I'll update you once I've finished disecting your query
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oh deary me, in the excitement I think something got lost in translation - I'm not getting the desired results from your query

    I'll repost the insert data and expected results - hopefully this will clear things up.

    I think I might end up going with 3 union queries; any opinions?
    Code:
    INSERT INTO @flex (employee_number, benefit_code, benefit_value, date_loaded)
    SELECT '000001', 'BONUS', 60.00, '20070101' UNION ----Zero
    SELECT '000001', 'BUPA' , 10.00, '20070101' UNION --
    SELECT '000001', 'BUPA' , 10.00, '20070202' UNION --NoChange
    SELECT '000001', 'DEN'  ,  5.00, '20070101' UNION --
    SELECT '000001', 'DEN'  , 15.00, '20070202' UNION ----NewValue
    SELECT '000002', 'CAR'  , 15.00, '20070202' UNION ----NewBenefit
    SELECT '000002', 'DEN'  , 15.00, '20070202' UNION ----NewBenefit
    SELECT '000003', 'DEN'  ,  7.00, '20070101' UNION ----Zero
    SELECT '000003', 'BONUS', 65.00, '20070101' UNION ----Zero
    SELECT '000004', 'DEN'  ,  6.00, '20070101' UNION --
    SELECT '000004', 'BUPA' , 10.00, '20070202' UNION ----NewBenefit
    SELECT '000004', 'DEN'  ,  6.00, '20070202' UNION --NoChange
    SELECT '000005', 'BONUS', 80.00, '20070101'       ----Zero
    
    /*     Expected Results
    +-----------------+-----------+--------------+---------------+
    | employee_number | ni_number | description  | benefit_value |
    +-----------------+-----------+--------------+---------------+
    | 000001          | 00A0000AA | Anual Bonus  |  0.0000       |
    | 000001          | 00A0000AA | Dental Care  | 15.0000       |
    | 000002          | 11B11111B | Company Car  | 15.0000       |
    | 000002          | 11B11111B | Dental Care  | 15.0000       |
    | 000003          | 22C2222CC | Dental Care  |   .0000       |
    | 000003          | 22C2222CC | Annual Bonus |   .0000       |
    | 000004          | 33D3333DD | BUPA Cover   | 10.0000       |
    | 000005          | 44E4444EE | Anual Bonus  |   .0000       |
    +-----------------+-----------+--------------+---------------+
    */
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    Oh deary me, in the excitement I think something got lost in translation - I'm not getting the desired results from your query
    quite possibly because i wrote it off the top of my head without testing it


    Quote Originally Posted by georgev
    I think I might end up going with 3 union queries; any opinions?
    your first two requirements can be satisfied by a single LEFT OUTER JOIN, as i attempted to do in the first of the SELECTS in my UNION query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Using UNIONs
    Code:
    --New Benefits
    SELECT employee_number
         , benefit_code
         , benefit_value
    FROM   @flex
    WHERE  date_loaded = @topDate
    AND    employee_number + benefit_code IN (
              SELECT employee_number + benefit_code
              FROM   @flex
              GROUP
                  BY employee_number, benefit_code
              HAVING Count(*) = 1
              )
    UNION
    --Changed benefit value
    SELECT f1.employee_number
         , f1.benefit_code
         , f1.benefit_value
    FROM   @flex f1
     INNER
      JOIN @flex f2
        ON f2.employee_number = f1.employee_number
       AND f2.benefit_code = f1.benefit_code
       AND f2.benefit_value <> f1.benefit_value
       AND f1.date_loaded > f2.date_loaded
    WHERE  f1.date_loaded = @topDate
    UNION
    --Discontinued Benefits
    SELECT prev.employee_number
         , prev.benefit_code
         , Cast(0 As money) As [benefit_value]
    FROM  (
          SELECT employee_number
               , benefit_code
               , benefit_value
          FROM   @flex
          WHERE  date_loaded = @topDate
          ) As [curr]
    RIGHT
     OUTER
      JOIN @flex As [prev]
        ON prev.employee_number = curr.employee_number
       AND prev.benefit_code = curr.benefit_code
    WHERE  curr.employee_number IS NULL
    This produces the correct results; but I'm not happy with it - so I'm still open to suggestions.

    Rudy, I am going to try play with the left outer to neaten up the first two.

    Thanks for all your help
    George
    Home | Blog

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    eeeeeeeeeewwwwwwwwwwwwwwwww

    do i spy a RIGHT OUTER JOIN

    you dog!!!

    also, your UNIONs should be UNION ALL to prevent the unnecessary and inefficient sort for dupes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    They're deliberately not ALLs, because I don't want duplicates...
    Are you suggesting UNION ALLs with a GROUP / DISTINCT?

    And what be wrong with my RIGHT OUTER..?
    Should it be changed to
    Code:
         --Discontinued Benefits
         SELECT prev.employee_number
              , prev.benefit_code
              , Cast(0 As money) As [benefit_value]
         FROM  @flex As [prev]
         LEFT
          OUTER
           JOIN (
               SELECT employee_number
                    , benefit_code
                    , benefit_value
               FROM   @flex
               WHERE  date_loaded = @topDate
               ) As [curr]
             ON prev.employee_number = curr.employee_number
            AND prev.benefit_code = curr.benefit_code
         WHERE  curr.employee_number IS NULL
    ?
    George
    Home | Blog

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    They're deliberately not ALLs, because I don't want duplicates...
    think about the results of each of the SELECTs and ask yourself under what circumstances would you actually get a dupe row...


    Quote Originally Posted by georgev
    And what be wrong with my RIGHT OUTER..?
    right to left from work minds people's most

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    And what be wrong with my RIGHT OUTER..?
    Heh - I thought you were an avid reader of Jeff Smith's blog

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Touch&#233; Rudy, touch&#233;...
    I've changed them to ALLs; "efficiency is fun!"

    I've not read Jeff's (or any other) blogs recently - was it a recent one or is my memory just leaking?
    George
    Home | Blog

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Here's the full thing.
    I'm still not happy with the highlighted code, so any ideas are more than welcome
    Code:
    SELECT r.employee_number
         , e.ni_number
         , b.description
         , r.benefit_value
    FROM (
         --New Benefits
         SELECT employee_number
              , benefit_code
              , benefit_value
         FROM   @flex
         WHERE  date_loaded = @topDate
         AND    employee_number + benefit_code IN (
                   SELECT employee_number + benefit_code
                   FROM   @flex
                   GROUP
                       BY employee_number, benefit_code
                   HAVING Count(*) = 1
                   )
         UNION ALL
         --Changed benefit value
         SELECT f1.employee_number
              , f1.benefit_code
              , f1.benefit_value
         FROM   @flex f1
          INNER
           JOIN @flex f2
             ON f2.employee_number = f1.employee_number
            AND f2.benefit_code = f1.benefit_code
            AND f2.benefit_value <> f1.benefit_value
            AND f1.date_loaded > f2.date_loaded
         WHERE  f1.date_loaded = @topDate
         UNION ALL
         --Discontinued Benefits
         SELECT prev.employee_number
              , prev.benefit_code
              , Cast(0 As money) As [benefit_value]
         FROM  @flex As [prev]
         LEFT
         OUTER
          JOIN (
               SELECT employee_number
                    , benefit_code
                    , benefit_value
               FROM   @flex
               WHERE  date_loaded = @topDate
               ) As [curr]
             ON prev.employee_number = curr.employee_number
            AND prev.benefit_code = curr.benefit_code
         WHERE  curr.employee_number IS NULL
         ) As [r]
     INNER
      JOIN @xbenefit b
        ON b.code = r.benefit_code
     INNER
      JOIN @employee e
        ON e.employee_number = r.employee_number
    George
    Home | Blog

Posting Permissions

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