Dividing weekly salary info into daily

Question

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.

Please don't post images of your data, take the time to post it as tabular formatted `text`, or (even better) as DDL and DML statements. Then we can consume it. Don't expect the users here to transcribe your data, as it's very unlikely they will; especially when it's far easier for you to copy your data and format it well. — Thom A, Sep 21 '20 at 19:59
Sorry, an image was the default paste so I just ran with it. I've updated (which a moderator may have done some of the leg work already). — SaintFrag, Sep 21 '20 at 20:11

score 2 · Accepted Answer · answered Sep 21 '20 at 19:58

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

I tested top one, as the second wouldn't work for my data. With a single, minor tweak, it works brilliantly. I just added a +1 in the denominator of the laborhours and laborcost calculations to correctly count the days. Also alleviates the divide by zero error if both beginning and end dates are the same (but should be one day). — SaintFrag, Sep 22 '20 at 12:14

Dividing weekly salary info into daily

1 Answers1