Jonas Claudelin bidrag SQL SM 2017

---Jonas Claudelin, Hypergene, 2017-03-14 (insickat)
---SQL SM 2017 - TWO


if object_id('dbo.CustomerWorkTable') Is not null drop table dbo.CustomerWorkTable
go
if object_id('dbo.Purchases2') Is not null drop table [dbo].[Purchases2]
go
if object_id('dbo.Customer1') Is not null drop table [dbo].Customer1
go
if object_id('dbo.Customer2') Is not null drop table [dbo].Customer2
go
if object_id('dbo.Customer3') Is not null drop table [dbo].Customer3
go

--Arbetstabell
CREATE TABLE dbo.CustomerWorkTable (
    [CustomerID] INT  not null PRIMARY KEY NONCLUSTERED, ---kommer innehålla alla purchaseid
    [CustomerID1] INT not null, --för matchning 1
    [CustomerID2] INT not null, --för matchning 2
    [CustomerID3] INT not null, --för matchning 3
    [FirstName] [varchar](50) NOT NULL,
    [LastName] [varchar](50) NOT NULL,
    [Email] [varchar](50) NOT NULL,
    [StreetAddress] [varchar](50) NOT NULL,
    [ZipCode] [int] NOT NULL,
    [CreditCard] [char](19) NOT NULL,
    [PurchaseID] INT  not null --,
    ,loopnr char(10) not null
) WITH (MEMORY_OPTIMIZED=ON, durability = schema_only)
GO

--In-memorytabell för att snabba upp
CREATE TABLE [dbo].[Purchases2](
    [PurchaseID] [int] NOT NULL PRIMARY KEY NONCLUSTERED,
    [Date] [date] NOT NULL,
    [FirstName] [varchar](50)  COLLATE Finnish_Swedish_100_BIN2 NOT NULL,
    [LastName] [varchar](50) COLLATE Finnish_Swedish_100_BIN2  NOT NULL,
    [Email] [varchar](50)  COLLATE Finnish_Swedish_100_BIN2 NOT NULL,
    [StreetAddress] [varchar](50) COLLATE Finnish_Swedish_100_BIN2  NOT NULL,
    [ZipCode] [int] NOT NULL,
    [CreditCard] [char](19)  COLLATE Finnish_Swedish_100_BIN2 NOT NULL,
    loopnr INT not null  INDEX IX_loopnr (loopnr ASC),
) WITH (MEMORY_OPTIMIZED=ON, durability = schema_only)
GO


-----Hålla koll på kunder och gilltiga identifieringar av dessa, en för varje identifieringstyp
CREATE TABLE [dbo].[Customer1](
    [CustomerID] [int] NOT NULL,
    [FirstName] [varchar](50) COLLATE Finnish_Swedish_100_BIN2 NOT NULL,
    [CreditCard] [char](19) COLLATE Finnish_Swedish_100_BIN2 NOT NULL
 PRIMARY KEY NONCLUSTERED
(
    [CustomerID] ASC,
    [FirstName] ASC,
    [CreditCard] ASC
)
) WITH (MEMORY_OPTIMIZED=ON, durability = schema_only) --kan inte ha in memory på viss typ av text kolumner
GO

CREATE TABLE [dbo].[Customer2](
    [CustomerID] [int] NOT NULL,
    [FirstName] [varchar](50) COLLATE Finnish_Swedish_100_BIN2  NOT NULL,
    [Email] [varchar](50) COLLATE Finnish_Swedish_100_BIN2  NOT NULL
PRIMARY KEY NONCLUSTERED
(
    [CustomerID] ASC,
    [FirstName] ASC,
    [Email] ASC
)
) WITH (MEMORY_OPTIMIZED=ON, durability = schema_only)
GO

CREATE TABLE [dbo].[Customer3](
    [CustomerID] [int] NOT NULL ,
    [FirstName] [varchar](50) COLLATE Finnish_Swedish_100_BIN2 NOT NULL,
    [LastName] [varchar](50) COLLATE Finnish_Swedish_100_BIN2  NOT NULL,
    [StreetAddress] [varchar](50) COLLATE Finnish_Swedish_100_BIN2  NOT NULL,
    [ZipCode] [int] NOT NULL
PRIMARY KEY NONCLUSTERED
(
    [CustomerID] ASC,
    [FirstName] ASC,
    [LastName] ASC,
    [StreetAddress] ASC,
    [ZipCode] ASC
)
) WITH (MEMORY_OPTIMIZED=ON, durability = schema_only)
GO

---Fyller in-memory tabell
INSERT INTO [dbo].[Purchases2]
           ([PurchaseID]
           ,[Date]
           ,[FirstName]
           ,[LastName]
           ,[Email]
           ,[StreetAddress]
           ,[ZipCode]
           ,[CreditCard]
           ,[loopnr])
select [PurchaseID]
           ,[Date]
           ,[FirstName]
           ,[LastName]
           ,[Email]
           ,[StreetAddress]
           ,[ZipCode]
           ,[CreditCard]
            ,CAST(LEFT(CONVERT(CHAR(8),[Date],112),6)+'01' AS int) as loopnr ---ger ett löpmånad per unik yearmonth
