August 6, 2008 at 11:47 am
Hi,
I am trying to setup some data in this case open sales orders showing open all orders by period.
Collecting the data is the easy part but what I am missing is the periods in between.
For example if I have a sales order setup in Period 200801 with the goods shipped out in 200804 I have records for periods 200801 and 200804.
How can I include records for periods 200802 and 200803 in my collection ?
Note this would need to be done for each and every sales order found in the collection.
Thanks in Advance.
August 6, 2008 at 12:01 pm
You could left join with a table of all periods, including 0s for NULLs (CASE or ISNULL).
Or you could "populate" 0 orders with the missing periods.
August 6, 2008 at 12:31 pm
I am thinking we need more information, tabble DDL (create statements), sample data (insert statements that can cut, paste, and run in SSMS), expected results based on the sample data, and what code you currently have done.
😎
August 6, 2008 at 1:23 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply