Post reply

What makes it faster?

  • October 12, 2006 at 8:22 am

    #665505

    You're welcome...

    One thing that did concern me about your original query is that the I.InvoiceDate is likely a Datetime, and so the @EndDate you're using may not include everything you think it does. 

    An invoice date of "10/12/2006 08:15:00.000" will not appear on a report from "10/11/2006" to "10/12/2006"  since "10/12/2006" is actually midnight "10/12/2006 00:00:00.000"  Another example would be a report for "10/12/2006" through "10/12/2006" which would only include InvoiceDate values that occurred during the 1 millisecond interval of the report "10/12/2006 00:00:00.000"

    You may want to add 1 day to your @EndDate variable before using it.  Also, you may want to change the search criteria for it to be:

    AND I.InvoiceDate >= @StartDate

    AND I.InvoiceDate < dateadd(dd, 1, @EndDate)

    Therefore, midnight on the modified @EndDate is not included so that you do not get overlapping results (by including Midnight on "10/13/2006" on two different days.

     

Viewing post 16 (of 15 total)

You must be logged in to reply to this topic. Login to reply