SUM(Case) issue

  • 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

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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!

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • 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'

  • 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

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell,

    Same results.

    Thanks,

    Khalif

  • 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?

  • 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

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

  • 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

  • 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