December 2, 2009 at 6:54 am
Good morning all,
Can someone tell me why I am getting a value in shift 3 when, this order had no defects on shift 3? I should get a zero but, I am getting 4.
select h.Inspect_date,
sum(case p.shift when 1 then 1 else 0 end) Shift_1,
sum(case p.shift when 2 then 1 else 0 end) Shift_2,
sum(case p.shift when 3 then 1 else 0 end) Shift_3
from tbl_assembly_production as p
join tbl_assembly_holds as h on p.workorder = h.workorder
JOIN tbl_Assembly_hold_defects as d on h.tagnumber = d.tagnumber
WHERE d.defect_id = '19' and h.inspect_date = '11/30/2009' and p.startunits < = h.wounit and h.workorder = '71113'
group by h.Inspect_date
Thanks in advance,
Khalif
December 2, 2009 at 7:06 am
well the SQL looks right, so it's gotta be the data not meeting your expectations.
i would expect the SQL below to show you that there really is 4 items in shift 3:
select
h.Inspect_date,
p.shift
from tbl_assembly_production as p
join tbl_assembly_holds as h on p.workorder = h.workorder
JOIN tbl_Assembly_hold_defects as d on h.tagnumber = d.tagnumber
WHERE d.defect_id = '19'
and h.inspect_date = '11/30/2009'
and p.startunits < = h.wounit
and h.workorder = '71113'
and p.shift = 3
Lowell
December 2, 2009 at 7:41 am
Lowell,
There were 15 on shift 1 and 1 on shift 3.
SELECT p.startUnits, p.endUnits, p.Shift, p.WorkOrder
FROM tbl_Assembly_Production as p
WHERE (p.WorkOrder = '71113')
But there were 9 defect out of the 16
SELECT h.WOUnit, h.TagNumber
FROM tbl_Assembly_Holds as h
WHERE (h.WorkOrder = '71113')
All of the defective ones were produced on shift 1 based on the unit#. (1,2,3,4,5 and 12,13,14,15). Unit 16 was produced on shift 3 with no defects.
December 2, 2009 at 7:46 am
Here's the problem. You have given us the code but no data or table structures with which to work with. Since we can't see your data there isn't much more we can do.
December 2, 2009 at 7:49 am
well, if there was only one in the "p" table for shift 3, the issue is your joins are bringing in 4 more rows, right? i would guess the other shifts are being inflated as well.
is there a 1:1 relationship for these two joins? i don't think so, so your numbers are being inflated.
join tbl_assembly_holds as h on p.workorder = h.workorder
JOIN tbl_Assembly_hold_defects as d on h.tagnumber = d.tagnumber
Lowell
December 2, 2009 at 7:49 am
I agree with Lynn! Check my signature and why not to post the problem in the correct way and find the solution as better as we can!
December 2, 2009 at 9:03 am
CREATE TABLE [dbo].[tbl_Assembly_Holds](
[TagNumber] [float] NOT NULL,
[WorkOrder] [nvarchar](7) NOT NULL,
[WOUnit] [nvarchar](2) NOT NULL,
[WOYear] [nvarchar](4) NOT NULL,
[Inspect_EmpNo] [int] NOT NULL,
[Inspect_Date] [datetime] NOT NULL,
[Disposition_ID] [int] NOT NULL,
[Comment] [nvarchar](400) NULL,
[Repair_EmpNo] [float] NULL,
[Repair_Date] [datetime] NULL,
[Repair_Notes] [nvarchar](50) NULL,
[Rejar] [smallint] NULL,
[Record_Date] [datetime] NOT NULL,
CONSTRAINT [PK_tbl_Assembly_Holds] PRIMARY KEY CLUSTERED
(
[TagNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbl_Assembly_Holds] WITH CHECK ADD CONSTRAINT [FK_tbl_Assembly_Holds_tlkp_Employees] FOREIGN KEY([Repair_EmpNo])
REFERENCES [dbo].[tlkp_Employees] ([EmpNo])
GO
ALTER TABLE [dbo].[tbl_Assembly_Holds] CHECK CONSTRAINT [FK_tbl_Assembly_Holds_tlkp_Employees]
INsert into holds
(WOUnit,TagNumber,WorkOrder,Inspect_Date,Record_Date)
SELECT '2','48551','71113','2009-11-30 00:00:00.000','2009-11-30 00:00:00.000'
SELECT '1','48552','71113','2009-11-30 00:00:00.000','2009-11-30 00:00:00.000'
SELECT '15','48767','71113','2009-11-30 00:00:00.000','2009-11-30 00:00:00.000'
SELECT '13','48768','71113','2009-11-30 00:00:00.000','2009-11-30 00:00:00.000'
SELECT '12','48769','71113','2009-11-30 00:00:00.000','2009-11-30 00:00:00.000'
SELECT '5','48771','71113','2009-11-30 00:00:00.000','2009-11-30 00:00:00.000'
SELECT '4','48772','71113','2009-11-30 00:00:00.000','2009-11-30 00:00:00.000'
SELECT '3','48773','71113','2009-11-30 00:00:00.000','2009-11-30 00:00:00.000'
SELECT '14','48779','71113','2009-11-30 00:00:00.000','2009-11-30 00:00:00.000'
December 2, 2009 at 9:07 am
Lowell,
That's true. I inherited this project and have not changed much in the way of table structure. I'm open for suggestion. I need to get the correct output.
Thanks,
Khalif
December 2, 2009 at 9:40 am
this is just a guess, where i'm trying to reduce the rows in the join by using a sub select;
does this produce results that are close to what you want?
SELECT
MySubSelect.Inspect_date,
sum(case p.shift when 1 then 1 else 0 end) Shift_1,
sum(case p.shift when 2 then 1 else 0 end) Shift_2,
sum(case p.shift when 3 then 1 else 0 end) Shift_3
from tbl_assembly_production as p
INNER JOIN (
SELECT DISTINCT
h.workorder,
h.wounit,
h.tagnumber,
h.inspect_date
FROM tbl_assembly_holds as h
INNER JOIN tbl_Assembly_hold_defects as d
ON h.tagnumber = d.tagnumber
WHERE d.defect_id = '19'
AND h.workorder = '71113'
) MySubSelect ON p.workorder = MySubSelect.workorder
WHERE p.workorder = '71113'
AND p.startunits < = MySubSelect.wounit
group by MySubSelect.Inspect_date
Lowell
December 2, 2009 at 9:55 am
Lowell,
Same results.
Thanks,
Khalif
December 2, 2009 at 10:24 am
Sorry, but I tried using the code you provided to setup a test environment and it is failing. You provided a foreign key reference that apparently doesn't apply, for one. Second, there isn't enough data in your sample data set as I get errors about not being able to insert null values in some of the columns.
Are the missing values relevant to the problem or can I change them from NOT NULL to NULL?
December 2, 2009 at 10:37 am
Lowell,
The problem is this:
units 1-15 were produced on shift 1
unit 16 was produced on shift 3
We record startunit and endunit then, units produced.
Ex. startunits = 1, endunits = 15 then, units produced = 15 shift = 1
startunits = 16, endunits = 16 then, units produced = 1 shift = 3
Therefore, it sums MySubSelect.wounit < = p.endunits as
shift 1 = 4
shift 3 = 4
And this MySubSelect.wounit > = p.startunits
shift 1 = 8
shift 3 = 4
THanks,
Khalif
December 2, 2009 at 10:51 am
Not only that, looking at the code Lowell is writing for you, there isn't enough info (tables or sample data) in your previous post to even work with as it is.
December 2, 2009 at 3:25 pm
Kabaari,
Lynn has put it very well and Lowell has helped tremendously with little to go on. There are many people in this forum ready to help, if they can get a little more information.
It is really necessary, to help you get the best answer possible as quickly as possible, to include the sample data, table structures, and sample desired output for the query in question. We have some of the data and one of the tables. Please help us help you - it appears the answer truly lies in the missing tables not included in this thread so far.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 3, 2009 at 6:55 am
Two more tables will be posted.
USE [productDetails]
GO
/****** Object: Table [dbo].[tbl_Assembly_Production] Script Date: 12/03/2009 07:42:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_Assembly_Production](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DateProd] [datetime] NULL,
[Shift] [nvarchar](1) NULL,
[Area] [nvarchar](3) NULL,
[Jig] [smallint] NULL,
[EmpNo] [float] NULL,
[WorkOrder] [nvarchar](7) NULL,
[Item] [nvarchar](15) NULL,
[Model] [nvarchar](15) NULL,
[ProdTime] [nvarchar](50) NULL,
[CoTime] [nvarchar](50) NULL,
[startUnits] [nvarchar](4) NULL,
[endUnits] [nvarchar](4) NULL,
[NpTime] [nvarchar](50) NULL,
[UnitsProd] [int] NULL,
[ProdDelayTime] [nvarchar](50) NULL,
[CoDelayTime] [nvarchar](50) NULL,
[Comment] [nvarchar](400) NULL,
CONSTRAINT [PK_tbl_Assembly_Production] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO Production
(ID,DateProd,Shift,Area,Jig,EmpNo,WorkOrder,Item,Model,ProdTime,CoTime,startUnits,endUnits,NpTime,UnitsProd,ProdDelayTime,CoDelayTime,Comment)
SELECT '7153','2009-11-30 00:00:00.000','1','LP','3','1354','71116','CW17866-PC','4XTHC 13','2.75','0','3','16','','14','','',
SELECt '7154','2009-11-30 00:00:00.000','1','LP','3','1354','71123','CW16427-PC','4XTLC 09','3.08333333333','0','1','8','','8','','',
SELECT '7155','2009-11-30 00:00:00.000','1','LP','4','543','71115','CW17866-PC','4XTHC 13','1.83333333333','0','11','16','','6','','',
SELECT '7156','2009-11-30 00:00:00.000','1','LP','4','543','70355','CW17867-SC','4XTHC 15','4.91666666667','0','1','16','','16','','',
SELECT '7157','2009-11-30 00:00:00.000','1','LP','5','75','72226','CW16608-PCTL','KCRC 17','3.58333333333','0','44','64','','21','','',
SELECT '7158','2009-11-30 00:00:00.000','1','LP','5','75','72227','CW16608-PCTL','KCRC 17','3.25','0','1','34','','34','','',
SELECT '7159','2009-11-30 00:00:00.000','1','LP','6','699','71102','CW16431-SC','2XTLC 17','2.83333333333','0','15','32','','18','','',
SELECT '7160','2009-11-30 00:00:00.000','1','LP','6','699','71103','CW16431-SC','2XTLC 17','4.91666666667','0','1','32','','32','','',
SELECT '7161','2009-11-30 00:00:00.000','1','LP','7','1209','70273','CW16759-SC','4XTLCP 11','1.75','0','13','16','','4','','',
SELECT '7162','2009-11-30 00:00:00.000','1','LP','7','1209','70274','CW16759-SC','4XTLCP 11','1.75','0','1','3','','3','','',
SELECT '7163','2009-11-30 00:00:00.000','1','LP','8','853','71119','CW16427-PC','4XTLC 09','1','0','11','16','','6','','',
SELECT '7164','2009-11-30 00:00:00.000','1','LP','8','853','71120','CW16427-PC','4XTLC 09','3.33333333333','0','1','16','','16','','',
SELECT '7165','2009-11-30 00:00:00.000','1','LP','8','853','71121','CW16427-PC','4XTLC 09','1.5','0','1','9','','9','','',
SELECT '7166','2009-11-30 00:00:00.000','1','DJ','2','1293','72224','CW17491-S','4JC 50','3.25','0','12','16','','5','','',
SELECT '7167','2009-11-30 00:00:00.000','1','DJ','2','1293','72225','CW17491-S','4JC 50','3.58333333333','0',1,7,,7,,,
SELECT '7168','2009-11-30 00:00:00.000','1','DJ','1','798','72216','CW17655-PVCC','3XDJC 11','0.333333333333','0','20','22','','3','','',
SELECT '7169','2009-11-30 00:00:00.000','1','DJ','1','798','72217','CW17655-PVCC','3XDJC 11','4','0','1','22','','22','','',
SELECT '7170','2009-11-30 00:00:00.000','1','DJ','1','798','72218','CW17655-PVCC','3XDJC 11','2.5','0','1','14','','14','','',
SELECT '7171','2009-11-30 00:00:00.000','1','MCT','1','314','71127','CW17017-PVC','MCT 4000','4','0','6','24','','19','','',
SELECT '7172','2009-11-30 00:00:00.000','1','MCT','1','314','71128','CW17017-PVC','MCT 4000','2.83333333333','0','1','10','','10','','',
SELECT '7173','2009-11-30 00:00:00.000','1','MCT','2','1353','71126','CW16967-PVC-NP','MCT 4000','6.83333333333','0','7','22','','16','','',
SELECT '7174','2009-11-30 00:00:00.000','1','LP','1','663','72129','CW18161-SC','4XTHC 19','6.83333333333','0','1','13','','13','','',
SELECT '7175','2009-11-30 00:00:00.000','1','LP','1','1354','72129','CW18161-SC','4XTHC 19','1.83333333333','0','13','16','','4','','',
SELECT '7176','2009-11-30 00:00:00.000','1','LP','2','889','71064','CW16793-S','LCZ 2500','5.08333333333','0','1','14','','14','','','.5 day utility'
SELECT '7178','2009-11-30 00:00:00.000','1','LP','4','543','70356','CW17867-SC','4XTHC 15','1.83333333333','0','1','8','','8','','',
SELECT '7179','2009-11-30 00:00:00.000','1','LP','6','699','72581','CW16434-SC','2XTLC 23','1.83333333333','0','1','4','','4','','',
SELECT '7180','2009-11-30 00:00:00.000','3','LP','1','1115','71109','CW17866-PC','4XTHC 13','3','0','6','16','','11','','',
SELECT '7181','2009-11-30 00:00:00.000','3','LP','1','1115','71110','CW17866-PC','4XTHC 13','3.83333333333','0','1','16','','16','','',
SELECT '7182','2009-11-30 00:00:00.000','3','LP','2','1549','71940','CW17778-PVC','LCT 1805HP','6.83333333333','0','34','60','','27','','',
SELECT '7183','2009-11-30 00:00:00.000','3','LP','3','1180','71112','CW17866-PC','4XTHC 13','6','0','1','16','','16','','',
SELECT '7184','2009-11-30 00:00:00.000','3','LP','3','1180','71116','CW17866-PC','4XTHC 13','0.833333333333','0','1','2','','2','','',
SELECT '7185','2009-11-30 00:00:00.000','3','LP','4','848','71113','CW17866-PC','4XTHC 13','0.333333333333','0','16','16','','1','','',
SELECT '7186','2009-11-30 00:00:00.000','3','LP','4','848','71114','CW17866-PC','4XTHC 13','4','0','1','16','','16','','',
SELECT '7187','2009-11-30 00:00:00.000','3','LP','4','848','71115','CW17866-PC','4XTHC 13','2.5','0','1','10','','10','','',
SELECT '7188','2009-11-30 00:00:00.000','3','LP','5','1448','72226','CW16608-PCTL','KCRC 17','4.83333333333','0','1','43','','43','','',
SELECT '7189','2009-11-30 00:00:00.000','3','LP','6','1140','71070','CW16431-SC','2XTLC 17','4','0','12','32','','21','','',
SELECT '7190','2009-11-30 00:00:00.000','3','LP','6','1140','71102','CW16431-SC','2XTLC 17','2.83333333333','0','1','14','','14','','',
SELECT '7191','2009-11-30 00:00:00.000','3','LP','7','1327','70273','CW16759-SC','4XTLCP 11','2','0','5','12','','8','','',
SELECT '7192','2009-11-30 00:00:00.000','3','LP','8','1546','71118','CW16427-PC','4XTLC 09','1.83333333333','0','12','16','','5','','',
SELECT '7193','2009-11-30 00:00:00.000','3','LP','8','1546','71119','CW16427-PC','4XTLC 09','3','0','1','10','','10','','',
SELECT '7194','2009-11-30 00:00:00.000','3','J','9','1456','72224','CW17491-S','4JC 50','6.83333333333','0','1','11','','11','','',
SELECT '7195','2009-11-30 00:00:00.000','3','DJ','10','816','71051','CW17655-PVC','3XDJ 11','1','0','19','22','','4','','',
SELECT '7196','2009-11-30 00:00:00.000','3','DJ','10','816','72216','CW17655-PVCC','3XDJC 11','2.83333333333','0','1','19','','19','','',
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply