SQL Server Performance Forum – Threads Archive
Fun with Report Queries, Part 2
Hello again,<br /><br />This topic is around just in case there is a solution to Part 1. If you haven’t read Part 1, I’m basically at a dead-end trying to set up some views/queries for reporting. The cause of all my grief is that all the record filtering has to be done in the WHERE clause of the query.<br /><br />I have 2 tables, a and b. Table a has products, and table b has annual prices for each product. Table b has zero to many records for each record in table a. Part 1 is the task of retrieving all of a small set of records from table a and all matching records in table b in an efficient manner, all without using FROM clause subqueries or table-valued functions.<br /><br />Part 2 refines the Part 1 query slightly, returning at most 1 record from table b (a price for a specific year) for each record in table a. This could easily be done in the ON section of the JOIN clause, but again, the reporting restrictions dictate WHERE clause filtering only.<br /><br />Given the query from Part 1:<pre id="code"><font face="courier" size="2" id="code">select *<br />from a<br /> left hash join b<br /> on a.k1 = b.k1<br /> and a.k2 = b.k2<br /> and a.k3 = b.k3<br />where a.k1 = 1<br /> and a.k2 = 1</font id="code"></pre id="code">one might consider adding<pre id="code"><font face="courier" size="2" id="code">…<br /> and (b.k4 = 2000 or b.k4 is null)<br />…</font id="code"></pre id="code">However, this approach will eliminate rows where there is a price for 2002 and later, but no price for 2000.<br /><br />The closest I’ve gotten on this one is a view that cross joins table a with the invaluable numbers table:<pre id="code"><font face="courier" size="2" id="code">create view a_year<br />as<br /> select a.*, convert( smallint, nums.n ) year<br /> from a<br /> cross join nums</font id="code"></pre id="code">This view is then left joined to table b:<pre id="code"><font face="courier" size="2" id="code">select *<br />from a_year a<br /> left hash join b<br /> on a.k1 = b.k1<br /> and a.k2 = b.k2<br /> and a.k3 = b.k3<br /> and a.year = b.k4<br />where a.k1 = 1<br /> and a.k2 = 1<br /> and a.year = 2000</font id="code"></pre id="code">Using this setup generates the following plan:<pre id="code"><font face="courier" size="2" id="code"> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’![Frown :( :(](styles/default/xenforo/smilies/frown.png)
![Frown :( :(](styles/default/xenforo/smilies/frown.png)
![Frown :( :(](styles/default/xenforo/smilies/frown.png)
![Frown :( :(](styles/default/xenforo/smilies/frown.png)
![Frown :( :(](styles/default/xenforo/smilies/frown.png)
![Frown :( :(](styles/default/xenforo/smilies/frown.png)
![Frown :( :(](styles/default/xenforo/smilies/frown.png)
![Frown :( :(](styles/default/xenforo/smilies/frown.png)
![Frown :( :(](styles/default/xenforo/smilies/frown.png)
![Frown :( :(](styles/default/xenforo/smilies/frown.png)
![Frown :( :(](styles/default/xenforo/smilies/frown.png)
![Frown :( :(](styles/default/xenforo/smilies/frown.png)
]]>