Help with Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help with Join

Author

Topic

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-11-16 : 09:20:05

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. Two tables:

The Tables are:

CREATE TABLE [tbl_ProdSchedule] (
[RecordID] [numeric](18, 0) NOT NULL ,
[Record_date] [datetime] NULL ,
[PartNumber] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Work_order_no] [numeric](18, 0) NULL ,
[PartType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FrameType] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FrameNumber] [numeric](18, 0) NULL ,
[Shift] [numeric](18, 0) NULL ,
[Sch1_date] [datetime] NULL ,
[Sch1_spins] [numeric](18, 0) NULL ,
[Sch1_sheets] [numeric](18, 0) NULL ,
[Sch2_date] [datetime] NULL ,
[Sch2_spins] [numeric](18, 0) NULL ,
[Sch2_sheets] [numeric](18, 0) NULL ,
[Sch3_date] [datetime] NULL ,
[Sch3_spins] [numeric](18, 0) NULL ,
[Sch3_sheets] [numeric](18, 0) NULL ,
[Sch4_date] [datetime] NULL ,
[Sch4_spins] [numeric](18, 0) NULL ,
[Sch4_sheets] [numeric](18, 0) NULL ,
[Sch5_date] [datetime] NULL ,
[Sch5_spins] [numeric](18, 0) NULL ,
[Sch5_sheets] [numeric](18, 0) NULL ,
[Sch6_date] [datetime] NULL ,
[Sch6_spins] [numeric](18, 0) NULL ,
[Sch6_sheets] [numeric](18, 0) NULL ,
[Sch7_date] [datetime] NULL ,
[Sch7_spins] [numeric](18, 0) NULL ,
[Sch7_sheets] [numeric](18, 0) NULL ,
[Frames_due_date] [datetime] NULL ,
[SpinCoaterNo] [numeric](18, 0) NULL ,
[Comments] [varchar] (400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[timestamp_col] [timestamp] NULL ,
CONSTRAINT [PK_tbl_ProdSchedule] PRIMARY KEY CLUSTERED
(
[RecordID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

AND:

CREATE TABLE [prod_data] (
[ID] [numeric](18, 0) NULL ,
[date_time] [datetime] NOT NULL ,
[work_order_no] [numeric](18, 0) NOT NULL ,
[item_no] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[coat_no] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lot_no] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stack_no] [numeric](18, 0) NOT NULL ,
[stk_wt_init] [numeric](18, 3) NOT NULL CONSTRAINT [DF_prod_data_stk_wt_init] DEFAULT (0),
[stk_wt_final] [numeric](18, 3) NOT NULL CONSTRAINT [DF_prod_data_stk_wt_final] DEFAULT (0),
[stk_wt_net] AS ([stk_wt_final] - [stk_wt_init]) ,
[stk_wt_init2] [numeric](18, 3) NULL ,
[stk_wt_final2] [numeric](18, 3) NULL ,
[stk_wt_net2] AS ([stk_wt_final2] - [stk_wt_init2]) ,
[coat_lot_no] [numeric](18, 0) NOT NULL ,
[coat_lot_no2] [numeric](18, 0) NULL ,
[coat_lot_no3] [numeric](18, 0) NULL ,
[coat_no2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[coat_no3] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sht_per_stk] [numeric](18, 0) NOT NULL ,
[parts_per_sheet] [numeric](18, 0) NULL ,
[sheets_into_oven] [numeric](18, 0) NULL ,
[rpm_sp] [numeric](18, 2) NOT NULL ,
[cycle_time_sp] [numeric](18, 2) NOT NULL ,
[delay_sp] [numeric](18, 2) NOT NULL ,
[delay_acc] [numeric](18, 2) NOT NULL ,
[oven_spd_sp] [numeric](18, 2) NOT NULL ,
[oven_temp_dry] [numeric](18, 2) NOT NULL ,
[oven_temp_cure] [numeric](18, 2) NOT NULL ,
[TIMESTAMP_VAL] [timestamp] NOT NULL ,
[bolt_torq] [numeric](18, 0) NULL ,
[bolt_num] [numeric](18, 0) NULL ,
[viscosity] [numeric](18, 0) NULL ,
[viscosity2] [numeric](18, 0) NULL ,
[viscosity3] [numeric](18, 0) NULL ,
[Stack1Moisture] [numeric](18, 1) NULL ,
[Stack2Moisture] [numeric](18, 1) NULL ,
[No_Stacks_run] [numeric](18, 0) NULL ,
[Stack1CutSeq] [numeric](18, 0) NULL ,
[Stack2CutSeq] [numeric](18, 0) NULL ,
[Total_Sheets] AS ([sht_per_stk] * [No_Stacks_run]) ,
[date_qc_inspected] [datetime] NULL ,
[seal_test_pass] [numeric](18, 0) NULL ,
[seal_test_fail] [numeric](18, 0) NULL ,
[QC_persons_initials] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[auto_inspect] [numeric](18, 0) NULL ,
[avg_lip_height] [numeric](18, 3) NULL ,
[std_dev_lip_height] [numeric](18, 4) NULL ,
[min_lip_height] [numeric](18, 4) NULL ,
[max_lip_height] [numeric](18, 4) NULL ,
[ave_min_lip_height] [numeric](18, 4) NULL ,
[ave_max_lip_height] [numeric](18, 4) NULL ,
[ave_range] [numeric](18, 4) NULL ,
[good_parts] [numeric](18, 0) NULL ,
[bad_parts] AS ([sheets_into_oven] * [parts_per_sheet] - [good_parts]) ,
[low_lip] [numeric](18, 0) NULL CONSTRAINT [DF_prod_data_low_lip] DEFAULT (0),
[high_lip] [numeric](18, 0) NULL CONSTRAINT [DF_prod_data_high_lip] DEFAULT (0),
[v_cuts] [numeric](18, 0) NULL CONSTRAINT [DF_prod_data_v_cuts] DEFAULT (0),
[channel] [numeric](18, 0) NULL CONSTRAINT [DF_prod_data_channel] DEFAULT (0),
[leakage] [numeric](18, 0) NULL CONSTRAINT [DF_prod_data_leakage] DEFAULT (0),
[torn] [numeric](18, 0) NULL CONSTRAINT [DF_prod_data_torn] DEFAULT (0),
[bubbles] [numeric](18, 0) NULL CONSTRAINT [DF_prod_data_bubbles] DEFAULT (0),
[other] [numeric](18, 0) NULL CONSTRAINT [DF_prod_data_other] DEFAULT (0),
[auto_insp_timestamp] [datetime] NULL ,
[comments] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Shts_into_oven_time] [datetime] NULL ,
[auto_insp_start_time] [datetime] NULL ,
[Time_to_inspect] [numeric](18, 2) NULL ,
[Auto_Time_to_inspect] [numeric](18, 2) NULL ,
CONSTRAINT [PK_prod_data] PRIMARY KEY CLUSTERED
(
[date_time]
) ON [PRIMARY]
) ON [PRIMARY]
GO

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

To post the DDL would exceed the 4000 char limit. i hope this is enough information. Any help is greatly appreciated.

Thanks!

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-11-16 : 14:00:21

[code]select Coalesce(q1.Machine,q2.Machine) Machine
,Coalesce(q1.WO,q2.WO) WO
,Coalesce(q1.Part,q2.Part) Part
,case when q1.Sheets is null then q2.No else q1.No end No
,coalesce(q1.Sched,q2.Actual) Sched
,q1.Sheets Qty1
,q2.Sheets Qty2
from
q1 -- your 1st query
FULL JOIN
q2 --your 2nd Query
ON q1.Machine=q2.Machine and q1.WO =q2.WO and q1.Sched=q2.Actual
Order by 3,4[/code]

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-11-16 : 16:03:25

Well Im getting closer. Had to rem out some of the code to get it to run. Perhaps you can explain what it was doing ??

Here's what I have:

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

select Coalesce(q1.Machine,q2.Machine) Machine
,Coalesce(q1.WO,q2.WO) WO
,Coalesce(q1.Part,q2.Part) Part
--,case when q1.Sched is null then q2.No else q1.No end No
--,coalesce(q1.Sched,q2.Actual) Sched
,q1.Sched Qty1
,q2.Actual Qty2
from
(Select
'Spin Coater ' + LEFT(sch.SpinCoaterNo,1) AS [Machine], work_order_no as WO, PartNumber as Part,
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]
From tbl_ProdSchedule sch
Where Sch1_date = @StartDate
Group by SpinCoaterNo, work_order_no, PartNumber) q1

FULL JOIN
(Select Distinct 'Spin Coater ' + LEFT(stack_no,1) AS [Machine], work_order_no As WO, item_no as [Part],
Sum(Total_Sheets) As [Actual]
From prod_data
Where date_time >= @StartDate and date_time <= @EndDate
GROUP BY
'Spin Coater ' + LEFT(stack_no,1), work_order_no, item_no) q2

ON q1.Machine=q2.Machine and q1.WO =q2.WO and q1.Sched=q2.Actual
--Order by 3,4
Order by [Machine], part Asc

Here was the result set:

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

Your limits are only as far as you set your boundries....

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-11-16 : 19:58:50

Thanks for your help VIG. You got my wheels rolling.

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

Thanks again!

John

Your limits are only as far as you set your boundries....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources