October 17, 2013 at 5:52 am
Okay I've been given a fun assignment,fun as they didn't tell me what the end goal is & I'm not allowed to make changes to the existing tables without permission of the endusers (which are half a planet away)
SO I can guess what happened
I got a table Period, it contains 12 months stored as yyyymm in string format.
I got a table Routing History which contains warehouse_code,item_number,origin_warehouse_code,Fromdate
The Fromdate is also stored as yyyymm in string format.
Now they want the following
Period.Month warehouse_code,item_number,origin_warehouse_code,FromDate
And for the period.Months we don't have entries get the most recent warehouse_code,item_number,origin_warehouse_code,FromDate
There over 50 million records in the Routing History
SO what's happened is that they wanted a application that tracked movement of goods per month,but only inserted new records when the origin_warehouse changed and now i need to give them everyhing without the gaps.
My current solution definatly has RBAR in it and is slow,it literally grinds a full day without completing anything on even a single warehouse.
But I'm having trouble finding an altenative way can somebody give me an idea of what I could do.
October 17, 2013 at 6:08 am
I think what you want is to query all routing_history rows and show period_month column only if available. This is a left join :
select
p.period_month,r.warehouse_code,r.item_number,r.origin_warehouse_code,r.Fromdate
from
Routing_History r
left join Period p on p.period_month= r.Fromdate
Jonathan Bernardez Bernardez
___________________________________________________________
DBD. MCSA SQL Server 2012
October 17, 2013 at 6:17 am
jonysuise (10/17/2013)
I think what you want is to query all routing_history rows and show period_month column only if available. This is a left join :select
p.period_month,r.warehouse_code,r.item_number,r.origin_warehouse_code,r.Fromdate
from
Routing_History r
left join Period p on p.period_month= r.Fromdate
That would be a too easy answer
Cause then you get null values for the p.period_month for which for a given r.warehouse_code, r.item_number combination does not have a r.Fromdate that's equal to the p.period_month.
Its these null values that have to be replaced with the values found for the most recent known Fromdate (recent as in a Fromdate that is closest to the p.period)
October 17, 2013 at 6:23 am
Then this should do the trick :
select
coalesce(p.period_month,r.Fromdate) as period_month,r.warehouse_code,r.item_number,r.origin_warehouse_code,r.Fromdate
from
Routing_History r
left join Period p on p.period_month= r.Fromdate
Jonathan Bernardez Bernardez
___________________________________________________________
DBD. MCSA SQL Server 2012
October 17, 2013 at 7:09 am
What should you return if there is no prior record in routing history for that period (ie: item introduced a month later)?
Also, what are you grouping by? Just item #?
As in, should there be 1 record for each period and each item #?
October 17, 2013 at 7:15 am
Nevyn (10/17/2013)
What should you return if there is no prior record in routing history for that period (ie: item introduced a month later)?Also, what are you grouping by? Just item #?
As in, should there be 1 record for each period and each item #?
This can't happen as an item can not be taken into the routing history if it does not exist in the items list.
The grouping has to happen on Period,Warehouse,item_number
Yes the end result should be that for each warehouse item combination we have 1 record for each period
Lets asume the following data
Period table contains the following
201211
201212
201301
201302
201303
201304
201305
201306
201307
201308
201309
201310
The Routing History table for an warehouse/item contains the following
ABC I123 DEF 201201
ABC I123 GHI 201302
ABC I123 JKL 201308
And I need the following results
201211 ABC I123 DEF 201201
201212 ABC I123 DEF 201201
201301 ABC I123 DEF 201201
201302 ABC I123 GHI 201302
201303 ABC I123 GHI 201302
201304 ABC I123 GHI 201302
201305 ABC I123 GHI 201302
201306 ABC I123 GHI 201302
201307 ABC I123 GHI 201302
201308 ABC I123 JKL 201308
201309 ABC I123 JKL 201308
201310 ABC I123 JKL 201308
October 17, 2013 at 7:40 am
So given that period table you'd never have a record like:
XYZ l456 DEF 201301
Where there were no earlier routing records for XYZ l456?
October 17, 2013 at 7:40 am
How about some ddl and sample data? This doesn't sound too bad but without something to work with I have a hard time visualizing it, and it is nearly impossible to write any code.
Please see the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 17, 2013 at 7:43 am
Take a look and tell me if this is what you want ... I included one case like the one I was asking about.
Data types are incorrect as I started building before you clarified, but it should work regardless
Create table #Period (
[month]char(6)
)
Create table #Routing_History (
warehouse_codeint,
item_numberint,
origin_warehouse_codeint,
from_datechar(6)
)
insert into #Period ([month])
SELECT '201201'
UNION
SELECT '201202'
UNION
SELECT '201203'
UNION
SELECT '201204'
UNION
SELECT '201205'
insert into #Routing_History ([warehouse_code],item_number,origin_warehouse_code,from_date)
SELECT 1,1,1,'201201'
UNION
SELECT 1,1,3,'201203'
UNION
SELECT 2,2,2,'201202'
UNION
SELECT 2,2,3,'201205'
;
WITH FirstPass AS (
SELECT p.[Month], r.warehouse_code,r.item_number,max(r.from_date) AS from_date
FROM #Routing_History r
INNER JOIN #Period p
ON r.from_date <= p.[month]
GROUP BY r.warehouse_code,r.item_number,p.[month]
)
SELECT f.[month],f.warehouse_code,f.item_number,r.origin_warehouse_code,f.from_date
FROM FirstPass f
INNER JOIN #Routing_History r
ON f.from_date = r.from_date
AND f.item_number = r.item_number
AND f.warehouse_code = r.warehouse_code
Drop table #Period
Drop table #Routing_History
October 17, 2013 at 8:15 am
Nevyn (10/17/2013)
So given that period table you'd never have a record like:XYZ l456 DEF 201301
Where there were no earlier routing records for XYZ l456?
As far as I'm told, an item will only be present if it either not in the items table or it ever been shipped
Sean Lange (10/17/2013)
How about some ddl and sample data? This doesn't sound too bad but without something to work with I have a hard time visualizing it, and it is nearly impossible to write any code.Please see the first link in my signature for best practices when posting questions.
I know but at the time I originally posted I was being pressured by my superiors,also I have limited knowledge of what the data means as its part of a HRM system I normally never have access to (Basically they outsourced the task to me cause in between batches on my main assignment)
The data itself is confidential & losing my job over a breaching it would be the least of my concerns.
Nevyn (10/17/2013)
Take a look and tell me if this is what you want ... I included one case like the one I was asking about.Data types are incorrect as I started building before you clarified, but it should work regardless
The fact that the datatypes were incorrect was no problem as the tables themself have yet to be completly finalized.
I changed the code to fit both the tables & to test the workings for all the items in a single warehouse,which gave me all the results in under 3 minutes.
Atm I'm running a check on how much resources its costing & checking if the data it returned does not contain any anomalies.
October 17, 2013 at 8:27 am
This is not what you need ?
select
coalesce(p.period_month,r.Fromdate) as period_month,r.warehouse_code,r.item_number,r.origin_warehouse_code,r.Fromdate
from
Routing_History r
left join Period p on p.period_month= r.Fromdate
Jonathan Bernardez Bernardez
___________________________________________________________
DBD. MCSA SQL Server 2012
October 17, 2013 at 8:28 am
Resender (10/17/2013)
Sean Lange (10/17/2013)
How about some ddl and sample data? This doesn't sound too bad but without something to work with I have a hard time visualizing it, and it is nearly impossible to write any code.Please see the first link in my signature for best practices when posting questions.
I know but at the time I originally posted I was being pressured by my superiors,also I have limited knowledge of what the data means as its part of a HRM system I normally never have access to (Basically they outsourced the task to me cause in between batches on my main assignment)
The data itself is confidential & losing my job over a breaching it would be the least of my concerns.
That is why it would be sample data, not the real data. Being pressured to get it done quickly should be more of an argument to post enough details for somebody to help, not the other way around.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 17, 2013 at 8:38 am
jonysuise (10/17/2013)
This is not what you need ?select
coalesce(p.period_month,r.Fromdate) as period_month,r.warehouse_code,r.item_number,r.origin_warehouse_code,r.Fromdate
from
Routing_History r
left join Period p on p.period_month= r.Fromdate
No the code Nevyn posted did what had to be done.
As for sample data yes I could & I should have made some,but this was bugging me alot.
But we can considered this solved now.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply