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......
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......