Getting data out of table

[parshupooja]

I have table with following columns

period (i have multiple records per period)
year
Employee
Type
expensecode
netpay

I want to extract information like this

period-----year------servicecharge------expense-------bonus-----request----total
89-----------2007--------912-------------------10-----------------20------------10--------952

service charge should be where type =4 or 3 or(expensecode= 70001)
expense will be where type = 2 and expensecode <> 70001 or 7000
bonus will be where type = 2 and expensecode = 7000
request will be where type = 1

i am able to get this values individiually eg: service charge but don't know how to make it all together

select period, year, sum(netpay) as servicecharge from payment table where employee = "jim"
where type between 4 and 3 and (expensecode= 70001) group by period, year

result

period year servicecharge
89 2007 912


Plz help

[iburyak]

Try this:

Select period, year, ServiceCharge, expense, bonus, request, 
ServiceCharge + expense + bonus + request Total
From (
    select period, year, Employee,
    sum(Case when type in(3,4) and expensecode= 70001 then netpay else 0 end) ServiceCharge,
    sum(Case when type = 2 and expensecode not in( 70001, 7000) then netpay else 0 end) expense,
    sum(Case when type = 2 and expensecode = 7000 then netpay else 0 end) bonus,
    sum(Case when type = 1 then netpay else 0 end) request,
    from table 
    group by period, year, Employee)
 
where Employee = 'Jim'

Good Luck.

[parshupooja]

Hey thanks for replying but it throws this error

Incorrect syntax near ')'. if i am not including where and if I include where it throws where Employee = 'Jim' . I also removed , near request(bolded down)
but still no use

sum(Case when type = 1 then netpay else 0 end) request,
Help Plz

Try this:

Select period, year, ServiceCharge, expense, bonus, request, 
ServiceCharge + expense + bonus + request Total
From (
    select period, year, Employee,
    sum(Case when type in(3,4) and expensecode= 70001 then netpay else 0 end) ServiceCharge,
    sum(Case when type = 2 and expensecode not in( 70001, 7000) then netpay else 0 end) expense,
    sum(Case when type = 2 and expensecode = 7000 then netpay else 0 end) bonus,
    sum(Case when type = 1 then netpay else 0 end) request,
    from table 
    group by period, year, Employee)
 
where Employee = 'Jim'

Good Luck.

[iburyak]

Oops, sorry try this:

Select period, year, ServiceCharge, expense, bonus, request, 
ServiceCharge + expense + bonus + request Total
From (
    select period, year, Employee,
    sum(Case when type in(3,4) and expensecode= 70001 then netpay else 0 end) ServiceCharge,
    sum(Case when type = 2 and expensecode not in( 70001, 7000) then netpay else 0 end) expense,
    sum(Case when type = 2 and expensecode = 7000 then netpay else 0 end) bonus,
    sum(Case when type = 1 then netpay else 0 end) request,
    from table 
    group by period, year, Employee) a
 
where Employee = 'Jim'

[parshupooja]

Thank You for helping me out

Now I have another issue

I want to add 4 more coulmns to my resultset. 3 columns(paymentdate, startdate, enddate) comes from table1 and 1 column(hrs) comes from table2
all the tables have common column period and its unique in table1 and table2 but not not unique in table thats why we did group by

This is what i tried but it thows error

Select a.payementdate, a.startdate, a.enddate, c.hrs, b.ServiceCharge, b.expense, b.bonus, b.request, 
b.ServiceCharge + b.expense + b.bonus + b.request Total
From (
    select 
    sum(Case when b.type in(3,4) and b.expensecode= 70001 then b.netpay else 0 end) ServiceCharge,
    sum(Case when b.type = 2 and b.expensecode not in( 70001, 7000) then netpay else 0 end) expense,
    sum(Case when b.type = 2 and b.expensecode = 7000 then b.netpay else 0 end) bonus,
    sum(Case when b.type = 1 then b.netpay else 0 end) request
    from table b, table1 a, table2 c
    group by b.period) a
 
where b.Employee = 'Jim' a.period = b.period and c.period = b.period

Oops, sorry try this:

Select period, year, ServiceCharge, expense, bonus, request, 
ServiceCharge + expense + bonus + request Total
From (
    select period, year, Employee,
    sum(Case when type in(3,4) and expensecode= 70001 then netpay else 0 end) ServiceCharge,
    sum(Case when type = 2 and expensecode not in( 70001, 7000) then netpay else 0 end) expense,
    sum(Case when type = 2 and expensecode = 7000 then netpay else 0 end) bonus,
    sum(Case when type = 1 then netpay else 0 end) request,
    from table 
    group by period, year, Employee) a
 
where Employee = 'Jim'

[parshupooja]

Nevermind, I figured out. I wrote 4 scalar function which return Paymentdate, enddate,startdate and hrs based on period

Thank You

Thank You for helping me out

Now I have another issue

I want to add 4 more coulmns to my resultset. 3 columns(paymentdate, startdate, enddate) comes from table1 and 1 column(hrs) comes from table2
all the tables have common column period and its unique in table1 and table2 but not not unique in table thats why we did group by

This is what i tried but it thows error

Select a.payementdate, a.startdate, a.enddate, c.hrs, b.ServiceCharge, b.expense, b.bonus, b.request, 
b.ServiceCharge + b.expense + b.bonus + b.request Total
From (
    select 
    sum(Case when b.type in(3,4) and b.expensecode= 70001 then b.netpay else 0 end) ServiceCharge,
    sum(Case when b.type = 2 and b.expensecode not in( 70001, 7000) then netpay else 0 end) expense,
    sum(Case when b.type = 2 and b.expensecode = 7000 then b.netpay else 0 end) bonus,
    sum(Case when b.type = 1 then b.netpay else 0 end) request
    from table b, table1 a, table2 c
    group by b.period) a
 
where b.Employee = 'Jim' a.period = b.period and c.period = b.period