存储过程问题，与我的 where 子句和 if 语句有关 [英] stored procedure issue, has to do with my where clause and if statement

问题描述

现在我的存储过程返回 2 个不同的结果集，一个用于 @booked，另一个用于 @booked1

right now my stored procedure is returning 2 different result sets one for @booked and the other for @booked1

如果您仔细观察，我的查询对每个 @booked 和 @booked 执行相同的操作，但其中一个针对用户选择的年份，另一个针对当前年份.

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.

我不想要两个不同的结果集，我想通过 SDESCR 并排连接所选年份和当前年份(这是它们共有的列)

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)

我面临的另一个障碍是我使用@mode 来决定用户是否需要网络销售、销售......等等.

another hurdle i am facing is i am use @mode to decide whether the user wants netsales, sales... so on.

我知道我需要某种类型的连接，但是它不起作用，因为我有一个 where 语句，说明 where dyyyy= @yeartoget

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 

推荐答案

获得并排结果的一种简单方法是在 From 旁边使用子查询

One easy approach to have the side by side results is to use sub-queries in side the From

注意:这仅适用于 mode = Sales

Note: this is just for mode = Sales

SELECT
           b.Division,
           b.SDESCR,
           b.DYYYY,
           b.YENetSales,
           b.YEPAX
           b1.Division,
           b1.SDESCR,
           b1.DYYYY,
           b1.currentNetSales,
           b1.currentPAX     

FROM
(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         
) b

  FULL OUTER JOIN 
  (
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  ) b1
  ON b.divsion = b1.divsion
     and
     b.SDESCR = b1.SDESCR   --might not be required

 Order By b.Division, b.SDESCR, b.DYYYY

另一种方法是更改​​ where 子句以包含 @booked 和 @booked1，然后对每个字段执行 case 语句

The other approach is to change your where clause to include both @booked and @booked1 and then do a case statement on each field

这篇关于存储过程问题，与我的 where 子句和 if 语句有关的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！