Like a cross tab query.

[tijenderpopli]

There is one table Payroll
having field names
Date employid Paidamount
I want to get sum of Paidamount on different dates (from given date to six days back) in a table like Dates as column name and sum of paid amont at that date as data.
the query i have used is

select
sum(CASE when Date=DATE_ADD('yyyy-mm-dd',INTERVAL -6 DAY) then Paidamount else 0 end) as FisrtDay,
sum(CASE when Date=DATE_ADD('yyyy-mm-dd',INTERVAL -5 DAY) then Paidamount else 0 end) as SecondDay,
sum(CASE when Date=DATE_ADD('yyyy-mm-dd',INTERVAL -4 DAY) then Paidamount else 0 end) as ThirdDay,
sum(CASE when Date=DATE_ADD('yyyy-mm-dd',INTERVAL -3 DAY) then Paidamount else 0 end) as FourthDay,
sum(CASE when Date=DATE_ADD('yyyy-mm-dd',INTERVAL -2 DAY) then Paidamount else 0 end)as FifthDay,
sum(CASE when Date='yyyy-mm-dd' then Paidamount else 0 end) as sixthDay
from Payroll

(Here 'yyyy-mm-dd' represents the given date.)
But i want to get the value of Date field itself as the column name(there should be date in place of FirstDay,SecondDay,ThirdDay,FourthDay,FifthDay.



Edited 5 time(s). Last edit at 08/16/2006 04:04AM by tijenderpopli.