Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help with Join 1

Status
Not open for further replies.
jpiscit1

jpiscit1

Technical User
Oct 9, 2002
44
0
0
US
I have two queries.

Declare @StartDate as datetime
Declare @EndDate as datetime
Set @StartDate = '10/10/04'
Set @EndDate = '10/16/04'

1st Query:

Select
'Spin Coater ' + LEFT(sch.SpinCoaterNo,1) AS [Machine], work_order_no, PartNumber,
SUM(Isnull(sch.Sch1_sheets,0)+Isnull(sch.Sch2_sheets,0)+Isnull(sch.Sch3_sheets,0)+Isnull(sch.Sch4_sheets,0)+Isnull(sch.Sch5_sheets,0)+Isnull(sch.Sch6_sheets,0)+Isnull(sch.Sch7_sheets,0)) As [Sched Sheets]
From tbl_ProdSchedule sch
Where Sch1_date = @StartDate
Group by SpinCoaterNo, work_order_no, PartNumber

Which produces:

Machine WO Part No Sched Sheets
Spin Coater 1 NULL ISI0237 1200
Spin Coater 1 1328 ISI0122 700
Spin Coater 1 1330 ISI0080 1200
Spin Coater 1 1335 ISI0308 1050
Spin Coater 2 NULL ISI0079 4200
Spin Coater 2 1335 ISI0308 1050
Spin Coater 3 NULL ISI0032 3150
Spin Coater 3 1335 ISI0308 1050

2nd query:
Select Distinct 'Spin Coater ' + LEFT(stack_no,1) AS [Machine], work_order_no, item_no as [PartNumber],
Sum(Total_Sheets) As [Sheets Prod]
From prod_data
Where date_time >= @StartDate and date_time <= @EndDate
GROUP BY
'Spin Coater ' + LEFT(stack_no,1), work_order_no, item_no
Order By [Machine] ASC

Which produces:
Machine WO Part No Actual Sheets
Spin Coater 1 1328 ISI0122 700
Spin Coater 1 1330 ISI0080 1400
Spin Coater 1 1335 ISI0308 1625
Spin Coater 1 1337 ISI0237 1200
Spin Coater 2 1335 ISO0308B 1050
Spin Coater 2 1336 ISI0079 4200
Spin Coater 3 1329 ISI0032 2700
Spin Coater 3 1335 ISI0308B 975

i would like to combine the results. I tried this:
Select
'Spin Coater ' + LEFT(sch.SpinCoaterNo,1) AS [Machine], sch.work_order_no, sch.PartNumber,
SUM(Isnull(sch.Sch2_sheets,0)+Isnull(sch.Sch2_sheets,0)+Isnull(sch.Sch3_sheets,0)+Isnull(sch.Sch4_sheets,0)+Isnull(sch.Sch5_sheets,0)+Isnull(sch.Sch6_sheets,0)+Isnull(sch.Sch7_sheets,0)) As [Sched Sheets],
Sum(Total_Sheets) As [Sheets Prod]
From tbl_ProdSchedule sch
Inner join prod_data prd
ON sch.PartNumber = prd.item_no
Where sch.Sch1_date = @StartDate and
(prd.date_time >= @StartDate and prd.date_time <= @EndDate)
Group by sch.SpinCoaterNo, sch.work_order_no, sch.PartNumber

but it produces:
Machine WO Part No Sched Actual
Spin Coater 1 NULL ISI0237 7200 1200
Spin Coater 1 1328 ISI0122 4900 700
Spin Coater 1 1330 ISI0080 8400 1400
Spin Coater 1 1335 ISI0308 26775 1625
Spin Coater 2 NULL ISI0079 88200 4200
Spin Coater 2 1335 ISI0308 26775 1625
Spin Coater 3 NULL ISI0032 56700 2700
Spin Coater 3 1335 ISI0308 26775 1625

This query is being used to show what is scheduled vs. what was actually produced.

Here is what I would like to see:
Machine WO Part No Sched Actual
Spin Coater 1 NULL ISI0237 1200 1200
Spin Coater 1 1328 ISI0122 700 700
Spin Coater 1 1330 ISI0080 1200 1400
Spin Coater 1 1335 ISI0308 1050 1625
Spin Coater 2 NULL ISI0079 4200 4200
Spin Coater 2 1335 ISI0308 1050 <NULL>
Spin Coater 2 1335 ISO0308B <NULL> 1050
Spin Coater 3 NULL ISI0032 3150 2700
Spin Coater 3 1335 ISI0308 1050 <NULL>
Spin Coater 3 1335 ISI0308B <NULL> 975

I hope this is enough information. Any help is greatly appreciated.

Thanks!


Your limits are only as far as you set your boundries......
 
Sort by date Sort by votes
Wrap each query into derived table, then use FULL OUTER JOIN. Then use ISNULL() where necessary.
 
Upvote 0 Downvote
I am unfamiliar with this technique. Can you show an example? What is meant by "Wrap each query into derived table"



Your limits are only as far as you set your boundries......
 
Upvote 0 Downvote
This is example for wrapping into derived table:
Code: 
select A.* from
(	select
	'Spin Coater ' + LEFT(sch.SpinCoaterNo,1) AS [Machine], work_order_no, PartNumber,
	SUM( blah blah + ... ) As [Sched Sheets]
	From tbl_ProdSchedule sch
	Where Sch1_date = @StartDate 
	Group by SpinCoaterNo, work_order_no, PartNumber
) A
As you see, code between () is identical to your first query. Derived table is named "A".

Now, wrap 2nd query into another derived table and name it "B". Then join these two tables. From results you'd like to see I think that FULL OUTER JOIN should do the trick.
 
Upvote 0 Downvote
Am I close ?

select A.* from
( Select
'Spin Coater ' + LEFT(SpinCoaterNo,1) AS [Machine], work_order_no, PartNumber,
SUM(Isnull(Sch1_sheets,0)+Isnull(Sch2_sheets,0)+Isnull(Sch3_sheets,0)+Isnull(Sch4_sheets,0)+Isnull(Sch5_sheets,0)+Isnull(Sch6_sheets,0)+Isnull(Sch7_sheets,0)) As [Sched Sheets]
From tbl_ProdSchedule
Where Sch1_date = @StartDate
Group by SpinCoaterNo, work_order_no, PartNumber
) A
FULL OUTER JOIN

(Select 'Spin Coater ' + LEFT(stack_no,1) AS [Machine], work_order_no, item_no as [PartNumber],
Sum(Total_Sheets) As [Sheets Prod]
From prod_data
Where date_time >= @StartDate and date_time <= @EndDate
GROUP BY
'Spin Coater ' + LEFT(stack_no,1), work_order_no, item_no
) B
ON A.PartNumber = B.item_no

I follow what your saying with the derived tables but having trouble knowing where to place the Full Outer Join. this produced the error

Server: Msg 207, Level 16, State 3, Line 6
Invalid column name 'item_no'.

Thanks.

Your limits are only as far as you set your boundries......
 
Upvote 0 Downvote
Relatively close... outside derived table B item_no is aliased as [PartNumber]. Use that name in JOIN.

Also: part_no in JOIN may not be enough. It isn't completely unique (first query has value 1335 3 times). Which columns together are unique in each query?
 
Upvote 0 Downvote
B does not have a field named item_no, you renamed it [PartNumber] in the select.

Questions about posting. See faq183-874
 
Upvote 0 Downvote
Ahhh yes. My mistake.

Ragarding unique fields:

In the first table(production schedule table), the only unique field is RecordID.

In the second table (Production actual table), the unique field would be date_time.

As it stands now my query using:

Declare @StartDate as datetime
Declare @EndDate as datetime
Set @StartDate = '10/10/04'
Set @EndDate = '10/16/04'

select A.* from
( Select
'Spin Coater ' + LEFT(SpinCoaterNo,1) AS [Machine], work_order_no, PartNumber,
SUM(Isnull(Sch1_sheets,0)+Isnull(Sch2_sheets,0)+Isnull(Sch3_sheets,0)+Isnull(Sch4_sheets,0)+Isnull(Sch5_sheets,0)+Isnull(Sch6_sheets,0)+Isnull(Sch7_sheets,0)) As [Sched Sheets]
From tbl_ProdSchedule
Where Sch1_date = @StartDate
Group by SpinCoaterNo, work_order_no, PartNumber
) A
FULL OUTER JOIN

(Select 'Spin Coater ' + LEFT(stack_no,1) AS [Machine], work_order_no, item_no as [PartNumber],
Sum(Total_Sheets) As [Sheets Prod]
From prod_data
Where date_time >= @StartDate and date_time <= @EndDate
GROUP BY
'Spin Coater ' + LEFT(stack_no,1), work_order_no, item_no
) B
ON A.PartNumber = B.[PartNumber]
Order by A.[Machine] ASC

