Aggregate and join table with date columns falling between dates of consecutive rows of another table

Question

I am trying to join two tables one with date column on another table with datetime column. The dates of the table say A might be equal are fall in between datetime range of consecutive rows in table B. I want to aggregate the values falling between the dates of consecutive rows of table B. I made small example tables and expected outcome below. (Just a note: There tables shows here are available as views (temporary tables created using existing tables). The actual sizes of tables are much large with around half a million rows and about 50 columns each and the joining is done based on other columns also)

Table A

+---------------------+
|      DateTime       |
+---------------------+
| 2020-01-01 08:00:00 |
| 2020-01-04 08:00:00 |
| 2020-01-07 08:00:00 |
| 2020-01-07 09:00:00 |
| 2020-01-08 08:00:00 |
| 2020-01-11 08:00:00 |
| 2020-01-14 08:00:00 |
| 2020-01-14 15:00:00 |
| 2020-01-16 08:00:00 |
| 2020-01-19 10:00:00 |
+---------------------+

Table B

+------------+-------+
|    Date    | value |
+------------+-------+
| 2020-01-01 |     2 |
| 2020-01-02 |     7 |
| 2020-01-04 |     5 |
| 2020-01-05 |     1 |
| 2020-01-06 |     1 |
| 2020-01-08 |     6 |
| 2020-01-10 |     8 |
| 2020-01-11 |     4 |
| 2020-01-13 |     7 |
| 2020-01-17 |     6 |
+------------+-------+

The resultant table I am looking for should be as follows

Table C

+--------------------+-------+
|      DateTime      | Value |
+--------------------+-------+
| 2020-01-0108:00:00 |     9 |
| 2020-01-0408:00:00 |     7 |
| 2020-01-0708:00:00 |     0 |
| 2020-01-0807:00:00 |    14 |
| 2020-01-0808:00:00 |    14 |
| 2020-01-1108:00:00 |    11 |
| 2020-01-1408:00:00 |     0 |
| 2020-01-1415:00:00 |     0 |
| 2020-01-1608:00:00 |     6 |
| 2020-01-1910:00:00 |     0 |
+--------------------+-------+

Looking forward for suggestions and solutions.

Thank you in advance.

Which dbms are you using? (When it comes to date/time many products are far from ANSI SQL compliant.) — jarlh, Jul 16 '20 at 13:05
Welcome to Stack Overflow. You will have a better experience here if you take the [Tour](https://stackoverflow.com/tour) and read through [How To Ask](https://stackoverflow.com/help/how-to-ask), then write your questions with the details needed to create [a Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example). See [How to post a T-SQL question on a public forum](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for tips. — Eric Brandt, Jul 16 '20 at 13:30

score 0 · Answer 1 · answered Nov 23 '20 at 22:42

This might not be the complete solution but you can do something like this..

WITH T1 AS (
SELECT
    a.*, LEAD(COLUMN1, 1) OVER(
ORDER BY
    COLUMN1) AS LEAD_COL  --Use Lead function to get next line value
FROM
    TABLE_A a)
SELECT
    COLUMN1,
    CAST(COLUMN1 AS DATE) AS NEW_COL1, -- This is to convert timestamps to dates
    CAST(LEAD_COL AS DATE) AS NEW_COL2,
    TABLE_B.*
FROM
    T1
JOIN TABLE_B ON
    (DATE_COL >= CAST(COLUMN1 AS DATE)
    AND DATE_COL <CAST(LEAD_COL AS DATE) ) -- JOIN WITH TABLE B . AFTER this sum the records TO GET FINAL OUTPUT

Aggregate and join table with date columns falling between dates of consecutive rows of another table

1 Answers1