Good evening
Looking a bit oh help with the dbr.running_total usage.
The following code runs as expected if I remove the insert into statement.
Leaving the insert in the code runs but the Cum_StkChng values are not cumulative.
Is it possible to use dbr.running_total with insert? Really hope so 🤞
Thanks
Jake
select 'dbr.running_total', 'Cum_StkChng';
with cte_items as
(select distinct ItemCode as iCode
from #wksales)
insert into #stock1
select d.CurDate as sDate,
b.[Code] as sItemCode,
sum(iif(a.[TransactionTypeID] in (0,1,2,3,4,14,16,17,19),a.Quantity,a.Quantity*-1)) as StkChng,
sum(iif(a.[TransactionTypeID] in (0,1,2,3,4,14,16,17,19),a.Quantity,a.Quantity*-1)) as Cum_ StkChng
from [SAGE200].[200_Consumer_Live].[dbo].[TransactionHistory] a
join [SAGE200].[200_Consumer_Live].[dbo].[StockItem] b on a.ItemID=b.ItemID
join cte_items c on b.[Code]=c.iCode
join [pharma].dbo.tblDates d on a.TransactionDate=d.CurDate
where a.TransactionDate >=dateadd(month, datediff(month, 0, @sdate), 0)
and a.[TransactionTypeID] not in (4,9,11,12,13,21,22,23,24,25)
group by d.CurDate,b.[Code];
