SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 totals per month
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

emailuser
Yak Posting Veteran

74 Posts

Posted - 05/02/2013 :  11:55:11  Show Profile  Reply with Quote
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)

James K
Flowing Fount of Yak Knowledge

3723 Posts

Posted - 05/02/2013 :  12:17:50  Show Profile  Reply with Quote
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)
Go to Top of Page

emailuser
Yak Posting Veteran

74 Posts

Posted - 05/02/2013 :  12:47:07  Show Profile  Reply with Quote
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

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3723 Posts

Posted - 05/02/2013 :  13:07:03  Show Profile  Reply with Quote
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











Edited by - James K on 05/02/2013 13:07:39
Go to Top of Page

emailuser
Yak Posting Veteran

74 Posts

Posted - 05/03/2013 :  04:26:33  Show Profile  Reply with Quote
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 ?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000