Selecting across date range - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Selecting across date range

Author

Topic

madlan
Starting Member

5 Posts

Posted - 2010-11-10 : 13:28:30

Hello,

I'm trying to select some data totalled across a rolling 12 month period with the month name listed and two totals.

Expected output:

Open Closed
January 2 2
February 2 1
March 0 1

Table:

PROBLEM_ID OPENED CLOSED
1 2010-01-21 16:03:09.0 2010-02-22 16:03:09.0
2 2010-01-15 16:03:09.0 2010-03-16 16:03:09.0
3 2010-02-22 16:03:09.0 2010-01-22 16:03:09.0
4 2010-02-27 16:03:09.0 2010-01-29 16:03:09.0

My attempt (Returns two columns for the last month):


SELECT
SUM(CASE WHEN i.OPENED between SYSDATE-30 and SYSDATE THEN 1 ELSE 0 END) AS Opened, 
SUM(CASE WHEN i.CLOSED between SYSDATE-30 and SYSDATE THEN 1 ELSE 0 END) AS Closed 
FROM incident i
GROUP BY TO_CHAR(SYSDATE, 'YYYY'), TO_CHAR(SYSDATE, 'MM')

Returns:

Opened Closed
121 221

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-10 : 13:31:34

You've posted your question on a Microsoft SQL Server site. You should instead try a site that specializes in Oracle. Try the Oracle site or dbforums.com, I'm sure there are many other sites that offer help too.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madlan
Starting Member

5 Posts

Posted - 2010-11-10 : 13:51:56

Hi tkizer,
thanks for the reply.

I'm using a linked server so MSSQL syntax would be fine.

I can produce results for a selected month with the above attempt but have no idea where to start with grouping by month and displaying the actual month name!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-11 : 05:08:39

Try this

SELECT TO_CHAR(SYSDATE, 'MON'),
SUM(CASE WHEN i.OPENED between SYSDATE-30 and SYSDATE THEN 1 ELSE 0 END) AS Opened,
SUM(CASE WHEN i.CLOSED between SYSDATE-30 and SYSDATE THEN 1 ELSE 0 END) AS Closed
FROM incident i
GROUP BY TO_CHAR(SYSDATE, 'MON')

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources