0

Table

ID Date Time

001 12/01/2011 08:00:00
001 12/01/2011 08:30:00
001 12/01/2011 11:30:00
001 12/01/2011 12:35:00
001 12/01/2011 03:30:00
001 12/01/2011 04:30:00
002 12/01/2011 08:30:00
002 12/01/2011 09:30:00
....

I want to select intime, outtime, breakouttime, breakintime from table

Select id, date, min(time) as intime, max(time) as outtime from table group by id, date

the above query is working for intime and outtime, but i want to select the break outtime and break intime for the dates.

For Example BreakOut time is between 11:30:00 to 12:30:00 means then query should check the time from min(Punchtime) and max(Punchtime) between 11:00:00 to 01:00:00.

Expected Output

Id Date intime outtime breakout breakin

001 08:00:00 04:30:00 11:30:00 12:35:00
002 08:30:00 09:30:00 null null 'so there is no punches between 11 to 1 for the break time, so break time should be null

How to make a query for the above condition

Need query help

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Gopal
  • 11,712
  • 52
  • 154
  • 229
  • How do you know 11:30 and 12:35 are the break in and break out times? Why not 8:30 or 3:30? – Bert Dec 17 '11 at 10:35

1 Answers1

3

Assuming that break times are always between 11:00 AM and 1:00 PM, you could do something like below. Basically, use a CTE to fabricate an extra "BreakTime" column based on some logic.

To make the query work with your data which is shown in 12-hour time, I made the (poor) assumptions in the logic that anything between 11:00 and 13:00, or 0:00 and 1:00 are break times. In your real system, you should use 24-hour times so you don't have the AM/PM ambiguity. Furthermore, I'm not sure you need 2 separate columns for date and time.

But anyway, since this is just sample code to show the query, I left it alone.

Here's a complete example, with a test table and your data:

* Updated to use derived table for SQL 2000 instead of CTE *

CREATE TABLE [dbo].[MyTable]
(
    ID nvarchar(5),
    MyDate date,
    MyTime time
)

insert into MyTable (ID, MyDate, MyTime) values ('001', '12/01/2011', '08:00:00')
insert into MyTable (ID, MyDate, MyTime) values ('001', '12/01/2011', '08:30:00')
insert into MyTable (ID, MyDate, MyTime) values ('001', '12/01/2011', '11:30:00')
insert into MyTable (ID, MyDate, MyTime) values ('001', '12/01/2011', '12:35:00')
insert into MyTable (ID, MyDate, MyTime) values ('001', '12/01/2011', '03:30:00')
insert into MyTable (ID, MyDate, MyTime) values ('001', '12/01/2011', '04:30:00')
insert into MyTable (ID, MyDate, MyTime) values ('002', '12/01/2011', '08:30:00')
insert into MyTable (ID, MyDate, MyTime) values ('002', '12/01/2011', '09:30:00')

select 
    ID, 
    MyDate, 
    min(MyTime) as intime, 
    max(MyTime) as outtime,
    min(BreakTime) as break_intime, 
    max(BreakTime) as break_outtime
FROM 
(
    SELECT *,
        CASE 
            WHEN (MyTime > '11:00:00' and MyTime < '13:00:00') THEN MyTime
            WHEN (MyTime > '0:00:00' and MyTime < '1:00:00') THEN MyTime
            ELSE null
        END AS BreakTime
    from MyTable 
) as DerivedTable
group by ID, MyDate

And the output:

ID  MyDate  intime  outtime break_intime    break_outtime
001 2011-12-01  03:30:00.0000000    12:35:00.0000000    11:30:00.0000000    12:35:00.0000000
002 2011-12-01  08:30:00.0000000    09:30:00.0000000    NULL    NULL
JohnD
  • 14,327
  • 4
  • 40
  • 53