TSql - Unroll inventory transactions and compute daily average, Sql Server

TSql - Unroll inventory transactions and compute daily average

create table PartWhse (PartNum varchar(5) not null, OnHandQty int not null);
create table PartTran (PartNum varchar(5) not null, TranDate date not null, TranQty int not null);

insert into PartWhse (PartNum, OnHandQty)
values ('P1', 30), ('P2', 2);

insert into PartTran (PartNum, TranDate, TranQty)
values ('P1', '20160628', 5), ('P1', '20160626', 3), ('P1', '20160626', -1), ('P1', '20160615', 2), ('P2', '20160615', 1);

with trn as (
    select PartNum, TranDate, TranQty from PartTran
    union all
    select PartNum, cast('20160601' as date), 0 from PartWhse
    union all
    select PartNum, cast('20160630' as date), 0 from PartWhse
), qty as (
    select
        t.PartNum, t.TranDate,
        min(w.OnHandQty) + sum(t.TranQty) - sum(sum(t.TranQty)) over (partition by t.PartNum order by t.TranDate desc) as DailyOnHand,
        coalesce(lead(t.TranDate) over (partition by t.PartNum order by t.TranDate), dateadd(day, 1, t.TranDate)) as NextTranDate
        -- if lead() isn't available...
        -- coalesce(
        --    (select min(t2.TranDate) from trn as t2 where t2.PartNum = t.PartNum and t2.TranDate > t.TranDate),
        --    dateadd(day, 1, t.TranDate)
        -- ) as NextTranDate
    from PartWhse as w inner join trn as t on t.PartNum = w.PartNum
    where t.TranDate between '20160601' and '20160630'
    group by t.PartNum, t.TranDate
)
select PartNum, sum(datediff(day, TranDate, NextTranDate) * DailyOnHand) * 1.00 / sum(datediff(day, TranDate, NextTranDate)) as DailyAvg
from qty
group by PartNum;

-- works but unnecessarily complicated; there's really no need to densify the dates.
with z(n) as (
    select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5
), numbers(num) as (
    select z1.n * 6 + z0.n from z as z0 cross join z as z1 where z1.n * 6 + z0.n between 1 and 31
), trn as (
    select PartNum, TranDate, TranQty from PartTran
    union all
    select PartNum, cast('20160601' as date), 0 from PartWhse
), qty as (
    select
        t.PartNum, TranDate,
        min(w.OnHandQty) + sum(t.TranQty) - sum(sum(t.TranQty)) over (partition by t.PartNum order by t.TranDate desc) as DailyOnHand
    from PartWhse as w inner join trn as t on t.PartNum = w.PartNum
    where t.TranDate between '20160601' and '20160630'
    group by t.PartNum, t.TranDate
), dense as (
    select p.PartNum, dateadd(day, n.num - 1, '20160601') as Dt, q.DailyOnHand
    from
        (select num from numbers where num <= datepart(day, dateadd(month, 1, dateadd(day, -1, '20160601')))) as n
          cross join
        (select distinct PartNum from qty) as p
          left outer join
        qty as q
            on q.PartNum = p.PartNum and datepart(day, q.TranDate) = n.num
)
select d.PartNum, avg(coalesce(d.DailyOnHand, mrq.DailyOnHand) * 1.00)
from
    dense d outer apply (
        select max(q.TranDate) as MRTranDate from qty as q
        where q.PartNum = d.PartNum and q.TranDate < d.Dt and d.DailyOnHand is null
    ) as mr outer apply (
        select DailyOnHand from qty as q
        where q.PartNum = d.PartNum and q.TranDate = mr.MRTranDate
    ) as mrq
group by d.PartNum;

-- not sure if your query matches up to mine
declare @StartDate date = '20160601';
select 
    [PartTran].[PartNum] as [Part_PartNum],
    (max(PartWhse.OnHandQty)*datediff(day,@StartDate, getdate())+
         sum(PartTran.TranQty*datediff(day,@StartDate,PartTran.TranDate))) / DATEDIFF(day, @StartDate, getdate()) as [???]
    --,(WeightedSum/DATEDIFF(day, @StartDate, getdate())) as [Calculated_AverageOnHand]
from
    PartTran as PartTran
inner join PartWhse as PartWhse on 
    PartTran.PartNum = PartWhse.PartNum
group by [PartTran].[PartNum];


run \| edit \| history \| help	0