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?
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_PLANTSELECT [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
SELECT
[JAN]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN (PC.TONS * 2000 / PM.EA_WT) / (PC.HOURS * 60) ELSE 0 END)|||Or...
...ELSE 1|||(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
??|||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.|||(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.|||I think you want to do this
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|||That looks promising. I'll give it a go.|||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
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|||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...
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|||Why...what did the output look like without distinct?|||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|||I still don't get it.
I would definetly figure out why, so as to not use DISTINCT as a hack|||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|||So shouldn't you be grouping?|||I could do a
Group By Report_Plant, Jan, Feb, Mar...
and get the same result. Is there a reason to do that over DISTINCT?|||I could do a
Group By Report_Plant, Jan, Feb, Mar...
and get the same result. Is there a reason to do that over DISTINCT?No.
BTW - you are scannig the table 12 times here. If it chugs along a bit slow you can optimise this:
....
WHERE YEAR(PC.MNTHYR) = YEAR(GETDATE())
AND MONTH(PC.MNTHYR) = 1
....to
PC.MNTHYR BETWEEN StartOfMonth AND EndOfMonth and ditto for the start and end of year. {Assuming it is indexed} this may be quicker.|||No speed issues. Takes less than a second to run. It's part of an overnight job, anyway.
Next problem, though...
This whole thing runs as part of a series of loops. The UPDATE portion of the query looks the same, I plug in a few different queries in the FROM portion,
and change the parameters in the WHERE portion. I guess you'd call it dynamic SQL. By the time I glue it all together, I end up with a string that's 7500 characters long. I'm afraid that when I rewrite the other queries that need it, I'll end up with some that are more than 8000 characters long.
The max length for VARCHAR appears to be 8000. So now what?|||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
I don't see how. You are GROUPING on Report_Plan
Post the Query you finished, DDL and sample data|||PC.MNTHYR BETWEEN StartOfMonth AND EndOfMonth and ditto for the start and end of year. {Assuming it is indexed} this may be quicker.
How do you figure???|||Split it into multiple statements and then concat when you execute them?
Declare @.a varchar(8000), @.b varchar(8000), @.c varchar(8000)
SET @.a = "SELECT dave "
SET @.b = "FROM thetable "
SET @.c = "WHERE name='dave'"
EXEC(@.a + @.b + @.c)|||Split it into multiple statements and then concat when you execute them?
Declare @.a varchar(8000), @.b varchar(8000), @.c varchar(8000)
SET @.a = "SELECT dave "
SET @.b = "FROM thetable "
SET @.c = "WHERE name='dave'"
EXEC(@.a + @.b + @.c)
Because it doesn't work that way
DECLARE @.a varchar(8000), @.b varchar(8000), @.c varchar(8000)
SELECT @.a = REPLICATE('a',8000), @.b = REPLICATE('b',8000), @.c = REPLICATE('c',8000)
SELECT LEN(@.a+@.b+@.c)
SELECT @.a+@.b+@.c|||I'm afraid to say I have no idea what replicate does... I've used the above method before when I was writing dynamic SQL (ok, it was at the beginning of my SQL learning ;)) and if you use the exec line, the concats are fine.|||How do you figure???Wiv me noggin govnor.
MONTH(PC.MNTHYR) = 1
is destined to scan.
PC.MNTHYR BETWEEN '20070201' AND '20070228'
is not. It might do but if there is sufficient data it will not.
I assume MNTHYR has no time element otherwise that needs covering too.|||Because it doesn't work that way
actually, EXEC(@.a + @.b) does work that way.
see: http://www.sommarskog.se/dynamic_sql.html#EXEC4
afaik this is the only advantage that EXEC has over sp_executesql when it comes to dynamic sql|||afaik this is the only advantage that EXEC has over sp_executesql when it comes to dynamic sqlUnless you are attempting to inject sql rather than, er, eject it of course :)|||Wiv me noggin govnor.
ummmmmm...did you execute the sample code?|||ummmmmm...did you execute the sample code?
Try it yourself :)
IF EXISTS (SELECT NULL FROM sys.tables WHERE name = N'date_t') BEGIN
DROP TABLE dbo.date_t
END
CREATE TABLE dbo.date_t
(
date_c SMALLDATETIME
, CONSTRAINT pk_date_t PRIMARY KEY NONCLUSTERED (date_c) WITH (FILLFACTOR = 100)
)
GO
INSERT dbo.date_t
SELECT DATEADD(d, number, 0)
FROM dbo.numbers
GO
SET showplan_all on
GO
SELECT *
FROM dbo.date_t
WHERE month(date_c) = 2
AND year(date_c) = 1901
SELECT *
FROM dbo.date_t
WHERE date_c BETWEEN '19010201' AND '19010228'
GO
SET showplan_all off
go
SELECT *
FROM dbo.date_t
WHERE month(date_c) = 2
AND year(date_c) = 1901 1 1 0 NULL NULL 1 NULL 2048 NULL NULL NULL 0.1531494 NULL NULL SELECT 0 NULL
|--Table Scan(OBJECT:([dh_test_db].[dbo].[date_t]), WHERE:(datepart(month,CONVERT_IMPLICIT(datetime,[dh_test_db].[dbo].[date_t].[date_c],0))=(2) AND datepart(year,CONVERT_IMPLICIT(datetime,[dh_test_db].[dbo].[date_t].[date_c],0))=(1901))) 1 2 1 Table Scan Table Scan OBJECT:([dh_test_db].[dbo].[date_t]), WHERE:(datepart(month,CONVERT_IMPLICIT(datetime,[dh_test_db].[dbo].[date_t].[date_c],0))=(2) AND datepart(year,CONVERT_IMPLICIT(datetime,[dh_test_db].[dbo].[date_t].[date_c],0))=(1901)) [dh_test_db].[dbo].[date_t].[date_c] 2048 0.08090278 0.0722466 11 0.1531494 [dh_test_db].[dbo].[date_t].[date_c] NULL PLAN_ROW 0 1
SELECT *
FROM dbo.date_t
WHERE date_c BETWEEN '19010201' AND '19010228' 2 3 0 NULL NULL 2 NULL 28.86587 NULL NULL NULL 0.003313752 NULL NULL SELECT 0 NULL
|--Index Seek(OBJECT:([dh_test_db].[dbo].[date_t].[pk_date_t]), SEEK:([dh_test_db].[dbo].[date_t].[date_c] >= CONVERT_IMPLICIT(smalldatetime,[@.1],0) AND [dh_test_db].[dbo].[date_t].[date_c] <= CONVERT_IMPLICIT(smalldatetime,[@.2],0)) ORDERED FORWARD) 2 4 3 Index Seek Index Seek OBJECT:([dh_test_db].[dbo].[date_t].[pk_date_t]), SEEK:([dh_test_db].[dbo].[date_t].[date_c] >= CONVERT_IMPLICIT(smalldatetime,[@.1],0) AND [dh_test_db].[dbo].[date_t].[date_c] <= CONVERT_IMPLICIT(smalldatetime,[@.2],0)) ORDERED FORWARD [dh_test_db].[dbo].[date_t].[date_c] 28.86587 0.003125 0.0001887525 11 0.003313752 [dh_test_db].[dbo].[date_t].[date_c] NULL PLAN_ROW 0 1
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment