Hello,
I am using SQL Server 2012, and I have a query that gets data from 4 different sources and sums them all up into one field to get a total, and right now I am using 4 queries for each data source and using UNION ALLs to combine them and I was wondering if there is a different way to accomplish this without using UNION statements.
Here is a sample of my code.
SELECT
Resv01 = ISNULL(SUM(Resv01),0)
FROM
(
SELECT
Resv01 = SUM(Reservations)
FROM txnPasskeyAgent
WHERE OfficeCode in ('center')
AND PasskeyBookingDate = '2015-01-30'
UNION ALL
SELECT
Resv01 = COUNT(BookingDate)
FROM Reservations RS
INNER JOIN
Property PR ON
PR.PropCode = RS.HotelCode
WHERE EntryHotelCode IN ('center')
AND BookingDate = '2015-01-30'
UNION ALL
SELECT
Resv01 = Count(PRS.BookingDate)
FROM vGCCPasskeyRes PRS
INNER JOIN
Reservations RS ON
RS.ConfirmationNumber = PRS.ConfirmationNumber
AND RS.PageNumber = PRS.PageNumber
INNER JOIN
PasskeyAgentLogins PAL ON
PAL.PasskeyLogin = PRS.PasskeyLogin
WHERE PAL.SharedServiceCenter in ('center')
AND PRS.BookingDate = '2015-01-30'
AND PRS.ActionCode IN('SEL', 'SHR')
UNION ALL
SELECT
Resv01 = COUNT(BookingDate)
FROM Reservations RS
INNER JOIN property PR
ON RS.hOTELCODE = Pr.propcode
WHERE bookingdate = '2015-01-30'
and entryhotelcode ='omgds'
AND ACTIONCODE IN ('SEL','SHR','RML','PIK')
and recordlocatorcode like 'WB-C0085%'
and rs.Hotelcode in (SELECT propcode FROM property WHERE SharedServiceCenter in('center'))
AND RatePlanCode <>'HYCM'
) RawData
Thanks,
Sam