Newbie date start problem

Question

Post reply

Newbie date start problem

tod.novak

SSC Veteran

Points: 240
More actions
December 24, 2013 at 11:15 am

#283926

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];

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

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

Chrissy321 SSCoach Points: 15763 More actions · Answer 1

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.

tod.novak SSC Veteran Points: 240 More actions · Answer 2

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!

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

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

tod.novak SSC Veteran Points: 240 More actions · Answer 4

Thank you Luis! I will try that as well. I appreciate your response!