Monthly Aggregation

Question

Post reply

Monthly Aggregation

Sezam

Ten Centuries

Points: 1232
More actions
January 15, 2013 at 11:37 pm

#272890

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 MonthRegisteredFinilisedRemainig
1 2012 January762 685 77
2 2012 February719 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 🙂

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

Please provide table definitions and sample data.

(read the first link in my signature on how to do this)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

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

Sezam Ten Centuries Points: 1232 More actions · Answer 3

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

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}

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

Sezam Ten Centuries Points: 1232 More actions · Answer 5

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.

demonfox SSCertifiable Points: 6289 More actions · Answer 6

Dehqon D. (1/16/2013)
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.

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

19

217

322

429

538

Let me know , if it helps.

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

demonfox SSCertifiable Points: 6289 More actions · Answer 7

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:

Sezam Ten Centuries Points: 1232 More actions · Answer 8

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

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}

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

Sezam Ten Centuries Points: 1232 More actions · Answer 10

Thanks ChrisM@Work, it worked with adding (maxrecursion 365); at the end.

Later on I'll have to find a way how to do the same thing in the Cube, as it was for POC. May be i'll have to go with 'Additive facts' as demonfox mentioned. If you have any better idea, feel free to share.

Thanks for all to solve the mystery 🙂