Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Newbie date start problem Expand / Collapse
Author
Message
Posted Tuesday, December 24, 2013 11:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 5:08 PM
Points: 6, Visits: 59
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 @TicketStartDate DATETIME = '2013-12-15' --change date to Sunday's date
DECLARE @TicketEndDate DATETIME = 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];

Post #1525788
Posted Tuesday, December 24, 2013 12:57 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 1:15 PM
Points: 653, Visits: 3,831
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.
Post #1525809
Posted Tuesday, December 24, 2013 1:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 5:08 PM
Points: 6, Visits: 59
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!
Post #1525810
Posted Thursday, December 26, 2013 9:44 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:19 PM
Points: 3,499, Visits: 7,549
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1525995
Posted Thursday, December 26, 2013 2:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 5:08 PM
Points: 6, Visits: 59
Thank you Luis! I will try that as well. I appreciate your response!
Post #1526048
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse