Alternative to cursor when applying a list of values to a where clause?

Question

What's an alternative to getting a distinct number of dates, say all the dates for September:

9/1/2016
9/2/2016
9/3/2016

and apply each value to a query. Say something like:

Select GuitarId,GuitarBrand
From GuitarSales
Where GuitarDate = @date

I don't want to use a cursor, is there an alternative to doing this?

I tried a CTE but even then I'd have to apply the cursor for each date.

lookup/temp table, then join. preferably with indexes on both tables. — Beth, Oct 05 '16 at 19:30
Can you elaborate? Where do you want to use the result of this query? — Rick Wolff, Oct 05 '16 at 19:45
Are you looking to find all rows for a specific September (like 2016) or all Septembers? If it is a specific september you don't need to generate a list of all dates, you can do that with some simple date math. — Sean Lange, Oct 05 '16 at 19:51

ScaisEdge · Accepted Answer · 2016-10-05T20:09:08.670

4

If you want all the dates for a month you can use

Select GuitarId,GuitarBrand
From GuitarSales
Where month(GuitarDate) = 9
and year(GuitarDate) = 2016;

edited Oct 05 '16 at 20:09

answered Oct 05 '16 at 19:33

ScaisEdge

131,976
10
91
107

But don't forget the year :-) – Shnugo Oct 05 '16 at 20:08
Not the most efficient way to go about this. A SARGable predicate would be better so you can utilize indexes. – Sean Lange Oct 05 '16 at 20:54
1

@SeanLange i know this but the OP don't show the indexs structure or others informatio about schema, storage or performance but seems only interested to obatin short condition .. but if you have a better where post an you answer .. so all the community can evaluate .. it – ScaisEdge Oct 05 '16 at 21:04

score 2 · Answer 2 · edited May 23 '17 at 12:00

If I understand you correctly, you need a list of all dates in September. This is a quick solution to get a gapless list of all days in September: In your query you can use this as source and LEFT JOIN your actual data.

WITH RunningNumbers AS
(
    SELECT TOP(30) ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1 AS Nr
    FROM sys.objects 
)
SELECT {d'2016-09-01'}+Nr AS RunningDate
FROM RunningNumbers

There are many examples, how you can create a tally table on the fly. Small numbers (like 30 in this example) can be taken easily from any table with sufficient rows.

If you need this more often you might think about a Numbers-Table

a related question: https://stackoverflow.com/a/39387790/5089204
create a persitant numbers table with a lot of usefull side data: https://stackoverflow.com/a/32474751/5089204

score 0 · Answer 3 · answered Oct 05 '16 at 21:39

Assuming you have an index on GuitarDate here is a way you can create a SARGable where predicate so you can still leverage the speed of using an index seek.

declare @date datetime = '2016-09-10' --just to demonstrate starting with September 10, 2016

select gs.GuitarId
    , gs.GuitarBrand
From GuitarSales gs
where gs.GuitarDate >= dateadd(month, datediff(month, 0, @date), 0) --beginning of the month for @date
    and gs.GuitarDate < dateadd(month, datediff(month, 0, @date) + 1, 0) --beginning of next month

Alternative to cursor when applying a list of values to a where clause?

3 Answers3