Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2011
    Posts
    5

    Unanswered: Grouping Like Data

    I have a factory production database that I need to extract some tricky info from. Every product we run has a unique code (PREMIX CODE). Each premix code belongs to a family (ROOT BLEND). I need to calculate the average run size for a root blend. Average run size is just taking the total amount of product run over consecutive shifts/days. I can't figure out how to tell whether data is consecutive or not.

    Here's some sample data:

    Code:
    create table #tmpdata (
    	PRDCTN_DAY	DATETIME,
    	SHIFT		INTEGER,
    	PREMIX_CODE	VARCHAR(12),
    	ROOT_BLEND	VARCHAR(12),
    	UNITS_PROD	INTEGER
    )
    
    INSERT INTO #TMPDATA (PRDCTN_DAY, SHIFT, PREMIX_CODE, ROOT_BLEND, UNITS_PROD)
    SELECT '8/25/2011', 3, 'PM24010P', '24-0-10', 12000
    UNION
    SELECT '8/25/2011', 1, 'PM24010P', '24-0-10', 22000
    UNION
    SELECT '8/25/2011', 2, 'PM24010P', '24-0-10', 20000
    UNION
    SELECT '8/26/2011', 3, 'PM24010P', '24-0-10', 8000
    UNION
    SELECT '8/26/2011', 3, 'PM14714NDG', '14-7-14', 5000
    UNION
    SELECT '8/26/2011', 1, 'PM14714NDG', '14-7-14', 5000
    UNION
    SELECT '8/26/2011', 1, 'PM14714HVG', '14-7-14', 17000
    UNION
    SELECT '8/26/2011', 2, 'PM14714NDG', '14-7-14', 28000
    UNION
    SELECT '8/27/2011', 3, 'PM14714NDG', '14-7-14', 27000
    UNION
    SELECT '8/27/2011', 1, 'PM13026HVG', '13-0-26', 30000
    
    SELECT PRDCTN_DAY, SHIFT, ROOT_BLEND, UNITS_PROD 
    FROM #TMPDATA
    ORDER BY PRDCTN_DAY,  CASE SHIFT WHEN 3 THEN 'A' WHEN 1 THEN 'B' WHEN 2 THEN 'C' END
    
    DROP TABLE #TMPDATA
    My select statement almost gets the data in the right order - except where 2 products are run in the same shift.

    The result I'm looking for would be something like:
    ROOT_BLEND UNITS_PROD
    24-0-10 62000
    14-7-14 82000
    13-0-26 30000

    So what I need is to SUM records if the root blend is the same as what was run on the previous shift.

    Any suggestions to get me going in the right direction?

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I don't really understand what you want. This query gives the result you gave as the desired result.
    Code:
    SELECT 
    	ROOT_BLEND, 
    	SUM(UNITS_PROD) as UNITS_PROD
    FROM #TMPDATA
    GROUP BY 
    	ROOT_BLEND
    ORDER BY 
    	ROOT_BLEND
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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