Summation of historical value booked time (single effective date)

Question

We have a time management system when our employees or contractors (resources) enter during business hours, and we get costs for this. I have a table with historical costs:

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

So, I have one date field that marks the effective date. If we have a record that

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

and I add a record that

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

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

Assuming my timeline is as follows

 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 entries in 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 would like to receive a request that returns the total cost of resources

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

Can someone provide a sample SQL query? I know that this example does not use TimeType (that is, it is always "Normal"), but I would like to see how it is also considered

I cannot change the database structure. Thank you very much 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 

Answer 2

You have a cost table, which some say will be a slowly changing quantity. Firstly, it will help to get an effective and final date for the cost table. We can get this by making an independent association and group:

 with costs as (select c.ResourceCode, c.EffectiveDate as effdate, dateadd(day, -1, min(c1.EffectiveDate)) as endDate, datediff(day, c.EffectiveDate, c1.EffectiveDate) - 1 as Span from ResourceTimeTypeCost c left outer join ResourceTimeTypeCost c1 group by c.ResourceCode, c.EffectiveDate ) 

Although you say that you cannot change the structure of the table when you have a slowly changing size, the effective and final date is good practice.

Now you can use this information with TimeEntered as follows:

 select te.*, c.CostRate * te.HoursWorked as dayCost from TimeEntered te join Costs c on te.ResouceCode = c.ResourceCode and te.TimeEntered between c.EffDate and c.EndDate 

Summing up the resource for a given time range, the full request will look like this:

  with costs as (select c.ResourceCode, c.EffectiveDate as effdate, dateadd(day, -1, min(c1.EffectiveDate)) as endDate, datediff(day, c.EffectiveDate, c1.EffectiveDate) - 1 as Span from ResourceTimeTypeCost c left outer join ResourceTimeTypeCost c1 group by c.ResourceCode, c.EffectiveDate ), te as (select te.*, c.CostRate * te.HoursWorked as dayCost from TimeEntered te join Costs c on te.ResouceCode = c.ResourceCode and te.TimeEntered between c.EffDate and c.EndDate ) select te.ResourceCode, sum(dayCost) from te where te.TimeEntered >= <date1> and te.TimeEntered < <date2> 

Answer 3

You may try. CROSS APPLY will find the first ResourceTimeTypeCost with an old or equal date and the same ResourceCode and TimeTypeCode code as the current TimeEntered entry.

 SELECT te.ResourceCode, te.TimeTypeCode, te.ProjectCode, te.ActivityCode, te.TimeEnteredDate, te.HoursWorked, te.HoursWorked * rttc.CostRate Cost FROM TimeEntered te CROSS APPLY ( -- First one only SELECT top 1 CostRate FROM ResourceTimeTypeCost WHERE te.ResourceCode = ResourceTimeTypeCost.ResourceCode AND te.TimeTypeCode = ResourceTimeTypeCost.TimeTypeCode AND te.TimeEnteredDate >= ResourceTimeTypeCost.EffectiveDate -- By most recent date ORDER BY ResourceTimeTypeCost.EffectiveDate DESC ) rttc 

Unfortunately, I can no longer find an article on msdn, so the blog is in the link above.

Live test @Sql Fiddle .