dcsimg
Results 1 to 6 of 6

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

Threaded View

  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.

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