Final SELECT with WHERE based on passing in variable from Dropdown box

  • 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
  • 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

  • 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;
  • 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)
  • 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

  • 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
  • DaveBriCam wrote:

    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.

  • DesNorton wrote:

    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

     

  • 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