Summing historic cost rates over booked time (single effective date)

Question

We have a time management system where our employees or contractors (resources) enter the hours they have worked, and we derive a cost for it. I have a table with the historic costs:

CREATE TABLE ResourceTimeTypeCost (
 ResourceCode VARCHAR(32),
 TimeTypeCode VARCHAR(32),
 EffectiveDate DATETIME,
 CostRate DECIMAL(12,2)
)

So I have one date field which marks the effective date. If we have a record which is

('ResourceA', 'Normal', '2012-04-30', 40.00)

and I add a record which is

('ResourceA', 'Normal', '2012-05-04', 50.00) 

So all hours entered between the 30th April and the 3rd of May will be at £40.00, all time after midnight on the 4th will be at £50.00. I understand this in principle but how do you write a query expressing this logic?

Assuming my time table looks like the below

CREATE TABLE TimeEntered (
 ResourceCode VARCHAR(32),
 TimeTypeCode VARCHAR(32),
 ProjectCode VARCHAR(32),
 ActivityCode VARCHAR(32),
 TimeEnteredDate DATETIME,
 HoursWorked DECIMAL(12,2)
)

If I insert the following records into the TimeEntered table

('ResourceA','Normal','Project1','Management1','2012-04-30',7.5)
('ResourceA','Normal','Project1','Management1','2012-05-01',7.5)
('ResourceA','Normal','Project1','Management1','2012-05-02',7.5)
('ResourceA','Normal','Project1','Management1','2012-05-03',7.5)
('ResourceA','Normal','Project1','Management1','2012-05-04',7.5)
('ResourceA','Normal','Project1','Management1','2012-05-07',7.5)
('ResourceA','Normal','Project1','Management1','2012-05-08',7.5)

I'd like to get a query that returns the total cost by resource

So in the case above it would be 'ResourceA', (4 * 7.5 * 40) + (3 * 7.5 * 50) = 2325.00

Can anyone provide a sample SQL query? I know this example doesn't make use of TimeType (i.e. it's always 'Normal') but I'd like to see how this is dealt with as well

I can't change the structure of the database. Many thanks in advance

Answer 1

IF OBJECT_ID ('tempdb..#ResourceTimeTypeCost') IS NOT NULL DROP TABLE #ResourceTimeTypeCost
CREATE TABLE #ResourceTimeTypeCost (  ResourceCode VARCHAR(32),  TimeTypeCode VARCHAR(32),  EffectiveDate DATETIME,  CostRate DECIMAL(12,2) ) 
INSERT INTO #ResourceTimeTypeCost 
SELECT 'ResourceA' as resourcecode, 'Normal' as timetypecode, '2012-04-30' as effectivedate, 40.00 as costrate
UNION ALL
SELECT 'ResourceA', 'Normal', '2012-05-04', 50.00

IF OBJECT_ID ('tempdb..#TimeEntered') IS NOT NULL DROP TABLE #TimeEntered
CREATE TABLE #TimeEntered (  ResourceCode VARCHAR(32),  TimeTypeCode VARCHAR(32),  ProjectCode VARCHAR(32),  ActivityCode VARCHAR(32),  TimeEnteredDate DATETIME,  HoursWorked DECIMAL(12,2) ) 
INSERT INTO #TimeEntered 
SELECT 'ResourceA','Normal','Project1','Management1','2012-04-30',7.5 
UNION ALL SELECT 'ResourceA','Normal','Project1','Management1','2012-05-01',7.5 
UNION ALL SELECT 'ResourceA','Normal','Project1','Management1','2012-05-02',7.5 
UNION ALL SELECT 'ResourceA','Normal','Project1','Management1','2012-05-03',7.5 
UNION ALL SELECT 'ResourceA','Normal','Project1','Management1','2012-05-04',7.5 
UNION ALL SELECT 'ResourceA','Normal','Project1','Management1','2012-05-07',7.5 
UNION ALL SELECT 'ResourceA','Normal','Project1','Management1','2012-05-08',7.5 

;with ranges as
(
select 
resourcecode 
,TimeTypeCode
,EffectiveDate
,costrate
,row_number() OVER (PARTITION BY resourcecode,timetypecode ORDER BY effectivedate ASC) as row
from #ResourceTimeTypeCost 
)
,ranges2 AS
(
SELECT 
r1.resourcecode 
,r1.TimeTypeCode
,r1.EffectiveDate
,r1.costrate
,r1.effectivedate as start_date
,ISNULL(DATEADD(ms,-3,r2.effectivedate),GETDATE()) as end_date
FROM ranges r1
LEFT OUTER JOIN ranges r2 on r2.row = r1.row + 1 --joins onto the next date row
                    AND r2.resourcecode = r1.resourcecode 
                    AND r2.TimeTypeCode = r1.TimeTypeCode
)
SELECT 
tee.resourcecode
,tee.timetypecode
,tee.projectcode
,tee.activitycode
,SUM(ranges2.costrate * tee.hoursworked) as total_cost
FROM #TimeEntered tee
INNER JOIN ranges2 ON tee.TimeEnteredDate >= ranges2.start_date
                    AND tee.TimeEnteredDate <= ranges2.end_date
                    AND tee.resourcecode = ranges2.resourcecode
                    AND tee.timetypecode = ranges2.TimeTypeCode
GROUP BY tee.resourcecode
,tee.timetypecode
,tee.projectcode
,tee.activitycode