Min() And Max() Based On Partition In Sql Server

I want to use min & max function but on certain criteria. Create Table #Test (Id Int Identity(1,1), Category Varchar(100), DateTimeStamp DateTime) Insert into #Test (Ca

Solution 1:

You can try below - it's a gap & island problem

DEMO

select category, min(datetimestamp),max(datetimestamp)
from
(
select*,row_number() over(orderby datetimestamp) -row_number() over(partitionby category orderby datetimestamp) as rn2
from #Test
)A groupby category,rn2 orderby2

OUTPUT:

category       minval               maxval
c1             13/08/2019 01:00:13  13/08/2019 05:00:13
c2             13/08/2019 06:00:13  13/08/2019 10:00:13
c1             13/08/2019 11:00:13  13/08/2019 11:00:13

Solution 2:

For postgres:

SELECT category, min(DateTimeStamp) as minn , max(DateTimeStamp) as maxx 
FROM (Select*,
      SUM(CASEWHEN Category <> PrevCategory THEN1ELSE0END)  OVER (ORDERBY 
      ID,Category,DateTimeStamp) AspartitionFrom (Select* ,LAG (Category, 1) OVER (ORDERBY ID) AS PrevCategory From Test)  As 
           help) As helper 
GROUPBY category,partition;