Combining multiple case statements with Sum

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Combining multiple case statements with Sum

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-12-02 : 23:01:57

Hi,

I am trying to combine case statements in an aggregated value but it limits to just two case statements. What am I doing wrong?

As I am getting a syntax error, I am thinking of using two buckets to store the SUM of two case statements for each bucket. I want to combine all four multiple cases and store in one bucket.

SUM (CASE WHEN
TS.TransactionType = 'Redemptions' THEN
CASE WHEN @ExcludeBegRed = 1 AND @IncludeNextRed = 1 AND TS.DealDate > @FromDate AND TS.DealDate <= @TSToDate THEN ISNULL(TS.GrossAmount,0) END ELSE
CASE WHEN @ExcludeBegRed = 0 AND @IncludeNextRed = 1 AND TS.DealDate >= @FromDate AND TS.DealDate <= @TSToDate THEN ISNULL(TS.GrossAmount,0) ELSE 0
END END )WDGrossCapitalAmountNext

SUM (CASE WHEN
TS.TransactionType = 'Redemptions' THEN
CASE WHEN @ExcludeBegRed = 1 AND @IncludeNextRed = 0 AND TS.DealDate > @FromDate AND TS.DealDate <= @ToDate THEN ISNULL(TS.GrossAmount,0) END ELSE
CASE WHEN @ExcludeBegRed = 0 AND @IncludeNextRed = 0 AND TS.DealDate >= @FromDate AND TS.DealDate <= @ToDate THEN ISNULL(TS.GrossAmount,0) ELSE 0
END END )WDGrossCapitalAmountNext

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-02 : 23:51:27

No matter the result you are summing the same field?

Can't you do

SUM(Case When @excludeBegRed in (1,0)
and @IncludeNextRed in (1,0)
and TS.DealDate >=@FromDate and
(TS.DealDate <=@TSToDate OR TS.DealDate <= @ToDate)
then isNull(TS.GrossAmount,0)
else 0 end)

unless your posted evaluations are wrong..that seems to combine them the way you might need.

Post sample data and expected results?

Poor planning on your part does not constitute an emergency on my part.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-12-03 : 00:30:42

It is better to explain what you are actually trying to do, rather that posting code that dosn't work and expecting us to try to figure out what you want.

CODO ERGO SUM

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-12-03 : 06:07:03

I have two buckets CurrentGrossAmount for current period and NextGrossAmount for Next period. I have @FromDate = 1/1/2009. I have @ToDate = 12/31/2009.

I have @TSToDate set to @ToDate + month which is 1/31/2010 when variable @IncludeNextPeriod is 1 else when @IncludeNextPeriod is 0 then @TSToDate is set to @Todate. @ToDate is called as a parameter in a function.

I have to check the following cases for the next period bucket which is NextGrossAmount:
1) IncludeNextPeriod = 0 ExcludeBegPeriod = 1 this will give date for 2/1/2009 to 12/31/2009
2) IncludeNextPeriod = 0 and ExcludeBegPeriod = 0 this will give data for 1/1/2009 to 12/31/2009
3) IncludeNextPeriod = 1 and ExcludeBegPeriod = 0 this will give data for 1/1/2009 to 1/1/2010
4) IncludeNextPeriod = 1 and ExcludeBegPeriod = 1 this will give data for 2/1/2009 to 1/1/2010

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-12-03 : 06:16:47

Note that the flags ExcludeBegPeriod and IncludeNextPeriod can be check or unchecked by users using a gui reporting application. Maybe I can use OR for common value flags @IncludeNextPeriod and common values @ToDate and @TSToDate
and for common value flags @ExcludeBegPeriod and common values @FromDate ? But I did not test this approach yet.

The NextGrossAmount should reflect data for trades that fall in the above date range which is checked by TS.DealDate where TS is the function

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-12-03 : 08:55:48

I try the below and get a syntax error near '='. Can I combine setting a variable within a CASE statement? What is the syntax?

