Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
SQL
USE [TestDemo]
GO

/****** Object:  Table [dbo].[Table_1]    Script Date: 04/28/2015 16:05:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Table_1](
    [Id] [int] NOT NULL,
    [Status] [varchar](50) NULL,
    [Time] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


Insert value

ID STATUS TIME
1 IN 8:00
1 OUT 8:5
1 IN 8:10
2 IN 8:00
2 OUT 8:5
3 IN 8:5
3 OUT 8:15

Out can given current status
=====================
ID STATUS
1 IN
2 OUT
3 OUT

Given for sql query

[edit]SHOUTING removed - OriginalGriff[/edit]
Posted
Updated 28-Apr-15 0:54am
v2
Comments
Tomas Takac 28-Apr-15 7:18am    
I would say Time column needs to be datetime. Otherwise it will only work for one day. It definitely must not be a varchar because then the sort won't work correctly.
krishna97 28-Apr-15 7:47am    
yes time and date also
John C Rayan 28-Apr-15 8:15am    
Can you explain in bit more detail what you are trying to do? I am totally confused with your table structure.

1 solution

The trick is to find the latest value per [Id]. There are two ways, either you use group by Id and find out max Time then join back to the original table. Or you can use ROW_NUMBER[^] function to generate a descending sequence by Time per Id.
SQL
select [Id], [Status], [Time]
from (
	select [Id], [Status], [Time], 
		row_number() over(partition by [Id] order by [Time] desc) seq 
	from [dbo].[Table_1] 
	) x
where x.seq = 1

This of course assumes that the Time columns is datetime not varchar.
 
Share this answer
 
v2
Comments
krishna97 28-Apr-15 8:50am    
out put will given wrong
out put be can this format...

ID STATUS TIME
1 IN
2 OUT
3 OUT
Tomas Takac 28-Apr-15 9:05am    
Sorry but I don't understand what's the problem. The query returns correct results, 1 is in, 2 & 3 are out.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900