September 3, 2015 at 3:28 am
hi,
what would be the best way to do the join I have in the example file? It is getting a bit too difficult for me... 🙂
I tried to do:
SELECT tb2.date,tb1.contract,tb1.amount FROM tb2 LEFT OUTER JOIN
ON tb1.date=tb2.date
GROUP BY tb2.date,tb1.contract,tb1.amount
but the problem is that I want to get for example 2.1.2016 for each contract but now I only get it once because such a date doesn't exist in the tb1 table for any contract. So, something more complex is needed
br,
Jack
September 3, 2015 at 4:34 am
Hi Jack
Please check this out:
WITH rt (rn, date,contact,ammout) AS
(SELECT ROW_NUMBER() OVER (PARTITION BY s1.contact ORDER BY s2.date) AS rn ,s2.date,s1.contact,s1.amount from tb2 AS s2 LEFT OUTER JOIN tb1 AS s1 ON s2.date >= s1.date)
SELECT rt.date,rt.contact,rt.ammout FROM rt INNER JOIN
(SELECT date,contact,max(rn) as rn FROM rt
GROUP BY contact,date) as t ON rt.rn =t.rn AND rt.date = t.date and rt.contact = t.contact
ORDER BY rt.contact,rt.date;
Best regards
Mike
September 3, 2015 at 6:19 am
-- sample data:
DROP TABLE #source_table_1
CREATE TABLE #source_table_1 ([contract] VARCHAR(2), [date] DATE, amount INT)
INSERT INTO #source_table_1 ([contract], [date], amount) VALUES
('XX','20160101',100),
('XX','20160104',200),
('YY','20160101',300),
('YY','20160104',400),
('ZZ','20160101',500),
('ZZ','20160104',600)
DROP TABLE #source_table_2
CREATE TABLE #source_table_2 ([date] DATE)
INSERT INTO #source_table_2 ([date]) VALUES
('20160101'),
('20160102'),
('20160103'),
('20160104')
-- query:
SELECT matrix.[contract], matrix.[date], st1.amount
FROM (
SELECT t1.[contract], t2.[date]
FROM (SELECT DISTINCT [contract] FROM #source_table_1) t1
CROSS JOIN #source_table_2 t2
) matrix
LEFT JOIN #source_table_1 st1
ON st1.[contract] = matrix.[contract] AND st1.[date] = matrix.[date]
-- Can you explain what the rules are for filling in the NULL values?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 3, 2015 at 7:21 am
As long as you really are on SQL Server 2012, the following will jive you the desired result...
IF OBJECT_ID('tempdb..#Table1') IS NOT NULL
DROP TABLE #Table1;
CREATE TABLE #Table1 (
Contract CHAR(2),
[Date] DATE,
Amount MONEY
);
IF OBJECT_ID('tempdb..#Table2') IS NOT NULL
DROP TABLE #Table2;
CREATE TABLE #Table2 (
[Date] DATE
);
INSERT #Table1 (Contract,Date,Amount) VALUES
('XX', '20160101',100),
('XX', '20160104',200),
('YY', '20160101',300),
('YY', '20160104',400),
('ZZ', '20160101',500),
('ZZ', '20160104',600);
INSERT #Table2 (Date) VALUES
('20160101'),
('20160102'),
('20160103'),
('20160104');
WITH CrossDates AS (
SELECT
c.Contract,
t2.Date
FROM (
SELECT DISTINCT t1.Contract
FROM #Table1 t1
) c
CROSS JOIN #Table2 t2
)
SELECT
cd.Contract,
cd.Date,
MAX(t1.Amount) OVER (PARTITION BY cd.Contract ORDER BY cd.Date) AS Amount
FROM
#Table1 t1
FULL JOIN CrossDates cd
ON t1.Contract = cd.Contract
AND t1.Date = cd.Date
ORDER BY
cd.Contract,
cd.Date
Results...
Contract Date Amount
-------- ---------- ---------------------
XX 2016-01-01 100.00
XX 2016-01-02 100.00
XX 2016-01-03 100.00
XX 2016-01-04 200.00
YY 2016-01-01 300.00
YY 2016-01-02 300.00
YY 2016-01-03 300.00
YY 2016-01-04 400.00
ZZ 2016-01-01 500.00
ZZ 2016-01-02 500.00
ZZ 2016-01-03 500.00
ZZ 2016-01-04 600.00
September 21, 2015 at 4:45 am
Thanks a lot guys for your answers, it has been really busy times, so apologies for a late reply. Unfortunately I read the best practices in posting questions after I asked mine. So, for the next one I'll add a code that inserts the data into tempdb! Sorry for the extra work you had to do. 🙂
The answers you gave are really cool, only small part missing. The code from Chris creates NULL amounts for dates not included in table_1. Amounts should be based on the date. So, for contract XX if date is <4.1.2016 amount should be 100 and if date=4.1.2016 it should be 200.
The code sent by Jason works otherwise but the logic to fill NULL amounts isn't the MAX as in the script. So, if the amount for XX in 4.1.2016 would be 50 the script would return wrong result for 4.1.2016: 100 instead of 50.
Any ideas how to implement the logic above? Thanks already for your answers, have helped a lot!
br,
Jack
September 21, 2015 at 12:33 pm
jaakkojuntunen (9/21/2015)
Thanks a lot guys for your answers, it has been really busy times, so apologies for a late reply. Unfortunately I read the best practices in posting questions after I asked mine. So, for the next one I'll add a code that inserts the data into tempdb! Sorry for the extra work you had to do. 🙂The answers you gave are really cool, only small part missing. The code from Chris creates NULL amounts for dates not included in table_1. Amounts should be based on the date. So, for contract XX if date is <4.1.2016 amount should be 100 and if date=4.1.2016 it should be 200.
The code sent by Jason works otherwise but the logic to fill NULL amounts isn't the MAX as in the script. So, if the amount for XX in 4.1.2016 would be 50 the script would return wrong result for 4.1.2016: 100 instead of 50.
Any ideas how to implement the logic above? Thanks already for your answers, have helped a lot!
br,
Jack
Try this:
IF OBJECT_ID('tempdb..#Table1') IS NOT NULL
DROP TABLE #Table1;
CREATE TABLE #Table1 (
Contract CHAR(2),
[Date] DATE,
Amount MONEY
);
IF OBJECT_ID('tempdb..#Table2') IS NOT NULL
DROP TABLE #Table2;
CREATE TABLE #Table2 (
[Date] DATE
);
INSERT #Table1 (Contract,Date,Amount) VALUES
('XX', '20160101',100),
('XX', '20160104',200),
('YY', '20160101',300),
('YY', '20160104',400),
('ZZ', '20160101',500),
('ZZ', '20160104',600);
INSERT #Table2 (Date) VALUES
('20160101'),
('20160102'),
('20160103'),
('20160104');
SELECT t2.Date, t1.Contract, t1.Amount
FROM #Table1 t1
CROSS JOIN #Table2 t2
WHERE NOT EXISTS
(
SELECT 1
FROM #Table1 t1v2
WHERE t1v2.Date > t1.Date
AND t1v2.Date <= t2.Date
)
GROUP BY t1.Contract, t1.Date, t1.Amount, t2.Date
HAVING t1.Date <= t2.Date
September 21, 2015 at 2:10 pm
jaakkojuntunen (9/21/2015)
Any ideas how to implement the logic above? Thanks already for your answers, have helped a lot!br,
Jack
It would be helpful if you were to supply some representative test data. Without that, we're all just guessing.
September 25, 2015 at 6:55 am
Thanks a lot guys for your help! I'll try to form a better question next time! 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply