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
CodePudding user response：
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)