I have a table Events Events containing cardno,time,id,name--each id has

I have a table Events

Events containing

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

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:40 06:45
where 1,2...... are dates for example january 1,2, etc. 6
and 7 are saturday and sunday

how it is posssible

Question Posted / klbaiju

1 Answers
2325 Views
HCL, Wipro, I also Faced
E-Mail Answers

I have a table Events Events containing cardno,time,id,name--each id has a cardno my r..

Answer / Atul Chaudhary

To achieve this in SQL Server, you can use the ROW_NUMBER() function to number each row for each cardno and day, and then select the rows with the minimum and maximum time for each cardno. Here's an example query:
SELECT
id,
cardno,
MIN(time) AS 'in',
MAX(time) AS 'out',
DATEPART(dd, time) AS 'day',
DATENAME(dw, time) AS 'weekday'
FROM Events
WHERE time IS NOT NULL
GROUP BY id, cardno, weekday
ORDER BY cardno, day

Is This Answer Correct ?

0 Yes

0 No

Post New Answer