April 20, 2009 at 3:25 pm
Hello there, on SQL 2000, what would be the best way of creating an aggregate column (INT) to count the number of April 1st's between a given date (in another column) and getdate()?
i'm guessing a custom function will need creating here, dunno, something like:
SELECT EmployeeStartDate, dbo.YearEndsSince(EmployeeStartDate) as NumberOfYearEndsSince FROM whatever
few ways of doing this, however this is going to be used in a very busy enviroment, i was just wondering what would be the best way?
any tips or examples would be cool.
Ta.
Jordon.
Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....
April 21, 2009 at 3:44 am
I am just posting this so it will get you started with some other better solutions than this one.
DECLARE @SomeDate DATETIME
SET @SomeDate = '1-Apr-1986'
; WITH Employees
AS
(
SELECT1 AS EmployeeID, CONVERT( DATETIME, '15-Jun-1979' ) AS EmployeeStartDate
UNION ALL
SELECT2 AS EmployeeID, '12-Dec-2008' AS EmployeeStartDate
UNION ALL
SELECT3 AS EmployeeID, '23-Dec-1998' AS EmployeeStartDate
UNION ALL
SELECT4 AS EmployeeID, '04-Jan-2009' AS EmployeeStartDate
UNION ALL
SELECT5 AS EmployeeID, '01-Apr-2004' AS EmployeeStartDate
UNION ALL
SELECT6 AS EmployeeID, '11-Oct-1986' AS EmployeeStartDate
)
SELECTEmployeeID, EmployeeStartDate,
( CASE WHEN EmployeeStartDate = DATEADD( YEAR, DATEDIFF( YEAR, @SomeDate, GETDATE() ), @SomeDate ) THEN 1 ELSE 0 END )
+ DATEDIFF( YEAR, EmployeeStartDate, GETDATE() ) - 1 AS NumberOfYearEndsSinceStart
FROMEmployees
--Ramesh
April 21, 2009 at 4:00 am
hi m8,
I just kinda came up with a solution by using a function to take a date and return the year end for that date + 1 day to give me their 1st April, then simply calculate the difference in years +1 to account for zero:
DATEDIFF(YEAR, DATEADD(DAY,1,dbo.udf_FinYrEnd(EMDET.DET_SFT_DATED)), GETDATE()) + 1 AS LOS_Years
so say for example DET_SFT_DATED = 20th Dec 2004, the 1st april would be 1/4/2005 then Datediff that and getdate = 4 + 1 to account for zero, bascially mimics:
1. April 1st 2005
2. April 1st 2006
3. April 1st 2007
4. April 1st 2008
5. April 1st 2009
Code for udf_FinYrEnd if anyone is interested:
CREATE FUNCTION [dbo].[udf_FinYrEnd]
-- Required Parameters
(@PassedDte DateTime)
RETURNS DateTime
AS
BEGIN
DECLARE @FINYrEND as varchar(20)
Set @FINYrEND = Cast(DatePart(Year,@PassedDte) as varchar(4)) +'-03-31 23:59:59'
If DateDiff(day, @PassedDte, @FINYrEND) >=0 Begin
Set @FINYrEND = @FINYrEND
END
ELSE BEGIN
Set @FINYrEND = Cast(DatePart(Year,DateAdd(year, 1,@PassedDte)) as varchar(4)) +'-03-31 23:59:59'
END
Return @FINYrEND
END
now my DATEDIFF returned 4 so i am assuming i am right in adding + 1.... will do some testing
Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply