number range in the IN statement

Can anyone tell me what is wrong with this?
Month({Command.Date_Received}) IN [({?ParamMValue})]
?ParamMValue could have... (2,3,4,5) or (2) or (1,2,3,4,5,6,7,8,9)
Why is it telling me that I need a number range in the IN statement?
What should I do instead?Make sure your parameter is a number data type and not a date or string.
GJ|||your final formula should be in format like this..

month({field}) in [ parameter ] no need to you [(2,3,5)]..


Hope this helps yousql

Number of Periods in a date range

Guys
Date brain killer - I have a startdate and enddate and need, for a given period length in months, to identify the number
of periods betwen the startdate and enddate
so for example

DECLARE @.Periods TABLE
(
StartDate datetime,
Enddate datetime,
NumberOfMonthsInPeriod INT ,
TotPeriods INT
)

-- Initial data
insert @.Periods
(
StartDate ,
Enddate ,
NumberOfMonthsInPeriod ,
TotPeriods -- for this illustration, initialised to 0 but need to be UPDATEd as per detail below
)

select
'30-Sep-2005',
'10-Apr-2009',
1 -- 1 month period
0
union all
select
'30-Sep-2005',
'10-Apr-2009',
3 -- 3 month period
0
union all
select
'30-Sep-2005',
'10-Apr-2009',
6 -- 6 month period
0

The following rules regarding periods apply
Each 1 (NumberOfMonthsInPeriod) month period is as would be expected ie
01-Jan - 31 Jan
01-Feb - 28-Feb (ie 1st March - 1 day which would deal with leap years - dateadd(...)
and so on to December

Each 3 (NumberOfMonthsInPeriod) month period is one of the following 'bands' per year
01-Jan - 31-Mar
01-Apr - 30-Jun
01-Jul - 30-Sep
01-Oct - 31-Dec

Each 6 (NumberOfMonthsInPeriod) month period is one of the following
01-Jan - 30-Jun
01-Jul - 31-Dec

I need to derive TotPeriods as follows:

For the row where NumberOfMonthsInPeriod = 1, the first period ie the one the start date falls within is 01-Sep-2005 to 30-Sep-2005, second is 01-Oct-2005 - 31-Oct-2005 and so on until
last period ie the one the end datye falls within is 01-Apr-2009 - 30-Apr-2009, a TotPeriods value of 44

For the row where NumberOfMonthsInPeriod = 3, first period is 01-Jul-2005 to 30-Sep-2005, second is 01-Oct-2005 - 31-Dec-2005 and so on until
last period is 01-Apr-2009 - 30-Jun-2009, a TotPeriods value of 16

For the row where NumberOfMonthsInPeriod = 6, first period is 01-Jul-2005 to 31-Dec-2005, second is 01-Jan-2006 - 30-Jun-2006 and so on until
last period is 01-Jan-2009 - 30-Jun-2009, a TotPeriods value of 8

Hope this is clear and thanks in advanceYou started off good, but I still don't get it. What' sthe expect results, and what defines a period again?|||Brett
Expected results is the TotPeriods value - I've come up with a solution (below) but wonder if there isn't a more elegant or less verbose way of doing it

DECLARE @.Periods TABLE
(
StartDate datetime,
Enddate datetime,
NumberOfMonthsInPeriod INT ,
TotPeriods INT,
periodstartdate datetime ,
periodenddate datetime
)

-- Initial data
insert @.Periods
(
StartDate ,
Enddate ,
NumberOfMonthsInPeriod ,
TotPeriods
)

select
'02-Jul-2004',
'10-Dec-2005',
1 ,-- 1 month period
0
union all
select
'02-Jul-2004',
'10-Dec-2005',
3 ,-- 3 month period
0
union all
select
'02-Jul-2004',
'10-Dec-2005',
6 ,-- 6 month period
0

update @.Periods
set periodstartdate =
case when NumberOfMonthsInPeriod = 1 then cast('01' + '-' +
cast (datepart(mm,startdate) as varchar) + '-' +
cast (datepart(yyyy,startdate) as varchar)
as datetime)

when NumberOfMonthsInPeriod = 3 then cast(
'01' + '-' +
case when datepart(mm,startdate) in (1,2,3) then 'Jan'
when datepart(mm,startdate) in (4,5,6) then 'Apr'
when datepart(mm,startdate) in (7,8,9) then 'Jul'
when datepart(mm,startdate) in (10,11,12) then 'Oct'
end
+ '-' +
cast (datepart(yyyy,startdate) as varchar)
as datetime)

when NumberOfMonthsInPeriod = 6 then cast(
'01' + '-' +
case when datepart(mm,startdate) in (1,2,3,4,5,6) then 'Jan'
when datepart(mm,startdate) in (7,8,9,10,11,12) then 'Jul'
end
+ '-' +
cast (datepart(yyyy,startdate) as varchar)
as datetime)
end ,
--convert enddate to first of whatever instead of 16th, add a month and knock off a day
periodenddate =
case when NumberOfMonthsInPeriod = 1 then
dateadd(dd , -1 ,
dateadd (mm , 1 ,
cast(
'01' + '-' + cast(datepart(mm,enddate) as varchar) + '-' +
cast(datepart (yyyy, enddate) as varchar)
as varchar)
)
)

when NumberOfMonthsInPeriod = 3 then
dateadd(dd , -1 ,
dateadd (mm , 1 ,
cast(

'01' + '-' +
case when datepart(mm,enddate) in (1,2,3) then 'Mar'
when datepart(mm,enddate) in (4,5,6) then 'Jun'
when datepart(mm,enddate) in (7,8,9) then 'Sep'
when datepart(mm,enddate) in (10,11,12) then 'Dec'
end
+ '-' +

cast(datepart (yyyy, enddate) as varchar)
as varchar)
)
)

when NumberOfMonthsInPeriod = 6 then
dateadd(dd , -1 ,
dateadd (mm , 1 ,
cast(

'01' + '-' +
case when datepart(mm,enddate) in (1,2,3,4,5,6) then 'Jun'
when datepart(mm,enddate) in (7,8,9,10,11,12) then 'Dec'
end
+ '-' +
cast(datepart (yyyy, enddate) as varchar)
as varchar)
)
)

end

update @.Periods
set TotPeriods =
datediff(mm , periodstartdate , dateadd(dd,1,periodenddate) ) / NumberOfMonthsInPeriod

select * from @.Periods|||PS Brett - sorry to answer your question, a period is the length of time based on the NumberOfMonthsInPeriod value so if the value is 1 it's a 1 month period, if 3 it's a 3 month period etc

Thx
SWsql

Number of Days Of Week

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?
JimHere 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 <= @.EndDate )
BEGIN
INSERT INTO @.Calendar
SELECT @.StartDate
SET @.StartDate = dateadd( day, 1, @.StartDate )
END
RETURN
( isnull((
SELECT DayCount
FROM ( SELECT
DayCount = count(1),
DayName = datename( dw, CalendarDate )
FROM @.Calendar
GROUP BY datename( dw, CalendarDate )
) dt
WHERE upper( left( DayName, 2 )) = @.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.goog
legroups.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
>

Number of Days Of Week

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?
Jimdeclare @.date1 datetime set @.date1 = '20061001'
declare @.date2 datetime set @.date2 = '20061029'
declare @.anyMonday datetime set @.anyMonday = '19000102'
select
datediff(day,@.anyMonday,@.date2)/7 -
datediff(day,@.anyMonday,@.date1)/7 as MondayBetween
<Jim.Mueksch@.wellsfargo.com> wrote in message
news:1161982195.940615.282480@.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
>sql

Number of Days Of Week

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?
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
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--