SQL Server: Create new query that matches prices and values from two separate queries

I have a database problem that I have no idea how to code so any kind of help will be much appreciated.

Basically it is matching different schedules with their corresponding prices.

  • SQL Server : check if all rows exists in other table
  • SQL Server: how to know if any row is referencing the row to delete
  • What is the comparative speed of temporary tables to physical tables in SQL?
  • Query that Counts records with a WHERE clause
  • difference xp_sendmail and sp_send_dbmail proc
  • Product quantity bound to colors and sizes table structures SQL
  • I have two queries . . .

    First query contains prices based on whether it is a peak hour (Hour 7 to 22 of the day) or Offpeak hour (all other hours of the day)

    SQLQuery1.sql 
    dte          Peak          Offpeak
    3-23-2015    32.6          17.12
    3-24-2015    34.98         17.21
    3-25-2015    31.87         19.45
    

    The Second query contains different values per hour

    WP-Schedule.sql
    CPT         Resource      1    2    3    4    5    6    7    8  . . . 24
    3-23-2015   WP1         -34  -34  -37  -29  -17    0  -23   -3        25
    3-23-2015   WP2         -36  -35  -36  -31  -19   14   15    0        34
    3-23-2015   WP3         -34  -34  -35  -31  -18   18   19    3        31
    3-24-2015   WP1         -32  -32  -32  -34  -19   15   21    0        30
    3-24-2015   WP2         -34  -32  -29  -32  -20   21   23    4        18
    3-24-2015   WP3         -36  -35  -36  -31  -19   14   15    0        34
    3-25-2015   WP1         -34  -34  -37  -29  -17    0   23    0        25
    3-25-2015   WP2         -32  -32  -32  -34  -19   15   21    0        30
    3-25-2015   WP3         -34  -32  -29  -32  -20   21   23    16       18
    

    What I am trying to do with these two queries is use the information to create a new query that shows me the price every hour for each resource. The conditions are that for every hour their is only a charge if the value in the WP-Schedule.sqlquery is less than 0. Also if the hour is a Peak hour then it should use the Peak price for that day and if not then use the Offpeak price.

    If I were to manually create what the first row should look like it would be this:

    dte/CPT    Resource   1     2      3      4      5      6    7    8 . . . 24
    3-23-2015  WP1     17.12  17.12  17.12  17.12   17.12   0  32.6 32.6       0
    

    Help on this coding would be much appreciated!!!!

    One Solution collect form web for “SQL Server: Create new query that matches prices and values from two separate queries”

    One way to do this is with 24 CASE expressions.

    SELECT ...
    ...
    CASE WHEN s.[1] < 0 THEN p.OffPeak ELSE 0 END AS [1],
    ...
    CASE WHEN s.[7] < 0 THEN p.Peak ELSE 0 END AS [7],
    ...
    FROM (YourFirstQuery) p
    JOIN (YourSecondQuery) s
      ON p.dte=s.CPT
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.