CODEWITHSUNDEEP
sqlsql-servert-sqldatejoin
Francisco Lemos
Francisco Lemos

Reputation: 63

TSQL to find Null values

I have the following tables:

Product 

PRODUCT_ID  PRODUCT_NAME
1           SCREW1.20
2           SCREW1.23
3           SCREW2.5
4           SCREW3.10`

Sales

SALE_ID  PRODUCT_ID  YEAR   QUANTITY    PRICE
1        1           2019   14          1.64
2        2           2011   20          2.13
3        3           2018   22          2.29
4        4           2019   18          2.02
5        1           2016   13          2.42
6        2           2019   13          2.36
7        3           2013   20          2.38
8        4           2018   20          2.20
9        1           2010   23          1.69
10       2           2016   22          1.84
11       3           2015   12          1.88
12       4           2009   23          2.47
13       1           2019   14          1.57
14       2           2013   16          1.32
15       3           2016   17          1.66
16       4           2016   16          2.11
17       1           2009   13          2.33
18       2           2015   13          1.84
19       3           2016   12          1.73
20       4           2015   19          1.98
21       1           2018   22          2.17
22       2           2014   21          1.63
23       3           2009   19          1.85
24       4           2010   14          2.17
25       1           2014   13          2.09
26       2           2018   13          1.78
27       3           2011   20          1.34
28       4           2020   22          2.28
29       1           2013   16          1.96
30       2           2016   13          1.37
31       3           2010   21          1.53
32       4           2012   22          2.53
33       1           2019   18          2.57
34       2           2015   22          1.69
35       3           2020   12          2.18
36       4           2019   18          2.63
37       1           2011   21          2.05
38       2           2010   18          1.47
39       3           2012   15          2.37
40       4           2016   21          2.15
41       1           2017   20          1.28
42       2           2019   17          2.13
43       3           2017   14          2.27
44       4           2017   13          1.63
45       1           2009   23          1.89
46       2           2010   13          2.49
47       3           2020   12          2.19
48       4           2009   16          2.34
49       1           2018   13          2.37
50       2           2013   23          1.24
51       3           2011   21          1.58
52       4           2020   21          1.96
53       1           2014   14          1.53
54       2           2015   12          1.85
55       3           2014   13          1.23
56       4           2009   17          2.26
57       1           2017   22          2.41
58       2           2017   20          2.19
59       3           2016   13          1.47
60       4           2018   17          2.05
61       1           2020   15          2.22
62       2           2020   18          1.63
63       3           2016   15          2.67
64       4           2012   18          2.40
65       1           2018   17          2.16
66       2           2017   19          1.57
67       3           2014   23          1.82
68       4           2017   22          2.27
69       1           2019   16          2.65
70       2           2011   16          2.37
71       3           2014   20          1.53
72       4           2014   17          1.81
73       1           2009   23          1.40
74       2           2010   16          2.13
75       3           2013   20          2.27
76       4           2016   17          2.09
77       1           2013   19          2.30
78       2           2016   14          1.40
79       3           2012   12          1.75
80       4           2013   15          1.41
81       1           2015   21          2.30
82       2           2019   16          1.81

I want to find for each year the products which do not have any sales at all

So my train of thought is to find the distinct years for the sales, for each of these iterate for every product, for each product determine the sales: sum(quantity * price).

The result would have lines like these

YEAR    PRODUCT_NAME   Sales
(...)
2011    SCREW3.10      NULL
2012    SCREW3.10      98.96 (<- this record should not appear)
(...)

Upvotes: 0

Views: 61

Answers (2)

eriksv88
eriksv88

Reputation: 3526

;WITH Sales_CTE
AS
(
    SELECT S.*
        FROM Sales AS S
),
Products_CTE
AS
(
    SELECT PRODUCT_ID,PRODUCT_NAME
        FROM Products
),Years
AS 
(
    SELECT DISTINCT  [Year]
        FROM Sales_CTE AS S
        GROUP BY [YEAR]
)
SELECT Y.[YEAR],P.PRODUCT_NAME
    FROM Years AS Y
    CROSS JOIN Products_CTE AS P
    WHERE NOT EXISTS(SELECT *
                        FROM Sales_CTE AS S 
                        WHERE Y.YEAR=S.YEAR AND P.PRODUCT_ID=S.PRODUCT_ID)

Demo: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=75ff5f5bc3d79e348d92b5924af12bad

Upvotes: 0

GMB
GMB

Reputation: 222722

You can cross join the products tabe with the distinct years available in sales, and then bring the sales table with a left join. When the left join comes up empty, you know you have a product that was not sold on that particular year:

select p.*, y.year
from products p
cross join (select distinct year from sales) y
left join sales s on s.year = y.year and s.product_id = p.product_id
where s.sale_id is null

You can also express this with not exists instead of left join ... where ... is null:

select p.*, y.year
from products p
cross join (select distinct year from sales) y
where not exists (
    select 1 from sales s where s.year = y.year and s.product_id = p.product_id
)

Upvotes: 1

Related Questions