combine 2 rows into 1 row

Question

combine 2 rows into 1 row

kss113

SSChasing Mays

Points: 654
More actions
October 30, 2012 at 6:37 am

#284325

Hello All,
I hope someone can help me out here.
Currently my script will bring back 2 rows, all the fields in both rows are identical except for the Source field, this field is either "Labor" or "Parts" and "Total Amount"
I need to combine both rows into one row.
EquipIDLocationCyclesDateTimeStampSourceTotal Amount
JRH-681Daytona20793472012-01-17 06:36:22.897Labor2237.5
JRH-681Daytona20793472012-01-17 06:36:22.897Parts27791.85
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '1/1/2012'
SET @EndDate = '1/31/2012'
SELECT B.EngEquipID
,B.Location
,B.Cycles
,B.DateTimeStamp
,B.Source
,SUM(B.TRANS_tambase_amt) as [Total Amount]
FROM (
SELECT ER.EngEquipID
,ER.Location
,ER.Cycles
,ER.DateTimeStamp
,A.Source
,A.TRANS_tambase_amt
FROM Engine_Prod.mc.WORKORDER WO
LEFT OUTER JOIN MSM.dbo.tblEngRunData ER ON ER.WONumber = ltrim(rtrim(WO.id))
LEFT OUTER JOIN mc.WORKTASK WT ON WO.wordoi = WT.wrkord_oi
LEFT OUTER JOIN mc.WORKORDERTYPE WOT ON WO.wot_oi = WOT.wotyoi
LEFT OUTER JOIN (
SELECT 'Labor' Source
,CHARGEINF.wt_oi
,LABORTRANS.tambase_amt TRANS_tambase_amt
FROM mc.CHARGEINF CHARGEINF
INNER JOIN mc.lbtr_chgs lbtr_chgs ON CHARGEINF.chgioi = lbtr_chgs.ref_oi
INNER JOIN mc.LABORTRANS LABORTRANS ON lbtr_chgs.owner_oi = LABORTRANS.lbtroi
WHERE LABORTRANS.posted = 1
UNION ALL
SELECT 'Parts' Source
,CHARGEINF.wt_oi
,INVTRANS.tambase_amt TRANS_tambase_amt
FROM mc.CHARGEINF CHARGEINF
INNER JOIN mc.intr_chgs intr_chgs ON CHARGEINF.chgioi = intr_chgs.ref_oi
INNER JOIN mc.INVTRANS INVTRANS ON intr_chgs.owner_oi = INVTRANS.introi
WHERE INVTRANS.posted = 1
) A ON WT.wtskoi = A.wt_oi
WHERE ER.DateTimeStamp BETWEEN @StartDate
AND @EndDate
AND WO.clsdt_date BETWEEN @StartDate
AND @EndDate
AND WT.ci_cnc_date IS NULL
AND WT.tmplt = 0
AND A.Source is not null
GROUP BY ER.EngEquipID
,ER.Location
,ER.Cycles
,ER.DateTimeStamp
,A.Source
,A.TRANS_tambase_amt
) B
GROUP BY B.EngEquipID
,B.Location
,B.Cycles
,B.DateTimeStamp
,B.Source

Viewing 9 posts - 1 through 8 (of 8 total)

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

Mike.Hahn SSCarpal Tunnel Points: 4053 More actions · Answer 1

What do you want the "Source" field to say when the 2 rows are combined? I'm assuming the TotalAmount is summed but we can't really sum the source field. Can you give samples of what you're looking for as a result.

---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D

Gazareth One Orange Chip Points: 27737 More actions · Answer 2

Just remove the B.Source field from your outer SELECT & GROUP BY

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

What do you need the output to be

EquipID Location Cycles DateTimeStamp LaborAmount PartsAmount TotalAmount

JRH-681 Daytona 2079347 2012-01-17 06:36:22.897 2237.5 27791.85 30029.35

EquipID Location Cycles DateTimeStamp TotalAmount

JRH-681 Daytona 2079347 2012-01-17 06:36:22.897 30029.35

kss113 SSChasing Mays Points: 654 More actions · Answer 4

This is what I'd want my output to be.

From:

JRH-681 Daytona 2079347 2012-01-17 06:36:22.897 Labor 2237.5

JRH-681 Daytona 2079347 2012-01-17 06:36:22.897 Parts 27791.85

To:

JRH-681 Daytona 2079347 2012-01-17 06:36:22.897 Labor 2237.5 Parts 27791.85

source amount source amount

So Labor 2237 and parts 27791 are on the same line, with the rest of the info.

kss113 SSChasing Mays Points: 654 More actions · Answer 5

Doing that just gives me a total of 30029.35, both parts and labor added together, I need them separate fields/columns

Parts 27791.85 labor 2237.50

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

EDIT: Nevermind, I hadn't examined your code.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Ant-Green SSC Guru Points: 113507 More actions · Answer 7

Based on your original code and if you only ever have parts and labor then the below should work.

Not tested due to not having the DDL of the tables and sample data but the general idea is there for you to tweak.

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

SET @StartDate = '1/1/2012'

SET @EndDate = '1/31/2012'

SELECT B.EngEquipID

,B.Location

,B.Cycles

,B.DateTimeStamp

,'Labor' AS Labour

,CASE WHEN B.Source = 'Labor' THEN SUM(B.TRANS_tambase_amt) END as [Labor Total Amount]

,'Parts' AS Parts

,CASE WHEN B.Source = 'Parts' THEN SUM(B.TRANS_tambase_amt) END as [Parts Total Amount]

FROM (

SELECT ER.EngEquipID

,ER.Location

,ER.Cycles

,ER.DateTimeStamp

,A.Source

,A.TRANS_tambase_amt

FROM Engine_Prod.mc.WORKORDER WO

LEFT OUTER JOIN MSM.dbo.tblEngRunData ER ON ER.WONumber = ltrim(rtrim(WO.id))

LEFT OUTER JOIN mc.WORKTASK WT ON WO.wordoi = WT.wrkord_oi

LEFT OUTER JOIN mc.WORKORDERTYPE WOT ON WO.wot_oi = WOT.wotyoi

LEFT OUTER JOIN (

SELECT 'Labor' Source

,CHARGEINF.wt_oi

,LABORTRANS.tambase_amt TRANS_tambase_amt

FROM mc.CHARGEINF CHARGEINF

INNER JOIN mc.lbtr_chgs lbtr_chgs ON CHARGEINF.chgioi = lbtr_chgs.ref_oi

INNER JOIN mc.LABORTRANS LABORTRANS ON lbtr_chgs.owner_oi = LABORTRANS.lbtroi

WHERE LABORTRANS.posted = 1

UNION ALL

SELECT 'Parts' Source

,CHARGEINF.wt_oi

,INVTRANS.tambase_amt TRANS_tambase_amt

FROM mc.CHARGEINF CHARGEINF

INNER JOIN mc.intr_chgs intr_chgs ON CHARGEINF.chgioi = intr_chgs.ref_oi

INNER JOIN mc.INVTRANS INVTRANS ON intr_chgs.owner_oi = INVTRANS.introi

WHERE INVTRANS.posted = 1

) A ON WT.wtskoi = A.wt_oi

WHERE ER.DateTimeStamp BETWEEN @StartDate

AND @EndDate

AND WO.clsdt_date BETWEEN @StartDate

AND @EndDate

AND WT.ci_cnc_date IS NULL

AND WT.tmplt = 0

AND A.Source is not null

GROUP BY ER.EngEquipID

,ER.Location

,ER.Cycles

,ER.DateTimeStamp

,A.Source

,A.TRANS_tambase_amt

) B

GROUP BY B.EngEquipID

,B.Location

,B.Cycles

,B.DateTimeStamp

--,B.Source

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

I believe there's a way to do it much simpler. But you'll need to test.

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

SET @StartDate = '1/1/2012'

SET @EndDate = '1/31/2012'

SELECT ER.EngEquipID

,ER.Location

,ER.Cycles

,ER.DateTimeStamp

,'Labor' AS Labor

,ISNULL( A.TRANS_tambase_amt, 0) AS LaborAmount

,'Parts' AS Parts

,ISNULL( B.TRANS_tambase_amt, 0) AS PartsAmount

,ISNULL( A.TRANS_tambase_amt, 0) + ISNULL( B.TRANS_tambase_amt, 0) AS TotalAmount

FROM Engine_Prod.mc.WORKORDER WO

LEFT OUTER JOIN MSM.dbo.tblEngRunData ER ON ER.WONumber = ltrim(rtrim(WO.id))

LEFT OUTER JOIN mc.WORKTASK WT ON WO.wordoi = WT.wrkord_oi

LEFT OUTER JOIN mc.WORKORDERTYPE WOT ON WO.wot_oi = WOT.wotyoi

LEFT OUTER JOIN (

SELECT CHARGEINF.wt_oi

,LABORTRANS.tambase_amt TRANS_tambase_amt

FROM mc.CHARGEINF CHARGEINF

INNER JOIN mc.lbtr_chgs lbtr_chgs ON CHARGEINF.chgioi = lbtr_chgs.ref_oi

INNER JOIN mc.LABORTRANS LABORTRANS ON lbtr_chgs.owner_oi = LABORTRANS.lbtroi

WHERE LABORTRANS.posted = 1) A ON WT.wtskoi = A.wt_oi

LEFT OUTER JOIN (

SELECT CHARGEINF.wt_oi

,INVTRANS.tambase_amt TRANS_tambase_amt

FROM mc.CHARGEINF CHARGEINF

INNER JOIN mc.intr_chgs intr_chgs ON CHARGEINF.chgioi = intr_chgs.ref_oi

INNER JOIN mc.INVTRANS INVTRANS ON intr_chgs.owner_oi = INVTRANS.introi

WHERE INVTRANS.posted = 1

) B ON WT.wtskoi = B.wt_oi

WHERE ER.DateTimeStamp BETWEEN @StartDate

AND @EndDate

AND WO.clsdt_date BETWEEN @StartDate

AND @EndDate

AND WT.ci_cnc_date IS NULL

AND WT.tmplt = 0

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2