CODEWITHSUNDEEP
sqlexcelopenedgeprogress-dbms-query
Jack Williams
Jack Williams

Reputation: 141

How to improve CASE WHEN statement to return result on same line

Currently working on some SQL script from a OpenEdge progress database. At the moment i am trying to return time for an event based on if it was Estimated or Actual. This can be identified via a result in a column. The problem i get is the way the data work is it shows the same EveNumber but on two lines (one for estimated one for actual). I would like to combine these rows into one.

I have tried the code below but this gets me the result still of two separate lines. I tried putting the case when also into the Else section but this didn't work. Below is my condensed code: 

SELECT 

Event_0.EveNumber
, (CASE WHEN Object_0.ObjName = '818 Artwork typeset duration' THEN TemplateRunObject_0.TroValue ELSE NULL END) AS 'Actual'
, (CASE WHEN Object_0.ObjName = '818 Artwork Estimated typeset duration' THEN TemplateRunObject_0.TroValue ELSE NULL END) AS 'Estimated'

FROM 
SBS.PUB.Event Event_0
LEFT JOIN SBS.PUB.TemplateRunObject TemplateRunObject_0 ON Event_0.TemplateRunID = TemplateRunObject_0.TemplateRunID 
JOIN SBS.PUB.Object Object_0 ON TemplateRunObject_0.ObjectId = Object_0.ObjectId

Orginal Data layout

Evenumber             Ogjname                              TroValue
123          818 Artwork typeset duration                     15
123          818 Artwork Estimated typeset duration           30

Currently the results look like this:

EveNumber          Actual          Estimated
123                 15                0
123                  0                30

How i want them to appear

EveNumber          Actual          Estimated
123                 15                30

Upvotes: 0

Views: 801

Answers (2)

cansincini
cansincini

Reputation: 11

Its help you ; MAX() function . But you should forget GROUP BY.

   SELECT Event_0.EveNumber
    , (MAX(CASE WHEN Object_0.ObjName = '818 Artwork typeset duration' THEN TemplateRunObject_0.TroValue ELSE NULL END)) AS 'Actual'
    , (MAX(CASE WHEN Object_0.ObjName = '818 Artwork Estimated typeset duration' THEN TemplateRunObject_0.TroValue ELSE NULL END)) AS 'Estimated'
    FROM 
    SBS.PUB.Event Event_0
    LEFT JOIN SBS.PUB.TemplateRunObject TemplateRunObject_0 ON Event_0.TemplateRunID = TemplateRunObject_0.TemplateRunID 
    JOIN SBS.PUB.Object Object_0 ON TemplateRunObject_0.ObjectId = Object_0.ObjectId
    GROUP BY Event_0.EveNumber

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

use max() and group by

SELECT 

Event_0.EveNumber
, max((CASE WHEN Object_0.ObjName = '818 Artwork typeset duration' THEN TemplateRunObject_0.TroValue ELSE NULL END) ) AS 'Actual'
,max( (CASE WHEN Object_0.ObjName = '818 Artwork Estimated typeset duration' THEN TemplateRunObject_0.TroValue ELSE NULL END) )AS 'Estimated'

FROM 
SBS.PUB.Event Event_0
LEFT JOIN SBS.PUB.TemplateRunObject TemplateRunObject_0 ON Event_0.TemplateRunID = TemplateRunObject_0.TemplateRunID 
JOIN SBS.PUB.Object Object_0 ON TemplateRunObject_0.ObjectId = Object_0.ObjectId
group by  Event_0.EveNumber

Upvotes: 1

Related Questions