Hand needed with t-sql

[David]

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

[Erland Sommarskog]

David (au*******@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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Ricardo Vieira]

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" <au*******@hushmail.com> wrote in message news:<3f***********************@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

[Ricardo Vieira]

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" <au*******@hushmail.com> wrote in message news:<3f***********************@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