Using case statement to grab latest period

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)

Using case statement to grab latest period

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2011-02-09 : 12:54:11

Hi,

I am using sql 2005.

Based on whether a boolean box UseLatestPeriod is checked in the front end, I need to use this boolean value to check for a value rather than a selected date range in the where clause.

The front end has a checkbox for UseLatestPeriod.

If UseLatestPeriod is checked, I want to be able to use the latest period from the Pshipinfo table denoted by a column CurrentPeriod which has int values like 39, 82 etc instead of using the date range from the Period table which is denoted by BegDate and End Date.

@fromDate, @toDate and @CustBool1 are parameters passed to the procedure.

example

declare @FromDate datetime
declare @ToDate datetime

Declare @CustBool1 bit
Declare @UseLatestPeriod bit
Set @UseLatestPeriod =
CASE
WHEN @CustBool1 = 0 THEN NUL
ELSE @CustBool1
END

set @fromDate='01/01/2011'
set @toDate='01/31/2011'

SELECT Distinct
pif.pshipcode as PshipCode, pif.pshipname as PshipName
FROM EntityToFund ef
INNER JOIN Partner ON ef.EntityToFundID = partner.EntityToFundID
INNER JOIN Pshipinfo pif on partner.pshipid = pif.pshipid
INNER JOIN Period per on partner.pshipid = per.pshipid
INNER JOIN vABalance bal ON per.pshipid = bal.pshipid AND per.period = bal.period
AND partner.pshipid = bal.pshipid AND partner.PartnerID = bal.partnerID
where exists (select SF.pshipid from @SelectedFunds SF
where partner.pshipid = SF.pshipid)
AND per.BeginDate >= @FromDate
AND per.EndDate <= @ToDate
AND (bal.PrevEndCap >= .01 or bal.BegCap >= .01 or bal.EndCap >= .01)

SELECT Distinct
pif.pshipcode as PshipCode, pif.pshipname as PshipName
FROM EntityToFund ef
INNER JOIN Partner ON ef.EntityToFundID = partner.EntityToFundID
INNER JOIN Pshipinfo pif on partner.pshipid = pif.pshipid

INNER JOIN Period per on partner.pshipid = per.pshipid
INNER JOIN vABalance bal ON per.pshipid = bal.pshipid AND per.period = bal.period
AND partner.pshipid = bal.pshipid AND partner.PartnerID = bal.partnerID

where exists (select SF.pshipid from @SelectedFunds SF
where partner.pshipid = SF.pshipid)

AND CASE WHEN @UseLatestPeriod = 0 THEN (per.BeginDate >= @FromDate AND per.EndDate <= @ToDate) ELSE per.Period = pif.CurrentPeriod
AND (bal.PrevEndCap >= .01 or bal.BegCap >= .01 or bal.EndCap >= .01)

Please let me know if you need more information.

sqlnovice123
Thanks for your help in advance.

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2011-02-09 : 13:27:19

What if I use soemthing like below, I am not getting the syntax though

where exists (select SF.pshipid from @SelectedFunds SF
where partner.pshipid = SF.pshipid)
AND CASE WHEN @UseLatestPeriod = 1 THEN (per.Period IN(select pif.CurrentPeriod from pshipinfo)) ELSE (per.Period IN (select Period from Period
where BeginDate >= @FromDate AND EndDate <= @ToDate)) END
AND (bal.PrevEndCap >= .01 or bal.BegCap >= .01 or bal.EndCap >= .01)

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2011-02-09 : 13:37:07

I tried teh below but I get the syntax error. Any idea? The CASE syntax is wrong.

where exists (select SF.pshipid from @SelectedFunds SF
where partner.pshipid = SF.pshipid)
--AND per.BeginDate >= @FromDate
--AND per.EndDate <= @ToDate
AND (CASE WHEN @UseLatestPeriod = 1 THEN (select pif.CurrentPeriod from pshipinfo) ELSE (select Period from Period
where BeginDate >= @FromDate AND EndDate <= @ToDate) END)
AND (bal.PrevEndCap >= .01 or bal.BegCap >= .01 or bal.EndCap >= .01)

Msg 4145, Level 15, State 1, Procedure spGFSInvestorProfile, Line 171
An expression of non-boolean type specified in a context where a condition is expected, near 'end'.

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2011-02-09 : 13:42:52

I also tried using if but the syntax is wrong. What should I use then?

AND(
IF @UseLatestPeriod = 1
begin
select pif.CurrentPeriod from pshipinfo
end
ELSE
begin
select Period from Period
where BeginDate >= @FromDate AND EndDate <= @ToDate
end
)

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-09 : 13:52:48

WHERE
EXISTS (select SF.pshipid from @SelectedFunds SF where partner.pshipid = SF.pshipid)
AND (
( @UseLatestPeriod = 0
AND per.BeginDate >= @FromDate
AND per.EndDate <= @ToDate
)
OR
(@UseLatestPeriod = 1
AND per.Period = pif.CurrentPeriod)

)

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

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2011-02-10 : 14:50:12

Thank you so much for your help.

I want to exclude pshipids that have a terminated date when the @group is specified and the @LatestPeriod is specified.

The below query is not excluding the pshipids that are terminated.

AND not exists (select DISTINCT TD.pshipid from @TerminationDate TD
where TD.pshipid = partner.pshipid
AND (
(@UseLatestPeriod = 1 AND @Group IS NOT NULL)
OR
(@UseLatestPeriod = 1 OR @Group IS NOT NULL)
))

Regards,
sqlnovice123

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2011-02-10 : 14:53:34

I forgot to add that the temp table in the above query is declared as below:

DECLARE @TerminationDate TABLE(pshipid nvarchar(100), TerminationDate datetime)
insert into @TerminationDate

select
PshipId, CONVERT(nvarchar(100), UDFValue, 101)

from
dbo.GFS_FN_GetUDFValues('Fund', 'TerminationDate')

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-10 : 15:40:47

AND not exists (select DISTINCT TD.pshipid from @TerminationDate TD
where TD.pshipid = partner.pshipid
AND (
(@UseLatestPeriod = 1 AND @Group IS NOT NULL)
OR
(@UseLatestPeriod = 1 OR @Group IS NOT NULL)
))

Which is it? LatestPeriod AND @Group not null?

Or latestperiod OR @group not null.

I think your OR's and AND's are getting mixed up. it looks like all groups are returned, whether Null or Not as long as the @LatestPeriod = 1

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

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2011-02-10 : 15:54:32

For both cases the resultset is incorrect.

LatestPeriod AND @Group not null as well as

Or latestperiod OR @group not null.
When the @Group and LatestPeriod is specified, I wnat to exclude teh rows whose pshipid have a terminated date.

When either one of teh following are specified viz, @group or @LatestPeriod, I want to exclude rows with a Terminated date.

example if a fund say TAMXX has pshipid which is Terminated , then when I specify 'TAMXX' in the group and the latestPeriod is checked, I want to exclude the pshipids.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources