Btukfyl

I have a query that shows a list of products in the year 2018.

I want to view the products if they are created uniquely in 2018 and should not be shown if they have been created earlier in for example in 2017 or so.

Here is my SQL:

CREATE TABLE [dbo].[Products]

(

    [GTIN] [VARCHAR](50) NULL,

    [Location] [INT] NULL,

    [Date] [DATETIME] NULL

) ON [PRIMARY]

GO



INSERT INTO Products123(GTIN, Location, Date)

VALUES (12345678911, 1, '2017-01-01 00:00:00.000'),

       (12345678911, 1, '2018-01-01 00:00:00.000'),

       (12345678912, 1, '2018-02-01 00:00:00.000'),

       (12345678913, 1, '2018-03-01 00:00:00.000'),

       (12345678914, 1, '2018-03-01 00:00:00.000'),

       (12345678915, 1, '2018-04-01 00:00:00.000'),

       (12345678916, 1, '2018-05-01 00:00:00.000'),

       (12345678917, 1, '2018-06-01 00:00:00.000'),

       (12345678918, 1, '2018-07-01 00:00:00.000');

My query:

SELECT 

    DATENAME(MONTH, DATE) AS Dato,

    COUNT(DISTINCT GTIN) AS PRODUCTS

FROM 

    (SELECT  

         GTIN, DATE = MIN(DATE)

     FROM     

         Products

     WHERE 

         YEAR(DATE) = '2018' 

         AND YEAR(DATE) NOT IN (2017)

     GROUP BY

         GTIN) d

GROUP BY

    MONTH(DATE), DATENAME(MONTH, DATE)

ORDER BY   

    MONTH(DATE)

Result:

Dato    |   Products

January |     1

February|     1

April   |     2

May     |     1

June    |     1

July    |     1

Expected Result:

Dato    |   Products

January |     0

February|     1

April   |     2

May     |     1

June    |     1

July    |     1

January should be Zero since the product is already created in 2017 (or earlier) and therefore not unique anymore. Can someone help how retrieve the correct list

You could simply use NOT EXISTS:

WITH products2018 AS (

    SELECT *

    FROM @Products AS p

    WHERE Date >= '2018-01-01'

    AND NOT EXISTS (

        SELECT *

        FROM @Products AS x

        WHERE GTIN = p.GTIN

        AND Date < '2018-01-01'

    )

)

SELECT DATENAME(MONTH, d), COUNT(products2018.GTIN)

FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11)) AS numbers(n)

CROSS APPLY (SELECT DATEADD(MONTH, n, '2018-01-01')) AS dates(d)

LEFT JOIN products2018 ON YEAR(products2018.Date) = YEAR(d) AND MONTH(products2018.Date) = MONTH(d)

GROUP BY d

ORDER BY d

This uses a Calendar Table instead, but perhaps this get's you what you are after?

WITH CTE AS(

    SELECT CT.CalendarDate,

           CT.CalendarMonth,

           CT.CalendarMonthName,           

           P.GTIN,

           P.[Location],

           P.[Date],

           MIN(P.[date]) OVER (PARTITION BY P.GTIN) AS MinDate

    FROM dbo.CalendarTable CT

         LEFT JOIN dbo.Products P ON CT.CalendarDate = P.[date]

    WHERE CT.CalendarYear IN (2017,2018))

SELECT CTE.CalendarMonth,

       CTE.CalendarMonthName,

       COUNT(DISTINCT CASE WHEN CTE.MinDate > '20180101' THEN CTE.GTIN END) AS Products

FROM CTE

GROUP BY CTE.CalendarMonth,

         CTE.CalendarMonthName

ORDER BY CTE.CalendarMonth;