March 3, 2009 at 8:12 am
His method is working fine. I have tested it.
Actually my requiremrnt is little bit different what he has given.
karthik
March 3, 2009 at 8:19 am
karthikeyan (3/3/2009)
His method is working fine. I have tested it.Actually my requiremrnt is little bit different what he has given.
Can you demonstrate please, Karthik? I'd be very keen to see your solution, with a description of how the revised requirements differ from the original. New ideas are welcome!
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
March 3, 2009 at 8:38 am
karthikeyan (3/3/2009)
The below query will give the current week's first date and lastdate.Declare @InputDate Datetime
select @InputDate = '28/feb/2009'
select case when upper(datename(dw,@InputDate)) = 'MONDAY' then @InputDate
when upper(datename(dw,@InputDate)) = 'TUESDAY' then dateadd(dd,-1,@InputDate)
when upper(datename(dw,@InputDate)) = 'WEDNESDAY' then dateadd(dd,-2,@InputDate)
when upper(datename(dw,@InputDate)) = 'THURSDAY' then dateadd(dd,-3,@InputDate)
when upper(datename(dw,@InputDate)) = 'FRIDAY' then dateadd(dd,-4,@InputDate)
when upper(datename(dw,@InputDate)) = 'SATURDAY' then dateadd(dd,-5,@InputDate)
when upper(datename(dw,@InputDate)) = 'SUNDAY' then dateadd(dd,-6,@InputDate)
END as StartDate,
case when upper(datename(dw,@InputDate)) = 'MONDAY' then dateadd(dd,4,@InputDate)
when upper(datename(dw,@InputDate)) = 'TUESDAY' then dateadd(dd,3,@InputDate)
when upper(datename(dw,@InputDate)) = 'WEDNESDAY' then dateadd(dd,2,@InputDate)
when upper(datename(dw,@InputDate)) = 'THURSDAY' then dateadd(dd,1,@InputDate)
when upper(datename(dw,@InputDate)) = 'FRIDAY' then @InputDate
when upper(datename(dw,@InputDate)) = 'SATURDAY' then dateadd(dd,-1,@InputDate)
when upper(datename(dw,@InputDate)) = 'SUNDAY' then dateadd(dd,-2,@InputDate)
END as EndDate
This is quite a bit simpler, and it works the same no matter what the setting of language or datefirst is.
select
Monday = dateadd(dd,(datediff(dd,-53690,a.DT)/7)*7,-53690) ,
Friday = dateadd(dd,(datediff(dd,-53690,a.DT)/7)*7,-53690)+4
from
(-- Test Data
select DT = convert(datetime,'20090302')union all
select convert(datetime,'20090303')union all
select convert(datetime,'20090304')union all
select convert(datetime,'20090305')union all
select convert(datetime,'20090306')union all
select convert(datetime,'20090307')union all
select convert(datetime,'20090308')union all
select convert(datetime,'20090309')
) a
Results:
Monday Friday
----------------------- -----------------------
2009-03-02 00:00:00.000 2009-03-06 00:00:00.000
2009-03-02 00:00:00.000 2009-03-06 00:00:00.000
2009-03-02 00:00:00.000 2009-03-06 00:00:00.000
2009-03-02 00:00:00.000 2009-03-06 00:00:00.000
2009-03-02 00:00:00.000 2009-03-06 00:00:00.000
2009-03-02 00:00:00.000 2009-03-06 00:00:00.000
2009-03-02 00:00:00.000 2009-03-06 00:00:00.000
2009-03-09 00:00:00.000 2009-03-13 00:00:00.000
(8 row(s) affected)
March 3, 2009 at 8:55 am
Here is another way to accomplish this, based again an Sergiy's original code:
declare @pDate datetime;
set @pDate = getdate();
select
dateadd(wk, datediff(wk, 0, @pDate - 1), 0) as BeginDate,
case when dateadd(wk, datediff(wk, 0, @pDate - 1), 0) + 4 > dateadd(dd, datediff(dd, 0, @pDate), 0)
then dateadd(dd, datediff(dd, 0, @pDate), 0)
else dateadd(wk, datediff(wk, 0, @pDate - 1), 0) + 4
end as EndDate;
March 3, 2009 at 9:58 am
Lynn Pettis (3/3/2009)
Here is another way to accomplish this, based again an Sergiy's original code:
declare @pDate datetime;
set @pDate = getdate();
select
dateadd(wk, datediff(wk, 0, @pDate - 1), 0) as BeginDate,
case when dateadd(wk, datediff(wk, 0, @pDate - 1), 0) + 4 > dateadd(dd, datediff(dd, 0, @pDate), 0)
then dateadd(dd, datediff(dd, 0, @pDate), 0)
else dateadd(wk, datediff(wk, 0, @pDate - 1), 0) + 4
end as EndDate;
Doesn't look like it returns the correct values for EndDate:
select
dateadd(wk, datediff(wk, 0, a.DT - 1), 0) as BeginDate,
case when dateadd(wk, datediff(wk, 0, a.DT - 1), 0) + 4 > dateadd(dd, datediff(dd, 0, a.DT), 0)
then dateadd(dd, datediff(dd, 0, a.DT), 0)
else dateadd(wk, datediff(wk, 0, a.DT - 1), 0) + 4
end as EndDate
from
(-- Test Data
select DT = convert(datetime,'20090302') union all
select convert(datetime,'20090303') union all
select convert(datetime,'20090304') union all
select convert(datetime,'20090305') union all
select convert(datetime,'20090306') union all
select convert(datetime,'20090307') union all
select convert(datetime,'20090308') union all
select convert(datetime,'20090309')
) a
Results:
BeginDate EndDate
----------------------- -----------------------
2009-03-02 00:00:00.000 2009-03-02 00:00:00.000
2009-03-02 00:00:00.000 2009-03-03 00:00:00.000
2009-03-02 00:00:00.000 2009-03-04 00:00:00.000
2009-03-02 00:00:00.000 2009-03-05 00:00:00.000
2009-03-02 00:00:00.000 2009-03-06 00:00:00.000
2009-03-02 00:00:00.000 2009-03-06 00:00:00.000
2009-03-02 00:00:00.000 2009-03-06 00:00:00.000
2009-03-09 00:00:00.000 2009-03-09 00:00:00.000
(8 row(s) affected)
March 3, 2009 at 10:19 am
Yep, your right, I had a slight mistake in my code. Here is the corrected code:
declare @pDate datetime;
set @pDate = getdate();
select
dateadd(wk, datediff(wk, 0, @pDate - 2), 0) as BeginDate,
case when dateadd(wk, datediff(wk, 0, @pDate - 2), 0) + 4 > dateadd(dd, datediff(dd, 0, @pDate), 0)
then dateadd(dd, datediff(dd, 0, @pDate), 0)
else dateadd(wk, datediff(wk, 0, @pDate - 2), 0) + 4
end as EndDate;
Also, you will notice the the end date is not the following Friday until you get to Friday, which is what the OP requested.
March 3, 2009 at 10:20 am
Michael, you might want to check your code, too...
DROP TABLE #Dates
CREATE TABLE #Dates (aDate DATETIME)
INSERT INTO #Dates
SELECT TOP 100 DATEADD(dd,DATEDIFF(dd, 0, getdate()),50-number)
FROM Numbers
SELECT aDate, SergiyStart, SergiyEnd, CJMStart, CJMEnd, MVJStart, MVJEnd,
datediff(dd, aDate, SergiyStart) AS DaysToStartweek,
DATENAME(dw,aDate) AS [Day]
FROM (
SELECT aDate,
DATEADD(wk, DATEDIFF(wk, 0, aDate - 2), 0) AS SergiyStart,
DATEADD(wk, DATEDIFF(wk, 0, aDate - 2), 0) + 4 AS SergiyEnd,
aDate - ISNULL(NULLIF(DATEDIFF(dd, 0, aDate) % 7, 0), 7) AS CJMStart,
aDate - ISNULL(NULLIF(DATEDIFF(dd, 0, aDate) % 7, 0), 7) + 4 AS CJMEnd,
dateadd(dd,(datediff(dd,-53690,aDate)/7)*7,-53690) AS MVJStart,
dateadd(dd,(datediff(dd,-53690,aDate)/7)*7,-53690)+4 AS MVJEnd
FROM #Dates ) d
Cheers
ChrisM
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
March 3, 2009 at 10:55 am
Yep that works, Lynn:
DROP TABLE #Dates
CREATE TABLE #Dates (aDate DATETIME)
INSERT INTO #Dates
SELECT TOP 100 DATEADD(dd,DATEDIFF(dd, 0, getdate()),50-number)
FROM Numbers
SELECT aDate, DATENAME(dw,aDate) AS [Day],
datediff(dd, aDate, SergiyStart) AS DaysToStartweek,
SergiyStart, SergiyEnd, CJMStart, CJMEnd, LPStart, LPEnd, MVJStart, MVJEnd
FROM (
SELECT aDate,
DATEADD(wk, DATEDIFF(wk, 0, aDate - 2), 0) AS SergiyStart,
DATEADD(wk, DATEDIFF(wk, 0, aDate - 2), 0) + 4 AS SergiyEnd,
aDate - ISNULL(NULLIF(DATEDIFF(dd, 0, aDate) % 7, 0), 7) AS CJMStart,
aDate - ISNULL(NULLIF(DATEDIFF(dd, 0, aDate) % 7, 0), 7) + 4 AS CJMEnd,
dateadd(wk, datediff(wk, 0, aDate - 2), 0) as LPStart,
case when dateadd(wk, datediff(wk, 0, aDate - 2), 0) + 4 > dateadd(dd, datediff(dd, 0, aDate), 0)
then dateadd(dd, datediff(dd, 0, aDate), 0)
else dateadd(wk, datediff(wk, 0, aDate - 2), 0) + 4
end as LPEnd,
dateadd(dd,(datediff(dd,-53690,aDate)/7)*7,-53690) AS MVJStart,
dateadd(dd,(datediff(dd,-53690,aDate)/7)*7,-53690)+4 AS MVJEnd
FROM #Dates ) d
Shame Karthik's done a runner with the new spec.
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
March 3, 2009 at 4:12 pm
Chris Morris (3/3/2009)
Michael, you might want to check your code, too...
DROP TABLE #DatesCREATE TABLE #Dates (aDate DATETIME)
INSERT INTO #Dates
SELECT TOP 100 DATEADD(dd,DATEDIFF(dd, 0, getdate()),50-number)
FROM Numbers
SELECT aDate, SergiyStart, SergiyEnd, CJMStart, CJMEnd, MVJStart, MVJEnd,
datediff(dd, aDate, SergiyStart) AS DaysToStartweek,
DATENAME(dw,aDate) AS [Day]
FROM (
SELECT aDate,
DATEADD(wk, DATEDIFF(wk, 0, aDate - 2), 0) AS SergiyStart,
DATEADD(wk, DATEDIFF(wk, 0, aDate - 2), 0) + 4 AS SergiyEnd,
aDate - ISNULL(NULLIF(DATEDIFF(dd, 0, aDate) % 7, 0), 7) AS CJMStart,
aDate - ISNULL(NULLIF(DATEDIFF(dd, 0, aDate) % 7, 0), 7) + 4 AS CJMEnd,
dateadd(dd,(datediff(dd,-53690,aDate)/7)*7,-53690) AS MVJStart,
dateadd(dd,(datediff(dd,-53690,aDate)/7)*7,-53690)+4 AS MVJEnd
FROM #Dates ) d
Cheers
ChrisM
The code you posted doesn't work, unless you happen to have a table named Numbers in the current database.
After I corrected that, it looks to me like my code is the only one returning the correct results:
The latest Monday on or before the current date, and the Friday following that Monday.
The other two seem to be returning the prior Monday when the current date is Monday.
However, the OPs statements about the actual requirements are ambiguous and confusing, so maybe we are working towards different objectives.
March 3, 2009 at 4:28 pm
Michael Valentine Jones (3/3/2009)
After I corrected that, it looks to me like my code is the only one returning the correct results:The latest Monday on or before the current date, and the Friday following that Monday.
The other two seem to be returning the prior Monday when the current date is Monday.
My first version (with " -1") was doing exactly what yours code is doing.
After Lynn pointed on the OP requirements I changed it to "- 2" to return previous week when the current date is Monday.
So, it all comes to 1 digit change.
_____________
Code for TallyGenerator
March 3, 2009 at 4:36 pm
Michael
If you read post numbers 666817, 666826, and 667164 above, you will see that on Moday you return the previous Monday through Friday. On Tuesday through Friday in is the current week from Monday through the date provided. Saturday and Sunday also return the Monday through Friday.
I tried to copy the relavent posts but for some reason, I can't post my reply when I do. It has to be something going on here at work, as I have that issue at times with posting code as well.
March 4, 2009 at 12:35 am
Chris Morris (3/3/2009)
karthikeyan (3/3/2009)
His method is working fine. I have tested it.Actually my requiremrnt is little bit different what he has given.
Can you demonstrate please, Karthik? I'd be very keen to see your solution, with a description of how the revised requirements differ from the original. New ideas are welcome!
Sure Chris.
Actully, Lynn, it's much easier.
If based on my code.
DECLARE @Date datetime
SET @Date = GETDATE()
SELECT @Date as [Date], WeekStart, WeekStart + 4 as WeekEnd
FROM (
select DATEADD(wk, DATEDIFF(wk, 0, @Date - 2), 0) as WeekStart
) DT
It is working fine. There is no doubt about it. But the output is
2009-03-04 02:30:31.0832009-03-02 00:00:00.0002009-03-06 00:00:00.000
But my requirement is slightly different .It should display the given date as the weekend.
i.e
2009-03-04 02:30:31.0832009-03-02 00:00:00.0002009-03-04 02:30:31.083
Thats what i have mentioned.
karthik
March 4, 2009 at 12:41 am
Lynn Pettis (3/3/2009)
Here is another way to accomplish this, based again an Sergiy's original code:
declare @pDate datetime;
set @pDate = getdate();
select
dateadd(wk, datediff(wk, 0, @pDate - 1), 0) as BeginDate,
case when dateadd(wk, datediff(wk, 0, @pDate - 1), 0) + 4 > dateadd(dd, datediff(dd, 0, @pDate), 0)
then dateadd(dd, datediff(dd, 0, @pDate), 0)
else dateadd(wk, datediff(wk, 0, @pDate - 1), 0) + 4
end as EndDate;
Lynn,
This method works fine.
karthik
March 4, 2009 at 12:42 am
karthikeyan (3/4/2009)
Chris Morris (3/3/2009)
karthikeyan (3/3/2009)
His method is working fine. I have tested it.Actually my requiremrnt is little bit different what he has given.
Can you demonstrate please, Karthik? I'd be very keen to see your solution, with a description of how the revised requirements differ from the original. New ideas are welcome!
Sure Chris.
Actully, Lynn, it's much easier.
If based on my code.
DECLARE @Date datetime
SET @Date = GETDATE()
SELECT @Date as [Date], WeekStart, WeekStart + 4 as WeekEnd
FROM (
select DATEADD(wk, DATEDIFF(wk, 0, @Date - 2), 0) as WeekStart
) DT
It is working fine. There is no doubt about it. But the output is
2009-03-04 02:30:31.0832009-03-02 00:00:00.0002009-03-06 00:00:00.000
But my requirement is slightly different .It should display the given date as the weekend.
i.e
2009-03-04 02:30:31.0832009-03-02 00:00:00.0002009-03-04 02:30:31.083
Thats what i have mentioned.
Yes, I am quite aware of your requirements as i have pointed them out myself several times. Have you tried the code I provided in this post?
March 4, 2009 at 12:44 am
Chris Morris (3/3/2009)
karthikeyan (3/3/2009)
Hi All,Thanks for all your inputs!:)
I found the TSQL 101 solution.
Declare @InputDate Datetime
select @InputDate = '02/mar/2009'
select case when upper(datename(dw,@InputDate)) = 'MONDAY' then dateadd(dd,-7,@InputDate)
when upper(datename(dw,@InputDate)) = 'TUESDAY' then dateadd(dd,-1,@InputDate)
when upper(datename(dw,@InputDate)) = 'WEDNESDAY' then dateadd(dd,-2,@InputDate)
when upper(datename(dw,@InputDate)) = 'THURSDAY' then dateadd(dd,-3,@InputDate)
when upper(datename(dw,@InputDate)) = 'FRIDAY' then dateadd(dd,-4,@InputDate)
when upper(datename(dw,@InputDate)) = 'SATURDAY' then dateadd(dd,-5,@InputDate)
when upper(datename(dw,@InputDate)) = 'SUNDAY' then dateadd(dd,-6,@InputDate)
END as StartDate,
case when upper(datename(dw,@InputDate)) = 'MONDAY' then dateadd(dd,-3,@InputDate)
when upper(datename(dw,@InputDate)) in('TUESDAY','WEDNESDAY','THURSDAY','FRIDAY') then @InputDate
when upper(datename(dw,@InputDate)) = 'SATURDAY' then dateadd(dd,-1,@InputDate)
when upper(datename(dw,@InputDate)) = 'SUNDAY' then dateadd(dd,-2,@InputDate)
END as EndDate
It worked well for all the cases.
Karthik, did you bother to try Sergiy's solution? It's well worth spending a few minutes checking it out:
DROP TABLE #DatesCREATE TABLE #Dates (aDate DATETIME)
INSERT INTO #Dates
SELECT TOP 100 GETDATE()+50-number
FROM Numbers
SELECT aDate, WeekStart, WeekEnd, datediff(dd, aDate, WeekStart),
DATENAME(dw,aDate), DATENAME(dw,WeekStart), DATENAME(dw,WeekEnd)
FROM (
SELECT aDate,
DATEADD(wk, DATEDIFF(wk, 0, aDate - 2), 0) AS WeekStart,
DATEADD(wk, DATEDIFF(wk, 0, aDate - 2), 0) + 4 AS WeekEnd
FROM #Dates ) d
Chris,
Actually i have tested his method. It works fine. But as i mentioned in my previous thread, its slightly differ from the expected output.
karthik
Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply