# Thread: Calculate Cumulative Daily Costs

1. Registered User
Join Date
Oct 2011
Location
Pittsburgh, PA
Posts
13

## Unanswered: Calculate Cumulative Daily Costs

The below code gives me the daily cost per property for each capital type. Now what I need is to add a column for the cumulative costs. I've seen similar problems online but those only involve columns from a single table.

I'd Appreciate any help with this that I can get.

Code:
```SELECT
prop.leasecode AS Property#
,prop.propname AS PropertyName
,CONVERT(VARCHAR(8),jr.dttmstart,1) AS ReportDate
,jc.code4 AS CapitalType
,ROUND(SUM(jc.cost),2) AS DailyCost
FROM
INNER JOIN wvjobreport AS jr ON
prop.idprop = jr.idprop
INNER JOIN wvjobreportcostgen AS jc ON
jr.idrec = jc.idrecparent
WHERE
prop.leasecode IS NOT NULL
AND prop.division = 'East'
AND prop.operator = 'Mickey & Donald'
AND jc.cost IS NOT NULL
AND prop.leasecode = '50303'    --(included for testing purposes)
GROUP BY
prop.leasecode
,prop.propname
,jc.code4
,CONVERT(VARCHAR(8),jr.dttmstart,1)
ORDER BY 2,3,4```
Property#...PropertyName...ReportDate...CapitalTyp e...DailyCost...Cum Cost
50303 .......Ellwood 595 ..... 7/5/11 ....... IDC ............ 3300 ...... 3300
50303 .......Ellwood 595 ..... 7/5/11 ....... TCC ............11000 ......11000
50303 ...... Ellwood 595 ..... 7/6/11 ....... IDC ............ 6500 ...... 9800
50303 .......Ellwood 595 ..... 7/6/11 ....... TCC ............15000 ......26000

2. Registered User
Join Date
Sep 2011
Posts
71
Ok ,Let's try method below :

Summarizing Data Using ROLLUP
The ROLLUP operator is useful in generating reports that contain subtotals and totals. The ROLLUP operator generates a result set that is similar to the result sets generated by the CUBE operator. For more information, see Summarizing Data Using CUBE.

The differences between CUBE and ROLLUP are:

CUBE generates a result set showing aggregates for all combinations of values in the selected columns.

ROLLUP generates a result set showing aggregates for a hierarchy of values in the selected columns.
For example, a simple table Inventory contains:

Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 124
Table Red 223
Chair Blue 101
Chair Red 210
This query generates a subtotal report:

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP

Item Color QtySum
-------------------- -------------------- --------------------------
Chair Blue 101.00
Chair Red 210.00
Chair ALL 311.00
Table Blue 124.00
Table Red 223.00
Table ALL 347.00
ALL ALL 658.00

(7 row(s) affected)
If the ROLLUP keyword in the query is changed to CUBE, the CUBE result set is the same, except these two additional rows are returned at the end:

ALL Blue 225.00
ALL Red 433.00
The CUBE operation generated rows for possible combinations of values from both Item and Color. For example, not only does CUBE report all possible combinations of Color values combined with the Item value Chair (Red, Blue, and Red + Blue), it also reports all possible combinations of Item values combined with the Color value Red (Chair, Table, and Chair + Table).

For each value in the columns on the right in the GROUP BY clause, the ROLLUP operation does not report all possible combinations of values from the column (or columns) on the left. For example, ROLLUP does not report all the possible combinations of Item values for each Color value.

The result set of a ROLLUP operation has functionality similar to that returned by a COMPUTE BY; however, ROLLUP has these advantages:

ROLLUP returns a single result set; COMPUTE BY returns multiple result sets that increase the complexity of application code.

ROLLUP can be used in a server cursor; COMPUTE BY cannot.

The query optimizer can sometimes generate more efficient execution plans for ROLLUP than it can for COMPUTE BY
MS SQL ::

3. Registered User
Join Date
Oct 2011
Location
Pittsburgh, PA
Posts
13

4. Registered User
Join Date
May 2009
Posts
509
sisterworlf_pa, I can't be sure because you example is not very clear but I think if you take the line that calculates the Daily Cost:

