calculating Date

[Deam]

I am writing a stored proc to select all Active emps and all terminated emps within the last three calender months.For example if i execute the stored proc today ie-11/29/00 , apart from including all active emps ,all emps terminated between 8/1/00 and 10/31/00 should be included.The dates cannot be hard coded.
I need to modify this code:-
SELECT PersonTable.FullName AS FullName,TablePersonStatus.CurrentFlag AS CurrentFlag,
TablePersonStatus.StatusCode
FROM PersonTable INNER JOIN
TablePersonStatus ON
PersonTable.PersonID = TablePersonStatus.PersonID INNER JOIN
tStatus ON
TablePersonStatus.StatusCode = tStatus.StatusCode
WHERE (TablePersonStatus.CurrentFlag = 1) AND (tStatus.TerminationFlag = 1) AND (TablePersonStatus.StatusCode = 'TERM')
UNION
SELECT PersonTable.FullName AS FullName,TablePersonStatus.CurrentFlag AS CurrentFlag,
TablePersonStatus.StatusCode
FROM PersonTable INNER JOIN
TablePersonStatus ON
PersonTable.PersonID = TablePersonStatus.PersonID INNER JOIN tStatus ON
TablePersonStatus.StatusCode = tStatus.StatusCode
WHERE (TablePersonStatus.CurrentFlag = 1) AND (tStatus.ActiveFlag = 1) AND (TablePersonStatus.StatusCode = 'ACT')
ORDER BY fullname

would appreciate any help...

 

[TomSark]

Here's an example how to do it. I made the code verbose so you can understand it more easily.

DECLARE @Month integer,
@Year integer,
@calcDate1 datetime,
@strDate varchar(30),
@calcDate2 datetime,
@currdate datetime

SET @currdate = GetDate() -- this represents the date you will be working with

-- compute the first date
SET @Month = Month(@currdate)
SET @Year = Year(@currdate)
SET @strDate = convert(varchar(2), @Month) + '/01/' + convert(varchar(4), @Year)
SET @calcDate1 = DateAdd(mm, -3, convert(datetime, @strDate))

-- compute the second date, based on the first date
SET @calcDate2 = DateAdd(mm,4,@calcdate1)
SET @calcDate2 = DateAdd(dd,-1, @calcdate2)

-- Now, use the start and end dates in a BETWEEN statement
-- in a where clause to fix your problem.
SELECT PersonTable.FullName AS FullName,TablePersonStatus.CurrentFlag AS CurrentFlag,
TablePersonStatus.StatusCode
FROM PersonTable INNER JOIN
TablePersonStatus ON
PersonTable.PersonID = TablePersonStatus.PersonID INNER JOIN
tStatus ON
TablePersonStatus.StatusCode = tStatus.StatusCode
WHERE (TablePersonStatus.CurrentFlag = 1) AND (tStatus.TerminationFlag = 1) AND (TablePersonStatus.StatusCode = 'TERM')

-- new code
AND <theDATE> BETWEEN @calcDate1 and @calcDate2


UNION
SELECT PersonTable.FullName AS FullName,TablePersonStatus.CurrentFlag AS CurrentFlag,
TablePersonStatus.StatusCode
FROM PersonTable INNER JOIN
TablePersonStatus ON
PersonTable.PersonID = TablePersonStatus.PersonID INNER JOIN tStatus ON
TablePersonStatus.StatusCode = tStatus.StatusCode
WHERE (TablePersonStatus.CurrentFlag = 1) AND (tStatus.ActiveFlag = 1) AND (TablePersonStatus.StatusCode = 'ACT')

-- new code
AND <theDATE> BETWEEN @calcDate1 and @calcDate2

ORDER BY fullname



Hope this helps

Tom

 

[Deam]

This looks great and it works...Can this be done in a view also ??

 

[TomSark]

It can be done in a view as well provided you don't need to pass any parameters into it.. since the view cannot receive arguments...

Tom