?
Solved

Case statement in procedure to find records per month

Posted on 2007-10-01
9
Medium Priority
?
194 Views
Last Modified: 2010-03-20
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.
0
Comment
Question by:tiredntroubled
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 800 total points
ID: 19995140
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

by:tiredntroubled
ID: 19995319
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

by:danrosenthal
ID: 19995349
What is the result when you run this?:

select state
from table1
where datefield < getdate()
group by state
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 800 total points
ID: 19995371
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19996362
what data type is the field "State" ?
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 19997503
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 93

Expert Comment

by:Patrick Matthews
ID: 19997822
jinesh_kamdar,

Yes, SQL Server allows the DISTINCT keyword :)

Regards,

Patrick
0
 
LVL 18

Assisted Solution

by:Jinesh Kamdar
Jinesh Kamdar earned 400 total points
ID: 19997938
I guess i guessed it right then :)
0
 

Author Comment

by:tiredntroubled
ID: 20002739
Thanks for your responses guys.  I think I got it.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question