variable date doesn't work in cursor

Question

Hello,

First timer here. I'm having an issue when I use a date variable to set the 'between dates'. The query works when when I replace the variable w/ static dates. I get all rows returned when I use the variable and only three when I use the static dates. Could someone pls explain what I'm doing wrong?

thanks!!!

Brian

set nocount on;

declare @itemclass varchar(10)
declare @maxdate varchar(10)

declare datecursor cursor fast_forward for

select distinct f.itemclass
    , convert(char(10),max(f.invoicedate),21)
from forderhistdetail f
    inner join dcustomerorder co
    on f.orderrecid = co.recid
where co.salesdate between '2010-01-01' and '2010-03-31'
    and f.custnum = 'C001838'
    and not exists (select *
                    from forderhistdetail f2
                      where f2.custnum = 'C001838'
    				  and f2.itemclass = f.itemclass
                      and f2.invoicedate between dateadd(yy,-1,@maxdate) and @maxdate
--  				  and f2.invoicedate between '2009-01-01' and '2009-12-31' -- this line works
    				)
group by f.itemclass
order by f.itemclass

open datecursor
fetch next from datecursor
into @itemclass, @maxdate

while @@fetch_status = 0

begin
print @itemclass
print @maxdate
print convert(char(10),dateadd(yy,-1,@maxdate),21)
print '--'

select @itemclass as itemclass, @maxdate as dls

fetch next from datecursor
into @itemclass, @maxdate

end 
close datecursor
deallocate datecursor

Answer 1

@maxdate is declared as variable to capture the output of the cursor to, so on the invocation of the cursor, @maxdate = null, hence all the rows are returned since the inner query evaluates to

and not exists (nothing) = True!

EDIT: added solution following on from OP's comments below

Set up some sample data
I've ignored the dcustomerorder table as that is used just to limit the orders to a given sales date. Also ignored custnum.

declare @forderhistdetail table(itemclass int, invoicedate char(10))

insert into @forderhistdetail
      select 10,'2009-12-09'
union select 10,'2010-02-02'
union select 11,'2010-01-07'
union select 12,'2009-02-03'
union select 12,'2009-03-02' 
union select 33,'2009-11-02' 
union select 33,'2010-01-28' 
union select 36,'2010-01-25' 
union select 39,'2009-02-02' 
union select 39,'2009-06-30' 
union select 39,'2009-07-01'

now for the code

-- set dateformat so that implicit conversion to datetime works without issue
set dateformat mdy

select
    itemclass, max(invoicedate)
from @forderhistdetail f1
where
    invoicedate = (select max(invoicedate) 
                   from @forderhistdetail f2
                   where f1.itemclass = f2.itemclass)
and itemclass not in (select itemclass 
                      from @forderhistdetail f3 
                      where invoicedate > (select dateadd(yy, -1,max(invoicedate))
                                           from @forderhistdetail f4
                                           where f3.itemclass = f4.itemclass)
                      and invoicedate <  (select max(invoicedate)
                                         from @forderhistdetail f5
                                         where f3.itemclass = f5.itemclass))
group by itemclass

Explanation:
1st where clause, limits the returned rows to those with the latest invoicedate
2nd where clause limits the itemclasses to only those that haven't had another invoice in the 12 months leading up to the latest. Have not used between as that is inclusive of the bounds, so made it a simpler '<' and '>' check.

No guarantee that this is the most efficient solution, I wanted to lay it out almost step by step to show you the method, rather than give a whizz-bang solution [Note: this is similar in a sense to the DW stumper puzzle that Joe Celko posted at Simple Talk and subsequently discussed over on the SSC main site - I urge you to check out that article and the solutions provided.]

Answer 2

0

Where do you use the date variable? Do you mean @maxdate? I can't see where you initialize @maxdate before using it, but maybe you missed it in your question? Otherwise your subquery will not return any rows since @maxdate is null. Anything compared to NULL is always false. If your subquery doesn't return any rows then the outer query will return every row.

answered Feb 18 '10 at 01:00 PM

Håkan Winther
15k ● 29 ● 35 ● 46

@maxdate is set to convert(char(10),max(f.invoicedate),21) by the cursor

Feb 18 '10 at 01:02 PM Tom Staab

Yes, but the first fetch will return every row, wouldn't it? The variable is used before it is initialized.

Feb 18 '10 at 01:04 PM Håkan Winther

Ah! Yes. Very good. In fact, I decided to give you +1 for the answer and the additional comment. Great catch.

Feb 18 '10 at 01:07 PM Tom Staab

Can I get a 1/2 point? ;) I think I was correct about @maxdate being null, but your reason for that seems much more likely than mine. :)

Feb 18 '10 at 01:08 PM Tom Staab

I'll give you a point! :)

Feb 18 '10 at 01:15 PM Håkan Winther

(comments are locked)

Answer 3

0

If max(f.invoicedate) is null, @maxdate will be null. Therefore a "NOT EXISTS" check on a date comparison of null will always be true, and you will get all rows.

answered Feb 18 '10 at 01:01 PM

Tom Staab
5.8k ● 5 ● 8 ● 10

(comments are locked)

variable date doesn't work in cursor

Follow this question

Related Questions