sql query to display date using day name, week start date & week end date

Posted by Sampath1750 under Sql Server on 8/7/2013 | Points: 10 | Views : 1190 | Status : [Member] | Replies : 3
Hi,

sql query to display date using day name, week start date & week end date.

my data looks like below

DAYName hours WeekBeginDt WeekEndDt
MonHrs 8 2010-01-18 2010-01-24
TueHrs 8 2010-01-18 2010-01-24
WedHrs 8 2010-01-18 2010-01-24
ThuHrs 8 2010-01-18 2010-01-24
FriHrs 8 2010-01-18 2010-01-24
SatHrs 8 2010-01-18 2010-01-24

i want data like below

DAYName hours date WeekBeginDt WeekEndDt
MonHrs 8 2010-01-18 2010-01-18 2010-01-24
TueHrs 8 2010-01-19 2010-01-18 2010-01-24
WedHrs 8 2010-01-20 2010-01-18 2010-01-24
ThuHrs 8 2010-01-20 2010-01-18 2010-01-24
FriHrs 8 2010-01-21 2010-01-18 2010-01-24
SatHrs 8 2010-01-22 2010-01-18 2010-01-24

i want data like below


Thanks,




Responses

Posted by: Bandi on: 8/7/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
;WITH CTE(WeekDate, WeekEnd, DayNames) AS 

(SELECT DISTINCT WeekBeginDt, WeekEndDt, LEFT(DATENAME( WeekDay, WeekBeginDt), 3)
FROM TableName
UNION ALL
SELECT DATEADD( DD, 1, WeekDate), WeekEnd, LEFT(DATENAME(WeekDay , DATEADD( DD, 1, WeekDate)), 3)
FROM CTE
WHERE DATEADD( DD, 1, WeekDate) < WeekEnd
)
SELECT DayName, hours, WeekDate, WeekBeginDt, WeekEndDt
FROM TableName t
JOIN CTE c ON c.DayNames = LEFT(t.DayName,3)
WHERE c.WeekDate BETWEEN t.WeekDate AND t.WeekBeginDt


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sampath1750, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/12/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi Sampath,
Very simple solution for the above requirement is as follows:
SELECT	DayName,

WeekBeginDt,
WeekEndDt,
CASE DayName
WHEN 'MonHrs' THEN DATEADD(DAY, 0, WeekBeginDt)
WHEN 'TueHrs' THEN DATEADD(DAY, 1, WeekBeginDt)
WHEN 'WedHrs' THEN DATEADD(DAY, 2, WeekBeginDt)
WHEN 'ThuHrs' THEN DATEADD(DAY, 3, WeekBeginDt)
WHEN 'FriHrs' THEN DATEADD(DAY, 4, WeekBeginDt)
WHEN 'SatHrs' THEN DATEADD(DAY, 5, WeekBeginDt)
ELSE NULL
END AS WantedDate
FROM [b]TableName[/b]



Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sampath1750, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/28/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
mark it as answer if the above post gives you solution

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sampath1750, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response