CODEWITHSUNDEEP
sql-serversql-server-2008-r2
sai bharath
sai bharath

Reputation: 844

How to get this data?

I have something like this:

Time       EQID    STATUS  USER    
12:12am     EQ1     0       SA
12:14am     EQ1     1       SA
02:30am     EQ2     0       SA
03:30am     EQ2     1       SA
05:30am     EQ1     0       SA
06:30am     EQ2     1       SA

Now the required output is:

In_time   Out_time  EQID  USER
12:12am    12:14am   EQ1   SA
02:30am    03:30am   EQ2   SA
05:30am    06:30am   EQ1   SA

Here In_time is the time with status '0' and Out_time is the time with status '1'. How can I get this In_time and Out_time for that particular EQID and user.

Note: Like this I have so many rows with multiple users, how can I get this?

Upvotes: 0

Views: 69

Answers (3)

EMUEVIL
EMUEVIL

Reputation: 512

CREATE TABLE #data (
    [Time] datetime,
    [EQID] nvarchar(10),
    [Status] bit,
    [User] nvarchar(10)
)

INSERT INTO #data VALUES ('2017-12-27 12:12am', 'EQ1', 0, 'SA')
INSERT INTO #data VALUES ('2017-12-27 12:14am', 'EQ1', 1, 'SA')
INSERT INTO #data VALUES ('2017-12-27 02:30am', 'EQ1', 0, 'SA')
INSERT INTO #data VALUES ('2017-12-27 03:30am', 'EQ1', 1, 'SA')
INSERT INTO #data VALUES ('2017-12-27 05:30am', 'EQ1', 0, 'SA')
INSERT INTO #data VALUES ('2017-12-27 06:30am', 'EQ1', 1, 'SA')

-- Get the next time where status is 1
SELECT D1.[Time] as 'In_Time'
    , D2.[Time] as 'Out_Time'
    , D1.[EQID]
    , D1.[User]
FROM #data D1
    LEFT JOIN #data D2
        ON D2.[Time] = (SELECT TOP 1 [Time]
                        FROM #data
                        WHERE [Status] = 1
                        AND [Time] > D1.[Time]
                        AND [User] = D1.[User]
                        AND [EQID] = D1.[EQID]
                        ORDER BY [Time])
WHERE D1.[Status] = 0

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31775

One way is to do a main query of all the rows where Status=0. That would give you all the columns except out_time.

Add to this, a correlated subquery to get out_time and get the first row (order by Time) where the EQID and User are the same as the main query, the status=1, and the Time is greater than the Time in the main query.

(In other words, the out_Time is the MIN Time for this EQID & User with Status=1 that is greater than the current Status=0 row.)

Upvotes: 1

S3S
S3S

Reputation: 25112

You have a few problems here.

  1. You are storing TIME as VARCHAR
  2. You are storing the TIME without a DATE which makes it impossible to determine when a user clocked in on one day but didn't clock out on the next
  3. You haven't alluded to what EQID is or why you chose EQ1 over EQ2 for the last row

With that being said... here is a way.

declare @table table (  [Time] varchar(64), 
                        EQID char(3), 
                        [STATUS] int, 
                        [USER] char(2))

insert into @table
values
('12:12am','EQ1',0,'SA'),
('12:14am','EQ1',1,'SA'),
('02:30am','EQ2',0,'SA'),
('03:30am','EQ2',1,'SA'),
('05:30am','EQ1',0,'SA'),
('06:30am','EQ2',1,'SA')

;with cte as(
select
    [Time]
    ,EQID
    ,[USER]
    ,[Status]
    ,RN = row_number() over(partition by [USER] order by cast(stuff([Time],6,0,' ') as time))
from 
    @table t)

select
    In_Time = i.[Time]
    ,Out_Time = o.[Time]
    ,i.EQID
    ,i.[USER]
from
    cte i
    left join cte o on
    o.[USER] = i.[USER]
    and o.RN = i.RN + 1
where
    i.[STATUS] = 0

Upvotes: 0

Related Questions