from [dbo].[Purchases]



--Unika köp per månad, kör månads batch.
DECLARE @month int
DECLARE @count int
SELECT @month=MIN(loopnr) FROM [dbo].[Purchases2] --hur många gången vi ska loopa
SELECT @count=MAX(loopnr) FROM [dbo].[Purchases2] --hur många gången vi ska loopa

---Första inserten, för att få customerId (vi vet att det är unika kunder inom en månad)
---även in i koppling purchaseId - customerid
INSERT INTO [dbo].[CustomerWorkTable]
           ([CustomerID]
           ,[CustomerID1]
           ,[CustomerID2]
           ,[CustomerID3]
           ,[FirstName]
           ,[LastName]
           ,[Email]
           ,[StreetAddress]
           ,[ZipCode]
           ,[CreditCard]
           ,[PurchaseID]
           ,loopnr
           )
SELECT      [PurchaseID] AS [CustomerID] ---första PurchaseID används som customerID
            ,-1 AS [CustomerID1]
            ,-1 AS [CustomerID2]
            ,-1 AS [CustomerID3]
            ,[FirstName]
           ,[LastName]
           ,[Email]
           ,[StreetAddress]
           ,[ZipCode]
           ,[CreditCard]
           ,[PurchaseID]
           ,loopnr
FROM [dbo].[Purchases2] WHERE loopnr=@month

INSERT INTO [dbo].[Customer1]
           ([CustomerID]
           ,[FirstName]
           ,[CreditCard])
SELECT      [PurchaseID] AS [CustomerID] ---första PurchaseID används som customerID
            ,[FirstName]
           ,[CreditCard]
FROM [dbo].[Purchases2] WHERE loopnr=@month


INSERT INTO [dbo].[Customer2]
           ([CustomerID]
           ,[FirstName]
           ,[Email])
SELECT      [PurchaseID] AS [CustomerID] ---första PurchaseID används som customerID
            ,[FirstName]
           ,[Email]
FROM [dbo].[Purchases2] WHERE loopnr=@month

INSERT INTO [dbo].[Customer3]
           ([CustomerID]
           ,[FirstName]
           ,[LastName]
           ,[StreetAddress]
           ,[ZipCode])
SELECT      [PurchaseID] AS [CustomerID] ---första PurchaseID används som customerID
            ,[FirstName]
           ,[LastName]
           ,[StreetAddress]
           ,[ZipCode]
FROM [dbo].[Purchases2] WHERE loopnr=@month

---Nu har vi nått att utgå ifrån. Loopa igenom hela [dbo].[Purchases], månad för månad
--Unika köp per månad, kör månads batch.

SET @month= CAST(CONVERT(CHAR(8),DATEADD(M,1,CAST(LEFT(@month,4) AS char(4))+'-'+CAST(RIGHT(LEFT(@month,6),2) AS char(2))+'-'+CAST(RIGHT(@month,2) AS char(2))),112) AS int)

WHILE @month<=@count
BEGIN

INSERT INTO [dbo].[CustomerWorkTable]
           ([CustomerID]
           ,[CustomerID1]
           ,[CustomerID2]
           ,[CustomerID3]
           ,[FirstName]
           ,[LastName]
           ,[Email]
           ,[StreetAddress]
           ,[ZipCode]
           ,[CreditCard]
           ,[PurchaseID]
           ,loopnr
           )
--Kommer skapa en ny rad för varje köp.
(SELECT
A.[PurchaseID] AS [CustomerID]
,MIN(ISNULL(T1.CustomerID,-1)) AS CustomerID1
,MIN(ISNULL(T2.CustomerID,-1)) AS CustomerID2
,MIN(ISNULL(T3.CustomerID,-1)) AS CustomerID3
--Antingen samma kund, eller ny
,A.[FirstName]
,A.[LastName]
,A.[Email]
,A.[StreetAddress]
,A.[ZipCode]
,A.[CreditCard]
,A.[PurchaseID]
,A.loopnr
FROM
(SELECT PurchaseID, [Date], FirstName, LastName, Email, StreetAddress, ZipCode, CreditCard,loopnr
FROM [dbo].[Purchases2]
WHERE loopnr =@month
) AS A
--En join per villkor
LEFT JOIN [dbo].[Customer1] AS T1
ON A.FirstName=T1.FirstName
AND A.CreditCard=T1.CreditCard
LEFT JOIN [dbo].[Customer2] AS T2
ON A.FirstName=T2.FirstName
AND A.Email=T2.Email
LEFT JOIN [dbo].[Customer3] AS T3
ON A.FirstName=T3.FirstName
AND A.LastName=T3.LastName
AND A.StreetAddress=T3.StreetAddress
AND A.ZipCode=T3.ZipCode
GROUP BY A.[PurchaseID]
,A.[FirstName]
,A.[LastName]
,A.[Email]
,A.[StreetAddress]
,A.[ZipCode]
,A.[CreditCard]
,A.[PurchaseID]
,A.loopnr
) --END INSERT

