The T-SQL code is as follows, Still work in progress so bit messy.
Declare @StartDate13 datetime, @EndDate datetime
select @StartDate13 = dateadd(mm,datediff(mm,0,DateAdd (mm,-13,getdate())),0)
select @EndDate = DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)-1
print @EndDate
print @StartDate13
Declare @period1 varchar(6)
Select @period1 = year(dateadd(month,-13,getdate())) *100 + month(dateadd(month,-13,getdate()))
Declare @counter int
Declare @SOKEY int
DEclare @ITEMID varchar(30)
Declare @SOLINEKEY int
Declare @DESC varchar(40)
Declare @SOSUM Table
(
[SOKEY]int,
[SOLINEKEY] int,
[ITEM] varchar(30),
[DESC] varchar(40),
[QTYORD] Decimal(16,8),
[QTYSHIPPED] Decimal (16,8),
[PERIOD] varchar(6)
)
Declare @SOSUMPeriod Table
(
[SOKEY]int,
[SOLINEKEY] int,
[ITEM] varchar(30),
[DESC] varchar(40),
[QTYORD] Decimal(16,8),
[QTYSHIPPED] Decimal (16,8),
[BALANCE] Decimal (16,8),
[PERIOD] varchar(6)
)
Declare @SOBalPeriod Table
(
[SOKEY]int,
[SOLINEKEY] int,
[ITEM] varchar(30),
[DESC] varchar(40),
[QTYORD] Decimal(16,8),
[QTYSHIPPED] Decimal (16,8),
[BALANCE] Decimal (16,8),
[PERIOD] varchar(6)
)
/* Add the Sales Orders Created within date range
(This is seperate to ensure we have open order balance and that we
do not miss orders that have no shipments*/
Insert into @SOSUM
Select SO.SoKey, SL.SoLineKey, IM.ItemID, SL.Description, SD.QtyOrd, 0, year(SO.createdate)*100 + month(SO.createdate) from dbo.tsoSalesOrder as SO
left outer join dbo.tsoSOLine as SL on SL.Sokey = SO.Sokey
left outer join dbo.tsoSOLineDist as SD on SL.SOLineKey = SD.SoLineKey
left outer join dbo.timItem as IM on IM.ItemKey = SL.ItemKey
where SO.CompanyId = 'USA' and So.CreateDate >= @StartDate13 and So.CreateDate <= @EndDate
and SL.SOLinekey is not null
/* Add the Sales Order Shipments Created within Date Range */
Insert into @SOSUM
Select SO.SoKey, SL.SoLineKey, IM.ItemID, SL.Description, 0, SLD.QtyShipped, year(SHL.Shipdate)*100 + month(SHL.Shipdate) from dbo.tsoSalesOrder as SO
left outer join dbo.tsoSOLine as SL on SL.Sokey = SO.Sokey
left outer join dbo.tsoSOLineDist as SD on SL.SOLineKey = SD.SoLineKey
left outer join dbo.tsoShipLineDist as SLD on SD.SOLineDistKey = SLD.SOLineDistKey
left outer join dbo.tsoShipLine as SHL on SLD.ShipLineKey = SHL.ShipLineKey
left outer join dbo.timItem as IM on IM.ItemKey = SL.ItemKey
where SO.CompanyId = 'USA' and So.CreateDate >= @StartDate13 and So.CreateDate <= @EndDate
and SLD.QTYShipped is not null
--select ss.*,SO.Tranid from @SOSUM as SS
--left outer join dbo.tsoSalesOrder as SO on SS.Sokey = So.SOKEY
--order by SS.sokey
/* Reduce down to one transaction per period/Sales Line */
Insert into @SOSUMPeriod
Select SOKey, SOLINEKEY,ITEM,[DESC],sum(QtyOrd), Sum(QtyShipped),
(Select sum(QtyOrd) - Sum(QTyShipped) from @SOSUM as xSS
where xSS.SOLINEKEY = SS.SOLineKey and xSS.Period <= SS.Period),
Period
from @SOSUM as SS
group by SOKey, SOLINEKEY,ITEM,[DESC],PERIOD
Select * from @SOSUMPeriod
where SOKey = 14023 -- Limit to one order for testing
REsult Set
SOKEY SOLINEKEY ITEM DESCRIPTION ORDER QTY SHIPPED QTY BALANCE PERIOD
1402323012ECL-2055 "PANDROL" CLIPS - E2055600100.0000000 0.00000000600100.00000000200802
1402323012ECL-2055 "PANDROL" CLIPS - E20550.0000000050000.00000000550100.00000000200803
1402323012ECL-2055 "PANDROL" CLIPS - E20550.0000000025000.00000000525100.00000000200805
1402323012ECL-2055 "PANDROL" CLIPS - E20550.0000000050000.00000000475100.00000000200806
1402323012ECL-2055 "PANDROL" CLIPS - E20550.0000000025000.00000000450100.00000000200807
I woud like to create extra entries in here for period 200801, 200804, 200808