How to achieve this in SQL query ?

I have the following table

EmployeeId      LastAttDate
12                       2014-06-14
01                       2014-07-10
11                      2014-07-01
15                      2014-06-21
18                      2014-06-27

I need to list all the records in this above table only the following two scenarios

max(LastAttDate) exceeds 25 days.
OR
LastAttDate=CurrentDate

How to achieve this ?
Varshini SAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
Assuming LastAttDate is of data type datetime or similar:


DECLARE @dt date = DATEADD(day, -25, CONVERT(date, GETDATE()))

SELECT EmployeeID, LastAttDate
FROM SomeTable
WHERE LastAttDate <= @dt

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kyle AbrahamsSenior .Net DeveloperCommented:
Based off the above answer, adding in the part for date = today:

DECLARE @dt date = DATEADD(day, -25, CONVERT(date, GETDATE()))

SELECT EmployeeID, LastAttDate
FROM SomeTable
WHERE LastAttDate <= @dt OR
-- trims off time from lastAttDate and getdate()
DATEADD(day, 0, CAST(LastAttDate  As Date)) = DATEADD(day, 0, CAST(getdate() As Date))
0
Varshini SAuthor Commented:
I want to check both condition.
max(LastAttDate) exceeds 25 days.
 OR
 LastAttDate=CurrentDate
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Russell FoxDatabase DeveloperCommented:
SELECT *
FROM AttendanceTable
WHERE (
	CAST(LastAttDate AS DATE) <= DATEADD(DAY, -25, CAST(GETDATE() AS DATE))
	OR
	CAST(LastAttDate AS DATE) = CAST(GETDATE() AS DATE) 
	) 

Open in new window

0
PortletPaulfreelancerCommented:
I'll admit I don't really understand your question.

"I need to list all the records in this above table" =
"max(LastAttDate) exceeds 25 days OR LastAttDate=CurrentDate "

             You want ALL RECORDS from that table if ANY record
             has a LastAttDate older than today - 25 OR LastAttDate = today

This is my literal interpretation, it will list ALL RECORDS if either condition is met
SELECT
      *
FROM Table1
WHERE EXISTS (
            SELECT
                  1
            FROM Table1
            WHERE (
                  LastAttDate <= DATEADD(DAY, -25, DATEADD(DAY, DATEDIFF(dd, 0, GETDATE()), 0))
                  OR LastAttDate = DATEADD(DAY, DATEDIFF(dd, 0, GETDATE()), 0)
                  )
      )
;

Open in new window

If you don't want all records, but just the rows that meet the criteria:

It is preferable to avoid using functions on data so that indexes can be used. Comparing date to datetime works fine so it isn't really necessary to cast LastAttDate to date. Or  you can use an alternative method of truncating time from getdate (to 00:00:00) so that it remains as a datetime
-- using date data type
SELECT
      *
    , CAST(GETDATE() AS date)
FROM Table1
WHERE (
      LastAttDate <= DATEADD(DAY, -25, CAST(GETDATE() AS date))
      OR LastAttDate = CAST(GETDATE() AS date)
      )
;

-- using datetime data type
SELECT
      *
    , DATEADD(DAY, DATEDIFF(dd, 0, GETDATE()), 0)
FROM Table1
WHERE (
      LastAttDate <= DATEADD(DAY, -25, DATEADD(DAY, DATEDIFF(dd, 0, GETDATE()), 0))
      OR LastAttDate = DATEADD(DAY, DATEDIFF(dd, 0, GETDATE()), 0)
      )
;

Open in new window

0
Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

What do you mean by MAX(LastAttDate)

If you just want to check two separate conditions They try this

SELECT * FROM AttendanceTable  WHERE LastAttDate  = CurrentDate
UNION ALL
SELECT * FROM AttendanceTable  WHERE DATEADD(D,25,LastAttDate ) <= CurrentDate

It might give duplicates and if you want distinct Replace Union All by Union
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.