Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: Eliminate Division by Zero

    This query is part of a larger query that updates a table that holds statistics for reporting. It yields actual Unit per Minute by plant by month. Some of the plants don't produce anything in certain months, so I'm ending up with a Divide by Zero error. I think I just need to stick another CASE statement in for each month, but that seems like it could get pretty ugly.

    Any suggestions on how to improve this?

    Code:
    SELECT     FL.REPORT_PLANT, 
    	[JAN]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
    		 SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.HOURS*60 ELSE 0 END),
    	[FEB]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 2 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ 
    		SUM(CASE WHEN MONTH(PC.MNTHYR) = 2 THEN PC.HOURS*60 ELSE 0 END),
    	[MAR]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 3 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ 
    		SUM(CASE WHEN MONTH(PC.MNTHYR) = 3 THEN PC.HOURS*60 ELSE 0 END),
    	[APR]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 4 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
    		SUM(CASE WHEN MONTH(PC.MNTHYR) = 4 THEN PC.HOURS*60 ELSE 0 END),
    	[MAY]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 5 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
    		SUM(CASE WHEN MONTH(PC.MNTHYR) = 5 THEN PC.HOURS*60 ELSE 0 END),
    	[JUN]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 6 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
    		SUM(CASE WHEN MONTH(PC.MNTHYR) = 6 THEN PC.HOURS*60 ELSE 0 END),
    	[JUL]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 7 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
    		SUM(CASE WHEN MONTH(PC.MNTHYR) = 7 THEN PC.HOURS*60 ELSE 0 END),
    	[AUG]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 8 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
    		SUM(CASE WHEN MONTH(PC.MNTHYR) = 8 THEN PC.HOURS*60 ELSE 0 END),
    	[SEP]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 9 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
    		SUM(CASE WHEN MONTH(PC.MNTHYR) = 9 THEN PC.HOURS*60 ELSE 0 END),
    	[OCT]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 10 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
    		SUM(CASE WHEN MONTH(PC.MNTHYR) = 10 THEN PC.HOURS*60 ELSE 0 END),
    	[NOV]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 11 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
    		SUM(CASE WHEN MONTH(PC.MNTHYR) = 11 THEN PC.HOURS*60 ELSE 0 END),
    	[DEC]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 12 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
    		SUM(CASE WHEN MONTH(PC.MNTHYR) = 12 THEN PC.HOURS*60 ELSE 0 END)
    FROM         PRODUCTION_CMPLT PC INNER JOIN
                          FACILITY_LINES FL ON PC.MANUF_SITE = FL.MANUF_SITE AND 
                          PC.PROD_LINE = FL.PROD_LINE  INNER JOIN
    		PROD_MASTER PM ON PC.PRODUCT=PM.PRODUCT
    WHERE     YEAR(PC.MNTHYR) = YEAR(GETDATE()) AND PM.UOM<>'LB'
    GROUP BY FL.REPORT_PLANT
    Inspiration Through Fermentation

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT [JAN]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
    		 SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.HOURS*60 ELSE 0 END)
    the problem lies in your second CASE expression yielding 1 when the sum is not =1. What you want is for the whole expression to yield 0 if that =1...
    I think this might be the what you want
    Code:
    SELECT
    	[JAN]=  SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN (PC.TONS * 2000 / PM.EA_WT) / (PC.HOURS * 60) ELSE 0 END)
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Or...
    Code:
    ...ELSE 1
    George
    Home | Blog

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    (select Case When Ea_wt = 0 Then 1 Else Ea_wt End From Prod_master) As Pm

    Or


    Where Year(pc.mnthyr) = Year(getdate()) And Pm.uom<>'lb'
    And Pm. Ea_wt <> 0


    ????
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    re: georgev

    ahhh... But I need Sum(Units)/Sum(Minutes), which is not the same as
    Sum(Units/Minutes)

    Changing to Else 1 yields a "false positive" in the off months, and throws off my numbers.
    Inspiration Through Fermentation

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by Brett Kaiser
    (select Case When Ea_wt = 0 Then 1 Else Ea_wt End From Prod_master) As Pm

    Or


    Where Year(pc.mnthyr) = Year(getdate()) And Pm.uom<>'lb'
    And Pm. Ea_wt <> 0


    ????
    I don't have a problem with ea_wt. There is a constraint on the table that doesn't allow that field to be equal to 0.
    Inspiration Through Fermentation

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I think you want to do this

    Code:
        SELECT  FL.REPORT_PLANT, 
    	  , [JAN] = PC.TONS * 2000 /PM.EA_WT/PC.HOURS*60
          FROM  PRODUCTION_CMPLT PC 
    INNER JOIN  FACILITY_LINES FL 
    	ON  PC.MANUF_SITE = FL.MANUF_SITE 
           AND  PC.PROD_LINE = FL.PROD_LINE  
    INNER JOIN  PROD_MASTER PM 
    	ON PC.PRODUCT=PM.PRODUCT
         WHERE  YEAR(PC.MNTHYR) = YEAR(GETDATE()) 
           AND  MONTH(PC.MNTHYR) = 1
           AND  PM.UOM<>'LB'
      GROUP BY  FL.REPORT_PLANT
     UNION ALL
        SELECT  FL.REPORT_PLANT, 
    	  , [FEB] = PC.TONS * 2000 /PM.EA_WT/PC.HOURS*60
          FROM  PRODUCTION_CMPLT PC 
    INNER JOIN  FACILITY_LINES FL 
    	ON  PC.MANUF_SITE = FL.MANUF_SITE 
           AND  PC.PROD_LINE = FL.PROD_LINE  
    INNER JOIN  PROD_MASTER PM 
    	ON PC.PRODUCT=PM.PRODUCT
         WHERE  YEAR(PC.MNTHYR) = YEAR(GETDATE()) 
           AND  MONTH(PC.MNTHYR) = 2
           AND  PM.UOM<>'LB'
      GROUP BY  FL.REPORT_PLANT
     UNION ALL
    ..ect
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    That looks promising. I'll give it a go.
    Inspiration Through Fermentation

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Actually, with that one you will need a labl;e for each union to know what month it is...but you could do this as columns like you have

    Code:
        SELECT * 
          FROM FACILITY_LINES xxx
    INNER JOIN (
      
    	    SELECT  FL.REPORT_PLANT
    		  , [JAN] = PC.TONS * 2000 /PM.EA_WT/PC.HOURS*60
    	      FROM  PRODUCTION_CMPLT PC 
    	INNER JOIN  FACILITY_LINES FL 
    		ON  PC.MANUF_SITE = FL.MANUF_SITE 
    	       AND  PC.PROD_LINE = FL.PROD_LINE  
    	INNER JOIN  PROD_MASTER PM 
    		ON PC.PRODUCT=PM.PRODUCT
    	     WHERE  YEAR(PC.MNTHYR) = YEAR(GETDATE()) 
    	       AND  MONTH(PC.MNTHYR) = 1
    	       AND  PM.UOM<>'LB'
    	  GROUP BY  FL.REPORT_PLANT) AS JAN
    	ON xxx.REPORT_PLANT = JAN.REPORT_PLANT
    INNER JOIN (
    	    SELECT  FL.REPORT_PLANT
    		  , [FEB] = PC.TONS * 2000 /PM.EA_WT/PC.HOURS*60
    	      FROM  PRODUCTION_CMPLT PC 
    	INNER JOIN  FACILITY_LINES FL 
    		ON  PC.MANUF_SITE = FL.MANUF_SITE 
    	       AND  PC.PROD_LINE = FL.PROD_LINE  
    	INNER JOIN  PROD_MASTER PM 
    		ON PC.PRODUCT=PM.PRODUCT
    	     WHERE  YEAR(PC.MNTHYR) = YEAR(GETDATE()) 
    	       AND  MONTH(PC.MNTHYR) = 2
    	       AND  PM.UOM<>'LB'
    	  GROUP BY  FL.REPORT_PLANT) AS FEB
    	ON xxx.REPORT_PLANT = FEB.REPORT_PLANT
    ..ect
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    For some reason -that I can't figure out- I had to use SELECT DISTINCT.
    I also had to use a LEFT JOIN for each subquery...

    Code:
    SELECT DISTINCT FL.REPORT_PLANT, JAN, FEB, MAR, APR, MAY
    FROM FACILITY_LINES FL
    LEFT OUTER JOIN (
     SELECT  FL.REPORT_PLANT, [JAN] =sum( PC.TONS * 2000 /PM.EA_WT)/sum(PC.HOURS*60)
    	      FROM  PRODUCTION_CMPLT PC 
    	INNER JOIN  FACILITY_LINES FL 
    		ON  PC.MANUF_SITE = FL.MANUF_SITE 
    	       AND  PC.PROD_LINE = FL.PROD_LINE  
    	INNER JOIN  PROD_MASTER PM 
    		ON PC.PRODUCT=PM.PRODUCT
    	     WHERE  YEAR(PC.MNTHYR) = YEAR(GETDATE()) 
    	       AND  MONTH(PC.MNTHYR) = 1
    	       AND  PM.UOM<>'LB'
    	  GROUP BY  FL.REPORT_PLANT) AS JAN
    	ON FL.REPORT_PLANT = JAN.REPORT_PLANT
    LEFT OUTER JOIN (
    	    SELECT  FL.REPORT_PLANT
    		  , [FEB] = SUM(PC.TONS * 2000 /PM.EA_WT)/sUM(PC.HOURS*60)
    	      FROM  PRODUCTION_CMPLT PC 
    	INNER JOIN  FACILITY_LINES FL 
    		ON  PC.MANUF_SITE = FL.MANUF_SITE 
    	       AND  PC.PROD_LINE = FL.PROD_LINE  
    	INNER JOIN  PROD_MASTER PM 
    		ON PC.PRODUCT=PM.PRODUCT
    	     WHERE  YEAR(PC.MNTHYR) = YEAR(GETDATE()) 
    	       AND  MONTH(PC.MNTHYR) = 2
    	       AND  PM.UOM<>'LB'
    	  GROUP BY  FL.REPORT_PLANT) AS FEB
    	ON FL.REPORT_PLANT = FEB.REPORT_PLANT
    ...
    That's exactly what I needed.

    Thanks
    Mark
    Inspiration Through Fermentation

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why...what did the output look like without distinct?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    The Facility Lines table can have multiple production lines for each report plant.

    Facility Lines
    ==========
    PLANT
    PROD_LINE (pk)
    REPORT_PLANT

    I was getting one record for each production line
    Inspiration Through Fermentation

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I still don't get it.

    I would definetly figure out why, so as to not use DISTINCT as a hack
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Since the table has data like this:

    Plant----Prod_Line----Report_Plant
    ==========================
    -1---------A---------PLANT1
    -1---------B---------PLANT1
    -1---------C---------PLANT1
    -2---------D---------PLANT2
    -3---------E---------PLANT3

    My output looks like (without using distinct)

    Report_Plant--JAN--FEB--MAR...
    ========================
    PLANT1-------10---9-----10
    PLANT1-------10---9-----10
    PLANT1-------10---9-----10
    PLANT2-------17---24----25
    PLANT3-------8----6-----7
    Inspiration Through Fermentation

  15. #15
    Join Date
    Jul 2007
    Posts
    54
    So shouldn't you be grouping?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •