March 3, 2015 at 6:47 pm
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)
FROMtxnPasskeyAgent
WHEREOfficeCode in ('center')
ANDPasskeyBookingDate = '2015-01-30'
UNION ALL
SELECT
Resv01 = COUNT(BookingDate)
FROMReservations RS
INNER JOIN
Property PR ON
PR.PropCode = RS.HotelCode
WHEREEntryHotelCode IN ('center')
AND BookingDate = '2015-01-30'
UNION ALL
SELECT
Resv01 = Count(PRS.BookingDate)
FROMvGCCPasskeyRes PRS
INNER JOIN
Reservations RS ON
RS.ConfirmationNumber = PRS.ConfirmationNumber
AND RS.PageNumber = PRS.PageNumber
INNER JOIN
PasskeyAgentLogins PAL ON
PAL.PasskeyLogin = PRS.PasskeyLogin
WHEREPAL.SharedServiceCenter in ('center')
ANDPRS.BookingDate = '2015-01-30'
AND PRS.ActionCode IN('SEL', 'SHR')
UNION ALL
SELECT
Resv01 = COUNT(BookingDate)
FROMReservations 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
March 4, 2015 at 7:29 am
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply