Anyone up for a SQL challenge? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Anyone up for a SQL challenge?

Author

Topic

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-08-15 : 08:06:46

I'm doing a report in SQL and one part is busting my balls so I thought I'd do the decent thing and offer the challenge out to you clever bunch.

I need to list the days of a given month on a Y axis (the up and down bit to the left), but there's a caveat - I only want to show FULL weeks (Monday to Sunday). So where the month starts part way through the week, I need it to show the last few days of the previous month for the Monday, Tuesday etc and then 1st of the selected month from whatever day it starts. The same should be done for the end of the month, i.e. displaying the start of the next month if its in the same week although this is less important.

You can have parameters to work from, probably a start and end date which should be the first day and the last day in the selected month.

A full week is Monday to Sunday.

August isn't a good example of a month to do this as the first was a Monday, but taking June as an example, the desired output would have been:

Mon 30/05/2011
Tue 31/05/2011
Wed 01/06/2011
Thu 02/06/2011
Fri 03/06/2011
Sat 04/06/2011
Sun 05/06/2011
Mon 06/06/2011
Tue 07/06/2011
Wed 08/06/2011
Thu 09/06/2011
Fri 10/06/2011
Sat 11/06/2011
Sun 12/06/2011
Mon 13/06/2011
Tue 14/06/2011
Wed 15/06/2011
Thu 16/06/2011
Fri 17/06/2011
Sat 18/06/2011
Sun 19/06/2011
Mon 20/06/2011
Tue 21/06/2011
Wed 22/06/2011
Thu 23/06/2011
Fri 24/06/2011
Sat 25/06/2011
Sun 26/06/2011
Mon 27/06/2011
Tue 28/06/2011
Wed 29/06/2011
Thu 30/06/2011
Fri 01/07/2011
Sat 02/07/2011
Sun 03/07/2011

Here's your parameter declarations to get you started:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2011-06-01'
SET @EndDate = '2011-06-30'

I'd be chuffed to bits if someone could help out with this.

Many thanks.

Nick.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-15 : 08:23:03

for calculating the revised start date:

SELECT DATEADD(day,-(DATEDIFF(day,'19000101',@StartDate))%7,@startDate) AS NewStartDate;

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-08-15 : 12:34:06

Works a treat that, thanks a lot. Help yourself to a biscuit.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources