SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Newbie date start problem


Newbie date start problem

Author
Message
tod.novak
tod.novak
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 164
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];


Chrissy321
Chrissy321
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2960 Visits: 4793
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
tod.novak
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 164
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
Luis Cazares
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39849 Visits: 19802
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
tod.novak
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 164
Thank you Luis! I will try that as well. I appreciate your response!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search