CODEWITHSUNDEEP
sql-server
user2364296
user2364296

Reputation: 33

run query multiple times for date range one date at a time...cannot use sproc

I only have select access to the database, and cannot create a stored procedure. Any ideas on how to automate a query for a date range when the where clause filters on one data at a time?

For example....I need to run a query similar to the one below for every date between 4/1/2013 and 4/30/2013 one at a time. It seems like I should be able to do this with a cursor or something.

declare @AsOfDate datetime
SET @AsOfDate = '4/1/2013'

select *,@AsOfDate AS 'AsOfDate' from table p
where 
p.ENTER_WQ_DT <= @AsOfDate  and 
((coalesce(p.FILE_DATE,p.DELETE_DATE) > @AsOfDate ) or (p.FILE_DATE is null and p.DELETE_DATE is null and WQ_CTE.TAR_ID is not null))

I would love to be able to run this and generate a result that contains all records just like if I manually edited the AsOfDate variable and ran it for every day of the month.

Upvotes: 3

Views: 6146

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107766

declare @AsOfDateStart datetime, @AsOfDateEnd datetime;
select @AsOfDateStart = '20130401', @AsOfDateEnd = '20130430';

-- this "WITH" block starts a recursive Common Table Expression (CTE)
-- that makes up a "table" of the dates, one per row
with Dates as (
    select @AsOfDateStart AsOfDate
    union all
    select dateadd(d,1,AsOfDate)
    from Dates
    where AsOfDate < @AsOfDateEnd
)
select p.*, d.AsOfDate
from Dates d
join tablep p
  on p.ENTER_WQ_DT <= d.AsOfDate  and 
((coalesce(p.FILE_DATE,p.DELETE_DATE) > d.AsOfDate ) or (p.FILE_DATE is null and p.DELETE_DATE is null and WQ_CTE.TAR_ID is not null));

About this query:

  1. The JOIN effectively causes tablep to be searched for each of the rows (a date) in the Dates (virtual) table.

Notes about your query:

  1. Don't use ambiguous date literals ('4/1/2013'). The format YYYYMMDD is best
  2. Use square brackets for enclosing [names] instead of single/double quotes ('AsOfDate')

Upvotes: 3

ChrisLively
ChrisLively

Reputation: 88062

SQL server provides looping mechanisms.

something like:

declare @AsOfDate datetime = '2013-04-01'
declare @EndDate datetime = '2013-05-01'    

WHILE (@AsOfDate < @EndDate) BEGIN
    --your query here
    SET @AsOfDate = DATEADD(day, 1, @AsOfDate)
END

Upvotes: 2

Related Questions