Calendar table SELECT

I want to use a stored procedure and a calendar table to determine:
1) Is it the first business day of the month?; and
2) What was the last business day of the previous month?
CREATE PROCEDURE procTest
AS
--psuedo code
Is today the first business day of the month? If yes then determine the last
business day of the previous month and do something with that date; If no
end the procedure.
Thanks to anyone who could help.
CREATE TABLE [dbo].[Calendar] (
[CalDate] [smalldatetime] NOT NULL ,
[BusinessDay] [char] (1) NOT NULL
) ON [PRIMARY]
GO
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051201','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051202','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051203','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051204','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051205','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051206','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051207','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051208','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051209','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051210','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051211','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051212','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051213','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051214','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051215','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051216','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051217','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051218','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051219','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051220','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051221','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051222','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051223','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051224','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051225','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051226','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051227','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051228','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051229','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051230','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051231','N')
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')Try:
declare @.d datetime
set @.d = convert(char(8), getdate(), 112)
if exists (
select *
from dbo.calendar as c1
where CalDate = @.d
and BusinessDay = 'Y'
and not exists (
select *
from dbo.calendar as c2
where BusinessDay = 'Y'
and c2.CalDate < c1.CalDate
and c2.CalDate >= convert(char(6), @.d, 112) + '01'
)
)
select max(CalDate)
from dbo.calendar
where BusinessDay = 'Y'
and CalDate >= convert(char(6), dateadd(month, -1, @.d), 112) + '01'
and CalDate < convert(char(6), @.d, 112) + '01'
go
AMB
"Terri" wrote:

> I want to use a stored procedure and a calendar table to determine:
> 1) Is it the first business day of the month?; and
> 2) What was the last business day of the previous month?
>
> CREATE PROCEDURE procTest
> AS
> --psuedo code
> Is today the first business day of the month? If yes then determine the la
st
> business day of the previous month and do something with that date; If no
> end the procedure.
> Thanks to anyone who could help.
> CREATE TABLE [dbo].[Calendar] (
> [CalDate] [smalldatetime] NOT NULL ,
> [BusinessDay] [char] (1) NOT NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051201','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051202','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051203','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051204','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051205','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051206','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051207','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051208','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051209','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051210','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051211','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051212','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051213','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051214','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051215','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051216','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051217','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051218','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051219','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051220','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051221','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051222','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051223','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051224','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051225','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051226','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051227','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051228','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051229','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051230','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051231','N')
> 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')
>
>|||Thanks!
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:40481F08-FA9D-497A-AE1A-761DDF7FC229@.microsoft.com...
> Try:
> declare @.d datetime
> set @.d = convert(char(8), getdate(), 112)
> if exists (
> select *
> from dbo.calendar as c1
> where CalDate = @.d
> and BusinessDay = 'Y'
> and not exists (
> select *
> from dbo.calendar as c2
> where BusinessDay = 'Y'
> and c2.CalDate < c1.CalDate
> and c2.CalDate >= convert(char(6), @.d, 112) + '01'
> )
> )
> select max(CalDate)
> from dbo.calendar
> where BusinessDay = 'Y'
> and CalDate >= convert(char(6), dateadd(month, -1, @.d), 112) + '01'
> and CalDate < convert(char(6), @.d, 112) + '01'
> go
>|||Or if you fancy CLR, your could do something like below (and no tables):
declare @.today TDate
set @.today = TDate::Today
declare @.fbd TDate
declare @.lbdPriorMth TDate
set @.fbd = TDate::GetFirstBusinessDayOfMonth(@.today
)
set @.lbdPriorMth = TDate::GetLastBusinessDayOfMonth(@.today.AddMonths(-1))
select @.fbd.ToString() as FirstBizDayThisMonth, @.lbdPriorMth.ToString() as
LastBizDayPriorMonth
William Stacey [MVP]
"Terri" <terri@.cybernets.com> wrote in message
news:dqjbqv$m62$1@.reader2.nmix.net...
|I want to use a stored procedure and a calendar table to determine:
|
| 1) Is it the first business day of the month?; and
|
| 2) What was the last business day of the previous month?
|
|
| CREATE PROCEDURE procTest
| AS
| --psuedo code
|
| Is today the first business day of the month? If yes then determine the
last
| business day of the previous month and do something with that date; If no
| end the procedure.
|
| Thanks to anyone who could help.
|
| CREATE TABLE [dbo].[Calendar] (
| [CalDate] [smalldatetime] NOT NULL ,
| [BusinessDay] [char] (1) NOT NULL
| ) ON [PRIMARY]
| GO
|
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051201','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051202','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051203','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051204','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051205','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051206','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051207','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051208','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051209','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051210','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051211','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051212','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051213','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051214','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051215','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051216','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051217','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051218','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051219','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051220','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051221','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051222','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051223','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051224','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051225','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051226','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051227','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051228','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051229','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051230','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051231','N')
| 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')
|
|
||||CREATE TABLE Calendar
(cal_date DATETIME NOT NULL PRIMARY KEY
date_type INTEGER NOT NULL
CHECK (date_type > 0));
Instead of a Boolean flag, why not invent a numeric code that tells you
the type of date (work day, holiday, reporting, etc.)? This is very
flexible and makes the code extremely easy to write. Think in tersm of
data and not complex code.|||What if a day is both a holiday and a workday? (e.g. National holiday
but the company is open for business.)
--CELKO-- said the following on 1/17/2006 3:45 PM:
> CREATE TABLE Calendar
> (cal_date DATETIME NOT NULL PRIMARY KEY
> date_type INTEGER NOT NULL
> CHECK (date_type > 0));
> Instead of a Boolean flag, why not invent a numeric code that tells you
> the type of date (work day, holiday, reporting, etc.)? This is very
> flexible and makes the code extremely easy to write. Think in tersm of
> data and not complex code.
>|||<kidding>
I think he wants you to use a bitmask in these cases. Like 1 = wend, 2 =
holiday, 4 = reporting
</kidding>
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Steve Beach" <ohbillie--@.GGGGGGGGGGGGGGGGmail.com> wrote in message
news:OlgNyU8GGHA.3532@.TK2MSFTNGP14.phx.gbl...
> What if a day is both a holiday and a workday? (e.g. National holiday but
> the company is open for business.)
> --CELKO-- said the following on 1/17/2006 3:45 PM: