Date/Time Query to account for weekend

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date/Time Query to account for weekend

Author

Topic

specialk9203
Starting Member

11 Posts

Posted - 2014-06-09 : 19:59:59

Hi,

I am looking to create a query to select data to accomodate the weekend. For example, If today is Monday, then select Friday's records. This only has to be done for Monday's. For each additional day there would be a date difference of just 1 not 3. I cannot figure this out. Please help! I hard code the date diff when Monday rolls around. I have some criteria to help me figure this out, but I'm not sure how to make that specific so the CT.workeddate = what I have in my Where clause.

SELECT CT.ContactId, CT.OutreachId, CT.SchedulerId, CT.WorkedDate, CT.OutreachStatusId, CT.UpdateBy, C.CampaignID, C.PlanID, C.ProgramId, M.MarketID,
M.MarketStateName, CT.AppDate, DATENAME(dw, CT.WorkedDate) AS DayofWeek, DATEADD(Day, CASE DATENAME(WEEKDAY, GETDATE())
WHEN 'Sunday' THEN - 2 WHEN 'Monday' THEN - 3 ELSE - 1 END, DATEDIFF(DAY, 0, GETDATE())) AS WorkedDate2, CONVERT(DateTime, CONVERT(VarChar,
CT.WorkedDate, 101)) AS ModifiedWorkedDate
FROM dbo.RptOutreach AS O INNER JOIN
dbo.RptContacts AS CT ON CT.OutreachId = O.OutreachId INNER JOIN
dbo.RptCampaigns AS C ON C.CampaignID = O.CampaignId INNER JOIN
dbo.RptMarkets AS M ON M.MarketID = C.MarketId
WHERE (DATEDIFF(d, CT.WorkedDate, GETDATE()) = 3)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-10 : 13:44:32

[code]SELECT ct.ContactId,
ct.OutreachId,
ct.SchedulerId,
ct.WorkedDate,
ct.OutreachStatusId,
ct.UpdateBy,
c.CampaignID,
c.PlanID,
c.ProgramId,
m.MarketID,
m.MarketStateName,
ct.AppDate,
DATENAME(DAYOFWEEK, ct.WorkedDate) AS [DayofWeek],
DATEADD(DAY, f.Offset, f.theDay) AS WorkedDate2,
CONVERT(DATETIME, DATEDIFF(DAY, 0, CT.WorkedDate)) AS ModifiedWorkedDate
FROM dbo.RptOutreach AS o
INNER JOIN dbo.RptContacts AS ct ON ct.OutreachId = o.OutreachId
INNER JOIN dbo.RptCampaigns AS c ON c.CampaignID = o.CampaignId
INNER JOIN dbo.RptMarkets AS m ON m.MarketID = c.MarketId
INNER JOIN (
VALUES (0, -3, DATEDIFF(DAY, 0, GETDATE())),
(1, -1, DATEDIFF(DAY, 0, GETDATE())),
(2, -1, DATEDIFF(DAY, 0, GETDATE())),
(3, -1, DATEDIFF(DAY, 0, GETDATE())),
(4, -1, DATEDIFF(DAY, 0, GETDATE())),
(5, -1, DATEDIFF(DAY, 0, GETDATE())),
(6, -2, DATEDIFF(DAY, 0, GETDATE()))
) AS f(DoW, Offset, theDay) ON f.Dow = DATEDIFF(DAY, 0, GETDATE()) % 7
WHERE ct.WorkedDate >= DATEADD(DAY, f.Offset, f.theDay)
AND ct.WorkedDate < DATEADD(DAY, f.Offset + 1, f.theDay);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-10 : 14:30:45

Peter,

I've never seen a derived table like this:


INNER JOIN	(
			VALUES	(0, -3, DATEDIFF(DAY, 0, GETDATE())),
				(1, -1, DATEDIFF(DAY, 0, GETDATE())),
				(2, -1, DATEDIFF(DAY, 0, GETDATE())),
				(3, -1, DATEDIFF(DAY, 0, GETDATE())),
				(4, -1, DATEDIFF(DAY, 0, GETDATE())),
				(5, -1, DATEDIFF(DAY, 0, GETDATE())),
				(6, -2, DATEDIFF(DAY, 0, GETDATE()))
		) AS f(DoW, Offset, theDay)

Does it have a special name? Which version was it added?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-10 : 14:48:28

2008 I believe:
http://msdn.microsoft.com/en-us/library/dd776382.aspx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-10 : 15:00:30