declare @sessionid nvarchar(100)
declare @reportid int
declare @FromDate datetime
declare @ToDate datetime
DECLARE @ExcludeBegRed bit
DECLARE @IncludeNextRed bit
DECLARE @pshipcode nvarchar(100)
DECLARE @ReportLevel smallint
DECLARE @ReportDetailLevel smallint
DECLARE @LocalCurrency BIT -- local currency or not
DECLARE @Consolidate BIT -- consolidated report or not
DECLARE @Status nvarchar(100)
DECLARE @Type nvarchar(100)
DECLARE @Warnings nvarchar(100)
DECLARE @ShowZeroCashFlow bit
DECLARE @ShowClosingMethodDetail bit
DECLARE @SidePocketCapitalOption smallint

set @sessionid=dbo.GFS_fxGetSessionID()
set @reportid=-100
set @FromDate='01/01/2009'
set @ToDate='12/31/2009'
set @ExcludeBegRed = 0
set @IncludeNextRed = 1
SET @pshipcode='COLLT'
SET @Status = 'All'
SET @Type = 'All' --Transaction Type: All, Transfers, Redemptions, Subscriptions
SET @Warnings = ''
SET @ShowZeroCashFlow = 0
SET @ShowClosingMethodDetail = 1
SET @SidePocketCapitalOption = 2

-Set ToDate Variable to be called by the TS function if IncludeNextRed is checked
DECLARE @TSToDate datetime

SELECT @TSToDate = dateadd(m,1,@ToDate)
exec dbo.GFS_PopulateSelectedCapitalAccounts @sessionid,@reportid,@pshipcode

SELECT
TS.PshipID
,TS.ClassID
,TS.SeriesID
,TS.EntityID
,TS.SubEntityID
,SUM (CASE WHEN TS.TransactionType = 'Redemptions' AND CASE WHEN @IncludeNextRed = 1 THEN @TSToDate = TSToDate ELSE
CASE WHEN @IncludeNextRed = 0 THEN @TSToDate = @ToDate END AND
CASE WHEN @ExcludeBegRed = 1 AND TS.DealDate > @FromDate AND TS.DealDate <= @TSToDate THEN ISNULL(TS.NetAmountWithTransactionFees,0) END ELSE
CASE WHEN @ExcludeBegRed = 0 AND TS.DealDate >= @FromDate AND TS.DealDate <= @TSToDate THEN ISNULL(TS.NetAmountWithTransactionFees,0) ELSE 0
END END)WDGrossCapitalAmountNext
,TS.DealDate
,FromDate
,ToDate
,@TSToDate

FROM fAxpDVTransactionSummary(@SessionID, @ReportID, @FromDate, @TSToDate, @Status, @Type,
@Warnings, @ShowZeroCashFlow, @ShowClosingMethodDetail, @SidePocketCapitalOption) TS

LEFT JOIN PshipInfo psi ON TS.PshipID = psi.PshipID
INNER JOIN Period ON TS.PshipID = Period.PshipID AND
TS.DealDate Between BeginDate And EndDate
INNER JOIN Currency ON TS.CurrencyCode = Currency.CurrencyCode
INNER JOIN vFxRatesDirect ON Period.PshipID = vFxRatesDirect.PshipID AND
Period.Period = vFxRatesDirect.Period AND
Currency.CurrencyID = vFxRatesDirect.CurrencyID
WHERE Status not in ('Pending', 'Rejected')
GROUP BY TS.PshipID, TS.ClassID, TS.SeriesID, TS.SubEntityID,TS.EntityID, TS.DealDate

Expected results

PshipID ClassID SeriedID EntityID SubEntityID WDGrossCapitalAmountNext DealDate FromDate ToDate @TSToDate
9 1 3 70 114 -1122245.148974 2009-12-01 01/01/2009 01/31/2009 01/31/2010
9 1 3 70 114 -1265000 2010-01-01 01/01/2009 01/31/2009 01/31/2010
9 1 21 45 98 -676806359 2009-04-01 01/01/2009 01/31/2009 01/31/2010

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources