Monthly Aggregation

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Monthly Aggregation

11 posts, Page 1 of 2

1 2 »»

Monthly Aggregation

Rate Topic

Display Mode

Topic Options

Author

Message

Dehqon D.

Posted Tuesday, January 15, 2013 11:37 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, June 14, 2013 2:34 AM
Points: 40, Visits: 829

I've created following code:

select 		DATEpart(Mm,(Convert(Date,semua_tkh_Daftar_txt,103))) as MonthNo,
DATENAME(Year,(Convert(Date,semua_tkh_Daftar_txt,103))) as Year,
	    DATENAME(Mm,(Convert(Date,semua_tkh_Daftar_txt,103))) as Month,
	    COUNT(doclinkid) as Registered, 
		sum(case when REPWPK.T_BMAL.Semua_Status_Kod like 'S%' then 1 else 0 end) as Finilised,
		sum(case when REPWPK.T_BMAL.Semua_Status_Kod not like 'S%' then 1 else 0 end) as Remainig
from REPWPK.T_BMAL  
group by	
	DATEpart(Mm,(Convert(Date,semua_tkh_Daftar_txt,103))),
DATENAME(Year,(Convert(Date,semua_tkh_Daftar_txt,103))),
	DATENAME(Mm,(Convert(Date,semua_tkh_Daftar_txt,103)))
order by DATENAME(Year,(Convert(Date,semua_tkh_Daftar_txt,103))),
	DATEpart(Mm,(Convert(Date,semua_tkh_Daftar_txt,103)))

Now the result will appear as below:

MonthNo       Year                 Month	Registered	Finilised	Remainig
1	         2012	          January	762	        685	        77
2	         2012             February	719             653	        66
3	         2012	          March        759	        655	        104
4	         2012	          April	        704	        599	        105
5	         2012	          May	        821	        684	        137

The actual requirement for "remaining" is different. E.g. for January 2012 it supposed sum ALL not finilised cases before January 2012.
Any suggestion is appreciated. Feel free to change the code, as it is not optimized :)

Post #1407623

Koen Verbeeck

Posted Wednesday, January 16, 2013 12:59 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:30 AM
Points: 9,410, Visits: 6,495

Please provide table definitions and sample data.
(read the first link in my signature on how to do this)

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1407645

ChrisM@Work

Posted Wednesday, January 16, 2013 1:53 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:26 AM
Points: 5,705, Visits: 11,136

You may find this modification easier to work with:

SELECT
	x.MonthNo,
	x.[Year],
	x.[Month],
	COUNT(doclinkid) as Registered, -- is this correct?  
	COUNT(DISTINCT doclinkid) as Registered, -- OR is this correct?  
	SUM(x.Finalised) as Finalised,
	COUNT(*) - SUM(x.Finalised) AS [Remaining] 
FROM REPWPK.T_BMAL
CROSS APPLY (
	SELECT 
		semua_date = CONVERT(DATE,semua_tkh_Daftar_txt,103), 
		Finalised = CASE WHEN Semua_Status_Kod LIKE 'S%' THEN 1 ELSE 0 END
) x
CROSS APPLY (
	SELECT 
		[Year] = DATENAME(YEAR,x.semua_date),
		[Month] = DATENAME(Mm,x.semua_date),
		[MonthNo] = DATEPART(Mm,x.semua_date)
) y  
GROUP BY	
	x.MonthNo,
	x.[Year],
	x.[Month]
ORDER BY 
	x.[Year],
	x.MonthNo

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps

Post #1407677

Dehqon D.

Posted Wednesday, January 16, 2013 9:33 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, June 14, 2013 2:34 AM
Points: 40, Visits: 829

Thanks ChrisM@Work, it looks promising. I never thought of minus from total :)
just having following error, any idea how to fix that?

Msg 207, Level 16, State 1, Line 22
Invalid column name 'MonthNo'.
Msg 207, Level 16, State 1, Line 23
Invalid column name 'Year'.
Msg 207, Level 16, State 1, Line 24
Invalid column name 'Month'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'MonthNo'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'Year'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'Month'.

By the way we don't need to use distinct, row data itself has no duplicate doclinkid's.

Post #1407936

ChrisM@Work

Posted Wednesday, January 16, 2013 9:36 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:26 AM
Points: 5,705, Visits: 11,136

