Date Range Question

[renee35]

I am creating a report and need to pull all active employees whose startdate is at least 90 days prior to the first day of each quarter (1/1, 4/1, ect.) and the enddate is null.

Please advise!



Thanks a bunch!!

-T

 

[PWise]

Create A digits table

CREATE TABLE [Digits] (
    [DigitID] [int] NOT NULL ,
    CONSTRAINT [PK_Digits] PRIMARY KEY  CLUSTERED 
    (
        [DigitID]
    )  ON [PRIMARY] 
) ON [PRIMARY]
GO
DECLARE @position int
SET @position = 1
WHILE @position <= 1000
BEGIN
   insert into Digits(DigitID) values (@position)
   SET @position = @position + 1
END
go

run this select statement

Select eid, Startdate
from Tablename
inner join(
Select min(days)firstofQ
from(
     select top 100 dateadd(d,-DigitID,getdate()) days
     from Digits
     )dt
where  datepart(q,days)=datepart(q,getdate())
and  year(days)=year(getdate()))dt
on Startdate between dateadd(d,-90,fq) and fq
and enddate is null