May 12, 2017 at 10:00 pm
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 by going backward from sub total to detail & insert a referance field from sub-total table to detail table.
I'm not sure this 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.
May 13, 2017 at 12:18 am
Explain the logic of how to do it. I don't think it can be done definitively. Say you have a total for two invoices on the same date one is (100,400) and the other is (200,300). Which goes with which? No way to tell.
May 13, 2017 at 8:12 am
Thanks for the reply. You may be correct.
However daily totals values of two tables are equal since these sub totals were derived from the detail table rows.
So total of one or several items of detail tabble has to be equal with the value of a particular item in sub total table.
May 13, 2017 at 12:27 pm
kelum0823 - Saturday, May 13, 2017 8:12 AMThanks for the reply. You may be correct.However daily totals values of two tables are equal since these sub totals were derived from the detail table rows.
So total of one or several items of detail tabble has to be equal with the value of a particular item in sub total table.
You haven't given us enough details. Specifically, your sub totals table shows two records each for 2017-01-01 and 2017-01-02, but your details table provides no way to generate two records for those dates. You are missing another field required for the grouping.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply