Click here to Skip to main content
15,844,903 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i have a table which have 5 fields id,title,fileno,currentstatus,daterecieving.
In currentstatus four values are saved open,close,pending and inprogress,
i want monthly report.in my report there is a dropdown for select year e.g 2012.
when user select year it shown report like this

Month Open Close Pending Inprogress Total
jan 3 2 2 1 8
feb 2 1 2 2 7
.
.
dec 12 5 2 1 20

i use this query

SQL
SELECT 

SUM( case current_status when 'Open' then 1 else 0 end ) as 'Open',
SUM( case current_status when 'Close' then 1 else 0 end ) as 'Closed',
SUM( case current_status when 'Pending' then 1 else 0 end ) as 'Pending',
SUM( case current_status when 'In Progress' then 1 else 0 end ) as 'InProgress',
SUM(1) as 'Total' from FileRegister where YEAR(DATE_RECIEVING)='2011'


this give me whole result of year not month by month as i mentioned.




kindly help me through code
thanks
Posted
Updated 22-Feb-12 23:34pm
v3

1 solution

Try this:
SQL
SELECT MonthID=Month(DATE_RECIEVING),
SUM( case current_status when 'Open' then 1 else 0 end ) as 'Open',
SUM( case current_status when 'Close' then 1 else 0 end ) as 'Closed',
SUM( case current_status when 'Pending' then 1 else 0 end ) as 'Pending',
SUM( case current_status when 'In Progress' then 1 else 0 end ) as 'InProgress',
SUM(1) as 'Total' from FileRegister where YEAR(DATE_RECIEVING)='2011'
Group by Month(DATE_RECIEVING)
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900