Is there a way to base the final select statement based on whether a user chooses, from a report dropdown box,
a date range based on e.g.: PaidDate or DateOfService similar to the below (which doesn't work):
SELECT CASE
WHEN @dateChoice = 'DOS'
THEN (
SELECT DISTINCT tp.client_id AS Member_ID
,tp.PaidDate AS Paid_Date
,tp.DOS
FROM #ins_temp tp
WHERE tp.DOS BETWEEN @startDate
AND @endDate
)
WHEN @dateChoice = 'Paid_Date'
THEN (
SELECT DISTINCT tp.client_id AS Member_ID
,tp.PaidDate AS Paid_Date
,tp.DOS
FROM #ins_temp tp
WHERE tp.PaidDate BETWEEN @startDate
AND @endDate
)
END
January 24, 2025 at 1:28 am
The @dateChoice variable is passed in from a dropdown box and the WHERE clauses between the two queries are different. Syntax-wise you could try IF @dateChoice = 'DOS' SELECT ... However, that's not my recommended way. The switch between tp.DOS and tp.PaidDate could be done in the WHERE clause of a single query using IIF on the lefthand side of the condition. That's also not my recommended way. In short, two procs. Why not? When the WHERE clause conditions are different why combine them? Maybe in the past I've seen issues with these types of queries
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
January 24, 2025 at 6:09 am
You could use dynamic SQL
DECLARE @SQL nvarchar(MAX);
SELECT @SQL = N'
SELECT DISTINCT
Member_ID = tp.client_id
, Paid_Date = tp.PaidDate
, tp.DOS
FROM #ins_temp AS tp
WHERE tp.' + IIF(@dateChoice = 'DOS', N'DOS', N'PaidDate') + N' BETWEEN @startDate AND @endDate;
';
EXEC sys.sp_executesql @stmt = @SQL
, @params = N'@startDate datetime, @endDate datetime'
, @startDate = @startDate
, @endDate = @endDate;
January 24, 2025 at 9:42 am
SELECT DISTINCT tp.client_id AS Member_ID
, tp.PaidDate AS Paid_Date
, tp.DOS
FROM #ins_temp tp
WHERE (@dateChoice = 'Paid_Date' and tp.PaidDate BETWEEN @startDate AND @endDate)
or (@dateChoice = 'DOS' and tp.DOS BETWEEN @startDate AND @endDate)
January 24, 2025 at 9:51 am
SELECT DISTINCT tp.client_id AS Member_ID
, tp.PaidDate AS Paid_Date
, tp.DOS
FROM #ins_temp tp
WHERE (@dateChoice = 'Paid_Date' and tp.PaidDate BETWEEN @startDate AND @endDate)
or (@dateChoice = 'DOS' and tp.DOS BETWEEN @startDate AND @endDate)
Note that this query is non-SARGable, and will always result in a scan
January 24, 2025 at 3:22 pm
This does not bring back any records unfortunately:
SELECT DISTINCT tp.client_id AS Member_ID
, tp.PaidDate AS Paid_Date
, tp.DOS
FROM #ins_temp tp
WHERE (@dateChoice = 'Paid_Date' and tp.PaidDate BETWEEN @startDate AND @endDate)
or (@dateChoice = 'DOS' and tp.DOS BETWEEN @startDate AND @endDate)
But this does:
SELECT DISTINCT tp.client_id AS Member_ID
, tp.PaidDate AS Paid_Date
, tp.DOS
FROM #ins_temp tp
WHERE tp.DOS BETWEEN @startDate AND @endDate
This does not bring back any records unfortunately:
SELECT DISTINCT tp.client_id AS Member_ID
, tp.PaidDate AS Paid_Date
, tp.DOS
FROM #ins_temp tp
WHERE (@dateChoice = 'Paid_Date' and tp.PaidDate BETWEEN @startDate AND @endDate)
or (@dateChoice = 'DOS' and tp.DOS BETWEEN @startDate AND @endDate)But this does:
SELECT DISTINCT tp.client_id AS Member_ID
, tp.PaidDate AS Paid_Date
, tp.DOS
FROM #ins_temp tp
WHERE tp.DOS BETWEEN @startDate AND @endDate
then your variable @dateChoice is not being populated with the correct value you supplied. debug it and see what is different.
January 24, 2025 at 4:14 pm
frederico_fonseca wrote:SELECT DISTINCT tp.client_id AS Member_ID
, tp.PaidDate AS Paid_Date
, tp.DOS
FROM #ins_temp tp
WHERE (@dateChoice = 'Paid_Date' and tp.PaidDate BETWEEN @startDate AND @endDate)
or (@dateChoice = 'DOS' and tp.DOS BETWEEN @startDate AND @endDate)Note that this query is non-SARGable, and will always result in a scan
not really.. on my SQL (2019) on a table with 700 Million rows, its doing 2 clustered index seeks (1 per condition) with filder and merge join
final plan is
select <-stream agg <-merge join (concatenation) < filter <-clustered seek (with the correct seek predicate)
< filter <-clustered seek (with the correct seek predicate)
on the sample data I've tried it takes less than 1 second to filter and retrieve 2 million rows
January 24, 2025 at 5:56 pm
My bad... debugging clearly showed I had failed to declare the varchar variable '@dateChoice' with a length.... Thanks! It works now:
DECLARE@startDate datetime = '11/1/2024'
,@endDate datetime = '12/31/2024'
,@dateChoice varchar (10) = 'Date_Paid'
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy