dcsimg
Results 1 to 6 of 6

Thread: [RESOLVED] SQL - Running total for multiple columns, reset and special

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    428

    Resolved [RESOLVED] SQL - Running total for multiple columns, reset and special

    Hi,
    I need to calculate the running sum for two columns for each month of the year for multiple years. But what is interesting that the sum should reset when year changes and year should appear alternating.
    I have been playing around with this query; it almost does what I need but I think it has become unnecessary complex; and I think I am doing it wrong:

    What it does:
    Name:  2020-03-25_20-48-15.jpg
Views: 54
Size:  27.0 KB

    What I need:
    Name:  2020-03-25_21-02-47.jpg
Views: 56
Size:  84.0 KB


    So, it is sorted based on the month and ascending year each year/month at time.
    What is the right way to do this?

    Code:
    WITH T AS
    (   SELECT  CompletedDate_Year, MyOrder, Original, CompletedDate_MonthName, Major,Minor, Has_SR,
       ROW_NUMBER() OVER(PARTITION BY CompletedDate_Year ORDER BY CompletedDate_Year, MyOrder) [RowNumber]
        FROM   
    	(
    	Select Distinct CompletedDate_Year,DATEPART(mm, CAST(CompletedDate_MonthName + '1900' AS DATETIME)) As MyOrder, CompletedDate_MonthName AS Original, 
    	 Convert(nvarchar(10), CompletedDate_Year) + '-' +
    	Convert(nvarchar(10), CompletedDate_MonthName) As CompletedDate_MonthName, Sum(Case When Classification ='Major' then 1 else 0 End) As Major,
         Sum(Case When Classification ='Minor' then 1 else 0 End) As Minor,
         Sum(Case When Has_SR ='Yes' then 1 else 0 End) As Has_SR  
    	 FROM [DMS_DV_Data_NEW] 
    	 Where  Classification In ('Minor','Major','Unclassified') And 
    	 IS_PLANNED_DEVIATION = 'NO'   And 
    	 ( Format([COMPLETED_DATE],'yyyy-MM-dd')  >= '2019-01-01' And 
    	 Format([COMPLETED_DATE],'yyyy-MM-dd')  < '2020-03-25')  And 
    	 Staus = 'Closed' And 
    	 Phase <> 'Cancelled' 
    	 Group by CompletedDate_Year,CompletedDate_MonthName 
    	)Temp
    )
    
    SELECT  T1.CompletedDate_Year,T1.MyOrder,T1.Original,T1.CompletedDate_MonthName,        
            Major.Major,
    		Minor.Minor, 
    		Has_SR.Has_SR
    FROM    T T1
     OUTER APPLY
      (   SELECT SUM(Major) As Major
          FROM   T T2
          WHERE  T2.CompletedDate_Year = T1.CompletedDate_Year
        AND  T2.RowNumber <= T1.RowNumber
     ) Major
      OUTER APPLY
      (   SELECT SUM(Minor) As Minor
          FROM   T T2
          WHERE  T2.CompletedDate_Year = T1.CompletedDate_Year
        AND  T2.RowNumber <= T1.RowNumber
     ) Minor
    
       OUTER APPLY
      (   SELECT SUM(Has_SR) As Has_SR
          FROM   T T2
          WHERE  T2.CompletedDate_Year = T1.CompletedDate_Year
        AND  T2.RowNumber <= T1.RowNumber
     ) Has_SR
    Thanks for any help.
    Last edited by Grand; Mar 25th, 2020 at 03:23 PM.

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,803

    Re: SQL - Running total for multiple columns, reset and special

    Order by MyOrder, CompletedDate_Year

    I think that's what you need at the end there... you've got the data, you just need it sorted by those fields...

    And then you need to shoot the person who asked for that requirement. That flies in the face of a running total and will make it harder to read. But, eh... not my circus as they say.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,163

    Re: SQL - Running total for multiple columns, reset and special

    I'm not even going to pretend understanding a word of that requirement.....

    As for the running total: Has MSSQL functions like Lead and Lag?
    Those are usually used for stuff like that
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    428

    Re: SQL - Running total for multiple columns, reset and special

    Quote Originally Posted by techgnome View Post
    Order by MyOrder, CompletedDate_Year

    I think that's what you need at the end there... you've got the data, you just need it sorted by those fields...

    And then you need to shoot the person who asked for that requirement. That flies in the face of a running total and will make it harder to read. But, eh... not my circus as they say.


    -tg
    Thank you, that did the trick. I was doing the opposite "Order by CompletedDate_Year,MyOrder" and crying

    Shooting that person is in progress, I just need to make sure it will look like suicide.

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    428

    Re: SQL - Running total for multiple columns, reset and special

    Quote Originally Posted by Zvoni View Post
    I'm not even going to pretend understanding a word of that requirement.....

    As for the running total: Has MSSQL functions like Lead and Lag?
    Those are usually used for stuff like that
    I don't think that my database has those functions; I have tried to just write a dummy query with those and got an error that that re not recognised.

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    428

    Re: [RESOLVED] SQL - Running total for multiple columns, reset and special

    OK
    The query I use up there takes a very long time (3 second for 65 records, when comparing weeks of different years) to process. Any idea why and if we have a better approach?

    Edit: no reason to panic. As usual, I was complicating the stuff. A closer look showed that I can actually achive the same result much simpler:

    Code:
    	Select Distinct CompletedDate_Year,DATEPART(mm, CAST(CompletedDate_MonthName + '1900' AS DATETIME)) As MyOrder, CompletedDate_MonthName AS Original, 
    	 Convert(nvarchar(10), CompletedDate_Year) + '-' +
    	Convert(nvarchar(10), CompletedDate_MonthName) As CompletedDate_MonthName, 
    	Sum(CAST(Sum(Case When Classification ='Major' then 1 else 0 End) As int)) OVER (PARTITION BY CompletedDate_Year  Order by CompletedDate_Year,DATEPART(mm, CAST(CompletedDate_MonthName + '1900' AS DATETIME))) as Major,
    Sum(CAST(Sum(Case When Classification ='Minor' then 1 else 0 End) As int)) OVER (PARTITION BY CompletedDate_Year Order by CompletedDate_Year,DATEPART(mm, CAST(CompletedDate_MonthName + '1900' AS DATETIME))) as Minor,
    Sum(CAST(Sum(Case When Has_SR ='Yes' then 1 else 0 End) As int)) OVER (PARTITION BY CompletedDate_Year Order by CompletedDate_Year,DATEPART(mm, CAST(CompletedDate_MonthName + '1900' AS DATETIME))) as Has_SR  
    	 FROM [DMS_DV_Data_NEW] 
    	 Where  Classification In ('Minor','Major','Unclassified') And 
    	 IS_PLANNED_DEVIATION = 'NO'   And 
    	 ( Format([COMPLETED_DATE],'yyyy-MM-dd')  >= '2019-01-01' And 
    	 Format([COMPLETED_DATE],'yyyy-MM-dd')  < '2020-03-25')  And 
    	 Staus = 'Closed' And 
    	 Phase <> 'Cancelled' 
    	 Group by CompletedDate_Year,CompletedDate_MonthName 
    	 Order by MyOrder, CompletedDate_Year
    Thanks for looking into it.
    Last edited by Grand; Mar 26th, 2020 at 07:28 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width