Click here to Skip to main content
15,117,095 members
Please Sign up or sign in to vote.
1.19/5 (4 votes)
See more:
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 



Thanks,
Posted
Updated 7-Aug-13 3:08am
v2

1 solution

Try this:
SQL
DECLARE @tmp TABLE([DAYName] VARCHAR(30), hours INT, WeekBeginDt DATETIME, WeekEndDt DATETIME)

INSERT INTO @tmp ([DAYName], hours, WeekBeginDt, WeekEndDt)
SELECT 'MonHrs', 8, '2010-01-18', '2010-01-24'
UNION ALL SELECT 'TueHrs', 8, '2010-01-18', '2010-01-24'
UNION ALL SELECT 'WedHrs', 8, '2010-01-18', '2010-01-24' 
UNION ALL SELECT 'ThuHrs', 8, '2010-01-18', '2010-01-24' 
UNION ALL SELECT 'FriHrs', 8, '2010-01-18', '2010-01-24' 
UNION ALL SELECT 'SatHrs', 8, '2010-01-18', '2010-01-24'

;WITH MyDates AS
(
        --initial values
	SELECT [DAYName], hours, WeekBeginDt AS CurrDate, WeekBeginDt, WeekEndDt
	FROM @tmp
	UNION ALL
        --recursive part
	SELECT [DAYName], hours, DATEADD(dd,1, CurrDate) AS CurrDate, WeekBeginDt, WeekEndDt
	FROM MyDates
	WHERE DATEADD(dd,1, CurrDate)<=WeekEndDt
)
SELECT *
FROM MyDates
WHERE LEFT(DATENAME(dw, CurrDate),3) + 'Hrs' = [DAYName]
ORDER BY CurrDate


In above example i used Common Table Expression (CTE) .
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900