Knowledgebase

And the answer is: don’t use select top 1, instead use the row_number() function and the OVER clause.

Lets assume we have a table structure like:

Table [Observation]

Multiple observations during a TimeSpan on 1 barcode are collected into 1 [Observation] record.

if object_id('[dbo].[Observation]') is null
begin
  create table [dbo].[Observation]
  (
      [Id]                int not null identity(1,1)  constraint PK_Observation_Id primary key,
      [Barcode]            varchar(max) not null,
      [Postcode]        varchar(max) not null,
      [Date]            date not null,
      [Count]            int not null,
      [TimeSpanId]        int not null constraint FK_Observation_TimeSpanId foreign key references TimeSpan(Id)
  )
end
go

if not exists(select top 1 1 from [Observation])
begin
    insert into [dbo].Observation([Barcode],[Postcode],[Date],[Count],[TimeSpanId]) values ('NN10000','4000AA','2012-06-18',1,1)
    insert into [dbo].Observation([Barcode],[Postcode],[Date],[Count],[TimeSpanId]) values ('NN10001','4000AA','2012-07-02',1,1)
    insert into [dbo].Observation([Barcode],[Postcode],[Date],[Count],[TimeSpanId]) values ('NN20000','4000AA','2012-05-29',2,2)
    insert into [dbo].Observation([Barcode],[Postcode],[Date],[Count],[TimeSpanId]) values ('NN30000','4000AA','2012-06-18',2,3)
end

Table [TimeSpan]

The table TimeSpan contains timespans of 2 hours.

if object_id('[dbo].[TimeSpan]') is null
begin
  create table [dbo].[TimeSpan]
  (
      [Id]                int not null identity(1,1)  constraint PK_TimeSpan_Id primary key,
      [Name]            varchar(max),
      [Start]            time(7),
      [End]                time(7),
      [IsMostImportant] int not null default(0)
  )
end
go

if not exists(select top 1 1 from [TimeSpan])
begin
    insert into [dbo].[TimeSpan]([Name],[Start],[End],[IsMostImportant]) values ('07:00 - 09:00','07:00:00','09:00:00',0)
    insert into [dbo].[TimeSpan]([Name],[Start],[End],[IsMostImportant]) values ('10:00 - 12:00','10:00:00','12:00:00',0)           
    insert into [dbo].[TimeSpan]([Name],[Start],[End],[IsMostImportant]) values ('13:00 - 15:00','13:00:00','15:00:00',0)
end

The most important TimeSpan

The first TimeSpan with the most- and youngest observations.

Update Query

The column [IsMostImportant] can be updated by using the following query:

declare    @DateNow date
set @DateNow = '2012-07-12'
set nocount on                                -- SET NOCOUNT ON, added to prevent extra result sets from interfering with SELECT statements.
set datefirst 1                               -- Monday is first day of the week.

update [TimeSpan]
set [IsMostImportant] = 1
from [TimeSpan] ts
inner join
(
    select
        ob3.[RowNumber],
        ob3.[TimeSpanId]
    from
    (
        select
            row_number() over(partition by ob2.[Postcode] order by ob2.[Count] desc, ob2.[DateDiffInDays] asc) as [RowNumber],
            [Count],
            [DateDiffInDays],
            [Postcode],
            [TimeSpanId]
        from 
        (
            select
                sum(ob.[Count]) as [Count],
                sum(datediff(day, ob.[Date], @DateNow) * ob.[Count]) as [DateDiffInDays],
                ob.[Postcode],
                ob.[TimeSpanId]
            from Observation ob
            inner join TimeSpan ts on ob.TimeSpanId = ts.Id
            group by ob.[Postcode], ob.[TimeSpanId]
        ) ob2
    ) ob3
    where ob3.[RowNumber] = 1
) ob4
on ts.[Id] = ob4.[TimeSpanId]

Result