INSERT INTO [dbo].[Customer1]
           ([CustomerID]
           ,[FirstName]
           ,[CreditCard])
SELECT      CASE WHEN [CustomerID1]=-1 AND CustomerID2=-1 AND CustomerID3=-1 THEN [CustomerID]
              WHEN CustomerID1<>-1 AND CustomerID1<=IIF(CustomerID2=-1,CustomerID1,CustomerID2) AND CustomerID1<=IIF(CustomerID3=-1,CustomerID1,CustomerID3) THEN CustomerID1
              WHEN CustomerID2<>-1 AND CustomerID2<=IIF(CustomerID1=-1,CustomerID2,CustomerID1) AND CustomerID2<=IIF(CustomerID3=-1,CustomerID2,CustomerID3) THEN CustomerID2
              WHEN CustomerID3<>-1 AND CustomerID3<=IIF(CustomerID1=-1,CustomerID3,CustomerID1) AND CustomerID3<=IIF(CustomerID2=-1,CustomerID3,CustomerID2) THEN CustomerID3
              ELSE -1 END AS [CustomerID]
            ,[FirstName]
           ,[CreditCard]
FROM [dbo].[CustomerWorkTable] WHERE loopnr=@month
AND CustomerID1=-1


INSERT INTO [dbo].[Customer2]
           ([CustomerID]
           ,[FirstName]
           ,[Email])
SELECT      CASE WHEN [CustomerID1]=-1 AND CustomerID2=-1 AND CustomerID3=-1 THEN [CustomerID]
              WHEN CustomerID1<>-1 AND CustomerID1<=IIF(CustomerID2=-1,CustomerID1,CustomerID2) AND CustomerID1<=IIF(CustomerID3=-1,CustomerID1,CustomerID3) THEN CustomerID1
              WHEN CustomerID2<>-1 AND CustomerID2<=IIF(CustomerID1=-1,CustomerID2,CustomerID1) AND CustomerID2<=IIF(CustomerID3=-1,CustomerID2,CustomerID3) THEN CustomerID2
              WHEN CustomerID3<>-1 AND CustomerID3<=IIF(CustomerID1=-1,CustomerID3,CustomerID1) AND CustomerID3<=IIF(CustomerID2=-1,CustomerID3,CustomerID2) THEN CustomerID3
              ELSE -1 END AS [CustomerID]
            ,[FirstName]
           ,[Email]
FROM [dbo].[CustomerWorkTable] WHERE loopnr=@month
AND CustomerID2=-1

INSERT INTO [dbo].[Customer3]
           ([CustomerID]
           ,[FirstName]
           ,[LastName]
           ,[StreetAddress]
           ,[ZipCode])
SELECT      CASE WHEN [CustomerID1]=-1 AND CustomerID2=-1 AND CustomerID3=-1 THEN [CustomerID]
              WHEN CustomerID1<>-1 AND CustomerID1<=IIF(CustomerID2=-1,CustomerID1,CustomerID2) AND CustomerID1<=IIF(CustomerID3=-1,CustomerID1,CustomerID3) THEN CustomerID1
              WHEN CustomerID2<>-1 AND CustomerID2<=IIF(CustomerID1=-1,CustomerID2,CustomerID1) AND CustomerID2<=IIF(CustomerID3=-1,CustomerID2,CustomerID3) THEN CustomerID2
              WHEN CustomerID3<>-1 AND CustomerID3<=IIF(CustomerID1=-1,CustomerID3,CustomerID1) AND CustomerID3<=IIF(CustomerID2=-1,CustomerID3,CustomerID2) THEN CustomerID3
              ELSE -1 END AS [CustomerID]
            ,[FirstName]
           ,[LastName]
           ,[StreetAddress]
           ,[ZipCode]
FROM [dbo].[CustomerWorkTable] WHERE loopnr=@month
AND CustomerID3=-1

--Räknar upp för ett nytt varv
SET @month= CAST(CONVERT(CHAR(8),DATEADD(M,1,CAST(LEFT(@month,4) AS char(4))+'-'+CAST(RIGHT(LEFT(@month,6),2) AS char(2))+'-'+CAST(RIGHT(@month,2) AS char(2))),112) AS int)

END ---loop med ny month

---Loop slut
TRUNCATE TABLE [dbo].[Customers] --behövs ej i tävlingen

--spara resultat
INSERT INTO [dbo].[Customers]
           ([PurchaseID]
           ,[CustomerID])
SELECT [PurchaseID]
,CASE
      WHEN [CustomerID1]=-1 AND CustomerID2=-1 AND CustomerID3=-1 THEN [CustomerID]

      WHEN [CustomerID1]<>-1
            THEN [CustomerID1]
      WHEN [CustomerID2]<>-1
            THEN [CustomerID2]
      WHEN [CustomerID3]<>-1
            THEN [CustomerID3]
      ELSE -1 --NÅTT ÄR FEL!
      END AS [CustomerID]
FROM [dbo].[CustomerWorkTable]