,ROUND(SUM(jc.cost),2) AS DailyCost

And just do the SUM, you will get Cummalitive cost:

,SUM(jc.cost) AS CumCost

5. Registered User
Join Date
Oct 2011
Location
Pittsburgh, PA
Posts
13
The ROUND() was just a carry over from another formula but isn't really needed for this code as all amounts are entered in the 999.99 format. Removing it just gives me the daily sum amount not the running cumulative amount.

6. Registered User
Join Date
May 2009
Posts
509
sisterwolf_pa, sorry. I mistook the , for a / (I plead old eyes...).

conceptually, one way to do this to join the result set to itself on key columns = key columns and ReportDate of original result > Report date of cummalitive result. Summing the values should give you a running total.

This Join could be done earlier in the process and summing the daily and cumulative values at the same time.

Unfortunately, I don't have time right now to write this but I hope this can give you enough direction to figure something out.

7. Registered User
Join Date
Oct 2011
Location
Pittsburgh, PA
Posts
13

## Solved

I finally found the way to get this to work using a CROSS JOIN.

Code:
```SELECT
D.Property# AS [Property #]
,D.PropName AS [Property Name]
,D.ReportDate AS [Report Date]
,D.BudgetType AS [Budget Type]
,D.CapitalType AS [Capital Type]
,D.DailyCost AS [Daily Cost]
,SUM(C.CumCost) AS [Cum Cost]
FROM
(SELECT
prop.leasecode AS Property#
,prop.propname AS PropName
,CONVERT(VARCHAR(8),jr.dttmstart,1) AS ReportDate
,CASE WHEN jc.code2 IN ('1210','1310') THEN 'Project'
WHEN jc.code2 IN ('1220','1320','1410') THEN 'Completion'
ELSE 'Other'
END AS BudgetType
,jc.code4 AS CapitalType
,ROUND(SUM(jc.cost),2) AS DailyCost
FROM
INNER JOIN wvjobreport AS jr ON
well.idprop = jr.idprop
INNER JOIN wvjobreportcostgen AS jc ON
jr.idrec = jc.idrecparent
WHERE
prop.leasecode IS NOT NULL
AND jc.cost IS NOT NULL
AND prop.leasecode = '50303'         -- selection for testing purposes
GROUP BY
prop.leasecode
,prop.propname
,jc.code4
,CONVERT(VARCHAR(8),jr.dttmstart,1)
,(CASE WHEN jc.code2 IN ('1210','1310') THEN 'Project'
WHEN jc.code2 IN ('1220','1320','1410') THEN 'Completion'
ELSE 'Other'
END)
) AS D
CROSS JOIN (SELECT
prop.leasecode AS Property#
,prop.propname AS PropName
,CONVERT(VARCHAR(8),jr.dttmstart,1) AS ReportDate
,jc.code4 AS CapitalType
,SUM(jc.cost) AS CumCost
FROM
INNER JOIN  wvjobreport AS jr ON
prop.idprop = jr.idprop
INNER JOIN wvjobreportcostgen AS jc ON
jr.idrec = jc.idrecparent
WHERE
prop.leasecode IS NOT NULL
AND jc.cost IS NOT NULL
AND prop.leasecode = '50303'         -- selection for testing purposes
GROUP BY
prop.leasecode
,prop.propname
,jr.idrec
,CONVERT(VARCHAR(8),jr.dttmstart,1)
,jc.code4
) AS C
WHERE
D.Property# = C.Property#
AND D.CapitalType = C.CapitalType
AND D.ReportDate >= C.ReportDate
GROUP BY
D.Property#
,D.PropName
,D.ReportDate
,D.BudgetType
,D.CapitalType
,D.DailyCost
ORDER BY
D.PropName
,D.ReportDate
,D.BudgetType
,D.CapitalType```

8. Registered User
Join Date
Sep 2011
Posts
71
Hello SisterWolf

Now I want to thank you very much ,Because i learned and took very good experiance from your questions ,Also From your reply on your self
Good Job

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•