Solved

SQL 2008 Running Summary

Posted on 2011-02-17
5
301 Views
Last Modified: 2012-06-21
Hi Experts

The attached snippet produces a five column table that looks like the attached Excel file.

I would like to get a running total for each EOM_As_Of_Dt based on the data produced from the attached snippet.  I have never done this in SQL before and need to know how it can be done.  I thought ROLLUP or CUBE was the answer, but did not know how to apply it.

The Excel file shows what I am trying to get to.

Thanks,

Hubbs
SELECT     p.EOM_As_Of_Dt, p.DueDtAdvance, COUNT(p.VCC_LnNum) AS Units, SUM(p.CURR_UPB) AS Day_Tot_UPB, eomtots.EOM_TOT_UPB, (SUM(p.CURR_UPB)/eomtots.EOM_TOT_UPB) AS Day_Pct      
FROM       (SELECT  pmnts.EOM_As_Of_Dt, pmnts.VCC_LnNum, Hist_EOM_to_Daily.Guarantor_Last, Hist_EOM_to_Daily.Borrower, pmnts.DueDtAdvance, 
                    Hist_EOM_to_Daily.CURR_STATUS, Hist_EOM_to_Daily.CURR_UPB, Hist_EOM_to_Daily.LAST_PMT_REC, Hist_EOM_to_Daily.DUE_DT_CHANGE
            FROM    Hist_EOM_to_Daily RIGHT OUTER JOIN
                          (SELECT     DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,BEG_AS_OF_DT)+2,0)) As EOM_As_Of_Dt,
                                      VCC_LnNum, MIN(CURR_AS_OF_DT) AS DueDtAdvance
                           FROM          Hist_EOM_to_Daily AS Hist_EOM_to_Daily_1
                           WHERE      (BEG_AS_OF_DT > '10/1/2010') AND (DUE_DT_CHANGE IN ('Improved'))
                           GROUP BY   DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,BEG_AS_OF_DT)+2,0)), VCC_LnNum) AS pmnts 
                                       ON Hist_EOM_to_Daily.VCC_LnNum = pmnts.VCC_LnNum 
                                       AND Hist_EOM_to_Daily.CURR_AS_OF_DT = pmnts.DueDtAdvance 
                                       AND DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,Hist_EOM_to_Daily.BEG_AS_OF_DT)+2,0)) = pmnts.EOM_As_Of_Dt) AS p LEFT OUTER JOIN
                                        (SELECT  AS_OF_DT, SUM(UPB) AS EOM_TOT_UPB
                                         FROM    HIST_EOM_v2
                                         GROUP BY AS_OF_DT) AS eomtots 
                                           ON p.EOM_As_Of_Dt = eomtots.AS_OF_DT
GROUP BY p.EOM_As_Of_Dt, p.DueDtAdvance, eomtots.EOM_TOT_UPB

Open in new window

Book2.xlsx
0
Comment
Question by:Hubbsjp21
  • 3
5 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 34921144
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 34922476
check this.
;with cte as (
SELECT     p.EOM_As_Of_Dt, p.DueDtAdvance, COUNT(p.VCC_LnNum) AS Units, SUM(p.CURR_UPB) AS Day_Tot_UPB, eomtots.EOM_TOT_UPB, (SUM(p.CURR_UPB)/eomtots.EOM_TOT_UPB) AS Day_Pct      
FROM       (SELECT  pmnts.EOM_As_Of_Dt, pmnts.VCC_LnNum, Hist_EOM_to_Daily.Guarantor_Last, Hist_EOM_to_Daily.Borrower, pmnts.DueDtAdvance, 
                    Hist_EOM_to_Daily.CURR_STATUS, Hist_EOM_to_Daily.CURR_UPB, Hist_EOM_to_Daily.LAST_PMT_REC, Hist_EOM_to_Daily.DUE_DT_CHANGE
            FROM    Hist_EOM_to_Daily RIGHT OUTER JOIN
                          (SELECT     DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,BEG_AS_OF_DT)+2,0)) As EOM_As_Of_Dt,
                                      VCC_LnNum, MIN(CURR_AS_OF_DT) AS DueDtAdvance
                           FROM          Hist_EOM_to_Daily AS Hist_EOM_to_Daily_1
                           WHERE      (BEG_AS_OF_DT > '10/1/2010') AND (DUE_DT_CHANGE IN ('Improved'))
                           GROUP BY   DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,BEG_AS_OF_DT)+2,0)), VCC_LnNum) AS pmnts 
                                       ON Hist_EOM_to_Daily.VCC_LnNum = pmnts.VCC_LnNum 
                                       AND Hist_EOM_to_Daily.CURR_AS_OF_DT = pmnts.DueDtAdvance 
                                       AND DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,Hist_EOM_to_Daily.BEG_AS_OF_DT)+2,0)) = pmnts.EOM_As_Of_Dt) AS p LEFT OUTER JOIN
                                        (SELECT  AS_OF_DT, SUM(UPB) AS EOM_TOT_UPB
                                         FROM    HIST_EOM_v2
                                         GROUP BY AS_OF_DT) AS eomtots 
                                           ON p.EOM_As_Of_Dt = eomtots.AS_OF_DT
GROUP BY p.EOM_As_Of_Dt, p.DueDtAdvance, eomtots.EOM_TOT_UPB),
cte2 as (select *,ROW_NUMBER() over (order by EOM_As_Of_Dt,DueDtAdvance) rn from cte)
select *,(select SUM(c2.Day_Tot_UPB) from cte2 c2 where c2.rn <= c1.rn) [Cume of Day_Tot_UPB by EOM_As_Of_Dt]
  from cte2 c1

Open in new window

0
 

Author Comment

by:Hubbsjp21
ID: 35020915
Sharath,

Sorry it took so long to get back to you - I went on vacation.

See attached result set from your code.  It looks good, except that the rn & Cume Total fields should start over at each new EOM_As_Of_Dt.

I am going to first understand what you did, then try to change it myself, but would appreciate it if you could help with the change.

Thanks - Hubbs


sample-result.xlsx
0
 

Author Comment

by:Hubbsjp21
ID: 35022659
Okay - I figured it out.

Changed LINE 19 to:

cte2 as (SELECT *,ROW_NUMBER() OVER (PARTITION BY EOM_As_Of_Dt ORDER BY DueDtAdvance) As rn from cte)

Changed LINE 20 to:

select *,(select SUM(c2.Day_Tot_UPB) from cte2 c2 where c2.rn <= c1.rn AND c2.EOM_As_Of_Dt = c1.EOM_As_Of_Dt) [Cume of Day_Tot_UPB by EOM_As_Of_Dt]

Thanks for your help!

Hubbs


0
 

Author Closing Comment

by:Hubbsjp21
ID: 35022678
I only said partially because I may have given bad directions.  I am confident that the expert would have given a complete solution had I been clearer in my instructions.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question