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

SQL Cummulative/Running Total - Urgent Reply Needed Please

I have an urgent request and I am struggling to determine why I am not seeing the expected cumulative totals in my sql view.   I am wanting to see a cumulative total on the alias "TRACK"  (concatenation of three fields) for each PERIOD of the view.   I'm seeing results in CumTotal but not the expected results.  What am I doing wrong in the code below?
SELECT     TOP (100) PERCENT SC_1.BusinessCategoryCode + SC_1.EntityCode + SC_1.BusinessAreaCode + LEFT(SC_1.Period, 4) AS Track, SC_1.Period, 
                      SC_1.BusinessCategoryCode, SC_1.EntityCode, SC_1.BusinessAreaCode, SC_1.Legacy, 'Promotion' AS Category, SUM(CASE WHEN sc_1.Loss = 0 AND
                       sc_1.JobTitle LIKE N'%FIELD ENG II%' AND sc_1.GradeRanking > 9 THEN 1 ELSE 0 END) AS Total,
                          (SELECT     SUM(CASE WHEN SC_2.Loss = 0 AND SC_2.JobTitle LIKE N'%FIELD ENG II%' AND SC_2.GradeRanking > 9 THEN 1 ELSE 0 END) 
                                                   AS Expr1
                            FROM          dbo.ScorecardWorkforceAllEmployees_NEW AS SC_2
                            WHERE      (Period <= SC_1.Period) AND (BusinessCategoryCode + EntityCode + BusinessAreaCode + LEFT(Period, 4) 
                                                   = SC_1.BusinessCategoryCode + SC_1.EntityCode + SC_1.BusinessAreaCode + LEFT(SC_1.Period, 4))) AS CumTotal
FROM         dbo.ScorecardWorkforceAllEmployees_NEW AS SC_1 INNER JOIN
                          (SELECT     TOP (100) PERCENT Legacy, PersonID, Period, JobTitle
                            FROM          dbo.ScorecardWorkforceAllEmployees_NEW
                            WHERE      (Loss = 0) AND (CHARINDEX('Field Eng I - ', JobTitle) = 1) AND (Legacy = N'BHI')
                            ORDER BY Legacy) AS FE_PRIORLEVEL ON SC_1.PersonID = FE_PRIORLEVEL.PersonID AND SC_1.PriorPeriod = FE_PRIORLEVEL.Period
GROUP BY SC_1.BusinessCategoryCode + SC_1.EntityCode + SC_1.BusinessAreaCode + LEFT(SC_1.Period, 4), SC_1.Period, SC_1.BusinessCategoryCode, 
                      SC_1.EntityCode, SC_1.BusinessAreaCode, SC_1.Legacy
HAVING      (SC_1.Legacy = N'bhi') AND (LEFT(SC_1.Period, 4) < YEAR(GETDATE())) AND (LEFT(SC_1.Period, 4) <> '2009') AND (SC_1.EntityCode = N'af')
ORDER BY SC_1.BusinessCategoryCode + SC_1.EntityCode + SC_1.BusinessAreaCode + LEFT(SC_1.Period, 4), SC_1.Period, SC_1.Legacy, 
                      SC_1.BusinessCategoryCode, SC_1.EntityCode, SC_1.BusinessAreaCode

Open in new window

0
rsaphier
Asked:
rsaphier
  • 4
  • 3
  • 3
1 Solution
 
HainKurtSr. System AnalystCommented:
what result do you get from your query above... it is too complicated :)
0
 
Shaun KlineLead Software EngineerCommented:
And what result are you expecting?
0
 
rsaphierAuthor Commented:
It's truly not that complicated of a view.  I've attached a snapshot of the expected -vs- current results.  I cannot figure out what is summing. CumTotal Results
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
Shaun KlineLead Software EngineerCommented:
The INNER JOIN to the subquery doesn't seem to have a purpose. No data is being returned, and none of the fields in that subquery are used in the WHERE or HAVING clause. My guess would be that it is causing the counts to balloon. If you only need that query to ensure people had taken the prerequisite class, use an EXISTS clause in your WHERE Statement.
0
 
Shaun KlineLead Software EngineerCommented:
Another item...
Does your Cumulative Total query need to filter the data similar to you main query (i.e. Legacy = 'bhi')?
0
 
HainKurtSr. System AnalystCommented:
try this:

with cte (existing query)
select c1.*, (select sum(total) from cte c2 where c2.track=c1.track and c1.period <=c2.period) cumtotalNew
from cte c1

if this one gives correct values, then you can get rid of cumtotal calculation from your original query
0
 
HainKurtSr. System AnalystCommented:
what i mean is this
with cte as
(
SELECT TOP (100) PERCENT sc_1.businesscategorycode + sc_1.entitycode + 
                         sc_1.businessareacode + 
                                                  LEFT(sc_1.period, 4) AS track,
                          sc_1.period, 
                         sc_1.businesscategorycode, 
                         sc_1.entitycode, 
                         sc_1.businessareacode, 
                         sc_1.legacy, 
                         'Promotion'                                   AS 
                         category, 
                         SUM(CASE 
                               WHEN sc_1.loss = 0 
                                    AND sc_1.jobtitle LIKE N'%FIELD ENG II%' 
                                    AND sc_1.graderanking > 9 THEN 1 
                               ELSE 0 
                             END)                                      AS total 
FROM   dbo.scorecardworkforceallemployees_new AS sc_1 
       INNER JOIN (SELECT TOP (100) PERCENT legacy, 
                                            personid, 
                                            period, 
                                            jobtitle 
                   FROM   dbo.scorecardworkforceallemployees_new 
                   WHERE  ( loss = 0 ) 
                          AND ( Charindex('Field Eng I - ', jobtitle) = 1 ) 
                          AND ( legacy = N'BHI' ) 
                   ORDER  BY legacy) AS fe_priorlevel 
         ON sc_1.personid = fe_priorlevel.personid 
            AND sc_1.priorperiod = fe_priorlevel.period 
GROUP  BY sc_1.businesscategorycode + sc_1.entitycode + sc_1.businessareacode + 
                    LEFT(sc_1.period, 4), 
          sc_1.period, 
          sc_1.businesscategorycode, 
          sc_1.entitycode, 
          sc_1.businessareacode, 
          sc_1.legacy 
HAVING ( sc_1.legacy = N'bhi' ) 
       AND ( LEFT(sc_1.period, 4) < YEAR(Getdate()) ) 
       AND ( LEFT(sc_1.period, 4) <> '2009' ) 
       AND ( sc_1.entitycode = N'af' ) 
ORDER  BY sc_1.businesscategorycode + sc_1.entitycode + sc_1.businessareacode + 
                    LEFT(sc_1.period, 4), 
          sc_1.period, 
          sc_1.legacy, 
          sc_1.businesscategorycode, 
          sc_1.entitycode, 
          sc_1.businessareacode  
)
select c1.*, (select sum(total) from cte c2 where c2.track=c1.track and c1.period <=c2.period) cumtotalNew
from cte c1

Open in new window

0
 
HainKurtSr. System AnalystCommented:
oops, query should be (line 50 changes only)

with cte as
(
same as above
)
select c1.*, (select sum(total) from cte c2 where c2.track=c1.track and c2.period <= c1.period) cumtotalNew
from cte c1
0
 
rsaphierAuthor Commented:
HainKurt,

Didn't work initialy but adding a sort order produced the exact results expected.  Thank you very much.  Can you explain to me why my code did not work and what yours is doing differently.

Many many thanks!
0
 
rsaphierAuthor Commented:
Thank you very much!!
0

Featured Post

Technology Partners: 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!

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