Union All Won't work in stored procedure
- by MyHeadHurts
ALTER PROCEDURE [dbo].[MyStoredProcedure1]
@YearToGet int
AS
Select Division, SDESCR,
DYYYY,
Sum(APRICE) ASofSales,
Sum(PARTY) AS ASofPAX,
Sum(NetAmount) ASofNetSales,
Sum(InsAmount) ASofInsSales,
Sum(CancelRevenue) ASofCXSales,
Sum(OtherAmount) ASofOtherSales,
Sum(CXVALUE) ASofCXValue
From dbo.B101BookingsDetails
Where Booked <= CONVERT(int,DateAdd(year, @YearToGet - Year(getdate()),
DateAdd(day, DateDiff(day, 1, getdate()), 0) ) ) and
(DYYYY = @YearToGet)
Group By SDESCR, DYYYY, Division
Having (DYYYY = @YearToGet)
Order By Division, SDESCR, DYYYY
union all
SELECT
DIVISION,
SDESCR,
DYYYY,
SUM(APRICE) AS YESales,
SUM(PARTY) AS YEPAX,
SUM(NetAmount) AS YENetSales,
SUM(InsAmount) AS YEInsSales,
SUM(CancelRevenue) AS YECXSales,
SUM(OtherAmount) AS YEOtherSales,
SUM(CXVALUE) AS YECXValue
FROM dbo.B101BookingsDetails
Where (DYYYY=@YearToGet)
GROUP BY SDESCR, DYYYY, DIVISION
ORDER BY DIVISION, SDESCR, DYYYY
The error I am getting is
Msg 156, Level 15, State 1, Procedure
MyStoredProcedure1, Line 36 Incorrect
syntax near the keyword 'union'.
But my goal here is the user inputs a year for example 2009, my first query will get all the sales made in 2009 to the same date it is was yesterday 12/23/2009, while the second query is getting 2009 totals up to dec 31 2009.
I want the columns to be side by side not in one column
