Need to pick up the SUM OF Tax Amount for the highest Sequence number Per Year, Per SSN, Per employer

Consider Employee table:

Employerid ssn  year  Seqnumber     q1taxamt   q2taxamt     q3taxamt     q4taxamt
1004       101  2013    1             2000       0            0             0
1004       101  2013    2             2000       100          0             0
1004       101  2013    3             2000       100          200           0
1004       101  2013    4             2000       100          200           300
1004       102  2013    1             3000       0            0             0
1004       102  2013    2             3000       200          0             0
1004       102  2013    3             3000       200          300           0
1004       102  2013    4             3000       200          300           400
1004       102  2013    5             3000       200          300           400

Here the transformation rule is we need to pick the highest Seqnumber with respect to each ssn per year per
Employerid and the amounts.

  • How to delete duplicates in SQL table with Primary Key
  • Parse a date from unformatted text in SQL
  • Having trouble with ASP WebAPI 2.2 and IIS 7 with 404 Error Code
  • How to alias fields defined in Pivot SQL Server without getting duplicates
  • query a table so that results are combined and returned in rows
  • SQL based on my Example for Month
  • i.e for 10004 for sum(q1taxamt) is 2000 +3000 = 5000

    The Logic is ssn 101 has highest seq number of 4 and ssn 102 has highest seq number of 5 so we need to pick those values wrt to employerid

    Example:
    Want to check for q1taxamt: 2000 +3000 = 5000

    Want to check for q4taxamt: 300 +400 = 700

    output must be:

    Employerid  YEAR     q1taxamt   q2taxamt     q3taxamt     q4taxamt     
    10004        2013     5000        300          500           700
    

    The below query is generating wrong result:

    Select
        Sum(E1.q1taxamt) q1taxamt,
        Sum(E1.q2taxamt) q2taxamt,
        Sum(E1.q3taxamt) q3taxamt,
        Sum(E1.q4taxamt) q4taxamt,
        E1.Employerid,
        E1.YEAR
    from Employee E1
    join
    (
        select 
            E.Employerid,
            MAX(E.seqnumber) seqnumber,
            E.YEAR
        from Employee E
        group by E.Employerid,E.SSn,E.year
    )E2
    on E1.Employerid=E2.Employerid
    AND E1.YEAR=E2.YEAR
    and E1.seqnumber=E2.Taxseqnumber
    

    2 Solutions collect form web for “Need to pick up the SUM OF Tax Amount for the highest Sequence number Per Year, Per SSN, Per employer”

    Just use row_number():

    select e.*
    from (select e.*,
                 row_number() over (partition by E.Employerid, E.SSn, E.year
                                    order by e.seqnumber desc
                                   ) as seqnum
          from Employee e
         ) e
    where seqnum = 1;
    

    For best performance, you want an index on Employee(EmployerId, SSN, seqnumber desc).

    You missing SSN join predicate between E1 and E2 thats why you are getting wrong result. I think this might be faster than Row_Number method.

    Select
        Sum(E1.q1taxamt) q1taxamt,
        Sum(E1.q2taxamt) q2taxamt,
        Sum(E1.q3taxamt) q3taxamt,
        Sum(E1.q4taxamt) q4taxamt,
        E1.Employerid,
        E1.YEAR
    from Employee E1
    join
    (
        select 
            E.Employerid,
            E.SSn,
            MAX(E.seqnumber) seqnumber,
            E.YEAR
        from Employee E
        group by E.Employerid,E.SSn,E.year
    )E2
    on E1.Employerid=E2.Employerid
    AND E1.YEAR=E2.YEAR
    AND E1.SSN = E2.SSN --Here
    and E1.seqnumber=E2.Taxseqnumber
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.