In Oracle SQL, is it possible have an effective dated query that also show rows that occur between dates?

Question

I have the following tables:

ProductGroup

|GroupID|Product Group|Product Date|
|-|-------|--------|--------|
|A|Bicycles|1/1/2018|
|A|Two-Wheels|12/1/2018|
|A|Sport Bicycles|6/1/2019|
|A|Fast Bicycles|1/1/2020|

SubGroup

|SubgroupID|GroupID|SubGroup|SubGroupDate|
|-|-|-----|-----|
|1|A    |wheels |06/01/2015|
|2|A    |tires  |10/01/2015|
|3|A    |spokes |01/01/2017|
|4|A    |chains |01/01/2019|
|5|A    |brakes |03/01/2019|

I join them using a maximum effective dated query:

Select ProductName, ProductDate, tSubGroup.SubGroup, tSubGroup.SubGroupDate
FROM ProductGroup 
Left Join (SELECT SubGroupName, SubGroupDate 
            FROM SubGroup 
            WHERE SubGroup.SubGroupDate = (Select max(SubGroupDate)
                                            FROM SubGroup B
                                            where b.SubGroupName = SubGroup.SubGroupDate
                                            ) ) tSubGroup  on tSubGroup.GroupID = ProductGroup.GroupID 
                                                            and tSubGroup.SubGroupDate <= ProductGroup.ProductDate

I get the following results as expected:

|ProductGroup   |ProductDate    |SubGroup   |SubGroupDate|
|---|---|---|---|
|Bicycles   |01/01/2018 |Spokes |01/01/2017|
|Two-Wheels |12/01/2018 |Spokes |01/01/2017|
|Sport Bicycles |06/01/2019 |Chains |01/01/2019|
|Fast Bicycles  |01/01/2020 |Brakes |03/01/2019|

But what I want is this:

|ProductGroup   |ProductDate    |SubGroup   |SubGroupDate|
|----|---|---|----|
|Bicycles   |01/01/2018 |Spokes |01/01/2017|
|Two-Wheels |12/01/2018 |Spokes |01/01/2017|
|Sport Bicycles |06/01/2019 |Chains |01/01/2019|
|Sport Bicycles |06/01/2019 |Brakes |03/01/2019|
|Fast Bicycles  |01/01/2020 |Brakes |03/01/2019|

In these results, there are two rows for Sport Bicycles, because the SubGroup Chains (1/1/2019) and Brakes (3/1/2019) occurred between the ProductGroup Two-Wheels (12/1/2018) and Sport Bicycles (6/1/2019).

I don't know how to combine the two tables to get all the rows, yet making sure there are no extra rows added. I tried using a FULL JOIN and different variations of LAG, LEAD, RANK, etc. I am just stuck on figuring this out.

Is there a way to produce the results I am looking for? Also, there will be three more columns to add to this when I am done.

Thank you for the help.

It would really help if you described what you are trying to accomplish. — Gordon Linoff, Dec 21 '20 at 22:03

score 0 · Answer 1 · answered Dec 21 '20 at 22:14

0

Hmmm . . . I think you want overlapping intervals. If so, lead() can help:

select pg.*, sg.*
from (select pg.*,
             lead(productdate) over (partition by groupid order by productdate) as next_productdate
      from productgroup pg
     ) pg join
     (select sg.*,
             lead(subgroupdate) over (partition by groupid order by subgroupdate) as next_subgroupdate
      from SubGroup sg
     ) sg
     on sg.groupid = pg.groupid and
        sg.subgroupdate >= p.productdate and
        (sg.subgroupdate < p.next_productdate or p.next_productdate is null)

answered Dec 21 '20 at 22:14

Gordon Linoff

1,242,037
58
646
786

Thank you for the SQL, it helped a great deal. This SQL, returns only two rows, how can I return all the rows? – Sacheltry Dec 23 '20 at 16:43
@Sacheltry . . . This returns only rows that overlap. You can keep all rows in the first table using `left join`. – Gordon Linoff Dec 23 '20 at 19:42
thank you so much! Because of your help, I got it working the way I wanted to. – Sacheltry Jan 05 '21 at 20:43

In Oracle SQL, is it possible have an effective dated query that also show rows that occur between dates?

1 Answers1