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
6
Medium Priority
?
157 Views
Last Modified: 2014-07-18
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
Comment
Question by:Varshini S
6 Comments
 
LVL 93

Accepted Solution

by:
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

Open in new window

0
 
LVL 41

Assisted Solution

by:Kyle Abrahams
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

by:Varshini S
ID: 40195743
I want to check both condition.
max(LastAttDate) exceeds 25 days.
 OR
 LastAttDate=CurrentDate
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
LVL 14

Assisted Solution

by:Russell Fox
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) 
	) 

Open in new window

0
 
LVL 49

Assisted Solution

by:PortletPaul
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)
                  )
      )
;

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
 
LVL 15

Assisted Solution

by:Vikas Garg
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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

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.

Join & Ask a Question