SQLug 2014 Challenge Setup

/*
-- Cleanup code
drop procedure dbo.WarmupQueueReaders;
drop table StartIt;
drop procedure dbo.FillErsattning;
drop procedure dbo.FillErsattningMem;
drop table dbo.PrislistaMem;
drop type dbo.BesokMemType;
drop type dbo.VardenhetMemType;
drop table dbo.ErsattningMem;

drop procedure dbo.SendMsg;
drop procedure dbo.ReceiveMsg;
drop procedure dbo.SQLugChallenge2014_TargetQueue_Procedure

drop service [//SQLugChallenge2014/InitiatorService];
drop service [//SQLugChallenge2014/TargetService];

drop queue SQLugChallenge2014_InitiatorQueue;
drop queue SQLugChallenge2014_TargetQueue;

drop contract [//SQLugChallenge2014/Contract]

drop message type [//SQLugChallenge2014/RequestMessage];
drop message type [//SQLugChallenge2014/ReplyMessage];
drop message type [//SQLugChallenge2014/WarmupMessage];

*/

go

-- Enable Service Broker
if (select is_broker_enabled from sys.databases where name = db_name()) = 0
begin
  alter database current set single_user with rollback immediate;
  alter database current set enable_broker;
  alter database current set multi_user;
end;

