complex query to find attendance

Posted by Klbaiju under Sql Server on 2/11/2013 | Points: 10 | Views : 2086 | Status : [Member] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

I have two tables Events and emp

Events containing

cardno,time,id--each id has a cardno

and emp contains firstname,lastname,empid.

events.id and emp.empid are same

my requirement is every day each employee swipe the card several times

i want to calculate first and last time of each card

the output should be

name 1 2 6 7
in out in out holiday holiday
xxx 09:30 06:30 09:20 09:10
where 1,2 are dates for example january 1,2, etc. 6 and 7 are saturday and sunday

how it is posssible

Regards
Baiju

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Esauselo on: 2/11/2013 [Member] Starter | Points: 25

0	Select a.firstname + " " + a.lastname AS 'name', datename(month, time) as 'Month', datename(weekday, time) AS 'Day' from emp as a left join events as b on a.empid = b.id No sure what the Holiday Holiday info is coming from. Would be better if you provide sample info contained in both tables.. No security is a match for a stupid employee Klbaiju, if this helps please login to Mark As Answer. \| Alert Moderator

Select a.firstname + " " + a.lastname AS 'name', datename(month, time) as 'Month', datename(weekday, time) AS 'Day'
from emp as a
left join events as b
on a.empid = b.id

No sure what the Holiday Holiday info is coming from. Would be better if you provide sample info contained in both tables..

No security is a match for a stupid employee

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts