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
- 1 Answers
- 2326 Views
- HCL, Wipro, I also Faced
- E-Mail Answers
Answer Posted / 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 View All Answers
What are the properties of the transaction?
How to remove duplicate rows from table except one?
How can I check that whether automatic statistic update is enabled or not?
How can we solve concurrency problems?
Can one drop a column from a table?
What are the pros and cons of putting a scalar function in a queries select list or in the where clause?
Can we make the the chages By Using the Sql if u know any function or process please inform me Actuall result: BRK1 Break 1 Part 1 00:01:00:00 60 BRK1 Break 1 Part 2 00:01:00:00 60 BRK2 Break 2 Part 1 00:01:00:00 60 BRK2 Break 2 Part 2 00:01:00:00 60 BRK2 Break 2 Part 3 00:01:00:00 60 BRK3 Break 3 Part 1 00:01:00:00 60 BRK3 Break 3 Part 2 00:01:00:00 60 Desired O/P: BRK1 Break 1 Part 1 00:01:00:00 60 Part 2 00:01:00:00 60 BRK2 Break 2 Part 1 00:01:00:00 60 Part 2 00:01:00:00 60 Part 3 00:01:00:00 60
Why should you use or avoid select * statements?
If any stored procedure is encrypted, then can we see its definition in activity monitor?
What is the difference between upgrade and migration in sql server?
Explain “row_number()” in sql server with an example?
What is an indexed view?
do you know how to configure db2 side of the application? : Sql server database administration
what is the difference between openrowset and openquery?
What is normalization and what are the advantages of it?