create message type [//SQLugChallenge2014/RequestMessage] validation = well_formed_xml;
create message type [//SQLugChallenge2014/ReplyMessage]  validation = well_formed_xml;
create message type [//SQLugChallenge2014/WarmupMessage]  validation = well_formed_xml;

create contract [//SQLugChallenge2014/Contract]
(
  [//SQLugChallenge2014/RequestMessage] sent by initiator,
  [//SQLugChallenge2014/ReplyMessage] sent by target,
  [//SQLugChallenge2014/WarmupMessage] sent by initiator
);

create queue dbo.SQLugChallenge2014_TargetQueue;
create service [//SQLugChallenge2014/TargetService] on queue dbo.SQLugChallenge2014_TargetQueue ([//SQLugChallenge2014/Contract]);

create queue dbo.SQLugChallenge2014_InitiatorQueue;
create service [//SQLugChallenge2014/InitiatorService] on queue dbo.SQLugChallenge2014_InitiatorQueue;

go

-- Gatekeeper for queuereaders
create table dbo.StartIt
(
  PK bit not null primary key nonclustered hash with (bucket_count = 1)
) with (memory_optimized = on, durability = schema_only);

go

insert into dbo.StartIt(PK) values(0);

go

-- Holds Prislista in memory
create table dbo.PrislistaMem
(
  Taxekod char(3) collate Finnish_Swedish_100_BIN2 not null primary key nonclustered hash with (bucket_count = 16),
  Pris int not null
) with (memory_optimized = on, durability = schema_only);

go

-- Table valued parameter to dbo.FillErsattningMem
create type dbo.BesokMemType as table
(
  ID int not null primary key nonclustered hash with (bucket_count = 524288),
  PatientID uniqueidentifier not null,
  Taxekod char(3) collate Finnish_Swedish_100_BIN2 not null,
  Datum date not null,
  Pris int not null
) with (memory_optimized = on);
go

-- Holds a unique list of Vardenhet
create type dbo.VardenhetMemType as table
(
  Vardenhet char(5) collate Finnish_Swedish_100_BIN2 not null primary key nonclustered,
  index IX_VardenehetMem nonclustered (Vardenhet)
) with (memory_optimized = on);
go

-- Result of dbo.FillErsattningMem
create table dbo.ErsattningMem
(
  Vardenhet char(5) collate Finnish_Swedish_100_BIN2 not null,
  Datum date not null,
  Belopp int not null,
  index IX_ErsattningMem nonclustered (Vardenhet, Datum)
) with (memory_optimized = on, durability = schema_only);

go

-- Execute once per Vardehet to calculate Belopp per date and patient
create procedure dbo.FillErsattningMem
  @Vardenhet char(5),
  @BesokMem dbo.BesokMemType readonly
with native_compilation, schemabinding, execute as owner
as
begin atomic with (transaction isolation level = snapshot, language = N'us_english', delayed_durability = on)
  declare @ID int = 0;
  declare @RowsLeft bit = 1;
  declare @LastPatientID uniqueidentifier;
  declare @CurPatientID uniqueidentifier;
  declare @LastDate date;
  declare @CurDate date;
  declare @Belopp int = 0;
  declare @Taxekod char(3);
  declare @Pris int;
  declare @MaxA int = 0;
  declare @SumB int = 0;
  declare @MaxK12 int = 0;
  declare @MaxK34 int = 0;
  declare @MaxT1 int = 0;
  declare @MaxT2 int = 0;
  declare @LastT1Date date = '0001-01-01';
  declare @LastT2Date date = '0001-01-01';

  select @LastDate = B.Datum,
         @LastPatientID = B.PatientID
  from @BesokMem as B
  where B.ID = 1

  while @RowsLeft = 1
  begin
    set @ID += 1;

    select @CurDate = B.Datum,
           @CurPatientID = B.PatientID,
           @Taxekod = B.Taxekod,
           @Pris = B.Pris
    from @BesokMem as B
    where B.ID = @ID

    if @@rowcount = 1
    begin
      if @LastPatientID <> @CurPatientID
      begin
        -- New patient
        set @Belopp = @MaxA;
        if @Belopp < @MaxK12 + @MaxK34 set @Belopp = @MaxK12 + @MaxK34;
        if datediff(day, @LastT1Date, @LastDate) <= 90 set @MaxT1 = 0;
        if datediff(day, @LastT2Date, @LastDate) <= 90 set @MaxT2 = 0;
        if @Belopp < @MaxT1 set @Belopp = @MaxT1;
        if @Belopp < @MaxT2 set @Belopp = @MaxT2;

        if @Belopp + @SumB > 0
        begin
          insert into dbo.ErsattningMem(Vardenhet, Datum, Belopp) values
            (@Vardenhet, @LastDate, @Belopp + @SumB);
        end;

        select @MaxA = 0,
               @SumB = 0,
               @MaxK12 = 0,
               @MaxK34 = 0,
               @MaxT1 = 0,
               @MaxT2 = 0,
               @LastT1Date = '0001-01-01',
               @LastT2Date = '0001-01-01',
               @LastDate = @CurDate,
               @LastPatientID = @CurPatientID;
      end
      else
      if @LastDate <> @CurDate
      begin
        -- New date same patient
        set @Belopp = @MaxA;
        if @Belopp < @MaxK12 + @MaxK34 set @Belopp = @MaxK12 + @MaxK34;
        if datediff(day, @LastT1Date, @LastDate) <= 90 set @MaxT1 = 0;
        if datediff(day, @LastT2Date, @LastDate) <= 90 set @MaxT2 = 0;

        if @MaxT1 >= @MaxT2
        begin
          if @Belopp < @MaxT1
          begin
            set @Belopp = @MaxT1;
            set @LastT1Date = @LastDate;
          end
        end
        else
        begin
          if @Belopp < @MaxT2
          begin
            set @Belopp = @MaxT2;
            set @LastT2Date = @LastDate;
          end
        end;

        if @Belopp + @SumB > 0
        begin
          insert into dbo.ErsattningMem(Vardenhet, Datum, Belopp) values
            (@Vardenhet, @LastDate, @Belopp + @SumB);
        end;

        select @Belopp = 0,
               @MaxA = 0,
               @SumB = 0,
               @MaxK12 = 0,
               @MaxK34 = 0,
               @MaxT1 = 0,
               @MaxT2 = 0,
               @LastDate = @CurDate;
      end;

      -- Gather max values per day and patient
      if      @Taxekod = 'A01' and @Pris > @MaxA   set @MaxA = @Pris
      else if @Taxekod = 'A02' and @Pris > @MaxA   set @MaxA = @Pris
      else if @Taxekod = 'B01'                     set @SumB += @Pris
      else if @Taxekod = 'B02'                     set @SumB += @Pris
      else if @Taxekod = 'K01' and @Pris > @MaxK12 set @MaxK12 = @Pris
      else if @Taxekod = 'K02' and @Pris > @MaxK12 set @MaxK12 = @Pris
      else if @Taxekod = 'K03' and @Pris > @MaxK34 set @MaxK34 = @Pris
      else if @Taxekod = 'K04' and @Pris > @MaxK34 set @MaxK34 = @Pris
      else if @Taxekod = 'T01' and @Pris > @MaxT1  set @MaxT1 = @Pris
      else if @Taxekod = 'T02' and @Pris > @MaxT2  set @MaxT2 = @Pris;
    end
    else
    begin
      -- Last row
      set @Belopp = @MaxA;
      if @Belopp < @MaxK12 + @MaxK34 set @Belopp = @MaxK12 + @MaxK34;
      if datediff(day, @LastT1Date, @CurDate) <= 90 set @MaxT1 = 0;
      if datediff(day, @LastT2Date, @CurDate) <= 90 set @MaxT2 = 0;
      if @Belopp < @MaxT1 set @Belopp = @MaxT1;
      if @Belopp < @MaxT2 set @Belopp = @MaxT2;

      if @Belopp + @SumB > 0
      begin
        insert into dbo.ErsattningMem(Vardenhet, Datum, Belopp) values
          (@Vardenhet, @LastDate, @Belopp + @SumB);
      end;

      set @RowsLeft = 0;
    end
  end;
end;

go

-- Called from dbo.SQLugChallenge2014_TargetQueue_Procedure once per Vardenhet
create procedure dbo.FillErsattning
  @Vardenhet char(5)
as
begin
  set nocount on;

  declare @B dbo.BesokMemType;

  -- Get besok rows for one Vardenhet
  insert into @B(ID, PatientID, Taxekod, Datum, Pris)
  select row_number() over(order by B.PatientID, B.DatumOchTid), B.PatientID, B.Taxekod, cast(B.DatumOchTid as date), P.Pris
  from dbo.Besok as B
    inner join dbo.PrislistaMem as P
      on B.Taxekod = P.Taxekod
  where B.Vardenhet = @Vardenhet;

  -- Calucalte Belopp to dbo.ErsattningMem
  exec dbo.FillErsattningMem @Vardenhet, @B;

  -- Add rows to Ersattning
  insert into dbo.Ersattning(Vardenhet, Datum, Belopp)
  select @Vardenhet, E.Datum, sum(E.Belopp)
  from dbo.ErsattningMem as E
  where E.Vardenhet = @Vardenhet
  group by E.Datum;

end;

go

-- Target procedure for service borker queue
create procedure dbo.SQLugChallenge2014_TargetQueue_Procedure
as
begin
  set nocount on;

  declare @DlgHandle uniqueidentifier;
  declare @Msg xml;
  declare @MsgType sysname;
  declare @Vardenhet char(5);

  while 1 = 1
  begin
    -- Get one message
    waitfor
    (
      receive top(1) @DlgHandle = conversation_handle,
                     @Msg = message_body,
                     @MsgType = message_type_name
      from dbo.SQLugChallenge2014_TargetQueue
    ), timeout 1000;

    if @@rowcount = 0
    begin
      break;
    end

    if @MsgType = N'//SQLugChallenge2014/RequestMessage'
    begin
      -- Process one Vardenhet and send a reply
      set @Vardenhet = @Msg.value('(/E/text())[1]', 'char(5)');
      if @Vardenhet is not null
      begin
        exec dbo.FillErsattning @Vardenhet;
        send on conversation @DlgHandle message type [//SQLugChallenge2014/ReplyMessage](@Msg);
      end;
    end
    if @MsgType = N'//SQLugChallenge2014/WarmupMessage'
    begin
      -- Wait for start signal
      while 1 = 1
      begin
        waitfor delay '00:00:00.1';
        if not exists(select * from dbo.StartIt)
        begin
          break;
        end
      end
    end
    else
    if @MsgType = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
    begin
      -- Initiator wants to end conversation
      end conversation @DlgHandle;
    end
    else
    if @MsgType = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
    begin
      -- Something went wrong
      declare @Error int;
      declare @Description nvarchar(4000);

      with xmlnamespaces(default 'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
      select @Error = @Msg.value('(//Error/Code/text())[1]', 'int'),
             @Description = @Msg.value('(//Error/Description/text())[1]', 'nvarchar(4000)');

      raiserror(N'Error Code:%i Description:"%s"', 16, 1, @Error, @Description) with log;

      end conversation @DlgHandle;
    end
  end
end;

go

-- Generate one message per Vardenhet
create procedure dbo.SendMsg
  @NumMsgs int out
as
begin
  set nocount on;

  declare @E dbo.VardenhetMemType;
  declare @DlgHandle uniqueidentifier;
  declare @SQL nvarchar(max);

  -- Get unique list of Vardenhet
  with E as
  (
    select top (1) B.Vardenhet
    from dbo.Besok as B
    order by B.Vardenhet
    union all
    select R.Vardenhet
    from (
          select B.Vardenhet,
                row_number() over (order by B.Vardenhet) as rn
          from dbo.Besok as B
            inner join E
              on E.Vardenhet < B.Vardenhet
          ) as R
    where R.rn = 1
  )
  insert into @E(Vardenhet)
  select E.Vardenhet
  from E
  option (maxrecursion 0);

  set @NumMsgs = @@rowcount;

  -- Build code to send one message per Vardenhet
  set @SQL =
  (
    select N'send on conversation @DlgHandle message type [//SQLugChallenge2014/RequestMessage] (''<E>'+Vardenhet+N'</E>'');'+nchar(13)+nchar(10)
    from @E
    for xml path(''), type
  ).value('text()[1]', 'nvarchar(max)')

  -- Start dialog with target
  begin dialog @DlgHandle
  from service [//SQLugChallenge2014/InitiatorService]
  to service '//SQLugChallenge2014/TargetService'
  on contract [//SQLugChallenge2014/Contract]
  with encryption = off;

  -- Send messages
  exec sp_executesql @SQL, N'@DlgHandle uniqueidentifier', @DlgHandle;

end;

go

-- Receive replies from target when processing is done
create procedure dbo.ReceiveMsg
  @NumMsgs int
as
begin
  set nocount on;

  declare @DlgHandle uniqueidentifier;
  declare @Msg xml;
  declare @MsgType sysname;
  declare @I int = 0

  set @I = 0;

  while 1 = 1
  begin
    -- Get one reply message
    waitfor
    (
      receive top(1) @DlgHandle = conversation_handle,
                     @Msg = message_body,
                     @MsgType = message_type_name
      from dbo.SQLugChallenge2014_InitiatorQueue
    ), timeout 5000;

    if @MsgType = N'//SQLugChallenge2014/ReplyMessage'
    begin
      -- Count number of replies
      set @I += 1;
      if @I >= @NumMsgs
      begin
        -- Processing done, end conversation
        end conversation @DlgHandle;
        break;
      end;
    end
    else
    if @MsgType = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
    begin
      -- Target wants to end dialog
      end conversation @DlgHandle;
    end
    else
    if @MsgType = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
    begin
      -- Something went wrong
      declare @Error int;
      declare @Description nvarchar(4000);

      with xmlnamespaces(default 'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
      select @Error = @Msg.value('(//Error/Code/text())[1]', 'int'),
             @Description = @Msg.value('(//Error/Description/text())[1]', 'nvarchar(4000)');

      raiserror(N'Error Code:%i Description:"%s"', 16, 1, @Error, @Description) with log;

      end conversation @DlgHandle;
    end
  end;
end;

go

create procedure dbo.WarmupQueueReaders
as
begin
  declare @DlgHandle uniqueidentifier;

  begin dialog @DlgHandle
  from service [//SQLugChallenge2014/InitiatorService]
  to service '//SQLugChallenge2014/TargetService'
  on contract [//SQLugChallenge2014/Contract]
  with encryption = off;

  send on conversation @DlgHandle message type [//SQLugChallenge2014/WarmupMessage];
  end conversation @DlgHandle;
end

go

-- Hook to dbo.SQLugChallenge2014_TargetQueue_Procedure SQLugChallenge2014_TargetQueue
alter queue SQLugChallenge2014_TargetQueue with activation
(
  status = on,
  procedure_name = dbo.SQLugChallenge2014_TargetQueue_Procedure,
  max_queue_readers = 8,
  execute as owner
);

go

-- Add 8 messages to the queue. One for each queuereader
exec dbo.WarmupQueueReaders;
exec dbo.WarmupQueueReaders;
exec dbo.WarmupQueueReaders;
exec dbo.WarmupQueueReaders;
exec dbo.WarmupQueueReaders;
exec dbo.WarmupQueueReaders;
exec dbo.WarmupQueueReaders;
exec dbo.WarmupQueueReaders;

-- Wait for the queuemonitor to fire up the readers
waitfor delay '00:00:40';