Revisions to Count dated entries between dated milestones from 2 tables

added links to other questions

Source Link

edited Nov 11, 2021 at 20:13

11
4

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

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

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

deleted 39 characters in body

Source Link

edited Nov 11, 2021 at 19:57

sullivan.syd

11
4

This is sort of a shot in the dark but tryTry 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

This is sort of a shot in the dark but 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

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

Source Link

created Nov 11, 2021 at 19:40

sullivan.syd

11
4

This is sort of a shot in the dark but 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

Stack Exchange Network

Return to Answer