tiredntroubled
asked on
Case statement in procedure to find records per month
Hi,
I am working on trying to retrieve records grouped by state for each month.
so, I have
select state,
sum(case month(datefield) when 1 then 1 else 0 end)
sum(case month(datefield) when 2 then 1 else 0 end)
sum(case month(datefield) when 3 then 1 else 0 end)
sum(case month(datefield) when 4 then 1 else 0 end)
sum(case month(datefield) when 5 then 1 else 0 end)
sum(case month(datefield) when 6 then 1 else 0 end)
sum(case month(datefield) when 7 then 1 else 0 end)
sum(case month(datefield) when 8 then 1 else 0 end)
sum(case month(datefield) when 9 then 1 else 0 end)
sum(case month(datefield) when 10 then 1 else 0 end)
sum(case month(datefield) when 11 then 1 else 0 end)
sum(case month(datefield) when 12 then 1 else 0 end)
from table1
where datefield < getdate()
group by state.
As far as the states, there are 5 being pulled from the database: AR, WA,VA, IL, TX.
What I am now seeing in the resultset is an extra row for a different month. e.g.
State Jan Feb Mar. ................... Sep Oct
AR 0 0 0 10 0
AR 0 0 0 0 2
IL 0 0 0 3 0
TX 0 0 0 1 0
VA 0 0 0 2 0
WA 0 0 0 1 0
Is there a way to have 1 row for each state and then have appropriate value displayed for that specific month?
Thanks in advance.
I am working on trying to retrieve records grouped by state for each month.
so, I have
select state,
sum(case month(datefield) when 1 then 1 else 0 end)
sum(case month(datefield) when 2 then 1 else 0 end)
sum(case month(datefield) when 3 then 1 else 0 end)
sum(case month(datefield) when 4 then 1 else 0 end)
sum(case month(datefield) when 5 then 1 else 0 end)
sum(case month(datefield) when 6 then 1 else 0 end)
sum(case month(datefield) when 7 then 1 else 0 end)
sum(case month(datefield) when 8 then 1 else 0 end)
sum(case month(datefield) when 9 then 1 else 0 end)
sum(case month(datefield) when 10 then 1 else 0 end)
sum(case month(datefield) when 11 then 1 else 0 end)
sum(case month(datefield) when 12 then 1 else 0 end)
from table1
where datefield < getdate()
group by state.
As far as the states, there are 5 being pulled from the database: AR, WA,VA, IL, TX.
What I am now seeing in the resultset is an extra row for a different month. e.g.
State Jan Feb Mar. ................... Sep Oct
AR 0 0 0 10 0
AR 0 0 0 0 2
IL 0 0 0 3 0
TX 0 0 0 1 0
VA 0 0 0 2 0
WA 0 0 0 1 0
Is there a way to have 1 row for each state and then have appropriate value displayed for that specific month?
Thanks in advance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What is the result when you run this?:
select state
from table1
where datefield < getdate()
group by state
select state
from table1
where datefield < getdate()
group by state
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
what data type is the field "State" ?
Try to do a DISTINCT on the state from that table. I am not sure if its a keyword in SQL-Server though.
SELECT DISTINCT state
FROM table1
WHERE datefield < GetDate()
SELECT DISTINCT state
FROM table1
WHERE datefield < GetDate()
jinesh_kamdar,
Yes, SQL Server allows the DISTINCT keyword :)
Regards,
Patrick
Yes, SQL Server allows the DISTINCT keyword :)
Regards,
Patrick
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your responses guys. I think I got it.
ASKER