Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to insert missing periods ? Expand / Collapse
Author
Message
Posted Wednesday, August 6, 2008 11:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 30, 2013 10:17 PM
Points: 9, Visits: 57
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.



Post #547769
Posted Wednesday, August 6, 2008 12:01 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:53 PM
Points: 33,204, Visits: 15,353
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #547782
Posted Wednesday, August 6, 2008 12:31 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 23,299, Visits: 32,047
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.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #547804
Posted Wednesday, August 6, 2008 1:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 30, 2013 10:17 PM
Points: 9, Visits: 57
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
14023 23012 ECL-2055 "PANDROL" CLIPS - E2055 600100.0000000 0.00000000 600100.00000000 200802
14023 23012 ECL-2055 "PANDROL" CLIPS - E2055 0.00000000 50000.00000000 550100.00000000 200803
14023 23012 ECL-2055 "PANDROL" CLIPS - E2055 0.00000000 25000.00000000 525100.00000000 200805
14023 23012 ECL-2055 "PANDROL" CLIPS - E2055 0.00000000 50000.00000000 475100.00000000 200806
14023 23012 ECL-2055 "PANDROL" CLIPS - E2055 0.00000000 25000.00000000 450100.00000000 200807

I woud like to create extra entries in here for period 200801, 200804, 200808



Post #547837
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse