I need to find the number of Mondays within a given date range.
Example: How many Mondays between 01/01/2006 and 06/30/2006.
Any suggestions on how to do this?
JimThis is a multi-part message in MIME format.
--=_NextPart_000_0573_01C6F9CF.B01CC5E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Here is a function that will do this for you.
CREATE FUNCTION dbo.fnDaysBetween
( @.StartDate datetime,
@.EndDate datetime,
@.ConcernDay char(2) -- MO, TU, WE, etc.
)
RETURNS int
AS
BEGIN
DECLARE @.Calendar table
( CalendarDate smalldatetime )
WHILE ( @.StartDate <=3D @.EndDate )
BEGIN
INSERT INTO @.Calendar
SELECT @.StartDate
SET @.StartDate =3D dateadd( day, 1, @.StartDate )
END
RETURN
( isnull(( SELECT DayCount
FROM ( SELECT DayCount =3D count(1), DayName =3D datename( dw, CalendarDate )
FROM @.Calendar
GROUP BY datename( dw, CalendarDate )
) dt
WHERE upper( left( DayName, 2 )) =3D @.ConcernDay
), 0 )
)
END
GO
SELECT dbo.fnDaysBetween( '10-01-2006', '11-30-2006', 'mo' )
SELECT dbo.fnDaysBetween( '10-01-2006', '11-30-2006', 'fr' )
SELECT dbo.fnDaysBetween( '10-06-2006', '10-10-2006', 'fr' )
SELECT dbo.fnDaysBetween( '10-06-2006', '10-10-2006', 'we' )
DROP FUNCTION dbo.fnDaysBetween
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill without getting a little closer to =the top yourself.
- H. Norman Schwarzkopf
<Jim.Mueksch@.wellsfargo.com> wrote in message =news:1161982262.413737.288480@.k70g2000cwa.googlegroups.com...
> Hi,
> > > I need to find the number of Mondays within a given date range.
> > Example: How many Mondays between 01/01/2006 and 06/30/2006.
> > Any suggestions on how to do this?
> > > Jim
>
--=_NextPart_000_0573_01C6F9CF.B01CC5E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Here is a function that will do this =for you.
CREATE FUNCTION dbo.fnDaysBetween ( @.StartDate datetime, @.EndDate datetime, @.ConcernDay =char(2) -- MO, TU, WE, etc. ) RETURNS intAS BEGIN
=DECLARE @.Calendar table ( CalendarDate smalldatetime )
=WHILE ( @.StartDate <=3D @.EndDate =) BEGIN &nbs=p; INSERT INTO @.Calendar = SELECT @.StartDate =; SET @.StartDate =3D dateadd( day, 1, @.StartDate ) END
RETURN ( =isnull(( &nb=sp; SELECT DayCount &=nbsp; FROM ( SELECT &nb=sp; &nbs=p; DayCount =3D count(1), &nb=sp; &nbs=p; DayName =3D datename( dw, CalendarDate ) &n=bsp; FROM @.Calendar = GROUP BY datename( dw, CalendarDate ) &n=bsp; ) dt &=nbsp; WHERE upper( left( DayName, 2 )) =3D @.ConcernDay &nbs=p; ), 0 ) =) ENDGO
SELECT dbo.fnDaysBetween( ='10-01-2006', '11-30-2006', 'mo' )SELECT dbo.fnDaysBetween( '10-01-2006', ='11-30-2006', 'fr' )SELECT dbo.fnDaysBetween( '10-06-2006', '10-10-2006', 'fr' =)SELECT dbo.fnDaysBetween( '10-06-2006', '10-10-2006', 'we' )
DROP FUNCTION dbo.fnDaysBetween
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill =without getting a little closer to the top yourself.- H. Norman Schwarzkopf
--=_NextPart_000_0573_01C6F9CF.B01CC5E0--
No comments:
Post a Comment