Select first full week of the month

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 2005 Forums

Transact-SQL (2005)

Select first full week of the month

Author

Topic

jhaney
Starting Member

33 Posts

Posted - 2011-03-08 : 11:09:12

I have a stat report that pulls appointments by date.

select *
from appointments
where date between @startdate and @enddate

I need to find a way to run to run this report for the first full week of the month (M-F) without having to enter the dates?

Any ideas?

Thanks

Joshua

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-08 : 12:07:36

This is from Visakh, you should be able to adapt it to your needs

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME ='GetNthWeekday' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE='FUNCTION')
DROP FUNCTION dbo.GetNthWeekday
GO

CREATE FUNCTION dbo.GetNthWeekday
(
@date datetime,--Any date of month under consideration
@N int,-- Nth occurance
@WeekDay int--Which week day to look for (Sunday-1,Monday-2,..,Saturday-7)
--returns NULL when calculated date is out of scope of month
)
RETURNS datetime
AS
BEGIN
DECLARE @StartDate datetime,@ResultDate datetime

SET @StartDate=DATEADD(mm,DATEDIFF(mm,0,@date),0)

SELECT @ResultDate=DATEADD(
dd,
CASE WHEN DATEPART(DW,DATEADD(wk,@N-1,@StartDate)) > @WeekDay
THEN (7 - (DATEPART(DW,DATEADD(wk,@N-1,@StartDate))- @WeekDay))
ELSE (@WeekDay - DATEPART(DW,DATEADD(wk,@N-1,@StartDate)))
END,
DATEADD(wk,@N-1,@StartDate)
)
SET @ResultDate= CASE WHEN MONTH(@ResultDate) <> MONTH(@date)
THEN NULL
ELSE @ResultDate
END
RETURN @ResultDate
END

Jim

Everyday I learn something that somebody else already knew

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-03-08 : 12:12:46

Its a bit long winded but it works.

DECLARE @mStart DATETIME
SET @mStart = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)
DECLARE @mEnd DATETIME
SET @mEnd = DATEADD(MONTH,1,@mStart)

CREATE TABLE #Dates (tDate DATETIME)
WHILE @mStart < @mEnd
BEGIN
INSERT INTO #Dates (tDate)
SELECT @mStart
SET @mStart = DATEADD(DAY,1,@mStart)
END

--SELECT tDate,DATEPART(WEEKDAY,tDate) as DayNo
--FROM #Dates

DECLARE @startdate DATETIME
DECLARE @enddate DATETIME

SELECT @startdate = MIN(tDate) FROM #Dates WHERE DATEPART(WEEKDAY,tDate) = 2
SELECT @enddate = MIN(tDate) FROM #Dates WHERE DATEPART(WEEKDAY,tDate) = 6 AND tDate > @startdate

SELECT @startdate,@enddate

DROP TABLE #Dates

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-03-08 : 12:15:35

nb: Prob should have mentioned but I'm assuming that you have your SQL Server set up so that your dayofweek starts with Sunday. If its not then you should change that part of the query.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

jhaney
Starting Member

33 Posts

Posted - 2011-03-08 : 13:44:55

Thanks Guys I'll give it a shot.

jhaney
Starting Member

33 Posts

Posted - 2011-03-08 : 14:02:00

Trying now to get those dates into my select statement below

select *
from apptointments
where company = 'mycompany'
and adate between @startdate and @enddate

Thanks in advance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources