I have this table that lists which months each product is available on the market. For example product 1 is available from Mar to Dec and product 2 is available from Jan to Feb.
| product_id | start_month | end_month |
|---|---|---|
| 1 | 3 | 12 |
| 2 | 1 | 2 |
| 3 | 4 | 6 |
| 4 | 4 | 8 |
| 5 | 5 | 5 |
| 6 | 10 | 11 |
I need to count how many product_ids each month of the year has but can't think of how to put: WHERE month >= start_month AND month >= end_month. Can I use a loop for this or would that be overkill>
I used dbFiddle to test out this solution. It's dependent on there being at least 1 product available for sale in each month. Although, maybe it's better that a month isn't returned when there isn't a product for sale?
Could use @derviş-kayımbaşıoğlu approach to generating the months, but not group on product_id, but on month instead.
with months as (
Select distinct start_month [month]
from Product
)
Select m.month
,count(*) [products]
from months m
left join Product p
on m.month >= p.start_month and m.month <= p.end_month
group by m.month
something like this needs to help but you may have syntax error since we don't know exact DBMS and version
select product_id, count(*) cnts
from table1
inner join (
select 1 month union
select 2 union
select 3 union
select 4 union
select 5 union
select 6 union
select 7 union
select 8 union
select 9 union
select 10 union
select 11 union
select 12 union
) t2
on t2.month between table1.start_month and table1.end_month
group by product_id
