stored procedure issue, has to do with my where clause and if statement
- by MyHeadHurts
right now my stored procedure is returning 2 different result sets one for @booked and the other for @booked1
if you look closely my query is doing the same thing for each @booked and @booked but one is for a user selected year and the other for the current year.
I don't want two different result sets, i want to join the selected year and the current year side by side by SDESCR(which is a column that they have in common)
another hurdle i am facing is i am use @mode to decide whether the user wants netsales, sales... so on.
I know i need sometype of join but, it isnt working because i have a where statement that says where dyyyy= @yeartoget
which won't allow the current year data to work
ALTER PROCEDURE [dbo].[test1]
@mode varchar(20),
@YearToGet int
AS
SET NOCOUNT ON
Declare @Booked Int
Set @Booked = CONVERT(int,DateAdd(year, @YearToGet - Year(getdate() + 1),
DateAdd(day, DateDiff(day, 1, getdate()), 1) ) )
Declare @Booked1 Int
Set @Booked1 = CONVERT(int,DateAdd(year, (year( getdate() )) - Year(getdate() + 1),
DateAdd(day, DateDiff(day, 1, getdate()), 1) ) )
If @mode = 'Sales'
Select
Division,
SDESCR,
DYYYY,
Sum(Case When Booked <= @Booked Then NetAmount End) ASofNetSales,
SUM(NetAmount) AS YENetSales,
Sum(Case When Booked <= @Booked Then PARTY End) AS ASofPAX,
SUM(PARTY) AS YEPAX
From dbo.B101BookingsDetails
Where DYYYY = @YearToGet
Group By SDESCR, DYYYY, Division
Order By Division, SDESCR, DYYYY
else if @mode = 'netsales'
Select Division,
SDESCR,
DYYYY,
Sum(Case When Booked <= @Booked Then NetAmount End) ASofNetSales,
SUM(NetAmount) AS YENetSales,
Sum(Case When Booked <= @Booked Then PARTY End) AS ASofPAX,
SUM(PARTY) AS YEPAX
From dbo.B101BookingsDetails
Where DYYYY = @YearToGet
Group By SDESCR, DYYYY, Division
Order By Division, SDESCR, DYYYY
If @mode = 'Sales'
Select
Division,
SDESCR,
DYYYY,
Sum(Case When Booked <= @Booked1 Then NetAmount End) currentNetSales,
Sum(Case When Booked <= @Booked1 Then PARTY End) AS currentPAX
From dbo.B101BookingsDetails
Where DYYYY = (year( getdate() ))
Group By SDESCR, DYYYY, Division
Order By Division, SDESCR, DYYYY
else if @mode = 'netsales'
Select Division,
SDESCR,
DYYYY,
Sum(Case When Booked <= @Booked1 Then NetAmount End) currentNetSales,
Sum(Case When Booked <= @Booked1 Then PARTY End) AS currentPAX
From dbo.B101BookingsDetails
Where DYYYY = (year( getdate() ))
Group By SDESCR, DYYYY, Division
Order By Division, SDESCR, DYYYY
Else if @mode = 'Inssales'
Select Division,
SDESCR,
DYYYY,
Sum(Case When Booked <= @Booked1 Then InsAmount End) currentInsSales,
Sum(Case When Booked <= @Booked1 Then PARTY End) AS currentPAX
From dbo.B101BookingsDetails
Where DYYYY = (year( getdate() ))
Group By SDESCR, DYYYY, Division
Order By Division, SDESCR, DYYYY
