Count dated entries between dated milestones from 2 tables

Question

I have a set of milestones with random dates on table 1 and a set of items with random dates on table 2 (see below).

Table 1

Milestone	MilestoneDate
A	01/01/2021
B	03/15/2021
C	05/27/2021
D	09/09/2021

Table 2 (this table has hundreds of entries with different dates spanning the entire year)

ItemID	ItemCreateDate
1	01/17/2021
2	01/18/2021
3	01/25/2021
4	02/12/2021
...	...

I need to count the number of entries from table 2 with dates that fall between each milestone (see below).

Milestone	Items
A	24
B	31
C	44
D	37

My current query is:

SELECT
    tbl2.Milestone
    ,COUNT(DISTINCT tbl1.ItemID) 'Items'
FROM
    tbl1
        FULL JOIN tbl2
        ON tbl1.CreateDate = tbl2.MilestoneDate
GROUP BY
    tbl2.Milestone,tbl2.MilestoneDate
ORDER BY
    tbl2.MilestoneDate

Milestone	ItemCount
NULL	136

I've tried any number of configurations to solve this, including just using the date instead of the milestone name, dropping the ORDER BY, as well as totally different coding approaches. What is the best way to solve this issue? NOTE: I cannot use the keys present in either table because they would result in tracking something totally different. I'm not the dba for this database, so I cannot adjust the tables themselves.

Currently I am having to pull each of these values manually by specifying each date range and running the query, then another date range and running (there are dozens of milestones with new ones being added constantly).

Answer 1

Try this and let me know what you get:

SELECT
    tbl2.Milestone,
        (SELECT COUNT(DISTINCT tbl1.ItemID)
         FROM tbl1,
         WHERE tbl1.CreateDate >= tbl2.MilestoneDate AND
               tbl1.CreateDate < LEAD(tbl2.MilestoneDate) OVER (ORDER BY tbl2.MilestoneDate))
FROM
    tbl1
    tbl2
WHERE
    'tbl1.<column> = tbl2.<column> (see below)'
    tbl1.CreateDate >= MIN(tbl2.MilestoneDate) 'AND
    tbl1.CreateDate < <insert max date, if desired>'
GROUP BY
    tbl2.Milestone,tbl2.MilestoneDate
ORDER BY
    tbl2.MilestoneDate

Also, your full join would exclude any ItemID with a CreateDate that is not equal to a MilestoneDate (unless I've completely lost my mind from staring at code all day!). Are there any columns in the two tables that match up and would always be the same? (ex: both tables have a "customerID" field that should match for every line - if so, add that to your WHERE clause) Do you need any data from tbl1, or just the item count?

If what's above doesn't work, I would try this next:

SELECT
    tbl2.Milestone,
    COUNT(DISTINCT tbl1.ItemID)
FROM
    tbl2
        JOIN tbl1
        ON tbl1.CreateDate >= tbl2.MilestoneDate AND
           tbl1.CreateDate < LEAD(tbl2.MilestoneDate) OVER (ORDER BY tbl2.MilestoneDate))
GROUP BY
    tbl2.MilestoneDate, tbl2.Milestone, COUNT(DISTINCT tbl1.ItemID)
ORDER BY
    tbl2.MilestoneDate

Some other useful references:

Get next row (for finding next milestone date)

Subquery with COUNT in SELECT clause

Answer 2

I was able to make this work. The approach amounts to ordering the milestone table by date, then pulling a table that lists each milestone and date as well as the milestone and date in the row before. This allows you to pull ItemIDs with creation dates between the 2 dates that are now in each row (this is done as part of the JOIN).

This approach also makes it very easy to insert various filter criteria at different stages of the query. For example, if you wanted to limit which milestones get populated in your data you could add a WHERE statement to the inner most or middle sub-query. You can then filter tbl1 using the outermost query.

SELECT
    A.PreviousMilestone 'Milestone'
    ,A.PreviousMilestoneDate 'Milestone Date'
    ,COUNT(DISTINCT tbl1.ItemID) 'Total'
FROM    
    (
        SELECT
            currentmilestone 'Milestone'
            ,milestonedate 'MilestoneDate'
            ,lastmilestone 'PreviousMilestone'
            ,lastmilestonedate 'PreviousMilestoneDate'
        FROM
            (
                SELECT
                    tbl2.MilestoneDate AS milestonedate
                    ,tbl2.Milestone AS currentmilestone
                    ,LAG(tbl2.Milestone) OVER (ORDER BY tbl2.MilestoneDate) AS lastmilestone
                    ,LAG(tbl2.MilestoneDate) OVER (ORDER BY tbl2.MilestoneDate) AS lastmilestonedate
                FROM
                    tbl2
            ) as M
        WHERE
            --filter criteria here
    ) AS A
        JOIN tbl1
        ON tbl1.CreateDate >= A.PreviousMilestoneDate and tbl1.CreateDate < A.MilestoneDate
WHERE
    --filter criteria here
GROUP BY
    A.PreviousMilestone
    ,A.PreviousMilestoneDate