SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to insert missing periods ?


How to insert missing periods ?

Author
Message
Nick Bailey
Nick Bailey
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 58
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.



Steve Jones
Steve Jones
SSC Guru
SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)

Group: Administrators
Points: 146043 Visits: 19425
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
My Blog: www.voiceofthedba.com
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94569 Visits: 38956
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.

Cool

Cool
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)
Nick Bailey
Nick Bailey
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 58
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search