Results 1 to 8 of 8
  1. #1
    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 
      wvpropertyheader AS prop
      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. #2
    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
    To show Original and full Article :==>>
    MS SQL ::

  3. #3
    Join Date
    Oct 2011
    Location
    Pittsburgh, PA
    Posts
    13
    That code adds separate rows. I want to add a column.

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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. #5
    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. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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. #7
    Join Date
    Oct 2011
    Location
    Pittsburgh, PA
    Posts
    13

    Thumbs up 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 
         wvpropheader AS prop
         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 
                          wvpropheader AS prop
                          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. #8
    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
  •