存储过程中的问题,与我的where子句,如果语句来完成

现在我的存储过程将返回2不同的结果集一个@booked和其他为@ booked1

如果你仔细看我的查询是做对每个@booked和@booked,但一个是用户选择的一年,另一个用于本年度同样的事情。

我不想两个不同的结果集,我想参加所选年度和本年度并排通过SDESCR(这是一列,他们的共同点)

另一个障碍我面临的是我是用@mode来决定用户是否想要纯销售,销售......等等。

我知道我需要sometype。这时候的加入,但它不是工作,因为我有一个where语句,说在那里dyyyy = @yeartoget

这将不允许当前年数据的工作

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

--------------解决方案-------------

一个简单的方法,通过边结果有侧处于侧的从使用子查询

注意:这仅仅是模式=销售

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语句上的每个字段

分类:SQL 时间:2015-03-15 人气:0
分享到:

相关文章

Copyright (C) 55228885.com, All Rights Reserved.

55228885 版权所有 京ICP备15002868号

processed in 1.638 (s). 10 q(s)