SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


combine 2 rows into 1 row


combine 2 rows into 1 row

Author
Message
kss113
kss113
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 60
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.
EquipID Location Cycles DateTimeStamp Source Total Amount
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

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


Want a cool Sig
Want a cool Sig
SSC Eights!
SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)

Group: General Forum Members
Points: 960 Visits: 705
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 Cool
I want to win the lotto :-D
I want a gf like Tiffa w00t Oh wait I'm married!:-D
Gazareth
Gazareth
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7708 Visits: 6045
Just remove the B.Source field from your outer SELECT & GROUP BY
anthony.green
anthony.green
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24306 Visits: 6519
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



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


kss113
kss113
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 60
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
kss113
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 60
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
Luis Cazares
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41824 Visits: 19815
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
anthony.green
anthony.green
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24306 Visits: 6519
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





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41824 Visits: 19815
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search