CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
On The Net Again
On The Net Again

Reputation: 305

How to turn potential loop query to set based?

I have something like this currently -

declare @thisDate smalldatetime

create table #theseDates (thisDate smalldatetime)
insert into #theseDates(thisDate)
values ('11/1/2016', '5/1/2017', '9/30/2017)

create table #someData (productid int, productname varchar(32), productprice float, productsolddate smalldatetime)

declare date_cursor cursor for
select thisDate from #theseDates

open date_cursor
fetch next for date_cursor into @thisDate

while @@fetch_status = 0
begin
   insert into #someData(soldid, productid, productname, productprice, productsolddate)
   select soldid, productid, productname, productprice, productsolddate
   from soldproducts
   where productsolddate between @thisDate and getdate()

   fetch next from date_cursor into @thisDate
end

close date_cursor
deallocate date_cursor

How do I change this from a cursor to a set-based solution? NOTE: added fetch line

Upvotes: 1

Views: 66

Answers (1)

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

You don't need a cursor to solve this OP. Use CROSS Join instead
This way faster than Cursor.

See below.
FYI. the mydates table is just a random date example because I don't want type them 

declare @myproducts table (prodid int, proddesc varchar(30), prodprice money, solddate date)

insert into @myproducts
values 
(1,'prod 1',1,dateadd(day,-1,getdate())),
(2,'prod 2',10,dateadd(day,-10,getdate())),
(3,'prod 3',100,dateadd(day,-5,getdate())),
(1,'prod 1',1.5,dateadd(day,-6,getdate())),
(2,'prod 2',8.99,dateadd(day,-20,getdate())),
(3,'prod 3',95,dateadd(day,-15,getdate())),
(1,'prod 1',0.89,dateadd(day,-2,getdate()))

select * from @myproducts


declare @mydates table (mydate date)

/* reporting datefrom */

insert into @mydates
values (dateadd(day,-11,getdate())),(dateadd(day,-20,getdate())),(dateadd(day,-3,getdate()))

select * from @mydates

/* apply cross join to distribute the datefrom but should only be to data where solddate is between datefrom and today */

-- insert statement to dump all qualified data or not
select p.*,d.mydate [DateFrom] from @myproducts p
cross join @mydates d
where solddate between mydate and cast(getdate() as date)

Upvotes: 1

Related Questions