packing interval with prio

• Ralf

  SSC Enthusiast

  Points: 124

  More actions

  July 29, 2015 at 3:34 pm

  #310257

  Hi, I'm trying to consolidate time intervals.

  It's like the packing date and time intervals tsql challenge from Itzig Ben Gan.

  But unfortunalty I have to cope with time intervals priorties, e.g. if one time interval with a higher prio overlaps a time interval with a lower one

  something like this:

  time interval 1 prio 2: [--------------]

  time interval 2 prio 1: [---]

  result: [---][---][----]

  ..sorry I can't get this aligned, but my point is that from the 2 intervals above you get one interval result with the classic packing solution but because of the higher priority of interval 2, I should get 3 intervals as a result.

  IF OBJECT_ID('dbo.Sessions') IS NOT NULL DROP TABLE dbo.Sessions;

  CREATE TABLE dbo.Sessions

  (

  sessionid INT NOT NULL,

  actid INT NOT NULL,

  starttime DATETIME2(0) NOT NULL,

  endtime DATETIME2(0) NOT NULL,

  prio INT NOT NULL,

  value INT NOT NULL,

  CONSTRAINT PK_Sessions PRIMARY KEY(sessionid),

  CONSTRAINT CHK_endtime_gteq_starttime

  CHECK (endtime >= starttime)

  );

  GO

  INSERT INTO dbo.Sessions(sessionid, actid, starttime, endtime, prio, value) VALUES

  (1,4, '20151231 08:00:00', '20151231 12:00:00',3,44),

  (2,4, '20151231 09:00:00', '20151231 11:00:00',2,74),

  (3,4, '20151231 10:00:00', '20151231 10:30:00',1,444),

  (4,4, '20151231 12:00:00', '20151231 16:00:00',1,13),

  (5,4, '20151231 13:00:00', '20151231 14:00:00',2,33);

  IF OBJECT_ID('dbo.DesiredResult') IS NOT NULL DROP TABLE dbo.DesiredResult;

  CREATE TABLE dbo.DesiredResult

  (

  sessionid INT NOT NULL,

  actid INT NOT NULL,

  starttime DATETIME2(0) NOT NULL,

  endtime DATETIME2(0) NOT NULL,

  value INT NOT NULL,

  CONSTRAINT CHK_Result

  CHECK (endtime >= starttime)

  );

  INSERT INTO dbo.DesiredResult (sessionid, actid, starttime, endtime, value) VALUES

  (1 ,4, '20151231 08:00:00', '20151231 09:00:00',44),

  (2 ,4, '20151231 09:00:00', '20151231 10:00:00',74),

  (3 ,4, '20151231 10:00:00', '20151231 10:30:00',444),

  (2 ,4, '20151231 10:30:00', '20151231 11:00:00',74),

  (1 ,4, '20151231 11:00:00', '20151231 12:00:00',44),

  (4 ,4, '20151231 12:00:00', '20151231 16:00:00',13);

  select * from dbo.Sessions

  select * from dbo.DesiredResult

  hope It's clear what I'm trying to achieve...I'm fighting with this problem since a while and I really hope someone can bring some light to me

  thank you very much.

  Ralf

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  July 29, 2015 at 5:45 pm

  #1815979

  Maybe this will get you somewhere, but it's not quite right according to your desired output, I just don't have time to to spend trying to fix it and I don't have any quick ideas for fixing it.

  The problem is that you have "shared" times e.g. 10:00:00 which can be the end of one interval and the start of another, while this code will give the minute to the highest (lowest) priority...

  select G.sessionid, G.actid, min(G.RequiredMinute) starttime, max(G.RequiredMinute) endtime, G.value

  from (

  select R.sessionid, R.actid, R.value, R.RequiredMinute, R.MinuteSelector

  -- use the difference between two row numbers to group the minutes into islands

  , row_number() over(order by R.RequiredMinute)

  -

  row_number() over(partition by R.sessionid, R.actid order by R.RequiredMinute) grp

  from (

  select S.sessionid, S.actid, S.value

  -- Use the Tally table to generate every minute between starttime and endtime

  , dateadd(minute, T.N, S.starttime) RequiredMinute

  -- Use row_number() to flag each minute according to its priority

  , row_number() over(partition by dateadd(minute, T.N, S.starttime) order by prio) as MinuteSelector

  from dbo.Sessions S

  join Tally T

  -- we want an N for every minute

  on T.N>=0 and T.N<=datediff(minute, S.starttime, S.endtime)

  ) R

  -- Select the "first" row for every minute, based on priority

  where MinuteSelector=1

  ) G

  -- use grouping to "squish" the expanded minutes back down to starttime and endtime

  group by G.sessionid, G.actid, G.value, G.grp

  order by starttime;

  The algorithm in use:

  1. Expand each time period out into one row per minute (you could use second as long as your Tally table is big enough)

  2. Prioritise each minute

  3. Group the minutes back up taking the highest priority for each one, resulting in split sessions.

  The results (which do not exactly match yours):

  

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]