Problem with duplicate lines

Question

Problem with duplicate lines

laorozco1

SSC Enthusiast

Points: 189
More actions
January 30, 2015 at 7:22 pm

#397954

I'm trying to run a query to check the downtime in production lines, but if a line have 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
and attached is the query result with the duplicate data.
Can you helpme to identify what I'm doing bad??
Thanks in advance.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

You're not actually doing it "bad". It's running the way I'd expect it to without any additional information. With that thought in mind...

Please identify what you want the output to look like when more than 1 "down reason" is present.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

laorozco1 SSC Enthusiast Points: 189 More actions · Answer 2

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