SQLTeam.com | Weblogs | Forums

Match a table with sub-total values to its detail value table

tsql
sql2014

#1

I have two tables one with details (Date & Value) & the other with sub totals (Date, SubTotalValue & Referance).
I'm searching for a way to match these two tables by going backward from sub total to detail.

Daily totals values of two tables are equal.
Task is to, select for each day the rows from detail table where the total amount is match with the Amount field of each row in subtotal table.
And then insert the relevent referance field from sub-total table into detail table.
I'm not sure this is possible or not.

CREATE TABLE [TEST].[dbo].[subtotal]
(date DATE , amount INT , ref INT)

INSERT INTO [TEST].[dbo].[subtotal] VALUES
('2017-01-01',10000,1),
('2017-01-01',15000,2),
('2017-01-02',1000,4),
('2017-01-02',3000,3),
('2017-01-03',1000,5)

CREATE TABLE [TEST].[dbo].[detail]
(date DATE , amount INT)

INSERT INTO [TEST].[dbo].[detail] VALUES
('2017-01-01',4000),
('2017-01-01',6000),
('2017-01-01',7000),
('2017-01-01',8000),
('2017-01-02',1500),
('2017-01-02',1500),
('2017-01-02',600),
('2017-01-02',400),
('2017-01-03',1000)

I'm expecting a result like this.
Date Amount Ref
2017-01-01 4000 1
2017-01-01 6000 1
2017-01-01 7000 2
2017-01-01 8000 2
2017-01-02 1500 3
2017-01-02 1500 3
2017-01-02 600 4
2017-01-02 400 4
2017-01-03 1000 5

Highly appreciate if someone could help e to achive this.