June 16, 2010 at 7:40 am
Ok, so check out my query below:
SELECT BBB.INCIDENT_ID AS [INCIDENT ID], MSP_HIXX AS [MSP HIXX], MSP_DCN AS [MSP DCX], CATEGORY, SUBCATEGORY AS AREA, CONVERT([varchar](10), MSP_DOR, 101) AS [MSP DOR], CONVERT([varchar](10), CLOSE_TIME, 101) AS [CLOSE TIME], BBB.ASSIGNMENT,
MSP_STATUS AS [MSP STATUS], RESOLUTION_CODE AS [CLOSURE CODE], CLOSED_BY AS [CLOSED BY], [OPEN] AS [MSP CORR STATUS], MSP_ASSIGNEE AS [MSP ASSIGNEE], CONVERT([varchar](10), OPEN_TIME, 101) AS [OPEN TIME], UPDATED_BY AS [UPDATED BY], CONVERT([varchar](10), UPDATE_TIME, 101) AS [UPDATE TIME], CONVERT([varchar](10), MSP_SCAN_DATE, 101) AS [MSP SCAN DATE]
FROM [RSC].dbo.INCIDENTSM1 AS AAA
INNER JOIN [RSC].dbo.INCIDENTSA1 AS BBB ON BBB.INCIDENT_ID = AAA.INCIDENT_ID
WHERE MSP_ORIGIN='WC'
AND OPEN_TIME<CONVERT([varchar](10), GetDate(), 101)+' 00:00:00'
AND [OPEN] LIKE 'Open%';
The problem is that I need this query to check at runtime to see if it's being run on a Monday.
If it is, I need it to run exactly like it looks now.
But, if it's being run on a Tuesday, I need it to modify the GetDate parameters in the OPEN_TIME where clause to subtract a day to equal Monday.
And if it's run on a Wednesday, it needs to subtract 2 days, etc.
Can that be done?
June 16, 2010 at 7:45 am
you can use the DATEADD and DATEDIFF function to determine the monday of the current week.
--Monday of the Current Week
select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
so you want to avoid converting a datetime to varchar and use this instead:
(formatted for readability)
SELECT
BBB.INCIDENT_ID AS [INCIDENT ID],
MSP_HIXX AS [MSP HIXX],
MSP_DCN AS [MSP DCX],
CATEGORY,
SUBCATEGORY AS AREA,
CONVERT([varchar](10), MSP_DOR, 101) AS [MSP DOR],
CONVERT([varchar](10), CLOSE_TIME, 101) AS [CLOSE TIME], BBB.ASSIGNMENT,
MSP_STATUS AS [MSP STATUS],
RESOLUTION_CODE AS [CLOSURE CODE],
CLOSED_BY AS [CLOSED BY], [OPEN] AS [MSP CORR STATUS],
MSP_ASSIGNEE AS [MSP ASSIGNEE],
CONVERT([varchar](10), OPEN_TIME, 101) AS [OPEN TIME],
UPDATED_BY AS [UPDATED BY],
CONVERT([varchar](10), UPDATE_TIME, 101) AS [UPDATE TIME],
CONVERT([varchar](10), MSP_SCAN_DATE, 101) AS [MSP SCAN DATE]
FROM [RSC].dbo.INCIDENTSM1 AS AAA
INNER JOIN [RSC].dbo.INCIDENTSA1 AS BBB
ON BBB.INCIDENT_ID = AAA.INCIDENT_ID
WHERE MSP_ORIGIN='WC'
--Monday of the Current Week
--select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
AND OPEN_TIME < DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
AND [OPEN] LIKE 'Open%';
Lowell
June 16, 2010 at 7:59 am
I am going to ask a dumb question here but I am just trying to understand it. What are the elements in this that make it find mondays date.
select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
Is the 0 part of that. I understand how dateadd and datediff work and I see that the datepart of this element is wk (Week) but I can not wrap my head around how this would come up with monday.
I usually do this with a mouch more complex method that just made sense to me. I like this version much better but I like to understand how it works.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 16, 2010 at 8:15 am
Dan this is one of the coolest things to use once you get your head wrapped around it.
if you select DATEDIFF(wk,0,getdate())
this will return week '5763' , which is the # of weeks (wk) from the beginning of SQL time 01/01/1900
by adding it to zero(SQL starting date '01/01/1900') with DATEADD(which returns a datetime), you get the first day of the beginning of that week 5763, which is Monday. Mentally, i sometimes think of this method as "truncating" all the time portion, and then selecting the begining of the period.
the same concept works when you add months, years, or quarters...and even hours minutes seconds.....you get the # of [unit you selected] since the beginning of SQL Time, and by adding that #periods to zero(SQL start date), you get the beginning of that period.
run this query so you have everything in front of you:
select
getdate(), --2010-06-16 10:08:47.680
DATEDIFF(wk,0,getdate()), --week '5763' from the beginning of SQL time 01/01/1900
DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) --by adding zero, we return a date that is the first day of the week 5763
here's a collection of "firsts and lasts" i've saved in my snippets:
--find the first business day (Monday) of this month
select DATEADD(wk,DATEDIFF(wk,0,dateadd(dd,6 - datepart(day,getdate()),getdate())), 0)
--find the last day of the prior month
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))
--find the third friday of this month:
--14 two weeks plus the M-F offset of 4
select DATEADD(dd,18,DATEADD(wk,DATEDIFF(wk,0,dateadd(dd,6 - datepart(day,getdate()),getdate())), 0))
select DATEADD(wk,2,DATEADD(dd,4,DATEADD(wk,DATEDIFF(wk,0,dateadd(dd,6 - datepart(day,getdate()),getdate())), 0)) )
--last business day(Friday) of the prior month...
datename(dw,dateadd(dd,-3,DATEADD(wk,
DATEDIFF(wk,0,dateadd(dd,7-datepart(day,getdate()),getdate())), 0)))
--Monday of the Current Week
select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
--Friday of the Current Week
select dateadd(dd,4,DATEADD(wk, DATEDIFF(wk,0,getdate()), 0))
--First Day of this Month
select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
--First Day of the Year
select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
--First Day of the Quarter
select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
--Midnight for the Current Day
select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
--Last Day of Prior Year
select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))
--Last Day of Current Month
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0))
--Last Day of Current Year
select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() )+1, 0))
Lowell
June 16, 2010 at 8:22 am
Lowell, You clarify for me again why I love the DBA community. Thank you for going above and beyond with your script examples. I think I am getting it now. I had a working scripts to find most of these dates but I have been doing it a very complex way for some time.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 16, 2010 at 8:24 am
it might be more clear if it looked like this...the zero is the first date in SQL:
select DATEADD(wk, DATEDIFF(wk,'19000101 00:00:00.000',getdate()), '19000101 00:00:00.000')
--is the same as
select DATEADD(wk, DATEDIFF(wk,0,getdate()), '19000101 00:00:00.000')
--is the same as
select DATEADD(wk, DATEDIFF(wk,0,getdate()),0)
Lowell
June 16, 2010 at 8:24 am
Well stated Lowell.
Btw, I think that calc returns a Monday since 01/01/1900 happended to be a Monday 🙂 .
Scott Pletcher, SQL Server MVP 2008-2010
June 16, 2010 at 8:26 am
SELECT CAST(0 AS DATETIME) -- = '1900-01-01 00:00:00.000': 0 expressed as a date, the base year, see
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/3b078d5b-526c-4884-906a-e4442805795f.htm
-- what day of the week is the base date?
SELECT DATENAME(dw, CAST('1900-01-01 00:00:00.000' AS DATETIME)) -- it's a monday;
-- adding a whole number of weeks will always result in a monday
SELECT DATEDIFF(wk, 0, GETDATE()) -- = 5763: difference in whole weeks between the base year and now
-- add 5763 weeks to the base year and express the result as a date:
SELECT DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0) -- = '2010-06-14 00:00:00.000': monday of this week
Slightly different workings to Lowell's cool explanation.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 16, 2010 at 8:32 am
Chris the link to the help page is very useful thank you for the reference.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply