Пример использования case в секции where

Tsql теория > Пример использования case в секции where
17.04.2013 10:13:13



Статья:

Пример использования case в секции where

CASE when 1=@per THEN LEFT(CONVERT(CHAR(8),DATEADD(month, -2, GetDATE()),112),6) ELSE '' end

 

SELECT
[ОП].[SP2931] AS [Сделка_1C77_УИ]
,isNull([Сделки].[SP11203],' 0 ') AS [ПотПрод_1C77_УИ]
,isNull([Сделки].[SP9337] ,' 0 ') AS [Договор_1C77_УИ]
,isNull([Сделки].[CODE] ,' 0 ') AS [Сделка_Номер]
,isNull([Сделки].[SP557],' 0 ') AS [Покупатель_1C77_УИ]
,CONVERT(DATETIME,LEFT([ОП].[DATE_TIME_IDDOC],8),112) AS [Дата]
,isNull([ВидПродукции].[Descr],'') AS [ВидПродукции]
------------------------------------------------------
,[ОП].[SP2937]* (1-2*[ОП].[DEBKRED]) AS [ОбъемПродаж_Сумма_Доллар]
,cast([ОП].[SP2937]* (1-2*[ОП].[DEBKRED])*[Rates].[ExchangeRateToRUB] as decimal(16,2)) as [ОбъемПродаж_Сумма_Рубль]
,cast([ОП].[SP2937]* (1-2*[ОП].[DEBKRED])*[Rates].[ExchangeRateToEuro] as decimal(16,2)) as [ОбъемПродаж_Сумма_Евро]
------------------------------------------------------
,[ОП].[SP2938]* (1-2*[ОП].[DEBKRED]) AS [Себестоимость_Сумма_Доллар]
,cast([ОП].[SP2938]* (1-2*[ОП].[DEBKRED])*[Rates].[ExchangeRateToRUB] as decimal(16,2)) as [Себестоимость_Сумма_Рубль]
,cast([ОП].[SP2938]* (1-2*[ОП].[DEBKRED])*[Rates].[ExchangeRateToEuro] as decimal(16,2)) as [Себестоимость_Сумма_Евро]
------------------------------------------------------
,[ОП].[SP3203]* (1-2*[ОП].[DEBKRED]) AS [СуммаЗатрат_Сумма_Доллар]
,cast([ОП].[SP3203]* (1-2*[ОП].[DEBKRED])*[Rates].[ExchangeRateToRUB] as decimal(16,2)) as [СуммаЗатрат_Сумма_Рубль]
,cast([ОП].[SP3203]* (1-2*[ОП].[DEBKRED])*[Rates].[ExchangeRateToEuro] as decimal(16,2)) as [СуммаЗатрат_Сумма_Евро]
------------------------------------------------------
,[ОП].[SP3885]* (1-2*[ОП].[DEBKRED]) AS [СуммоваяРазница_Сумма_Доллар]
,cast([ОП].[SP3885]* (1-2*[ОП].[DEBKRED])*[Rates].[ExchangeRateToRUB] as decimal(16,2)) as [СуммоваяРазница_Сумма_Рубль]
,cast([ОП].[SP3885]* (1-2*[ОП].[DEBKRED])*[Rates].[ExchangeRateToEuro] as decimal(16,2)) as [СуммоваяРазница_Сумма_Евро]
------------------------------------------------------
,[ОП].[SP3886]* (1-2*[ОП].[DEBKRED]) AS [Конвертация_Сумма_Доллар]
,cast([ОП].[SP3886]* (1-2*[ОП].[DEBKRED])*[Rates].[ExchangeRateToRUB] as decimal(16,2)) as [Конвертация_Сумма_Рубль]
,cast([ОП].[SP3886]* (1-2*[ОП].[DEBKRED])*[Rates].[ExchangeRateToEuro] as decimal(16,2)) as [Конвертация_Сумма_Евро]
------------------------------------------------------
,[ОП].[SP3894]* (1-2*[ОП].[DEBKRED]) AS [КоррекцияМП_Сумма_Доллар]
,cast([ОП].[SP3894]* (1-2*[ОП].[DEBKRED])*[Rates].[ExchangeRateToRUB] as decimal(16,2)) as [КоррекцияМП_Сумма_Рубль]
,cast([ОП].[SP3894]* (1-2*[ОП].[DEBKRED])*[Rates].[ExchangeRateToEuro] as decimal(16,2)) as [КоррекцияМП_Сумма_Евро]
------------------------------------------------------
, ([ОП].[SP2937] - [ОП].[SP2938] - [ОП].[SP3203] + [ОП].[SP3885] - [ОП].[SP3886] - [ОП].[SP3894])* (1-2*[ОП].[DEBKRED]) AS [МП_Сумма_Доллар]
,cast(([ОП].[SP2937] - [ОП].[SP2938] - [ОП].[SP3203] + [ОП].[SP3885] - [ОП].[SP3886] - [ОП].[SP3894])* (1-2*[ОП].[DEBKRED])*[Rates].[ExchangeRateToRUB] as decimal(16,2)) as [МП_Сумма_Рубль]
,cast(([ОП].[SP2937] - [ОП].[SP2938] - [ОП].[SP3203] + [ОП].[SP3885] - [ОП].[SP3886] - [ОП].[SP3894])* (1-2*[ОП].[DEBKRED])*[Rates].[ExchangeRateToEuro] as decimal(16,2)) as [МП_Сумма_Евро]


FROM [RA2929] as [ОП](NOLOCK)
LEFT OUTER JOIN [SC556] AS [Сделки](NOLOCK) ON [Сделки].[ID] = [ОП].[SP2931]
LEFT OUTER JOIN [tblEnum_CN_ВидПродукции] AS [ВидПродукции] (NOLOCK) on [ВидПродукции].[IDCHar] = [ОП].[SP2935]
LEFT OUTER JOIN [tblExchangeRates] AS [Rates](NOLOCK) ON ( ([Rates].[CurrencyID] = ' 1 ') AND ([Rates].[Date] = CONVERT(DATETIME,LEFT([ОП].[DATE_TIME_IDDOC],8),112)) )
WHERE [ОП].[DATE_TIME_IDDOC] > CASE when 1=@per THEN LEFT(CONVERT(CHAR(8),DATEADD(month, -2, GetDATE()),112),6) ELSE '' end