"Terri" wrote:

> I'm looking to set the variable @BusinessDayFactor based on a select against
> a calendar table. The select will have one parameter @ReportDate. The logic
> is as follows:
>
> Given @ReportDate count the subsequent, contiguous days which are not
> business days if those non-business days are in the same month and year as
> @ReportDate.
>
> Given SET @ReportDate = '20060101' the result should be 1 because '20060102'
> is not a business day and '20060103' is a business day.
> Given SET @ReportDate = '20060106' the result should be 2 because '20060107'
> and '20060108' are not a business days and '20060109' is a business day.
> Given SET @ReportDate = '20060113' the result should be 3 because '20060114'
> and '20060115' and '20060116' are not a business days and '20060117' is a
> business day.
> Given SET @ReportDate = '20060117' the result should be 0 because '20060118'
> is a business day.
>
> My WHERE statement needs some help. Thanks
>
>
> CREATE TABLE [dbo].[#Calendar] (
>  [CalDate] [smalldatetime] NOT NULL ,
>  [BusinessDay] [char] (1)  NOT NULL
> ) ON [PRIMARY]
> GO
>
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060101','N')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060102','N')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060103','Y')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060104','Y')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060105','Y')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060106','Y')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060107','N')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060108','N')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060109','Y')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060110','Y')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060111','Y')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060112','Y')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060113','Y')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060114','N')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060115','N')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060116','N')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060117','Y')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060118','Y')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060119','Y')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060120','Y')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060121','N')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060122','N')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060123','Y')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060124','Y')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060125','Y')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060126','Y')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060127','Y')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060128','N')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060129','N')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060130','Y')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060131','Y')
>
> DECLARE @ReportDate datetime
> DECLARE @BusinessDayFactor int
>
> SET @ReportDate = '20060101'
>
> SELECT
> CalDate,
> BusinessDay,
> DATENAME(dw,CalDate)
> FROM        #Calendar
> WHERE     (CalDate > @ReportDate)
> AND MONTH(CalDate) = MONTH(@ReportDate)
> AND YEAR(CalDate) = YEAR(@ReportDate)
>
> SET @BusinessDayFactor= @@rowcount
> PRINT @BusinessDayFactor
>
> DROP TABLE #Calendar
>
>
>

> Try the following:
>
> SELECT count(*)
> FROM #Calendar
> WHERE CalDate > @ReportDate and CalDate <
>     (
>     SELECT TOP 1 CalDate
>     FROM #Calendar
>     WHERE CalDate > @ReportDate
>         and BusinessDay = 'Y'
>     ORDER BY CalDate
>     )
>
> Q
>
> "Terri" wrote:
>
> > I'm looking to set the variable @BusinessDayFactor based on a select against
> > a calendar table. The select will have one parameter @ReportDate. The logic
> > is as follows:
> >
> > Given @ReportDate count the subsequent, contiguous days which are not
> > business days if those non-business days are in the same month and year as
> > @ReportDate.
> >
> > Given SET @ReportDate = '20060101' the result should be 1 because '20060102'
> > is not a business day and '20060103' is a business day.
> > Given SET @ReportDate = '20060106' the result should be 2 because '20060107'
> > and '20060108' are not a business days and '20060109' is a business day.
> > Given SET @ReportDate = '20060113' the result should be 3 because '20060114'
> > and '20060115' and '20060116' are not a business days and '20060117' is a
> > business day.
> > Given SET @ReportDate = '20060117' the result should be 0 because '20060118'
> > is a business day.
> >
> > My WHERE statement needs some help. Thanks
> >
> >
> > CREATE TABLE [dbo].[#Calendar] (
> >  [CalDate] [smalldatetime] NOT NULL ,
> >  [BusinessDay] [char] (1)  NOT NULL
> > ) ON [PRIMARY]
> > GO
> >
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060101','N')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060102','N')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060103','Y')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060104','Y')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060105','Y')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060106','Y')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060107','N')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060108','N')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060109','Y')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060110','Y')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060111','Y')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060112','Y')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060113','Y')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060114','N')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060115','N')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060116','N')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060117','Y')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060118','Y')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060119','Y')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060120','Y')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060121','N')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060122','N')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060123','Y')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060124','Y')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060125','Y')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060126','Y')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060127','Y')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060128','N')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060129','N')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060130','Y')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060131','Y')
> >
> > DECLARE @ReportDate datetime
> > DECLARE @BusinessDayFactor int
> >
> > SET @ReportDate = '20060101'
> >
> > SELECT
> > CalDate,
> > BusinessDay,
> > DATENAME(dw,CalDate)
> > FROM        #Calendar
> > WHERE     (CalDate > @ReportDate)
> > AND MONTH(CalDate) = MONTH(@ReportDate)
> > AND YEAR(CalDate) = YEAR(@ReportDate)
> >
> > SET @BusinessDayFactor= @@rowcount
> > PRINT @BusinessDayFactor
> >
> > DROP TABLE #Calendar
> >
> >
> >