SQL Sum by Position Rolling Date Total

Question

I'm hoping someone may be able to help me with what should be a fairly straight forward query but has me stumped.

I have written the below query

SELECT a.post_id, 
       a.date_from, 
       a.date_to, 
       b.description, 
       b.parttime_pct                       AS budget_hours, 
       Sum(a.parttime_pct)                  AS contract_hours, 
       b.parttime_pct - Sum(a.parttime_pct) AS remainder 
FROM   aprresourcepost a 
       LEFT OUTER JOIN aprpost b 
                    ON(( a.client = b.client 
                         AND a.post_id = b.post_id )) 
       INNER JOIN ahsresources c 
               ON(( c.client = a.client 
                    AND c.resource_id = a.resource_id )) 
       LEFT OUTER JOIN agladdress d 
                    ON(( 'C0' = d.attribute_id 
                         AND c.client = d.client 
                         AND c.resource_id = d.dim_value 
                         AND d.address_type = '1' )) 
       LEFT OUTER JOIN agldimvalue e 
                    ON(( e.client = b.client 
                         AND e.dim_value = b.dim_value )) 
WHERE  a.client = 'CI' 
       AND a.post_id = '14013' 
GROUP  BY a.post_id, 
          b.description, 
          b.parttime_pct, 
          a.parttime_pct, 
          a.date_from, 
          a.date_to

Which returns the below

post_id   date_from   date_to     descr   budget_hours    contract_hours    remainder

14013     2014-10-01  2099-12-31  Reg     140.00          105.00             35.00           
14013     2014-12-20  2099-12-31  Reg     140.00          140.00            105.00

What I would like this to show (if possible) is

post_id   date_from   date_to     descr   budget_hours    contract_hours    remainder

14013     2014-10-01  2099-12-31  Reg     140.00          105.00             35.00           
14013     2014-12-20  2099-12-31  Reg     140.00          140.00              0.00

This basically indicates that as of any date before the 20th December there is 35 hours remaining on the recommended number of hours (employees) for this role, however as of the 20th December this position is 100% filled, however as I want to include date from and date to the data is summed based on position and date from and date to, I guess I kinda need a rolling total

I know if I remove the date from the select and group by it will show the 140.00 140.00 however I need a way to separate out the dates.

Any help is greatly appreciated.

Thanks guys

I'm not sure how to do this with a single query. Perhaps you could calculate it with a stored proc: http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver — Jeremy Stein, Dec 15 '14 at 22:09

score 0 · Answer 1 · answered Dec 15 '14 at 22:48

Without data I found this hard to test. But here is my best shot. Using a CTE and ROW_NUMBER to provide a virtual row id (needs 2005 onwards) we self join to the previous row to accumulate the remainder:

WITH qry AS 
(
  SELECT ROW_NUMBER() OVER (ORDER BY date_from) AS row,
         a.post_id, 
         a.date_from, 
         a.date_to, 
         b.description, 
         b.parttime_pct                       AS budget_hours, 
         Sum(a.parttime_pct)                  AS contract_hours, 
         b.parttime_pct - Sum(a.parttime_pct) AS remainder 
  FROM   aprresourcepost a 
         LEFT OUTER JOIN aprpost b 
                      ON(( a.client = b.client 
                           AND a.post_id = b.post_id )) 
         INNER JOIN ahsresources c 
                 ON(( c.client = a.client 
                      AND c.resource_id = a.resource_id )) 
         LEFT OUTER JOIN agladdress d 
                      ON(( 'C0' = d.attribute_id 
                           AND c.client = d.client 
                           AND c.resource_id = d.dim_value 
                           AND d.address_type = '1' )) 
         LEFT OUTER JOIN agldimvalue e 
                      ON(( e.client = b.client 
                           AND e.dim_value = b.dim_value )) 
  WHERE  a.client = 'CI' 
         AND a.post_id = '14013' 
  GROUP  BY a.post_id, 
            b.description, 
            b.parttime_pct, 
            a.parttime_pct, 
            a.date_from, 
            a.date_to 
)
SELECT
  qry2.post_id,
  qry2.date_from,
  qry2.date_to,
  qry2.description,
  qry2.budget_hours,
  qry2.contract_hours,
  qry2.budget_hours - (qry2.remainder + ISNULL(qry1.remainder, 0)) AS Cumulative_Remainder
FROM
  qry qry2
  LEFT JOIN qry qry1 ON qry1.row = qry2.row - 1

score 0 · Answer 2 · answered Dec 16 '14 at 01:36

All you need to do is make your query as subquery and find remainder in outer query

select *,
budget_hours- contract_hours AS remainder 
from(
SELECT a.post_id, 
       a.date_from, 
       a.date_to, 
       b.description, 
       b.parttime_pct                       AS budget_hours, 
       Sum(a.parttime_pct)                  AS contract_hours
FROM   aprresourcepost a 
       LEFT OUTER JOIN aprpost b 
                    ON(( a.client = b.client 
                         AND a.post_id = b.post_id )) 
       INNER JOIN ahsresources c 
               ON(( c.client = a.client 
                    AND c.resource_id = a.resource_id )) 
       LEFT OUTER JOIN agladdress d 
                    ON(( 'C0' = d.attribute_id 
                         AND c.client = d.client 
                         AND c.resource_id = d.dim_value 
                         AND d.address_type = '1' )) 
       LEFT OUTER JOIN agldimvalue e 
                    ON(( e.client = b.client 
                         AND e.dim_value = b.dim_value )) 
WHERE  a.client = 'CI' 
       AND a.post_id = '14013' 
GROUP  BY a.post_id, 
          b.description, 
          b.parttime_pct, 
          a.parttime_pct, 
          a.date_from, 
          a.date_to ) A

score 0 · Answer 3 · answered Dec 16 '14 at 04:52

Is there a reason you are grouping by a.parttime_pct ? What happens if you remove that from the group by clause? It's not being brought back as a column in the select statement, so you don't need to group by it.

SELECT a.post_id, 
   a.date_from, 
   a.date_to, 
   b.description, 
   b.parttime_pct                       AS budget_hours, 
   Sum(a.parttime_pct)                  AS contract_hours, 
   b.parttime_pct - Sum(a.parttime_pct) AS remainder 
FROM   aprresourcepost a 
   LEFT OUTER JOIN aprpost b 
                ON(( a.client = b.client 
                     AND a.post_id = b.post_id )) 
   INNER JOIN ahsresources c 
           ON(( c.client = a.client 
                AND c.resource_id = a.resource_id )) 
   LEFT OUTER JOIN agladdress d 
                ON(( 'C0' = d.attribute_id 
                     AND c.client = d.client 
                     AND c.resource_id = d.dim_value 
                     AND d.address_type = '1' )) 
   LEFT OUTER JOIN agldimvalue e 
                ON(( e.client = b.client 
                     AND e.dim_value = b.dim_value )) 
WHERE  a.client = 'CI' 
   AND a.post_id = '14013' 
GROUP  BY a.post_id, 
      b.description, 
      b.parttime_pct, 
      a.date_from, 
      a.date_to

SQL Sum by Position Rolling Date Total

3 Answers3