x

variable date doesn't work in cursor

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
more ▼

asked Feb 18, 2010 at 12:41 PM in Default

bvolk gravatar image

bvolk
33 2 2 2

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

@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.]

more ▼

answered Feb 18, 2010 at 01:09 PM

Kev Riley gravatar image

Kev Riley ♦♦
54.3k 47 49 76

Sorry walked in late to the party....that's what you get from starting to write an answer, then being distracted by real work!
Feb 18, 2010 at 01:25 PM Kev Riley ♦♦
thank you, I understand.. but I don't understand how to set the @maxdate in my select to each @maxdate that is returned by the fetch. ...I'll keep working. thanks again.
Feb 18, 2010 at 01:26 PM bvolk
you can't use a variable set by the cursor to define the cursor - what is the real requirement?
Feb 18, 2010 at 01:32 PM Kev Riley ♦♦
Are you getting quirky updates and cursors muddled?
Feb 18, 2010 at 02:11 PM Ian Roke
hi, sorry for the delay (meeting). my requirement is to set each @maxdate to the max(invoicedate) for each item class. The rows that should be returned are the itemclasses that have not be sold in one year. Basically, a rolling 12 months rather than a hard coded date. thank you!
Feb 18, 2010 at 03:33 PM bvolk
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Feb 18, 2010 at 01:00 PM

Håkan Winther gravatar image

Håkan Winther
16k 35 37 48

@maxdate is set to convert(char(10),max(f.invoicedate),21) by the cursor
Feb 18, 2010 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, 2010 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, 2010 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, 2010 at 01:08 PM Tom Staab
I'll give you a point! :)
Feb 18, 2010 at 01:15 PM Håkan Winther
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Feb 18, 2010 at 01:01 PM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1951
x59
x28

asked: Feb 18, 2010 at 12:41 PM

Seen: 1911 times

Last Updated: Feb 18, 2010 at 01:03 PM