November 22, 2011 at 6:26 am
Hi I am looking for some help with a query I have written. It's using a union and a selct case within the expression. I would like to summarise it further as it currently shows various production lines and I would like to split the figures to show production or non-production. I am not interested in values for A,B,C,
Normally I would drop this into a Tmp table and query off that to give me a final value. but I am thinking I must be able to create some sort of nested selct that will pull the data for me summarised, ie querying this query and not including STK_PRODUCTION_LINE
thanks for any ideas.
SELECT DATEADD([hour], DATEDIFF([hour], 0, SCAN_ASSEMBLED_DATE), 0) AS DATETY, COUNT(PROD_ASSEMBLY_BARCODES.MATERIAL_ID) AS QTY, '***' AS TYPE,
CASE WHEN [STK_PRODUCTION_LINE] = 'F' THEN 'N' WHEN [STK_PRODUCTION_LINE] = 'H' THEN 'N' WHEN [STK_PRODUCTION_LINE] = 'J' THEN 'N' WHEN [STK_PRODUCTION_LINE]
= 'I' THEN 'N' ELSE 'Y' END AS 'MANUF', STK_PRODUCTION_LINE
FROM dbo.PROD_ASSEMBLY_BARCODES
WHERE (PROD_ASSEMBLY_BARCODES.SCAN_ASSEMBLED_DATE BETWEEN '01/11/2011' AND '01/11/2011 23:59:59')
GROUP BY DATEADD([hour], DATEDIFF([hour], 0, SCAN_ASSEMBLED_DATE), 0), STK_PRODUCTION_LINE
UNION ALL
SELECT DATEADD([hour], DATEDIFF([hour], 0, SCAN_DESPATCHED_DATE), 0) AS DATETY, COUNT(MATERIAL_ID) AS QTY, 'LOD' AS TYPE,
CASE WHEN [PROD_ASSEMBLY_BARCODES_1].[STK_PRODUCTION_LINE] = 'F' THEN 'N' WHEN [PROD_ASSEMBLY_BARCODES_1].[STK_PRODUCTION_LINE] = 'H'
THEN 'N' WHEN [PROD_ASSEMBLY_BARCODES_1].[STK_PRODUCTION_LINE] = 'J' THEN 'N' WHEN [PROD_ASSEMBLY_BARCODES_1].[STK_PRODUCTION_LINE] =
'I' THEN 'N' ELSE 'Y' END AS 'MANUF', STK_PRODUCTION_LINE
FROM dbo.PROD_ASSEMBLY_BARCODES AS PROD_ASSEMBLY_BARCODES_1
WHERE (SCAN_DESPATCHED_DATE BETWEEN '01/11/2011' AND '01/11/2011 23:59:59')
GROUP BY DATEADD([hour], DATEDIFF([hour], 0, SCAN_DESPATCHED_DATE), 0), STK_PRODUCTION_LINE
November 22, 2011 at 6:30 am
Formatted for sanity!!
SELECT DATEADD([hour], DATEDIFF([hour], 0, SCAN_ASSEMBLED_DATE), 0) AS DATETY,
COUNT(PROD_ASSEMBLY_BARCODES.MATERIAL_ID) AS QTY, '***' AS TYPE,
CASE WHEN [STK_PRODUCTION_LINE] = 'F'
THEN 'N'
WHEN [STK_PRODUCTION_LINE] = 'H'
THEN 'N'
WHEN [STK_PRODUCTION_LINE] = 'J'
THEN 'N'
WHEN [STK_PRODUCTION_LINE] = 'I'
THEN 'N'
ELSE 'Y' END AS 'MANUF', STK_PRODUCTION_LINE
FROM dbo.PROD_ASSEMBLY_BARCODES
WHERE (PROD_ASSEMBLY_BARCODES.SCAN_ASSEMBLED_DATE BETWEEN '01/11/2011' AND '01/11/2011 23:59:59')
GROUP BY DATEADD([hour], DATEDIFF([hour], 0, SCAN_ASSEMBLED_DATE), 0), STK_PRODUCTION_LINE
UNION ALL
SELECT DATEADD([hour], DATEDIFF([hour], 0, SCAN_DESPATCHED_DATE), 0) AS DATETY,
COUNT(MATERIAL_ID) AS QTY, 'LOD' AS TYPE, CASE WHEN [PROD_ASSEMBLY_BARCODES_1].[STK_PRODUCTION_LINE] = 'F'
THEN 'N'
WHEN [PROD_ASSEMBLY_BARCODES_1].[STK_PRODUCTION_LINE] = 'H'
THEN 'N'
WHEN [PROD_ASSEMBLY_BARCODES_1].[STK_PRODUCTION_LINE] = 'J'
THEN 'N'
WHEN [PROD_ASSEMBLY_BARCODES_1].[STK_PRODUCTION_LINE] = 'I'
THEN 'N'
ELSE 'Y' END AS 'MANUF', STK_PRODUCTION_LINE
FROM dbo.PROD_ASSEMBLY_BARCODES AS PROD_ASSEMBLY_BARCODES_1
WHERE (SCAN_DESPATCHED_DATE BETWEEN '01/11/2011' AND '01/11/2011 23:59:59')
GROUP BY DATEADD([hour], DATEDIFF([hour], 0, SCAN_DESPATCHED_DATE), 0), STK_PRODUCTION_LINE
When you have time, please read this article[/url] about the best way to provide us with working sample data and DDL scripts. This will allow the unpaid volunteers of this site to provide you with working, tested code for your particular problem.
Thanks.
This is how to do what you're asking about. But I think if you read the article I referenced above that we'll be able to make it a little bit more efficient.
SELECT DATETY, QTY, TYPE, MANUF
FROM (SELECT DATEADD([hour], DATEDIFF([hour], 0, SCAN_ASSEMBLED_DATE), 0) AS DATETY,
COUNT(PROD_ASSEMBLY_BARCODES.MATERIAL_ID) AS QTY, '***' AS TYPE,
CASE WHEN [STK_PRODUCTION_LINE] IN ('F','H','J','I')
THEN 'N'
ELSE 'Y' END AS MANUF, STK_PRODUCTION_LINE
FROM dbo.PROD_ASSEMBLY_BARCODES
WHERE (PROD_ASSEMBLY_BARCODES.SCAN_ASSEMBLED_DATE BETWEEN '01/11/2011' AND '01/11/2011 23:59:59')
GROUP BY DATEADD([hour], DATEDIFF([hour], 0, SCAN_ASSEMBLED_DATE), 0), STK_PRODUCTION_LINE
UNION ALL
SELECT DATEADD([hour], DATEDIFF([hour], 0, SCAN_DESPATCHED_DATE), 0) AS DATETY,
COUNT(MATERIAL_ID) AS QTY, 'LOD' AS TYPE,
CASE WHEN [STK_PRODUCTION_LINE] IN ('F','H','J','I')
THEN 'N'
ELSE 'Y' END AS MANUF, STK_PRODUCTION_LINE
FROM dbo.PROD_ASSEMBLY_BARCODES
WHERE (SCAN_DESPATCHED_DATE BETWEEN '01/11/2011' AND '01/11/2011 23:59:59')
GROUP BY DATEADD([hour], DATEDIFF([hour], 0, SCAN_DESPATCHED_DATE), 0), STK_PRODUCTION_LINE) subQuery
November 22, 2011 at 6:52 am
Thanks for the quick reply, sorry for the format!!!!!
I have been dropping such queries into tmp tables for ages!!!!!!
Something learnt there!!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply