asked on

Help with SQL Statement - need new hires from 5 years ago

In an effort to display folks who were hired 5 years ago, I'm trying to write a SQL statement.

I have the following from our existing webpart, which shows people hired within the last 21 days (the value of days being set to 21 in our webpart).

SELECT Size, Rank, PictureURL, AccountName, PreferredName, HireDate, OfficeNumber, Path, Title, Description, Write, Department, LastName, FirstName, JobTitle, UserName, Location, WorkEmail, WorkPhone FROM scope() WHERE ( ("SCOPE" = 'People') ) AND ("HireDate" > DATEADD(day, -[days], GETGMTDATE()) )

This statement shows everyone hired within the last 21 days. I need to change it so I can see people hired 5 years ago. Can someone help?

dsacker

This gives you people hired anywhere from 5 years ago to today:

SELECT Size, Rank, PictureURL, AccountName, PreferredName, HireDate, OfficeNumber, Path, Title, Description, Write, Department, LastName, FirstName, JobTitle, UserName, Location, WorkEmail, WorkPhone
FROM scope() WHERE ( ("SCOPE" = 'People') ) AND ("HireDate" >= DATEADD(year, -[years], GETGMTDATE()) )

This gives you people hired anywhere from 5 years ago and older (not sure how to interpret " hired 5 years ago", so am giving both):

SELECT Size, Rank, PictureURL, AccountName, PreferredName, HireDate, OfficeNumber, Path, Title, Description, Write, Department, LastName, FirstName, JobTitle, UserName, Location, WorkEmail, WorkPhone
FROM scope() WHERE ( ("SCOPE" = 'People') ) AND ("HireDate" <= DATEADD(year, -[years], GETGMTDATE()) )

Kent Olsen

Hi angland,

SELECT Size, Rank, PictureURL, AccountName, PreferredName, HireDate, OfficeNumber, Path, Title, Description, Write, Department, LastName, FirstName, JobTitle, UserName, Location, WorkEmail, WorkPhone FROM scope() WHERE ( ("SCOPE" = 'People') ) AND ("HireDate" <= DATEADD(years, -5, GETGMTDATE()) )

Getting people exactly 5 years ago today probably isn't practical. The query above with get everyone that was hire 5 or more years ago.

Kent

ralmada

change it to

SELECT Size, Rank, PictureURL, AccountName, PreferredName, HireDate, OfficeNumber, Path, Title, Description, Write, Department, LastName, FirstName, JobTitle, UserName, Location, WorkEmail, WorkPhone FROM scope() WHERE ( ("SCOPE" = 'People') ) AND ("HireDate" > DATEADD(year, -5, GETGMTDATE()) )

anglandp

ASKER

Thanks - it's for an anniversary site. The idea being that we'll have this webpart and it will display those people celebrating 5 years with the company. The query I have now gives me anyone hired in the last 21 days. What I need is the query adjusted so that it gives me everyone hired 5 years ago today, and preferably within that same 21 day time span.

dsacker

Ah:

SELECT Size, Rank, PictureURL, AccountName, PreferredName, HireDate, OfficeNumber, Path, Title, Description, Write, Department, LastName, FirstName, JobTitle, UserName, Location, WorkEmail, WorkPhone FROM scope() WHERE ("SCOPE" = 'People') AND ("HireDate" BETWEEN DATEADD(year, -[years], GETGMTDATE()) AND DATEADD(day, [days], DATEADD(year, -[years], GETGMTDATE()))

Notice that I used [years], which I assume you will substitute with "5", and that you will substitute "21" for [days].

anglandp

ASKER

Thanks - been trying but am getting an error that the query is malformed:

SELECT Size, Rank, PictureURL, AccountName, PreferredName, HireDate, OfficeNumber, Path, Title, Description, Write, Department, LastName, FirstName, JobTitle, UserName, Location, WorkEmail, WorkPhone
FROM scope() WHERE ( ("SCOPE" = 'People') ) AND ("HireDate" BETWEEN DATEADD(year, -5, GETGMTDATE()) AND DATEADD(day, 21, DATEADD(year, -5, GETGMTDATE())) ) )

ASKER CERTIFIED SOLUTION

ralmada

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

plusone3055

SELECT Size, Rank, PictureURL, AccountName, PreferredName, HireDate, OfficeNumber, Path, Title, Description, Write, Department, LastName, FirstName, JobTitle, UserName, Location, WorkEmail, WorkPhone FROM scope() WHERE ( ("SCOPE" = 'People') )

AND ("HireDate" > DATEADD(day, -[days], GETGMTDATE()) ) - 5

ralmada

or are you looking for something like this

SELECT Size, Rank, PictureURL, AccountName, PreferredName, HireDate, OfficeNumber, Path, Title, Description, Write, Department, LastName, FirstName, JobTitle, UserName, Location, WorkEmail, WorkPhone FROM scope() WHERE  ( ("SCOPE" = 'People') ) AND ("HireDate" between DATEADD(year, -5, GETGMTDATE())-21 and DATEADD(year, -5, GETGMTDATE()))

Open in new window