sqlugchallenge 2017

/*
Stefan Gustafsson Acando

opt1

Process one month at a time, optimized
*/

set nocount on

truncate table dbo.Customers

declare @t0 datetime = getdate()
declare @ms int

-- Calculate the range of months to process
declare @firstdate date = (select top 1 date from dbo.Purchases order by PurchaseID asc)
declare @lastdate  date = (select top 1 date from dbo.Purchases order by PurchaseID desc)
declare @lastmonth int = datediff(month, @firstdate, @lastdate)
RAISERROR ('@lastmonth: %d',0,0,@lastmonth) WITH NOWAIT

-- Create temp table for edges
IF object_id('tempdb..#e') IS NOT NULL DROP TABLE #e
create table #e       (MonthNumber int not null, node_s int not null, node_t int not null)
-- disable statistics generation
create statistics e1 on #e(MonthNumber) with norecompute
create statistics e2 on #e(node_s) with norecompute
create statistics e3 on #e(node_t) with norecompute

-- Build edge list
;with
p as (
    select PurchaseID, DATEDIFF(month, @firstdate, date) as MonthNumber, FirstName, LastName, Email, StreetAddress, ZipCode, CreditCard
    from dbo.Purchases
)
, cte1a as (
    select
        PurchaseID,
        MonthNumber,
        Key1 = (Firstname+'/'+CreditCard) collate finnish_swedish_bin,
        Key2 = (Firstname+'/'+Email) collate finnish_swedish_bin,
        Key3 = (Firstname+'/'+LastName+'/'+StreetAddress+'/'+cast(ZipCode as varchar(10))) collate finnish_swedish_bin
    from p
)
, k1 as (
    select Key1, MIN(PurchaseID) as node
    from cte1a
    group by Key1
)
, k2 as (
    select Key2, MIN(PurchaseID) as node
    from cte1a
    group by Key2
)
, k3 as (
    select Key3, MIN(PurchaseID)as node
    from cte1a
    group by Key3
)
,cte2 as (
    select
        a.PurchaseID,
        a.MonthNumber,
        k1.node as n1,
        k2.node as n2,
        k3.node as n3
    from cte1a a
    join k1 on k1.Key1 = a.Key1
    join k2 on k2.Key2 = a.Key2
    join k3 on k3.Key3 = a.Key3
)
, cte3 as (
    select *,
        -- Set node_t to min(n1,n2,n3)
        node_t = case
            when n1 < n2 then
                case when n1 < n3 then n1 else n3 end
            else
                case when n2 < n3 then n2 else n3 end
            end
    from cte2
)
insert into #e
select
    MonthNumber,
    node_s = PurchaseID,
    node_t
from cte3

set @ms = datediff(ms, @t0, GETDATE())
RAISERROR ('#e duration: %d ms',0,0,@ms) WITH NOWAIT

-- Process edge list one month at a time
begin tran
declare @i int = 0
while @i <= @lastmonth
begin

    insert into dbo.Customers (PurchaseID, CustomerID)
    select node_s, ISNULL(b.CustomerID, a.node_t)
    from #e a
    left join dbo.Customers b with (forceseek)
        on b.PurchaseID = a.node_t
    where a.MonthNumber = @i
    option (keepfixed plan)

    set @i = @i+1

end
commit

set @ms = datediff(ms, @t0, GETDATE())
RAISERROR ('duration: %d ms',0,0,@ms) WITH NOWAIT