November 28, 2012 at 9:31 am
i need show datetime select only. But show all datetime in table filesTA.
this code:
SELECT *
FROM filesTA tf
WHERE EXISTS
(
SELECT *
FROM filesTA tf left join LeaveRecord lr ON tf.ChkDate = lr.StartDate and tf.ChkDate = '2012-10-01'
WHERE lr.StartDate = '2012-10-01'
)
OR CODE:
SELECT *
FROM filesTA tf
WHERE NOT tf.EmpNo
IN (
SELECT lr.EmployeeRun
FROM LeaveRecord lr
WHERE lr.StartDate = '2012-10-01'
)
Output:
EmpNo | ChkDate | ChkIn | ChkOut
00001 | 2012-10-01 00:00:00.000 | 2012-10-01 07:21:00.000 | 2012-10-01 12:21:00.000
00002 | 2012-10-01 00:00:00.000 | 2012-10-01 08:13:00.000 | 2012-10-01 19:55:00.000
00003 | 2012-10-15 00:00:00.000 | 2012-10-15 07:06:00.000 | 2012-10-15 20:12:00.000
00004 | 2012-10-22 00:00:00.000 | 2012-10-22 07:12:00.000 | 2012-10-22 19:15:00.000
I need Output:
EmpNo | ChkDate | ChkIn | ChkOut
00001 | 2012-10-01 00:00:00.000 | 2012-10-01 07:21:00.000 | 2012-10-01 12:21:00.000
00002 | 2012-10-01 00:00:00.000 | 2012-10-01 08:13:00.000 | 2012-10-01 19:55:00.000
THANKS FOR YOUR TIME. 😉
November 28, 2012 at 9:42 am
It is nearly impossible to do much but guess because you didn't provide much detail. Please see the first link in my signature for bet practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 28, 2012 at 9:47 am
Why do you need the if exists ? Why can you not just use a direct join with the same conditions ?
SELECT tf.*
FROM filesTA tf
left join LeaveRecord lr ON tf.ChkDate = lr.StartDate
and tf.ChkDate = '2012-10-01'
WHERE lr.StartDate = '2012-10-01'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply