I'm trying to take salary data from our financial system and break it down into daily 'costs' so that it lines up with our hourly staff for better data analytics.
Right now, salary data looks (something) like this:
TRXBEGDT trxenddt StoreID LaborHours LaborCost
----------------------------------------------
1/12/2020 1/18/2020 1000 40 2000
1/12/2020 1/18/2020 2300 80 4000
1/20/2020 1/20/2020 1000 8 400
1/20/2020 1/20/2020 2300 16 800
1/19/2020 1/25/2020 1000 32 1600
1/19/2020 1/25/2020 2300 64 3200
I need to split the LaborHours and LaborCost over the days between trxbegdt and trxenddt, which is generally one day or seven, but not necessarily.
I'd love to offer that I have a great starting point, but I'm stumped as to where to start.
The end result would look like this:
Date StoreID LaborHours LaborCost
-------------------------------------
1/12/2020 1000 5.71 285.5
1/13/2020 1000 5.71 285.5
1/14/2020 1000 5.71 285.5
1/15/2020 1000 5.71 285.5
1/16/2020 1000 5.71 285.5
1/17/2020 1000 5.71 285.5
1/18/2020 1000 5.71 285.5
1/19/2020 1000 4.57 228.5
1/20/2020 1000 12.57 628.5
1/21/2020 1000 4.57 228.5
1/22/2020 1000 4.57 228.5
1/23/2020 1000 4.57 228.5
1/24/2020 1000 4.57 228.5
1/25/2020 1000 4.57 228.5
1/12/2020 2300 11.43 571.5
1/13/2020 2300 11.43 571.5
1/14/2020 2300 11.43 571.5
1/15/2020 2300 11.43 571.5
1/16/2020 2300 11.43 571.5
1/17/2020 2300 11.43 571.5
1/18/2020 2300 11.43 571.5
1/19/2020 2300 9.14 457
1/20/2020 2300 25.14 1257
1/21/2020 2300 9.14 457
1/22/2020 2300 9.14 457
1/23/2020 2300 9.14 457
1/24/2020 2300 9.14 457
1/25/2020 2300 9.14 457
I do realize that this isn't making the data 100% accurate, but it's WAY more accurate than having a single day take the hit for all salaried employees.
Any help is appreciated.
One option uses a recursive query:
with cte as (
select
trxbedgt dt,
storeid,
1.0 * laborhours / datediff(day, trxbedgt, trxenddt) laborhours,
1.0 * laborcost / datediff(day, trxbedgt, trxenddt) laborcost,
trxenddt max_dt
from mytable
union all
select dateadd(day, 1, dt), store_id, laborhours, laborcost, max_dt
from cte
where dt < max_dt
)
select dt, storeid, laborhours, laborcost
from cte
order by store, dt
This would work with date ranges of variable length. If they are fixed, then cross apply is simpler:
select x.dt, t.store_id,
1.0 * t.laborhours / datediff(day, t.trxbedgt, t.trxenddt) laborhours,
1.0 * t.laborcost / datediff(day, t.trxbedgt, t.trxenddt) laborcost
from mytable t
cross apply (values
(trxbedgt),
(datead(day, 1, trxbedgt)),
(datead(day, 2, trxbedgt))
...
(datead(day, 6, trxbedgt))
) x(dt)
order by t.store_id, x.dt