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?
@maxdate is declared as variable to capture the output of the cursor to, so on the invocation of the cursor,
EDIT: added solution following on from OP's comments below
Set up some sample data
now for the code
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.]
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, 2010 at 01:00 PM
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, 2010 at 01:01 PM