get column data in stored procedure when schema is reversed

Question

In a SQL 2008 DB there are 2 tables of data that I need to use to build a result in a stored procedure. Table 1 looks something like this.

date         name  hour   amount   price 
------------------------------------------
2009-10-12   tom   12     20       15.43 
2009-10-13   fred  16     -10      6.98

Table 2 looks like this. Note that each hour is in a separate column versus all hours in a single column in table 1.

date         name   H12     H16
--------------------------------
2009-10-12   tom    15.75   0
2009-10-13   fred   0       12.54

I need to write a stored procedure that ends up with a result that looks like this.

date         name   hour   amount   price   result     actualsold 
---------------------------------------------------------------------
2009-10-12   tom    12     20       15.43   positive   15.75  
2009-10-13   fred   16    -10       6.98    negative   12.54

I can get most of the result I need doing this . . . yes, the easy part.

SELECT date, 
       name, 
       hour, 
       amount, 
       price, 
       case 
         when amount > 0 then 'positive' 
         when amount < 0 then 'negative' 
       end as result
FROM Table1

How do I get the value from Table 2 and add this to the actualsold column (preferable without using a cursor)?

I do not have control over the table schemas. The DB is running on SQL 2008 ENT.

Answer 1

You said you can change the table schema, but can you create views? If possible, I would create a view to rectify the structure of the second table, such as:

CREATE VIEW vw_Table2 AS
SELECT date, name, 12 as hour, H12 as sold
UNION ALL
SELECT date, name, 16 as hour, H16 as sold

From there, you could structure your query like this:

SELECT t1.date, 
    t1.name, 
    t1.hour, 
    t1.amount, 
    t1.price, 
    case 
     when t1.amount > 0 then 'positive' 
     when t1.amount < 0 then 'negative' 
    end as result,
    v2.sold as actualsold
FROM Table1 as t1
JOIN vw_Table2 as v2 ON
    v2.date = t1.date
    AND v2.name = t1.name
    AND v2.hour = t1.hour

Answer 2

Untested:

SELECT A.date, 
       A.name, 
       A.hour, 
       A.amount, 
       A.price, 
       case 
         when A.amount > 0 then 'positive' 
         when A.amount < 0 then 'negative' 
       end as result,
       (SELECT case A.hour when 1 then B.H1
                           when 2 then B.H2
                           ...
                           when 24 then B.H24 end
          FROM Table2 B
         WHERE A.date = B.date AND A.name = B.name) as actualsold
FROM Table1 A

This is assuming that you have columns for all hours (H1, ..., H24). If you only have H12 and H16, you can reduce the case list.

Answer 3

Use the UNPIVOT operator to get Table2 back into a normalised form (untested)

SELECT date,
       name,
       hour,
       Amount
FROM   Table2
UNPIVOT (Amount FOR [hour] IN ([H1], [H2], [H3], [H4], ... etc ...)) AS unpvt;

Answer 4

I think the best way is either do a join against an UNPIVOT, or do a cross-join of Table2 against the numbers with a CASE statement to pull out the columns. Neither way is always a clear winner, despite how nice the UNPIVOT syntax can be.

Sometimes the cross-join outperforms UNPIVOT and sometimes the other way around. Here is the cross-join method, which works even in SQL 2000:

SELECT
   A.date, 
   A.name, 
   A.hour, 
   A.amount, 
   A.price,
   result = 
      CASE
         WHEN A.amount > 0 THEN 'positive' 
         WHEN A.amount < 0 THEN 'negative'
      END,
   actualsold =
      CASE A.hour
         WHEN 12 THEN B.H12
         WHEN 16 THEN B.H16
      END
FROM
   Table1 A
   INNER JOIN Table2 B ON A.date = B.date and A.name = B.name

This is an implicit partial cross-join because Table1 has many rows per date & name but Table2 has only one row per date & name. So each row in Table2 gets repeated many times in the join, but we use a different column from each one.

Unpivoting would be similar, putting the UNPIVOT query in a derived table or CTE:

SELECT
   A.date,
   A.name,
   A.hour,
   amount,
   A.price,
   result =
      CASE
      WHEN A.amount > 0 THEN 'positive'
      WHEN A.amount < 0 THEN 'negative'
      END,
   B.actualsold
FROM
   Table1 A
   INNER JOIN (
      SELECT *
      FROM (SELECT date, name, [12] = H12, [16] = H16 FROM Table2) X
         UNPIVOT (actualsold FOR hour IN ([12], [16])) U
   ) B ON A.date = B.date and A.name = B.name AND A.hour = B.hour

The execution plans for the two queries above are very similar, though the cross-join method is slightly simpler, but this doesn't prove anything about actual performance until tried against real data in full tables with many rows. The UNPIVOT does an extra LEFT LOOP JOIN that the cross-join method doesn't.

One potential drawback of the UNPIVOT method is that if you want to specify criteria to limit date ranges and names, you'll probably need to do it in two places so the server doesn't unpivot the whole dang table (though it might be smart enough not to do this, I don't know). While the big 24-condition CASE statement is unwieldy, UNPIVOT has its own awkwardness to convert the column names to just numbers.

Here is the setup code for anyone that would like to see these queries in action:

USE tempdb
CREATE TABLE Table1 (
    date smalldatetime,
    name varchar(10),
    hour int,
    amount int,
    price decimal(15,2)
)

CREATE TABLE Table2 (
    date smalldatetime,
    name varchar(10),
    H12 decimal(15,2),
    H16 decimal(15,2),
)

INSERT Table1 VALUES ('20091012', 'tom', 12, 20, 15.43)
INSERT Table1 VALUES ('20091013', 'fred', 16, -10, 6.98)
INSERT Table2 VALUES ('20091012', 'tom', 15.75, 0)
INSERT Table2 VALUES ('20091013', 'fred', 0, 12.54)