Hi
I have a part number which has usage/good on-hand/defective on-hand etc... data in DUBLIN depot for a certain week, but there is no usage/good on-hand/defective on-hand etc for the part in the other 2 depots (TURKEY, GERMANY)
I need a query that will extract the usage data for the DUBLIN depot, but that will also return 2 lines for the additional depots, even though they may not have any usage/good on-hand/defective on-hand .
as an example of what I need:
PART NUM DEPOT USAGE QTY GOH QTY DOH QTY
abc123 DUBLIN 25 65 12
abc123 TURKEY 0 0 0
abc123 GERMANY 0 0 0
ie; I always need 3 rows of data returned per part number (corresponding to the 3 depots)
I was trying CROSS JOIN to a list of the 3 depots, but this was pulling back data as shown below:
PART NUM DEPOT USAGE QTY GOH QTY DOH QTY
abc123 DUBLIN 25 65 12
abc123 TURKEY 25 65 12
abc123 GERMANY 25 65 12
thanks
Fergal
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
You need a list of all the depots, and a list of all the partnumbers, and a list of the weekly data fields.
You can cross join the depots to the partnumbers, which will give you a partnumber list in triplicate, as if each part exists in every depot. Then, left join this to the table(s) of weekly data,
and you will have your desired report. Where no weekly data exists for a part in a certain depot, the report will have nulls.
if you don't have the required lists in separate tables, you can run preliminary select distinct queries to build temp tables of all the possible combinations of depots and parts that could exist in the weekly transaction tables. We can help with that if you give more info on your db organization.
cross join is correct to get the relationships or, maybe could use depot directly and join out from there...
Select Part.PartNum, Depot.DepotName, ST.UsageQty, ST.DOHQty, ST.GOHQty
From
(select distinct partnum, s.depotid Stock_Transactions S cross join Depot D where s.depotid = 'Dublin' and d.depotid in ('Dublin','Germany','Turke
Left Join Stock_Transactions ST on ST.partnum = StkDepot.partnum and ST.depotid = StkDepot
Left Join Depot on Depot.DepotID = ST.DepotID
Left Join Part on Part.PartNum = ST.PartNum
and probably want to do a group by or range the selection from that point.....
Business Accounts
Answer for Membership
by: Sharath_123Posted on 2008-12-03 at 11:48:37ID: 23089377
Whats the data in your table and your desired result?