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.

Answer 1

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