Newbie date start problem

  • I'm just learning SQL (on my own), and have run across a problem I can't seem to solve. I run the query below once per week to get the Help Desk ticket counts of the previous week for my boss. I may run it on Monday, or maybe Wednesday, but whichever day it's run, I need the results to be generated starting on Sunday of the previous week. If I run it today, I need the results for 12/15-12/21.

    If I can just figure out the @TicketStartDate portion to be automatically set for the previous Sunday I'd be happy. I know it's not the prettiest code in the world, but like I said, I'm just learning. Any help is greatly appreciated!

    USE MyDatabaseName;

    GO

    DECLARE @TicketStartDateDATETIME = '2013-12-15'--change date to Sunday's date

    DECLARE @TicketEndDateDATETIME = DATEADD(DAY, 7, @TicketStartDate)--adds 7 days to @TicketStartDate

    --opened tickets

    SELECT[TicketId],

    [From] 'From User',

    [Title] 'Title of Ticket',

    [DateCreated] 'Date Opened'

    FROM MyDatabaseTable

    WHERE [DateCreated] >= @TicketStartDate

    and [DateCreated] <= @TicketEndDate

    ORDER BY [DateCreated];

    --closed tickets

    SELECT[TicketId],

    [From] 'From User',

    [Title] 'Title of Ticket',

    [LastUpdated] 'Date Closed'

    FROM MyDatabaseTable

    WHERE [LastUpdated] >= @TicketStartDate

    and [LastUpdated] <= @TicketEndDate

    and Status = 'Closed'

    ORDER BY [LastUpdated];

  • This is one way.

    DECLARE @TicketStartDate datetime

    DECLARE @TicketEndDate datetime

    SET @TicketEndDate = DATEADD(DAY, DATEDIFF(DAY, -1, GETDATE()) / 7 * 7, -1)

    SET @TicketStartDate = DATEADD(DAY, -8, @TicketEndDate)

    PRINT @TicketStartDate

    PRINT @TicketEndDate

    Also search this site for 'calendar tables'. Probably overkill for this situation but the method is great for date logic.

  • Thank you very much! That worked like a charm. I don't fully understand the date calculations, but I'll study it and try to figure it out.

    Much appreciated!

  • This is slightly different.

    SET @TicketStartDate = DATEADD( DAY, -1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()) - 1, 0))

    SET @TicketEndDate = DATEADD(DAY, 6, @TicketStartDate)

    There's a date zero which is usually 1900-01-01. We calculate the number of weeks minus one from that date and them add those weeks to date zero. This gives us last week's monday and we just go back one day to get the start date. The end date is easier to get.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thank you Luis! I will try that as well. I appreciate your response!

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

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