Solved

# Case statement in procedure to find records per month

Posted on 2007-10-01
190 Views
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?
0
Question by:tiredntroubled

LVL 92

Accepted Solution

Hello tiredntroubled,

The syntax looks OK.  I suspect that the problem is in the state codes.  Specifically, I suspect
that you have some entries for Arkansas that have extraneous spaces somewhere.

Regards,

Patrick
0

Author Comment

No, I don't have extra spaces anywhere.  These states are an example of what I'm trying to do.  They are coming from the database.
0

LVL 15

Expert Comment

What is the result when you run this?:

select state
from table1
where datefield < getdate()
group by state
0

LVL 68

Assisted Solution

I agree with matthewspatrick: there is no way that query should give you multiple rows for the *exact* same state -- there must be a variation on "AR" in the table somewhere.
0

LVL 142

Expert Comment

what data type is the field "State" ?
0

LVL 18

Expert Comment

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()
0

LVL 92

Expert Comment

jinesh_kamdar,

Yes, SQL Server allows the DISTINCT keyword :)

Regards,

Patrick
0

LVL 18

Assisted Solution

I guess i guessed it right then :)
0

Author Comment

Thanks for your responses guys.  I think I got it.
0

## Featured Post

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finallâ€¦
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.