Help needed with Over () Clause

Transact-SQL
1

I have this table:.

Name       StartTime   EndTime    Incidence
--------------------------------------------------------
Peter      6:00         8:00      1
Steven     6:05         7:50      3
Peter      8:00        15:00      2
Steven     7:50        16:00      1

I need to get a summary with the total time worked and the incidence related to the last record (Peter 2, Steven 1). I was trying to get the incidence with the over () clause, partitioning by name and ordering by the StartTime, but it doesn't work.

How do I get the last incidence in the most elegant and fastest way?

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
DROP TABLE #Temp
CREATE TABLE #Temp (Name NVARCHAR(10), StartTime Time, EndTime Time, Incidence int)
INSERT INTO #Temp (Name,StartTime, EndTime, Incidence) values ('Peter','06:00','08:00',1)
INSERT INTO #Temp (Name,StartTime, EndTime, Incidence) values ('Steven','06:05','07:50',3)
INSERT INTO #Temp (Name,StartTime, EndTime, Incidence) values ('Peter','08:00','15:00',2)
INSERT INTO #Temp (Name,StartTime, EndTime, Incidence) values ('Steven','07:50','16:00',1)


select Name, Sum(DATEDIFF(minute, StartTime, EndTime)) as Minutes, last-incidence-of-day???
from #Temp
Group by Name
2 
;with cte_summary as (
    select Name, Sum(DATEDIFF(minute, StartTime, EndTime)) as Minutes, 
        max(convert(varchar(5), EndTime, 8) + right(replicate('0', 10) + 
            cast(Incidence as varchar(10)), 10)) as max_endtime_incidence     
    from #Temp
    Group by Name
)
select Name, Minutes, CAST(RIGHT(max_endtime_incidence, 10) AS int) AS Last_Incidence_Of_Day
from cte_summary
1 Like
3

I like that! Very tricky idea!
In my case it will do without the replicate part as the incidence is only a 2-digit number. So I add 100 to the incidence before the cast to string; later I fetch the last 2 digits.

max(convert(varchar(5), EndTime, 8) + cast(Incidence + 100 as varchar(10))) as max_endtime_incidence
1 Like
4

Nice .. that works too.

5

hi barnabeck

i know this is solved

could this be possibly what you are looking for ??

hope this helps :slight_smile: :slight_smile:

SQL with OVER clause and partitioning 
;with cte as (
    select ROW_NUMBER() over(partition by name order by (select null))  rn,* from #temp
)
select * from cte 
go

image

6

Hi

the clue is

order by (select null)