SQL Joining with single dates when Multiple Dates occur but limited by a frequency count

user_8972127 0
Viewed 47

In SQL I am looking to join two tables with multiple dates in one table linked to one date in another table which I want restricted by the frequency from one table. Instead I keep getting a total sum of all my records for all the frequencies.

I've tried joins, where clauses and subqueries but keep getting it wrong. I've recently been directed to Dibble and felt this was the best way to demo the problem:

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=a9fec08a4ec5130c93da6ed663259da4

Example tables are:

CREATE TABLE dbo.Action
(
ActionWeek date, 
ActionYear int,
Region nvarchar(50), 
Action nvarchar(50), 
ActionAmount int, 
Frequency int);

INSERT INTO Action values
('2019-01-01' , 2019, 'Wales', 'abc123', 123, 1),
('2019-03-10' , 2019, 'Wales', 'abc123', 132, 2),
('2019-06-01' , 2019, 'Wales', 'abc123', 153, 3),
('2020-01-11' , 2020, 'Wales', 'abc123', 123, 1),
('2019-01-01' , 2019, 'Australia', 'abd133', 723, 1),
('2019-08-01' , 2019, 'Australia', 'abd133', 763, 2),
('2020-04-01' , 2020, 'Australia', 'abd133', 823, 1),
('2019-01-01' , 2019, 'Mexico', 'cbc423', 3123, 1),
('2020-03-01' , 2020, 'Mexico', 'cbc423', 3223, 1),
('2020-05-01' , 2020, 'Mexico', 'cbc423', 3423, 2),
('2020-08-21' , 2020, 'Mexico', 'cbc423', 3623, 3),
('2021-01-01' , 2021, 'Mexico', 'cbc423', 3723, 1),
('2021-09-01' , 2021, 'Mexico', 'cbc423', 3823, 2)

-- Second Table
CREATE TABLE dbo.Response
(
ResponseYear int,
ResponseAction nvarchar(50),
ResponseDate date, 
ResponseWeek date, 
ResponseValue float,
);


INSERT INTO Response values
(2019, 'abc123', '2019-02-04', '2019-02-01', 50),
(2019, 'abc123', '2019-02-08', '2019-02-08', 25),
(2019, 'abc123', '2019-09-24', '2019-09-24', 100),
(2020, 'abc123', '2020-01-20', '2019-01-23', 5),
(2021, 'abc123', '2021-06-10', '2021-06-07', 3),
(2019, 'abd133', '2019-01-10', '2019-01-07', 15),
(2019, 'abd133', '2019-02-10', '2019-02-04', 25),
(2019, 'abd133', '2019-03-10', '2019-03-04', 35),
(2021, 'cbc423', '2021-01-13', '2021-01-11', 77)

Desired results would be:

A.ActionWeek / A.Action / A.Frequency / R.ResponseValue
2019-01-01 / abc123 / 1 / 75
2019-03-10 / abc123 / 2 / 0
2019-06-01 / abc123 / 3 / 100
2020-01-11 / abc123 / 1 / 8
2019-01-01 / abd133 / 1 / 75
2019-08-01 / abd133 / 2 / 0
2020-04-01 / abd133 / 1 / 0
2019-01-01 / cbc423 / 1 / 0
2020-03-01 / cbc423 / 1 / 0
2020-05-01 / cbc423 / 2 / 0
2020-08-21 / cbc423 / 3 / 0
2021-01-01 / cbc423 / 1 / 77
2021-09-01 / cbc423 / 2 / 0

Let me know if you other questions?

I have tried this:

SELECT  A.ActionWeek, A.Action, A.Frequency
, (SELECT SUM(R.ResponseValue)
 FROM Response R2
 JOIN Action A2 ON R2.Action and A2.Action
 WHERE A.Frequency = A2.Frequency
) AS ResponseValue
FROM Action A
JOIN Response R ON A.Action = R.ResponseAction 
GROUP BY A.ActionWeek, A.Action, A.Frequency
ORDER BY A.ActionWeek, A.Action, A.Frequency
subquery join sql-server sql
1 Answers

I have absolutely no idea what frequency has to do with this and you don't appear to need it given that it's simple to get the next date to use as a join test

with cte as
(select a.actionweek fromweek,a.action,a.frequency,
        lead(a.actionweek) over (partition by a.action order by a.actionweek) toweek
from  [action] a)
select cte.fromweek,cte.toweek, cte.action,cte.frequency,r.responseweek,r.responsevalue
       --sum(r.responsevalue) 
from cte
left join response r on cte.action = r.ResponseAction and  ResponseWeek between cte.fromweek and cte.toweek;

Results in

fromweek   toweek     action                                             frequency   responseweek responsevalue
---------- ---------- -------------------------------------------------- ----------- ------------ ----------------------
2019-01-01 2019-03-10 abc123                                             1           2019-02-01   50
2019-01-01 2019-03-10 abc123                                             1           2019-02-08   25
2019-01-01 2019-03-10 abc123                                             1           2019-01-23   5
2019-03-10 2019-06-01 abc123                                             2           NULL         NULL
2019-06-01 2020-01-11 abc123                                             3           2019-09-24   100
2020-01-11 NULL       abc123                                             1           NULL         NULL
2019-01-01 2019-08-01 abd133                                             1           2019-01-07   15
2019-01-01 2019-08-01 abd133                                             1           2019-02-04   25
2019-01-01 2019-08-01 abd133                                             1           2019-03-04   35
2019-08-01 2020-04-01 abd133                                             2           NULL         NULL
2020-04-01 NULL       abd133                                             1           NULL         NULL
2019-01-01 2020-03-01 cbc423                                             1           NULL         NULL
2020-03-01 2020-05-01 cbc423                                             1           NULL         NULL
2020-05-01 2020-08-21 cbc423                                             2           NULL         NULL
2020-08-21 2021-01-01 cbc423                                             3           NULL         NULL
2021-01-01 2021-09-01 cbc423                                             1           2021-01-11   77
2021-09-01 NULL       cbc423                                             2           NULL         NULL

(17 row(s) affected)

Changing this to sum

with cte as
(select a.actionweek fromweek,a.action,a.frequency,
        lead(a.actionweek) over (partition by a.action order by a.actionweek) toweek
from  [action] a)
select cte.fromweek,cte.action,cte.frequency,
       sum(r.responsevalue) 
from cte
left join response r on cte.action = r.ResponseAction and  ResponseWeek between cte.fromweek and cte.toweek
group by cte.action,cte.fromweek,cte.frequency
order by cte.action,cte.fromweek,cte.frequency

fromweek   action                                             frequency   
---------- -------------------------------------------------- ----------- ----------------------
2019-01-01 abc123                                             1           80
2019-03-10 abc123                                             2           NULL
2019-06-01 abc123                                             3           100
2020-01-11 abc123                                             1           NULL
2019-01-01 abd133                                             1           75
2019-08-01 abd133                                             2           NULL
2020-04-01 abd133                                             1           NULL
2019-01-01 cbc423                                             1           NULL
2020-03-01 cbc423                                             1           NULL
2020-05-01 cbc423                                             2           NULL
2020-08-21 cbc423                                             3           NULL
2021-01-01 cbc423                                             1           77
2021-09-01 cbc423                                             2           NULL
Warning: Null value is eliminated by an aggregate or other SET operation.

(13 row(s) affected)
user_61524000
Related