Post reply

Date range queries

  • August 8, 2003 at 1:33 pm

    #83034

    I am trying to design a report that allows users to select dates ranges, such as weekly, monthly, quarterly, anually, etc. However I'd like to have some ideas of how a query would be written if I am going to sum values for a quarterly period. I have a table that contains each day of the year for the next 3 years and a a table that contains values for each one of those days. Any help or comments are appreciated.

  • August 9, 2003 at 3:08 am

    #469286

    The easiest way is to define a @FromDate and a @ToDate parameter in your proc so you have solved your problem.

    Of cours you will have to define in your frontend application what @FromDate and @ToDate should be.

    And if you put a clustered index on your date column than the query will perform quite quickly.

    Gabor



    Bye
    Gabor

  • August 11, 2003 at 6:18 am

    #469383 

    

    CREATE PROC dbo.GetBasedOnDateRange

      @Start SMALLDATETIME

    , @End   SMALLDATETIME

    AS

    BEGIN

    --

    SELECT FieldList, SUM(YourValueField)

    FROM TableList

    WHERE YourDateField BETWEEN @Start AND @End

    GROUP BY FieldList

    --

    END

    GO

    Hope this gets you started...

  • August 11, 2003 at 7:19 am

    #469395

    thanks for your comments. Jay i think this give me a good start, i think I am on the right track...

    thanks again

  • August 11, 2003 at 4:22 pm

    #469479

    Basically useful advice, however from experience I would add the following reccomendations.

    1. To ensure the correct query plan is used for your SP, I would pass the date range parameters initially into the SP as type char or varchar.

    Then specifically CAST these to local datetime variables to ensure no parameter-sniffing issues.

    Also, note that if you do not include a Time portion of the date parameter it will default to midnight and you may not include rows on your range you would expect to.

    I always add 1 day (as no records exist in our system between 20:00 and 08:00) to the @ToDate parameter (or if your application is up 24hrs then add 23:59:59.997). This will guarantee it will include all records on the date you specify, not just those on this date with a time of 00:00.

    Stu

    Edited by - trekart on 08/11/2003 4:23:31 PM

    Edited by - trekart on 08/11/2003 4:24:59 PM

Viewing 5 posts - 1 through 4 (of 4 total)

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