How to query table based on specific rows in another table using SQL SELECT

There's a table with data for several teams that looks like this:

original_dates:

   date    |   team_id   |  value
---------------------------------
2019-01-01 |      1      |    13
2019-01-01 |      2      |    88
2019-01-02 |      1      |    17
2019-01-02 |      2      |    99  
2019-01-03 |      1      |    26  
2019-01-03 |      2      |    105
2019-01-04 |      1      |    49
2019-01-04 |      2      |    134
2019-01-04 |      1      |    56
2019-01-04 |      2      |    167

However, on a certain date, we want to reset that day's value to 0, set all previous dates with that ID to 0, and subtract that value from all following dates, with a minimum of 0. Here's a table of dates that need to be reset:

inflection_dates:

   date    |   team_id   |  value
-----------------------------------
2019-01-02 |      2      |    99
2019-01-03 |      1      |    26

And here's the resulting table, which I'm hoping to achieve:

result:

   date    |   team_id   |  value
---------------------------------
2019-01-01 |      1      |    0    
2019-01-01 |      2      |    0     
2019-01-02 |      1      |    0     
2019-01-02 |      2      |    0    <- row in inflection_dates (value was 99)
2019-01-03 |      1      |    0    <- row in inflection_dates (value was 26)
2019-01-03 |      2      |    6     (-99)
2019-01-04 |      1      |    23    (-26)
2019-01-04 |      2      |    35    (-99)
2019-01-04 |      1      |    30    (-26)
2019-01-04 |      2      |    68    (-99)

The only constraint is that all tables are read only, so I can only query them and not modify them.

Does anyone know if this might be possible?

2 answers

  • answered 2019-12-06 20:11 zip

    Try this:

     drop table #tmp
    ---------------------------------
    select '2019-01-01' as date, 1 as team_id, 13 as value into #tmp
    union select '2019-01-01', 2, 88
    union select '2019-01-02', 1, 17
    union select '2019-01-02', 2, 99  
    union select '2019-01-03', 1, 26  
    union select '2019-01-03', 2, 105
    union select '2019-01-04', 1, 49
    union select '2019-01-04', 2, 134
    union select '2019-01-04', 1, 56
    union select '2019-01-04', 2, 167
    
     drop table #tmpinflection
    ---------------------------------
    select '2019-01-02' as date, 2 as team_id, 99 as value  into #tmpinflection
    union select '2019-01-03', 1, 26 
    
    
    select a.date, a.team_id, 
    case when a.date <= b.date then 0 
    else a.value - b.value end as value
     from #tmp a left join #tmpinflection b on a.team_id = b.team_id where b.date is not null
    

  • answered 2019-12-06 20:13 forpas

    With a join of the tables and a CASE expression to calculate the new value:

    select o.date, o.team_id,
      case 
        when o.date <= i.date then 0
        else o.value - i.value
      end value
    from original_dates o inner join inflection_dates i
    on i.team_id = o.team_id
    

    See the demo (for MySql but it's standard SQL).
    Results:

    | date                | team_id | value|
    | ------------------- | ------- | ---- |
    | 2019-01-01 00:00:00 | 1       | 0    |
    | 2019-01-01 00:00:00 | 2       | 0    |
    | 2019-01-02 00:00:00 | 1       | 0    |
    | 2019-01-02 00:00:00 | 2       | 0    |
    | 2019-01-03 00:00:00 | 1       | 0    |
    | 2019-01-03 00:00:00 | 2       | 6    |
    | 2019-01-04 00:00:00 | 1       | 23   |
    | 2019-01-04 00:00:00 | 2       | 35   |
    | 2019-01-04 00:00:00 | 1       | 30   |
    | 2019-01-04 00:00:00 | 2       | 68   |