• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

SQL 2008 Running Summary

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
Hubbsjp21
Asked:
Hubbsjp21
  • 3
1 Solution
 
lcohanDatabase AnalystCommented:
0
 
SharathData EngineerCommented:
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
 
Hubbsjp21Author Commented:
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
 
Hubbsjp21Author Commented:
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
 
Hubbsjp21Author Commented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now