February 3, 2015 at 7:40 am
Good Morning:
I'm trying to run a query to check the downtime in production lines, but if a line has assigned more than one cause for the downtime it repeat the info for each cause.
this is the code.
SELECT D.Line AS Line, D.ProductionLine AS ProductionLine, D.Shift AS Shift, D.DownTime, CONVERT(VARCHAR(10), D.DatePacked,101) AS DatePacked, AssignedDowntime, (D.DownTime - AssignedDowntime) AS NOASSIGNED,
R.Enviromental,R.Equipment, R.IT_Systems, R.Material_External,R.Quality,R.Material_Internal,
R.Method,R.PreProduction,R.People
FROM (
SELECT Line, Shift, DatePacked, SUM(Cast(Downtime AS INT)) AS AssignedDowntime,
CASE
WHEN Category = '1' THEN SUM(Cast(ISNULL(Downtime,0) AS INT)) END AS Enviromental,
CASE
WHEN Category = '2' THEN SUM(Cast(ISNULL(Downtime,0) AS INT)) END AS Equipment,
CASE
WHEN Category = '3' THEN SUM(Cast(ISNULL(Downtime,0) AS INT)) END AS IT_Systems,
CASE
WHEN Category = '4' THEN SUM(Cast(ISNULL(Downtime,0) AS INT)) END AS Material_External,
CASE
WHEN Category = '5' THEN SUM(Cast(ISNULL(Downtime,0) AS INT)) END AS Quality,
CASE
WHEN Category = '6' THEN SUM(Cast(ISNULL(Downtime,0) AS INT)) END AS Material_Internal,
CASE
WHEN Category = '7' THEN SUM(Cast(ISNULL(Downtime,0) AS INT)) END AS Method,
CASE
WHEN Category = '8' THEN SUM(Cast(ISNULL(Downtime,0) AS INT)) END AS PreProduction,
CASE
WHEN Category = '10' THEN SUM(Cast(ISNULL(Downtime,0) AS INT)) END AS People
FROM Production.DownTimeReason
WHERE (CONVERT(VARCHAR(10), DatePacked, 101) = CONVERT(VARCHAR(10), '01/29/2015' , 101) )
GROUP BY Line, Shift, DatePacked, Category
) AS R
RIGHT outer JOIN (
SELECT Line, Shift, DatePacked, ProductionLine,
SUM(CAST(DownTime AS INT)) AS DownTime
FROM Production.DownTimeHistory
WHERE ((CONVERT(VARCHAR(10), DatePacked, 101) = CONVERT(VARCHAR(10), '01/29/2015' , 101) )
AND Shift = '1')
GROUP BY Line, Shift, DatePacked, ProductionLine
) AS D
ON D.Line = R.Line
AND D.Shift = R.Shift
AND D.DatePacked = R.DatePacked
ORDER BY D.Line, D.Shift, D.DatePacked
I'm expecting that if is more than one "Down Reason "it will include in the same line.
At this moment if i have more than one reason it create a line for each one for example:
If i have a total Downtime of 50 minutes and they are assigned 10 for itequipment, 30 by testequipment and 10 assigned to quality issues i will have and output like this:
Line Total_Downttime By itequipment by_testequipment bypeopleissues byquality
line1 50 0 30 0 0
line1 50 10 0 0 0
line1 50 0 0 0 10
What i want is to have a output like this:
Line Total_Downttime By itequipment by_testequipment bypeopleissues byquality
line1 50 10 30 0 10
All in one line.
I hope i explain this correctly
Thanks in Advance
February 4, 2015 at 3:06 am
Do you need to see the reasons in your "Production.DownTimeReason" table?
If not, you can create a CTE (Common Table Expression) or a #table (temp table), into which you insert the values that you need from the downtime reason table.
The rows in this table must be pre-aggregated so that you only have 1 row per instance, otherwise when you join to it you'll end up with multiple rows like you are getting now.
If you do need to see the reasons then you could maybe show only the latest one, in order to keep your record set at one row per instance.
You could achieve this by using ROW_NUMBER in your CTE or #table and then selecting only the records that have a "1".
With regards to the case statements, you are on the right track in order to get your values all on one line.
February 4, 2015 at 3:51 am
-- Before attempting to fix the results set of your query,
-- you might want to do some remedial work on the components of it.
-- Here's a little sample set and query which should give you a few hints.
DROP TABLE #DownTimeReason
CREATE TABLE #DownTimeReason (Line VARCHAR(6), Shift VARCHAR(2), DatePacked DATE, Category VARCHAR(2), Downtime INT)
INSERT INTO #DownTimeReason (Line, Shift, DatePacked, Category, Downtime)
SELECT 'Line1', '1', GETDATE(), '1', 1 UNION ALL
SELECT 'Line1', '1', GETDATE(), '1', 2 UNION ALL
SELECT 'Line1', '1', GETDATE(), '1', 3 UNION ALL
SELECT 'Line1', '1', GETDATE(), '2', 10 UNION ALL
SELECT 'Line1', '1', GETDATE(), '2', 20 UNION ALL
SELECT 'Line1', '1', GETDATE(), '2', NULL UNION ALL
SELECT 'Line1', '1', GETDATE(), '3', 101 UNION ALL
SELECT 'Line1', '1', GETDATE(), '3', 102 UNION ALL
SELECT 'Line1', '1', GETDATE(), '3', 103 UNION ALL
SELECT 'Line1', '1', GETDATE(), '4', 4000 UNION ALL
SELECT 'Line1', '1', GETDATE(), '5', 5000 UNION ALL
SELECT 'Line1', '1', GETDATE(), '6', 6000 UNION ALL
SELECT 'Line1', '1', GETDATE(), '7', 7000 UNION ALL
SELECT 'Line1', '1', GETDATE(), '8', 8000 UNION ALL
SELECT 'Line1', '1', GETDATE(), '9', 9000 UNION ALL
SELECT 'Line1', '1', GETDATE(), '10', 10000 UNION ALL
SELECT 'Line2', '1', GETDATE(), '1', 1 UNION ALL
SELECT 'Line2', '1', GETDATE(), '1', 2 UNION ALL
SELECT 'Line2', '1', GETDATE(), '1', 3 UNION ALL
SELECT 'Line2', '1', GETDATE(), '2', 10 UNION ALL
SELECT 'Line2', '1', GETDATE(), '2', 20 UNION ALL
SELECT 'Line2', '1', GETDATE(), '2', NULL UNION ALL
SELECT 'Line2', '1', GETDATE(), '3', 101 UNION ALL
SELECT 'Line2', '1', GETDATE(), '3', 102 UNION ALL
SELECT 'Line2', '1', GETDATE(), '3', 103
SELECT Line, Shift, DatePacked, SUM(Cast(Downtime AS INT)) AS AssignedDowntime,
SUM(CASE WHEN Category = '1' THEN Downtime END) AS Enviromental,
SUM(CASE WHEN Category = '2' THEN Downtime END) AS Equipment,
SUM(CASE WHEN Category = '3' THEN Downtime END) AS IT_Systems,
SUM(CASE WHEN Category = '4' THEN Downtime END) AS Material_External,
SUM(CASE WHEN Category = '5' THEN Downtime END) AS Quality,
SUM(CASE WHEN Category = '6' THEN Downtime END) AS Material_Internal,
SUM(CASE WHEN Category = '7' THEN Downtime END) AS Method,
SUM(CASE WHEN Category = '8' THEN Downtime END) AS PreProduction,
SUM(CASE WHEN Category = '10' THEN Downtime END) AS People
FROM #DownTimeReason
WHERE CAST(DatePacked AS DATE) = CAST('20150204' AS DATETIME)
GROUP BY Line, Shift, DatePacked
-- What's this for? Aggregates almost always eliminate NULLs.
Cast(ISNULL(Downtime,0) AS INT)
-- Don't use RIGHT JOIN's, convert the query to use a LEFT JOIN.
-- Many people have difficulty scanning and understanding queries
-- using RIGHT JOINs and will curse you for it, even though the results are the same.
RIGHT outer JOIN
-- This comparison can only be achieved with an index or table scan,
-- resulting in a slower query than it needs to be. It's a simple change,
-- see my query.
WHERE (CONVERT(VARCHAR(10), DatePacked, 101) = CONVERT(VARCHAR(10), '01/29/2015' , 101) )
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply