Menu
Normally I would just do this in the code itself, but I am curious if this can be accomplished efficiently in TSQL.
Table 1 Date - Value
Table 2 Date - Discount
Table 1 contains entries for each day. Table 2 contains entries only when the discount changes. A discount applied to a value is considered valid until a new discount is entered.
Example data:
Table 1 1/26/2010 - 10 1/25/2010 - 9 1/24/2010 - 8 1/24/2010 - 9 1/23/2010 - 7 1/22/2010 - 10 1/21/2010 - 11
Table 2 1/26/2010 - 2 1/23/2010 - 1 1/20/2010 - 0
What I need returned is the following: T1 Date - T1 Value - T2 Discount
Example data:
1/26/2010 - 10 - 2 1/25/2010 - 9 - 1 1/24/2010 - 8 - 1 1/24/2010 - 9 - 1 1/23/2010 - 7 - 1 1/22/2010 - 10 - 0 1/21/2010 - 11 - 0
Possible or am I better off just continuing to do this in the code?
721
The plus sign is Oracle syntax for an outer join. There isn't a minus operator for joins. An outer join means return all rows from one table. Also return the rows from the outer joined where there's a match on the join key. If there's no matching row, return null.
However, if you change the matching key in the join query from Name to ID and if there are a large number of rows in the table, then you will find that the inner join will be faster than the left outer join.
The join elimination rewrite optimization reduces the join degree of the query by eliminating tables from the query when it is safe to do so. Typically, this optimization is used when the query contains a primary key-foreign key join, and only primary key columns from the primary table are referenced in the query.
JOIN order doesn't matter, the query engine will reorganize their order based on statistics for indexes and other stuff. For test do the following: select show actual execution plan and run first query.
I believe this subquery will do it (not tested).
select *, (select top 1 Discount from table2 where table2.Date <= t.Date order by table2.Date desc) as Discount from Table1 t Perhaps not the most performant however.
Edit:
Test code:
create table #table1 ([date] datetime, val int) create table #table2 ([date] datetime, discount int) insert into #table1 ([date], val) values ('1/26/2010', 10) insert into #table1 ([date], val) values ('1/25/2010', 9) insert into #table1 ([date], val) values ('1/24/2010', 8) insert into #table1 ([date], val) values ('1/24/2010', 9) insert into #table1 ([date], val) values ('1/23/2010', 7) insert into #table1 ([date], val) values ('1/22/2010', 10) insert into #table1 ([date], val) values ('1/21/2010', 11) insert into #table2 ([date], discount) values ('1/26/2010', 2) insert into #table2 ([date], discount) values ('1/23/2010', 1) insert into #table2 ([date], discount) values ('1/20/2010', 0) select *, (select top 1 discount from #table2 where #table2.[date] <= t.[date] order by #table2.[date] desc) as discount from #table1 t drop table #table1 drop table #table2 Results:
2010-01-26 00:00:00.000 10 2 2010-01-25 00:00:00.000 9 1 2010-01-24 00:00:00.000 8 1 2010-01-24 00:00:00.000 9 1 2010-01-23 00:00:00.000 7 1 2010-01-22 00:00:00.000 10 0 2010-01-21 00:00:00.000 11 0
94
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
