totals per month - 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

totals per month

Author

Topic

emailuser
Yak Posting Veteran

74 Posts

Posted - 2013-05-02 : 11:55:11

Hi everyone , I have data in the following format
from a table called F0015
CATSTATUS refers to if the item status is open or closed 1346 closed , 1347 Open , the CLOSEDATE will show 1900-01-01 if the item is NOT closed

title, OPENDATE, CLOSEDATE, CATSTATUS
test1 2013-01-22 2013-01-22 1346
test2 2013-02-22 2013-02-24 1346
test3 2013-02-22 2013-03-23 1346
test4 2013-03-22 1900-01-01 1347

My output using the code below looks like this

Year, month , total_ref_open , CLOSED
2013 Jan 1 1
2013 Feb 2 1
2013 Mar 1 1

In addition I would like to show cumulative Open and Closed so the output will look like below , unfortunately I just cannot figure it out .. any help would be greatly appreciated !!!! :)

Year, month, total_ref_open, CLOSED, RollOpen, Roll Closed
2013 Jan 1 1 1 1
2013 Feb 2 1 3 2
2013 Mar 1 1 4 3

select
year(OPENDATE) as Year,
datename(mm,OPENDATE) as month,
count(REFERENCE) as total_ref_open,
count(case when CATSTATUS = '1346' then 1 else null end) AS CLOSED
from
F0015
group by
year(OPENDATE),
month(OPENDATE),
datename(mm,OPENDATE)
order by
year(OPENDATE),
month(OPENDATE),
datename(mm,OPENDATE)

[/code]

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-02 : 12:17:50

If you are on SQL 2012, this would be a little easier, but in SQL 2005 or later:


SELECT  YEAR(OPENDATE) AS Year ,
        DATENAME(mm, OPENDATE) AS month ,
        COUNT(REFERENCE) AS total_ref_open ,
        COUNT(CASE WHEN CATSTATUS = '1346' THEN 1
                   ELSE NULL
              END) AS CLOSED,
        f2.RollOpen,
        f2.[Roll Closed] 
            
FROM    F0015 f1
		CROSS APPLY
		(
			SELECT 
				COUNT(f2.REFERENCE) AS RollOpen,
				COUNT(CASE WHEN f2.CATSTATUS = '1346' THEN 1
                   ELSE NULL
				  END) AS [Roll Closed]
			FROM F0015 f2
			WHERE f2.OpenDate <= f1.OpenDate
		) f2
GROUP BY YEAR(OPENDATE) ,
        MONTH(OPENDATE) ,
        DATENAME(mm, OPENDATE),
        f2.[Roll Closed],
        f2.RollOpen
ORDER BY YEAR(OPENDATE) ,
        MONTH(OPENDATE) ,
        DATENAME(mm, OPENDATE)

emailuser
Yak Posting Veteran

74 Posts

Posted - 2013-05-02 : 12:47:07

Hi James, many many thanks for a quick reply , you are correct I am on 2005 , I tried the code and it works , apart from I get multiple lines for each month , ideally I am looking for a single set of values per month .. so nearly there :)

returned value
year, month, total_ref_open, Closed. RollOpen, Roll Closed
2013 February 1 0 1 0
2013 February 1 0 2 0
2013 February 1 0 3 0

looking for

year, month, total_ref_open, Closed. RollOpen, Roll Closed
2013 February 3 0 3 0

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-02 : 13:07:03

Can you try one of these?

SELECT  YEAR(OPENDATE) AS Year ,
        DATENAME(mm, OPENDATE) AS month ,
        COUNT(REFERENCE) AS total_ref_open ,
        COUNT(CASE WHEN CATSTATUS = '1346' THEN 1
                   ELSE NULL
              END) AS CLOSED,
        SUM(f2.RollOpen) AS RollOpen,
        SUM(f2.[Roll Closed] ) AS [Roll Closed]
            
FROM    F0015 f1
		CROSS APPLY
		(
			SELECT 
				COUNT(f2.REFERENCE) AS RollOpen,
				COUNT(CASE WHEN f2.CATSTATUS = '1346' THEN 1
                   ELSE NULL
				  END) AS [Roll Closed]
			FROM F0015 f2
			WHERE f2.OpenDate <= f1.OpenDate
		) f2
GROUP BY YEAR(OPENDATE) ,
        MONTH(OPENDATE) ,
        DATENAME(mm, OPENDATE)
ORDER BY YEAR(OPENDATE) ,
        MONTH(OPENDATE) ,
        DATENAME(mm, OPENDATE) 
        

;WITH cte AS
(
SELECT  YEAR(OPENDATE) AS Year ,
        DATENAME(mm, OPENDATE) AS month ,
        MONTH(opendate) AS Mnth,
        COUNT(REFERENCE) AS total_ref_open ,
        COUNT(CASE WHEN CATSTATUS = '1346' THEN 1
                   ELSE NULL
              END) AS CLOSED
            
FROM    F0015 f1
GROUP BY YEAR(OPENDATE) ,
        MONTH(OPENDATE) ,
        DATENAME(mm, OPENDATE)
)
SELECT a.*, b.RollOpen, b.[Roll Closed]
FROM cte a
CROSS APPLY
( SELECT SUM(total_ref_open) AS RollOpen,
	SUM(CLOSED) AS [Roll Closed] FROM cte b 
	WHERE b.YEAR <= a.YEAR AND b.Mnth <= a.Mnth
) b 
ORDER BY Year, mnth

emailuser
Yak Posting Veteran

74 Posts

Posted - 2013-05-03 : 04:26:33

Hi James, thanks for your reply , it seems to kind of work ,, when I run this in sql query I end up with 2 sets of results

I get ... and these numbers seem strange
year month total_ref_open closed RollOpen RollClosed
2013 February 3 0 6 0
2013 March 7 2 50 6

but sql query also returns in a separate window pane, this data is all good

year month Mnth total_ref_open Closed RollOpen RollClosed
2013 February 2 3 0 3 0
2013 March 3 7 2 10 2

Finally when I put the sql into my application I get an error message that select cannot start with a ; ... any ideas ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources