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

avatar image

bvolk
33 2 2 4

(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

avatar image

Kev Riley ♦♦
64k 48 61 81

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 ♦♦
(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

avatar image

Håkan Winther
16.5k 36 45 57

@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

avatar image

Tom Staab ♦
14.5k 7 14 18

(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:

x2018
x68
x29

asked: Feb 18, 2010 at 12:41 PM

Seen: 2082 times

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

Copyright 2016 Redgate Software. Privacy Policy