Sorry, my mistake:

SELECT
	y.MonthNo,
	y.[Year],
	y.[Month],
	COUNT(doclinkid) as Registered, -- is this correct?  
	COUNT(DISTINCT doclinkid) as Registered, -- OR is this correct?  
	SUM(x.Finalised) as Finalised,
	COUNT(*) - SUM(x.Finalised) AS [Remaining] 
FROM REPWPK.T_BMAL
CROSS APPLY (
	SELECT 
		semua_date = CONVERT(DATE,semua_tkh_Daftar_txt,103), 
		Finalised = CASE WHEN Semua_Status_Kod LIKE 'S%' THEN 1 ELSE 0 END
) x
CROSS APPLY (
	SELECT 
		[Year] = DATENAME(YEAR,x.semua_date),
		[Month] = DATENAME(Mm,x.semua_date),
		[MonthNo] = DATEPART(Mm,x.semua_date)
) y  
GROUP BY	
	y.MonthNo,
	y.[Year],
	y.[Month]
ORDER BY 
	y.[Year],
	y.MonthNo

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

Post #1407939

Dehqon D.

Posted Wednesday, January 16, 2013 8:31 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, June 14, 2013 2:34 AM
Points: 40, Visits: 829

The result is same with my previous query, I think I couldn't explain it right. By requirement for the column remaining it had to sum up all previous remaining cases. Let's say for January 2012, it has to give sum of all remaining cases <=January 2012. So the number keeps on increasing. For February 2012, all remaining cases from 2011, 2010... and 2012 January.

Any solution is appreciated. By the way thanks ChrisM@Work to give better code.

Post #1408127

demonfox

Posted Wednesday, January 16, 2013 11:52 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 4:10 AM
Points: 1,092, Visits: 1,122

Dehqon D. (1/16/2013)

It's concept of Additive facts. Coul you please provide the DDl's ??

Here is how I tried :

CREATE TABLE tblPopulation (
MonthNo VARCHAR(100),
Month_Name VARCHAR(100),
Population_A INT
)
GO
INSERT INTO tblPopulation VALUES('1', 'January',9 )
INSERT INTO tblPopulation VALUES('2', 'February',8 )
INSERT INTO tblPopulation VALUES('3', 'March',5.5)
INSERT INTO tblPopulation VALUES('4', 'April',7.5)
INSERT INTO tblPopulation VALUES('5', 'May',9.5)

-- Query 
SELECT a.MONTHNO,
       (SELECT SUM(POPULATION_A)
        FROM  (SELECT POPULATION_A
               FROM   TBLPOPULATION b
               WHERE  a.MONTHNO >= b.MONTHNO) test)
FROM   TBLPOPULATION a

Result set is :
MONTHNO SUM_COL
1 9
2 17
3 22
4 29
5 38

Let me know , if it helps.

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one Ermm

Post #1408165

demonfox

Posted Wednesday, January 16, 2013 11:55 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 4:10 AM
Points: 1,092, Visits: 1,122

Result set is all integer , because of Population_A INT, I should have added that as float.
with float , result set would be

MONTHNO SUM_COL
1 9
2 17
3 22.5
4 30
5 39.5

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one Ermm

Post #1408166

Dehqon D.

Posted Friday, January 18, 2013 7:25 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, June 14, 2013 2:34 AM
Points: 40, Visits: 829

Thanks for idea @demonfox, you have got it right. But how to change it to fit my ChrisM@Work code.

Here is DDL:

--===== Create the table 
CREATE TABLE [REPWPK].[T_BMAL](
	[DocLinkID] [nvarchar](50) NULL,
	[Semua_Tkh_Daftar_TXT] [varchar](50) NULL,
	[Semua_Status_Kod] [nvarchar](50) NULL,
) ON [PRIMARY]

--== Some sample data
INSERT INTO [REPWPK].[T_BMAL]
           ([DocLinkID]
           ,[Semua_Tkh_Daftar_TXT]
           ,[Semua_Status_Kod])