Produces:
Machine work_order_no PartNumber Sched Sheets
Spin Coater 1 1330 ISI0080 1200
Spin Coater 1 1328 ISI0122 700
Spin Coater 1 1337 ISI0237 1200
Spin Coater 1 1335 ISI0308 1050
Spin Coater 1 1335 ISI0308 1050
Spin Coater 1 1335 ISI0308 1050
Spin Coater 2 1335 ISI0308 1050
Spin Coater 2 1335 ISI0308 1050
Spin Coater 2 1336 ISI0079 4200
Spin Coater 2 1335 ISI0308 1050
Spin Coater 3 1335 ISI0308 1050
Spin Coater 3 1329 ISI0032 3150
Spin Coater 3 1335 ISI0308 1050
Spin Coater 3 1335 ISI0308 1050

Still not exactly what I was looking for.




Your limits are only as far as you set your boundries......
 
Upvote 0 Downvote
Try this:
Code: 
select 
isnull(A.Machine, B.Machine) as Machine,
A.work_order_no,
isnull(A.partNumber, B.partNumber) as partNumber, 
A.[Sched Sheets], B.[Sheets Prod]
from ( [b]first_query_here[/b] ) A
full outer join ( [b]second_query_here[/b] ) B 
on A.PartNumber=B.PartNumber and A.Machine=B.Machine 
order by Machine, A.work_order_no, partNumber
I got exactly 10 results, but there are minor differences in WO column.
 
Upvote 0 Downvote
Excellent! Thanks a bunch.

This is what I ended with.

Declare @StartDate as datetime
Declare @EndDate as datetime
Set @StartDate = '10/10/04'
Set @EndDate = '10/16/04'

select
isnull(A.Machine, B.Machine) as Machine,
A.work_order_no,
isnull(A.partNumber, B.partNumber) as partNumber,
A.[Sched Sheets], B.[Sheets Prod]
from ( Select
'Spin Coater ' + LEFT(SpinCoaterNo,1) AS [Machine], work_order_no, PartNumber,
SUM(Isnull(Sch1_sheets,0)+Isnull(Sch2_sheets,0)+Isnull(Sch3_sheets,0)+Isnull(Sch4_sheets,0)+Isnull(Sch5_sheets,0)+Isnull(Sch6_sheets,0)+Isnull(Sch7_sheets,0)) As [Sched Sheets]
From tbl_ProdSchedule
Where Sch1_date = @StartDate
Group by SpinCoaterNo, work_order_no, PartNumber
) A
full outer join ( Select 'Spin Coater ' + LEFT(stack_no,1) AS [Machine], work_order_no, item_no as [PartNumber],
Sum(Total_Sheets) As [Sheets Prod]
From prod_data
Where date_time >= @StartDate and date_time <= @EndDate
GROUP BY
'Spin Coater ' + LEFT(stack_no,1), work_order_no, item_no
) B
on A.PartNumber=B.PartNumber and A.Machine=B.Machine
order by Machine, A.work_order_no, partNumber

My Result set was as follows:

Spin Coater 1 1328 ISI0122 700 700
Spin Coater 1 1330 ISI0080 1200 1400
Spin Coater 1 1335 ISI0308 1050 1625
Spin Coater 1 1337 ISI0237 1200 1200
Spin Coater 2 1335 ISI0308 1050 1050
Spin Coater 2 1336 ISI0079 4200 4200
Spin Coater 3 1329 ISI0032 3150 2700
Spin Coater 3 1335 ISI0308 1050 975

You were a tremendous help!




Your limits are only as far as you set your boundries......
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

f0rg3tfu1
  • Locked
  • Question
PLSQL Package Help
Replies
0
Views
88
f0rg3tfu1
f0rg3tfu1
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
226
Chris Miller
Chris Miller
gussy81
  • Locked
  • Question
Decimal Datatype with negative value
Replies
5
Views
265
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question
Update with a month name 1
Replies
9
Views
168
gmmastros
gmmastros
bmacbmac
  • Locked
  • Question
Help searching within this Json file
Replies
0
Views
45
bmacbmac
bmacbmac

Part and Inventory Search

Sponsor

Back
Top