CREATE TABLE [dbo].[temptable] ( [Id] INT NOT NULL PRIMARY KEY IDENTITY, [uid] INT NOT NULL, [pid] INT NOT NULL, [event] INT NOT NULL, [timeStamp] DateTime NOT NULL )
I want to select count of uid (group by pid) where event = @event AND timeStamp = [MAX of timestamp](group by uid).
Id,uid,pid,event,timeStamp 1,20,300,100,'yesterday' 2,20,300,200,'today' 3,20,300,400,'daybefore' 4,30,300,100,'yesterday' 5,30,300,400,'daybefore' 6,40,300,100,'today' 7,40,300,200,'yesterday' 8,40,300,400,'daybefore'
For the above data, I should get below results if @event = 200
pid, count 300, 1
It shouldn't count uid 30 because there is no event 200 for uid 30. And it should also NOT count uid 40, because
event for max(timestamp) i.e.
today is 100 which doesn't match to @event i.e. 200.
DateTime field but I've kept is relative in sample data for simplicity.