Thread: Grouping Like Data
09-08-11, 14:27 #1Registered User
- Join Date
- Sep 2011
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:
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
The result I'm looking for would be something like:
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?
09-08-11, 19:04 #2Registered User
Provided Answers: 4
- Join Date
- Nov 2004
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_BLENDWith kind regards . . . . . SQL Server 2000/2005/2012
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