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 : 1891 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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,

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	;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

;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

0	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

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

0	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

Latest Posts