Combine 2 queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Combine 2 queries

Author

Topic

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2009-08-10 : 15:18:52

I built this first query to pull some data from a 3rd party vendors SQL database so I can create a flat file to send to our mainframe. Here is the sql statement:

SELECT dbo.tblTransaction.InvoiceNo, dbo.tblTransaction.Type, dbo.tblTransaction.Date, dbo.tblTransaction.GroupID,
dbo.tblTransaction.GroupID AS BillingAddress, dbo.tblTransaction.EventName, dbo.tblTransactionDetail.DsplText, dbo.tblTransactionDetail.DsplQty,
dbo.tblTransactionDetail.DsplPrice, dbo.tblTransactionDetail.DsplAmt, dbo.tblServiceOrderDetail.ServiceOrderID,
dbo.tblServiceOrderDetail.ResourceID, dbo.tblResource.ID, tblRoom_1.ID AS RoomID, tblAccount_1.Account, tblAccount_2.Account AS Expr1,
dbo.tblBooking.ID AS BookingID, dbo.tblAccount.Account AS Expr3
FROM dbo.tblAccount RIGHT OUTER JOIN
dbo.tblRoom ON dbo.tblAccount.ID = dbo.tblRoom.AccountID LEFT OUTER JOIN
dbo.tblTransaction INNER JOIN
dbo.tblTransactionDetail ON dbo.tblTransaction.ID = dbo.tblTransactionDetail.TransactionID INNER JOIN
dbo.tblServiceOrderDetail ON dbo.tblTransactionDetail.ServiceOrderDetailID = dbo.tblServiceOrderDetail.ID LEFT OUTER JOIN
dbo.tblBooking ON dbo.tblTransactionDetail.BookingID = dbo.tblBooking.ID LEFT OUTER JOIN
dbo.tblAccount AS tblAccount_2 RIGHT OUTER JOIN
dbo.tblRoom AS tblRoom_1 ON tblAccount_2.ID = tblRoom_1.AccountID ON dbo.tblServiceOrderDetail.ResourceID = tblRoom_1.ID LEFT OUTER JOIN
dbo.tblResource LEFT OUTER JOIN
dbo.tblAccount AS tblAccount_1 ON dbo.tblResource.AccountID = tblAccount_1.ID ON dbo.tblServiceOrderDetail.ResourceID = dbo.tblResource.ID ON
dbo.tblRoom.ID = dbo.tblBooking.RoomID

The group I am submitting this too has an addition they want on the end, a date. This part got trickier as the date wasn't in a date field but a string and that information was stored in a separate record then the details. With help this is the statement used to get the correct date attached to the correct detail record.

with CTE as (
select TransactionID,
PrintSequence,
ReservationID,
ServiceOrderID,
ServiceOrderdetailID,
RecordType,
CASE WHEN RecordType = 200 then convert(varchar, cast(right(DsplText, len(DsplText) - charindex(',', DsplText)) as datetime), 101)
else DsplText end DspText
from
tblTransactionDetail
)
select a.TransactionID,
a.PrintSequence,
a.ReservationID,
a.ServiceOrderID,
a.ServiceOrderdetailID,
a.RecordType,
(Select max(DspText) from CTE where TransactionID = a.TransactionID and PrintSequence < a.PrintSequence and Recordtype = 200) as tmpDate
from CTE a
where RecordType <> 200
I was hoping to get help to merge this all into one sql statement but wasn't sure if that was possible. Any ideas?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources