Hand needed with t-sql

Is it possible to write these two blocks of code as one? The only
difference between them is the AND clause: AND is_trade_date = 1 versus AND
is_sett_date = 1.

Cheers,

David

IF (@.trade_dates = 1)
BEGIN
IF EXISTS (SELECT 1 FROM calendar
WHERE calendar_date = @.date
AND is_trade_date = 1)
BEGIN
SELECT @.day_cnt = @.day_cnt + 1
END
END
ELSE
BEGIN
IF EXISTS (SELECT 1 FROM calendar
WHERE calendar_date = @.date
AND is_sett_date = 1)
BEGIN
SELECT @.day_cnt = @.day_cnt + 1
END
END
ENDDavid (auto87829@.hushmail.com) writes:
> Is it possible to write these two blocks of code as one? The only
> difference between them is the AND clause: AND is_trade_date = 1 versus
> AND is_sett_date = 1.

If my guess of the logic is right:

IF EXISTS (SELECT 1
FROM calendar
WHERE calendar_date = @.date
AND (is_trade_date = 1 OR @.trade_dates = 0)
AND (is_sett_date = 1 OR @.trade_dates = 1)
BEGIN
SELECT @.day_cnt = @.day_cnt + 1
END

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Why not:
IF (@.trade_dates = 1)
BEGIN
IF EXISTS (SELECT 1 FROM calendar
WHERE calendar_date = @.date AND
(is_trade_date = 1 OR is_sett_date = 1)
)
BEGIN
SELECT @.day_cnt = @.day_cnt + 1
END
END

Ricardo

"David" <auto87829@.hushmail.com> wrote in message news:<3fece73f$0$18747$afc38c87@.news.optusnet.com.au>...
> Is it possible to write these two blocks of code as one? The only
> difference between them is the AND clause: AND is_trade_date = 1 versus AND
> is_sett_date = 1.
> Cheers,
> David
> IF (@.trade_dates = 1)
> BEGIN
> IF EXISTS (SELECT 1 FROM calendar
> WHERE calendar_date = @.date
> AND is_trade_date = 1)
> BEGIN
> SELECT @.day_cnt = @.day_cnt + 1
> END
> END
> ELSE
> BEGIN
> IF EXISTS (SELECT 1 FROM calendar
> WHERE calendar_date = @.date
> AND is_sett_date = 1)
> BEGIN
> SELECT @.day_cnt = @.day_cnt + 1
> END
> END
> END|||The following block should do the trick:
IF EXISTS ( SELECT 1
FROM calendar
WHERE calendar_date = @.date AND
(
( is_trade_date = 1 AND
@.trade_dates = 1
)
OR
( is_sett_date = 1 AND
@.trade_dates <> 1
)
)
)
BEGIN
SELECT @.day_cnt = @.day_cnt + 1
END

although not very elegant...

Ricardo.
"David" <auto87829@.hushmail.com> wrote in message news:<3fece73f$0$18747$afc38c87@.news.optusnet.com.au>...
> Is it possible to write these two blocks of code as one? The only
> difference between them is the AND clause: AND is_trade_date = 1 versus AND
> is_sett_date = 1.
> Cheers,
> David
> IF (@.trade_dates = 1)
> BEGIN
> IF EXISTS (SELECT 1 FROM calendar
> WHERE calendar_date = @.date
> AND is_trade_date = 1)
> BEGIN
> SELECT @.day_cnt = @.day_cnt + 1
> END
> END
> ELSE
> BEGIN
> IF EXISTS (SELECT 1 FROM calendar
> WHERE calendar_date = @.date
> AND is_sett_date = 1)
> BEGIN
> SELECT @.day_cnt = @.day_cnt + 1
> END
> END
> END