I'm trying to summarize some data about our suppliers, but I'm having trouble showing 0 for suppliers with no data. Simplified SQL code:
with daterange as
(
select \* from (values (2017),(2018),(2019),(2020),(2021),(2022),(2023)) y(year)
)
select
count(po.purchaseorderpk) 'Total PO'
,daterange.year
,po.SupplierFK
from purchaseorder po
left join daterange on dateRange.year = year(po.ClosedDate)
group by
daterange.year
,po.supplierfk
order by daterange.year, po.supplierfk
To limit to just two suppliers for testing, I've tried adding
and po.SupplierFK in( 781, 1176)
to the left join, as well as
where po.SupplierFK = 781 or po.SupplierFK is null or po.SupplierFK = 1176
With the where, I'm getting this result, which is close to what I want:
| Total PO | year | SupplierFK |
|---|---|---|
| 4 | NULL | NULL |
| 11 | NULL | 781 |
| 5 | NULL | 1176 |
| 32 | 2018 | 781 |
| 3 | 2018 | 1176 |
| 49 | 2019 | 781 |
| 1 | 2019 | 1176 |
| 28 | 2020 | 781 |
| 3 | 2020 | 1176 |
| 17 | 2021 | 781 |
| 2 | 2021 | 1176 |
| 18 | 2022 | 781 |
I would also expect to get these rows:
| Total PO | year | SupplierFK |
|---|---|---|
| 0 | 2017 | 1176 |
| 0 | 2017 | 781 |
| 0 | 2023 | 1176 |
| 0 | 2023 | 781 |
I was under the impression that by making the CTE calendar/tally table and left joining it to my data table, it would force the summary calculations for each year specified, even if 0, but I can't seem to get that result. This is just one of several summary calculations I'll need to do on this data, so any help is appreciated.
Thanks!
The order of the joins matters, a left join returns all of the records from the left table and the matching records from the right table.
Instead of:
from purchaseorder po
left join daterange on dateRange.year = year(po.ClosedDate)
Try:
from daterange dr
left join purchaseorder po on year(po.ClosedDate) = dr.year
You won't get the suppliers, unless there's an intermediate supplier table you can join on, but you will see the year.
Something like:
| Total PO | year | SupplierFK |
|---|---|---|
| 0 | 2017 | NULL |
| 0 | 2023 | NULL |
If you have a supplier table a cross join may work as you were expecting.
select count(po.purchaseorderpk) 'Total PO', dr.year, s.supplierpk
from daterange dr
cross join supplier s
left join purchaseorder po on year(po.ClosedDate) = dr.year and po.supplierfk = s.supplierpk
group by dr.year, s.supplierpk