SQL - Date range Return all data

Question

I have a simple PHP web app for daily attendance of empployee I have a table named emp_master it would have all the employee data for example

emp_id   emp_name
1001     Abby
1002     Buub

And another table named timesheet_tran with dailt transaction when even employee log into the system

timesheet_tran table
date       emp_id 
02/01/14   1001
02/01/14   1002
03/01/14   1001
04/01/14   1001
04/01/14   1002

I was trying to figure out a single query to return data , between date range which would display all the employees who have logged / not logged for that day between the date range.

i need to display

emp_id   date      absent/present
1001     02/01/14  present
1002     02/01/14  present
1001     03/01/14  present
1002     03/01/14  absent

i tried

SELECT m.emp_id,t.date FROM timesheet_tran as t RIGHT OUTER JOIN emp_master m ON t.date BETWEEN '02/01/14' AND '04/01/14' AND t.emp_id = m.emp_id

but I need records for each date mentioned.

some thing like i have a loop in PHP for ( from to date ) { execute the query for each date individually } — epynic, Jan 11 '14 at 06:28

crafter · Answer 1 · 2014-01-11T07:07:13.567

1

SELECT m.emp_id,t.date 
FROM timesheet_tran as t 
RIGHT OUTER JOIN emp_master m ON t.date 
AND t.date BETWEEN '2014-02-01' AND '2014-04-01'
 AND t.emp_id = m.emp_id

EDIT: From your updated comments, you need to query a join for all users in a manner that they will be listed whether they are present or not.

SELECT m.emp_id,t.date 
FROM emp_master as m 
LEFT JOIN timesheet_tran t ON date

This will list all users entries. Where the timesheet data is not present, a null will be returned. You can then convert this to string absent/present as you see fit.

    SELECT m.emp_id, 
CASE WHEN t.date IS NOT NULL 
       THEN 'present'
       ELSE 'absent'
END AS T_date
FROM emp_master as m 
LEFT JOIN timesheet_tran t ON date

edited Jan 11 '14 at 07:07

answered Jan 11 '14 at 06:15

crafter

6,246
1
34
46

is this the same i have mentioned above ? – epynic Jan 11 '14 at 06:18
No, Notice the 'AND t.date' on line 4 – crafter Jan 11 '14 at 06:21
Also, be careful of the default mysql date format : See here for a discussion. http://stackoverflow.com/questions/5367721/mysql-date-format – crafter Jan 11 '14 at 06:23
This would return the employee whose are absent between the date range only once – epynic Jan 11 '14 at 06:26
I didnt know , but i want all the employee for each of the date , if they are absent / present – epynic Jan 11 '14 at 06:40

score 1 · Accepted Answer · answered Jan 11 '14 at 07:14

1

Try Like this

Select emp_id,emp_name,Date1,(
CASE WHEN EXISTS(
sELECT emp_id FROM timesheet_tran AT WHERE T.emp_id =AT.emp_id AND T.date1=AT.date
) then 'Present' Else 'Absent' End )as Status
FROM
(
Select emp_id ,emp_name,Cast(date as DATE)AS DATE1 from emp_master a,(Select Distinct date    from timesheet_tran ) b
) T

SQL FIDDLE DEMO

Hope this help you

answered Jan 11 '14 at 07:14

Vignesh Kumar A

27,863
13
63
115

Dude,,, your awesome , can you please suggest some good way to learn how to write this kind queries. This works perfectly fine :D – epynic Jan 11 '14 at 07:24

SQL - Date range Return all data

2 Answers2

SQL FIDDLE DEMO