Monthly Aggregation

  • 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 ๐Ÿ™‚

  • 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

  • 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

  • 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.

  • 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

  • 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.

  • 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:

  • 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:

  • 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

  • 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

  • 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 ๐Ÿ™‚

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

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