Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# How to achieve this in SQL query ?

Posted on 2014-07-14
Medium Priority
157 Views
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 ?
0
Question by:Varshini S

LVL 93

Accepted Solution

Patrick Matthews earned 400 total points
ID: 40195704
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
``````
0

LVL 41

Assisted Solution

Kyle Abrahams earned 400 total points
ID: 40195734
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

Author Comment

ID: 40195743
I want to check both condition.
max(LastAttDate) exceeds 25 days.
OR
LastAttDate=CurrentDate
0

LVL 14

Assisted Solution

Russell Fox earned 400 total points
ID: 40195760
``````SELECT *
FROM AttendanceTable
WHERE (
CAST(LastAttDate AS DATE) <= DATEADD(DAY, -25, CAST(GETDATE() AS DATE))
OR
CAST(LastAttDate AS DATE) = CAST(GETDATE() AS DATE)
)
``````
0

LVL 49

Assisted Solution

PortletPaul earned 400 total points
ID: 40195971
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)
)
)
;
``````
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)
)
;
``````
0

LVL 15

Assisted Solution

Vikas Garg earned 400 total points
ID: 40196559
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a â€¦
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to alâ€¦
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signatâ€¦
Screencast - Getting to Know the Pipeline
###### Suggested Courses
Course of the Month10 days, left to enroll

#### 569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.