SELECT '73G9VMWPKY2352007MCO','23/05/2007','S07' UNION ALL
SELECT '7L2BYCWPKY3112008MCO','03/11/2008','S02' UNION ALL
SELECT '6XJAUMWPKY1712007MCO','17/01/2007','S05' UNION ALL
SELECT '6KBAFLWPKY22122005MCO','22/12/2005','S07' UNION ALL
SELECT '8WABNCWPKY1772012MCO','17/07/2012','TT03' UNION ALL
SELECT '8FT6RLWPKY1142011MCO','1/04/2011','S05' UNION ALL
SELECT '76K7P7WPKY3082007P','30/08/2007','S01' UNION ALL
SELECT '8US5APWPKY3052012','30/05/2012','S06' UNION ALL
SELECT '93THMMWPKY1012013MCO','10/01/2013','TT02' UNION ALL
SELECT '7QNBNXWPKY3132009MCO','31/03/2009','S07'

Any help is appreciated

Post #1408911

ChrisM@Work

Posted Monday, January 21, 2013 1:51 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:26 AM
Points: 5,705, Visits: 11,136

Dehqon D. (1/18/2013)

Thanks for idea @demonfox, you have got it right. But how to change it to fit my ChrisM@Work code.

Like this?

--===== Create the table 
CREATE TABLE #T_BMAL(
	[DocLinkID] [nvarchar](50) NULL,
	[Semua_Tkh_Daftar_TXT] [varchar](50) NULL,
	[Semua_Status_Kod] [nvarchar](50) NULL,
) ON [PRIMARY]

--== Some sample data
INSERT INTO #T_BMAL
           ([DocLinkID]
           ,[Semua_Tkh_Daftar_TXT]
           ,[Semua_Status_Kod])
SELECT '73G9VMWPKY2352007MCO','23/05/2007','S07' UNION ALL
SELECT '7L2BYCWPKY3112008MCO','03/11/2008','S02' UNION ALL
SELECT '6XJAUMWPKY1712007MCO','17/01/2007','S05' UNION ALL
SELECT '6KBAFLWPKY22122005MCO','22/12/2005','S07' UNION ALL
SELECT '8WABNCWPKY1772012MCO','17/07/2012','TT03' UNION ALL
SELECT '8FT6RLWPKY1142011MCO','1/04/2011','S05' UNION ALL
SELECT '76K7P7WPKY3082007P','30/08/2007','S01' UNION ALL
SELECT '8US5APWPKY3052012','30/05/2012','S06' UNION ALL
SELECT '93THMMWPKY1012013MCO','10/01/2013','TT02' UNION ALL
SELECT '7QNBNXWPKY3132009MCO','31/03/2009','S07' 

-- Group by month, pop the results into a #temp table (or use a CTE)
SELECT
	Seq = ROW_NUMBER() OVER(ORDER BY y.[Year],y.MonthNo),
	y.MonthNo,
	y.[Year],
	y.[Month],
	Registered	= COUNT(*),   
	Finalised	= SUM(x.Finalised),
	Remaining	= COUNT(*) - SUM(x.Finalised)
INTO #Temp  
FROM #T_BMAL
CROSS APPLY (
	SELECT 
		semua_date = CONVERT(DATE,semua_tkh_Daftar_txt,103), 
		Finalised = CASE WHEN Semua_Status_Kod LIKE 'S%' THEN 1 ELSE 0 END
) x
CROSS APPLY (
	SELECT 
		[Year] = DATENAME(YEAR,x.semua_date),
		[Month] = DATENAME(Mm,x.semua_date),
		[MonthNo] = DATEPART(Mm,x.semua_date)
) y  
GROUP BY	
	y.MonthNo,
	y.[Year],
	y.[Month]
ORDER BY 
	y.[Year],
	y.MonthNo

-- Use a recursive cTE to calculate the running total of Remaining	
;WITH Calculator AS (
	SELECT t.Seq, t.[Year], t.[Month], t.MonthNo, t.Registered, t.Finalised, t.Remaining, 
		 RunningTotal = t.Remaining
	FROM #Temp t
	WHERE Seq = 1
	UNION ALL
	SELECT t.Seq, t.[Year], t.[Month], t.MonthNo, t.Registered, t.Finalised, t.Remaining,
		 RunningTotal = c.RunningTotal + t.Remaining
	FROM #Temp t
	INNER JOIN Calculator c ON c.Seq + 1 = t.Seq
) SELECT * 
FROM Calculator 
ORDER BY Seq

-- Clean up
DROP TABLE #Temp
	
DROP TABLE #T_BMAL

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

Post #1409419

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions