I need help with grouping and dynamic number of queried fields

• Tuesday, February 05, 2013 5:03 PM

   

   
  

  0

  Hi,

  I am trying to put together a report with totals from employees selected benefits per bill. The challenge is that the totals on the report are per bill but,  the columns with taxes must be split by states. The number of states can be one or few depending on a bill.

  I have 2 servers, one is SQL2005 and another 2008.

  Thank you in advance, Gena

  Here is the current result:

  coverage               premium                adjustment             tax
  ---------------------- ---------------------- ---------------------- ----------------------
  7000                   192.5                  -12                    23.12

  Here is what I need it to look:

  coverage               premium                adjustment             tax       NY      NJ      WI     CA
  ---------------------- ---------------------- ---------------------- --------- ------- ------- ------ ------
  7000                   192.5                  -12                    23.12     0.56    0.29    0.24   22.03

  Tables and data:

  create table #employees (employee_id int, state_id char(2))
  create table #empbenefits (employee_id int, bill_id int, benefit_id int, coverage float, premium float, adjustment float, tax float)
  set nocount on
  
  insert into #employees (employee_id, state_id) values (12, 'NY')
  insert into #employees (employee_id, state_id) values (14, 'NY')
  insert into #employees (employee_id, state_id) values (15, 'NJ')
  insert into #employees (employee_id, state_id) values (16, 'WI')
  insert into #employees (employee_id, state_id) values (18, 'CA')
  insert into #employees (employee_id, state_id) values (19, 'CA')
  insert into #employees (employee_id, state_id) values (20, 'CA')
  insert into #employees (employee_id, state_id) values (21, 'CA')
  
  -- bill id 1
  insert into #empbenefits (bill_id, employee_id, benefit_id, coverage, premium, adjustment, tax) values (1, 12, 21, 500, 10, -2, 0.12)
  insert into #empbenefits (bill_id, employee_id, benefit_id, coverage, premium, adjustment, tax) values (1, 12, 25, 100, 12, 0, 0.10)
  
  insert into #empbenefits (bill_id, employee_id, benefit_id, coverage, premium, adjustment, tax) values (1, 14, 21, 400, 10, -2, 0.12)
  insert into #empbenefits (bill_id, employee_id, benefit_id, coverage, premium, adjustment, tax) values (1, 14, 25, 200, 14, 0, 0.22)
  
  insert into #empbenefits (bill_id, employee_id, benefit_id, coverage, premium, adjustment, tax) values (1, 15, 21, 500, 10, -2, 0.12)
  insert into #empbenefits (bill_id, employee_id, benefit_id, coverage, premium, adjustment, tax) values (1, 15, 25, 700, 18, 10, 0.17)
  
  insert into #empbenefits (bill_id, employee_id, benefit_id, coverage, premium, adjustment, tax) values (1, 16, 21, 500, 10, -2, 0.12)
  insert into #empbenefits (bill_id, employee_id, benefit_id, coverage, premium, adjustment, tax) values (1, 16, 25, 500, 10, -2, 0.12)
  
  insert into #empbenefits (bill_id, employee_id, benefit_id, coverage, premium, adjustment, tax) values (1, 18, 21, 500, 10, 0, 1.12)
  insert into #empbenefits (bill_id, employee_id, benefit_id, coverage, premium, adjustment, tax) values (1, 18, 25, 400, 20, 0, 2.12)
  
  
  insert into #empbenefits (bill_id, employee_id, benefit_id, coverage, premium, adjustment, tax) values (1, 19, 21, 500, 10, 0, 2.15)
  insert into #empbenefits (bill_id, employee_id, benefit_id, coverage, premium, adjustment, tax) values (1, 19, 25, 900, 30, 0, 1.1)
  
  insert into #empbenefits (bill_id, employee_id, benefit_id, coverage, premium, adjustment, tax) values (1, 20, 21, 500, 10, 0, 2.1)
  insert into #empbenefits (bill_id, employee_id, benefit_id, coverage, premium, adjustment, tax) values (1, 20, 25, 100, 5, 0, 4.2)
  
  insert into #empbenefits (bill_id, employee_id, benefit_id, coverage, premium, adjustment, tax) values (1, 21, 21, 500, 8.5, -10, 8.12)
  insert into #empbenefits (bill_id, employee_id, benefit_id, coverage, premium, adjustment, tax) values (1, 21, 25, 200, 5, -2, 1.12)
  
  -- another bill id 3, i will not query it, just for illustration
  insert into #empbenefits (bill_id, employee_id, benefit_id, coverage, premium, adjustment, tax) values (3, 12, 21, 500, 10, -2, 0.12)
  insert into #empbenefits (bill_id, employee_id, benefit_id, coverage, premium, adjustment, tax) values (3, 12, 25, 500, 10, -2, 0.12)
  
  set nocount off
  select state_id,
  SUM(coverage) as coverage,
  SUM(premium) as premium,
  SUM(adjustment) as adjustment,
  SUM(tax) as tax
  from #empbenefits b
  join #employees e on e.employee_id = b.employee_id 
  where bill_id = 1
  --group by state_id
  
  drop table #employees, #empbenefits

  
  
  
  

  • Edited by gena_q Tuesday, February 05, 2013 5:06 PM
  •  
  • Reply
  • Quote