-- Drop stored procedure if it already exists
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'DD_SY40100_getEarliestOpenPeriod'
)
DROP PROCEDURE dbo.DD_SY40100_getEarliestOpenPeriod
GO
CREATE PROCEDURE dbo.DD_SY40100_getEarliestOpenPeriod
-- Test using these stubs
-- DD_SY40100_getEarliestOpenPeriod 3,'2/5/2011'
-- DD_SY40100_getEarliestOpenPeriod 3,'2/12/2010'
/*
The objective of this stored procedure is to find the first open period greater to or equal to the date submitted.
If the period in question is open, the same date is returned
If the period is closed, the next available date is returned
*/
@Series smallint,
@TargetDate date
/*
Field values:
1 = All
2 = Financial
3 = Sales
4 = Purchasing
5 = Inventory
6 = Payroll - USA
7 = Project
*/
AS
declare @ValidDate date
select @ValidDate = @TargetDate
from sy40100
where series = @Series
and PERIODID <> 0
and @TargetDate between PERIODDT and PERDENDT
and closed = 0
select isnull(@ValidDate,min(PERIODDT)) as PERIODDT
from sy40100 s
where SERIES = @Series
and closed = 0
and PERIODID <> 0
and PERIODDT >= @TargetDate
order by 1
GO
grant exec on DD_SY40100_getEarliestOpenPeriod to public
/*
select distinct perioddt from sy40100 where series = 3 and closed=0 and periodid <> 0 order by perioddt
*/