OVER PARTITION BY SQL SERVER Business case

Question

I need some help with a question. I work in SQL SERVER 2012. Basically this is the case:

A customer can buy three kinds of products:

1. A
2. B
3. C

If a customer buys two products( any combination, exemple, A+B OR B+C OR CA, any combination that results in two sold products) on the same day, we should show this as "Dual".

If a customer only buys 1 product on the same day, then we should call it "Single".

If the customer buys All three of the products on the same day, we should call it "Triple".

This is what my data looks like at the moment:

   YearMonth   Product CustomerNr        Sales Date
    201505      B      70056844         20150501
    201505      A      70056844         20150501
    201505      B      70057297         20150503
    201505      A      70057494         20150504
    201505          B      70057494         20150504
    201505      C      70057494         20150504
    201505      B      70033055         20150506
    201505      B      36021632         20150508
    201505      A      70060612         20150508
    201505      C      70060612         20150508

As we can see in the sample data, customer nr :70060612 has bought two products A and C on the same sales date and hence we can say he bought a dual package. Customer number :70057494 has bought on the same sales date, 3 products, A,B and C. We can call it Triple.

I want my data to include a column like this :

   YearMonth   Product CustomerNr        Sales Date  Package        
    201505      B      70056844         20150501    Dual
    201505      A      70056844         20150501    Dual
    201505      B      70057297         20150503    Single
    201505      A      70057494         20150504    Triple
    201505          B      70057494         20150504    Triple
    201505      C      70057494         20150504    Triple
    201505      B      70033055         20150506    Single
    201505      B      36021632         20150508    Single
    201505      A      70060612         20150508    Dual
    201505      C      70060612         20150508    Dual

How can I do this? I was thinking of doing some like

 Test=COUNT(*) OVER (PARTITION BY CustomerNr)

Answer 1

This is a solution with an corresponding data example. Hopefully this helps you.

create table #t(dt datetime, prod char(1), customer int)
declare @date datetime = getdate()

insert into #t(dt,prod,customer)
values(@date,N'A',1),(@date,N'A',1),(@date,N'C',1),(@date,N'B',2),(@date,N'C',2),(@date,N'B',3),(@date,N'A',3),(@date,N'A',4),(@date,N'B',5),(@date,N'C',6),
(@date,N'A',7),(@date,N'B',7),(@date,N'C',7)


SELECT dt,prod,customer, MAX(cntDay) OVER(PARTITION BY customer, dt) as cntDay, 
    CASE MAX(cntDay) OVER(PARTITION BY customer, dt)
        WHEN 2 THEN N'Dual'
        WHEN 3 THEN N'Triple'
        ELSE N'Single' END as package -- Your Case
FROM (
    SELECT *, DENSE_RANK() OVER(PARTITION BY customer, dt ORDER BY prod) as cntDay
    FROM #t
) as dat
ORDER BY customer, dt -- just for a better overview

drop table #t

Best regards, Ionic

Answer 2

Unfortunately SQL do not allow COUNT(Distinct) OVER, the DENSE_RANK() is a workaround

DECLARE @Table table
(
    id int identity(1,1) primary key,
    YearMonth int,
    Product char(1),
    CustomerNr int,
    SalesDate date
)

INSERT @Table VALUES
(201505, 'B', 70056844, '20150501'),
(201505, 'A', 70056844, '20150501'),
(201505, 'B', 70057297, '20150503'),
(201505, 'A', 70057494, '20150504'),
(201505, 'B', 70057494, '20150504'),
(201505, 'C', 70057494, '20150504'),
(201505, 'B', 70033055, '20150506'),
(201505, 'B', 36021632, '20150508'),
(201505, 'A', 70060612, '20150508'),
(201505, 'C', 70060612, '20150508'),
(201505, 'A', 70056844, '20150501') -- Additional for duplicated product

SELECT 
    *, 
    CASE 
        DENSE_RANK() OVER (PARTITION BY CustomerNr, SalesDate ORDER BY Product)
         + DENSE_RANK() OVER (PARTITION BY CustomerNr, SalesDate ORDER BY Product DESC) - 1
        WHEN 1 THEN 'Single'
        WHEN 2 THEN 'Dual'
        WHEN 3 THEN 'Triple'
        ELSE 'Multiple' -- Not defined case 
    END AS Package
FROM 
    @Table
ORDER BY 
    id

Result

id          YearMonth   Product CustomerNr  SalesDate  Package
----------- ----------- ------- ----------- ---------- --------
1           201505      B       70056844    2015-05-01 Dual
2           201505      A       70056844    2015-05-01 Dual
3           201505      B       70057297    2015-05-03 Single
4           201505      A       70057494    2015-05-04 Triple
5           201505      B       70057494    2015-05-04 Triple
6           201505      C       70057494    2015-05-04 Triple
7           201505      B       70033055    2015-05-06 Single
8           201505      B       36021632    2015-05-08 Single
9           201505      A       70060612    2015-05-08 Dual
10          201505      C       70060612    2015-05-08 Dual
11          201505      A       70056844    2015-05-01 Dual