/*
This table contains all activities for a person.
*/
CREATE TABLE #PersonActivity (
PersonID int ,
ActivityDate datetime ,
CONSTRAINT PK_Anniv_Person PRIMARY KEY CLUSTERED ( PersonID , ActivityDate )
);
INSERT INTO #PersonActivity VALUES( 1 , '12/01/99' );
INSERT INTO #PersonActivity VALUES( 1 , '01/15/00' );
INSERT INTO #PersonActivity VALUES( 1 , '03/31/00' );
INSERT INTO #PersonActivity VALUES( 1 , '05/10/00' );
INSERT INTO #PersonActivity VALUES( 1 , '08/06/00' );
INSERT INTO #PersonActivity VALUES( 1 , '10/05/00' );
SELECT * FROM #PersonActivity ORDER BY ActivityDate;
/*
PersonID ActivityDate
----------- ------------------------------------------------------
1 1999-12-01 00:00:00.000
1 2000-01-15 00:00:00.000
1 2000-03-31 00:00:00.000
1 2000-05-10 00:00:00.000
1 2000-08-06 00:00:00.000
1 2000-10-05 00:00:00.000
*/
GO
/*
Objective: Given the year value & Anniversary Date for a person , all
activities for the person within the anniversary date should be
obtained. The activity should fall between 365 days of 04/01 and
03/31 irrespective of the year.
Solutions: Use the anniversary date & change the year to the Search Year value.
This will give the Start Date for the activity. Next add 365 / 366 days
from the start date as appropriate to get the End Date.
*/
-- Search activities for year: 2000
DECLARE @AnniversaryDate datetime , @SearchYear smallint
SELECT @AnniversaryDate = '04/01/88' , @SearchYear = YEAR( CURRENT_TIMESTAMP )
SELECT * FROM #PersonActivity AS a
WHERE a.ActivityDate BETWEEN
DATEADD( YEAR , @SearchYear - YEAR( @AnniversaryDate ) , @AnniversaryDate ) And
-- Add 365 / 366 days based on non-leap or leap year respectively
DATEADD( DAY , CASE WHEN ( @SearchYear % 400 = 0 ) Or
( @SearchYear % 4 = 0 And @SearchYear % 100 <> 0 )
THEN 366 -- Leap year
ELSE 365 -- Normal year
END ,
DATEADD( YEAR , @SearchYear - YEAR( @AnniversaryDate ) , @AnniversaryDate )
)
ORDER BY a.PersonID , a.ActivityDate;
/*
PersonID ActivityDate
----------- ------------------------------------------------------
1 2000-05-10 00:00:00.000
1 2000-08-06 00:00:00.000
1 2000-10-05 00:00:00.000
*/
-- Search activities for year: 1999
SELECT @SearchYear = YEAR( CURRENT_TIMESTAMP ) - 1
SELECT * FROM #PersonActivity AS a
WHERE a.ActivityDate BETWEEN
DATEADD( YEAR , @SearchYear - YEAR( @AnniversaryDate ) , @AnniversaryDate ) And
-- Add 365 / 366 days based on non-leap or leap year respectively
DATEADD( DAY , CASE WHEN ( @SearchYear % 400 = 0 ) Or
( @SearchYear % 4 = 0 And @SearchYear % 100 <> 0 )
THEN 366 -- Leap year
ELSE 365 -- Normal year
END ,
DATEADD( YEAR , @SearchYear - YEAR( @AnniversaryDate ) , @AnniversaryDate )
)
ORDER BY a.PersonID , a.ActivityDate;
/*
PersonID ActivityDate
----------- ------------------------------------------------------
1 1999-12-01 00:00:00.000
1 2000-01-15 00:00:00.000
1 2000-03-31 00:00:00.000
*/
GO
DROP TABLE #PersonActivity;
GO
This page was last updated on May 01, 2006 04:28 PM.