asked on

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

HainKurt

what result do you get from your query above... it is too complicated :)

Shaun Kline

And what result are you expecting?

rsaphier

ASKER

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.

Shaun Kline

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.

Shaun Kline

Another item...
Does your Cumulative Total query need to filter the data similar to you main query (i.e. Legacy = 'bhi')?

HainKurt

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

HainKurt

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

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rsaphier

ASKER

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!

rsaphier

ASKER

Thank you very much!!