1

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.

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!!!!

Ray
  • 41
  • 7
  • 1
    sql server or mysql? – S3S Jul 10 '17 at 14:47
  • sql-server , sorry for the confusion! – Ray Jul 10 '17 at 14:47
  • I'm a little confused trying to understand what you're trying to do. So between hours 7 and 22, if the hour value in your second table for that day is negative, use the Peak value? Structuring your data with the hours as columns makes it kind of difficult to work with those values without doing a PIVOT or writing a really big CASE statement query. – EMUEVIL Jul 10 '17 at 15:00
  • Yeah thats basically what it is. First it depends on if the value in the second table is negative. If its not then the price is just going to be 0. If it is negative, then the price is determined by whether it is a peak hour or offpeak hour. Peak hours are hrs 7-22 and use the peak price for that day. – Ray Jul 10 '17 at 15:08
  • You have to put your aliases inside [square brackets] as Tab Alleman stated in the comments of his answer. You also have to wrap your table name WP-Schedule in square brackets. Ideally you should avoid special characters and numbers as object names to avoid this hassle. – Sean Lange Jul 10 '17 at 15:51
  • Okay, you can't do DECLARE statements inside a query. Move those lines all to the top, before the first SELECT. Same with the SET statements. – Tab Alleman Jul 11 '17 at 14:48
  • Move this line to the bottom of the script: `order by CPT, resource` – Tab Alleman Jul 11 '17 at 15:33
  • Take out `select * from` after `JOIN` – Tab Alleman Jul 11 '17 at 15:35
  • Looks like you removed the open parenthesis after JOIN. Put it back. – Tab Alleman Jul 11 '17 at 15:42
  • It could look that way because you've got two of each column that has just a number for a name ([1],[2], etc.) Try changing the alias of each of the CASE expressions to something slightly different to distinguish from the Schedule amount. Like `Price1`, `Price2`, etc. – Tab Alleman Jul 11 '17 at 17:29

1 Answers1

2

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
Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • Hey i typed it all out but it didn't work. It reads that there is an error message saying "Incorrect syntax near '.1'- – Ray Jul 10 '17 at 15:31
  • Can you edit your question with the entire query you tried, so we can help you find the error? It's probably that you need to put your column names in brackets if you are really using numbers for column aliases. (So instead of `s.1`, use `s.[1]`. – Tab Alleman Jul 10 '17 at 15:33
  • Yes I just posted it – Ray Jul 10 '17 at 15:44
  • Oops, you need commas after each column in your select list. I have edited my answer. Also you know you can't literally use a .sql file in your FROM clause, right? It has to be a table, view, cte or derived table. – Tab Alleman Jul 10 '17 at 17:00
  • sorry if this is a dumb question btw Iv been thrust into an internship where I have to do all this even though I only know the very basics of sql – Ray Jul 10 '17 at 17:35
  • Simplest thing to do is to copy the entire query from the file, and use it to replace the `YourFirstQuery` and `YourSecondQuery`, respectively, in my answer. Putting them in parenthesis and giving them an alias as I did in my example makes them what is called "derived tables". – Tab Alleman Jul 10 '17 at 18:39
  • So I did as you suggested and copied both entire queries and pasted them into the areas where you told me to, however now it sends two error messages, each stating there is an incorrect syntax near the parentheses encompassing the second query – Ray Jul 11 '17 at 13:36
  • Can you update your question and add the complete FROM clause of your query, showing how you pasted the queries into the parenthesis? – Tab Alleman Jul 11 '17 at 13:41
  • Move the DECLARE and SET statements to the top of the script, before any SELECT statements. You can't do DECLARE/SET inside a query. – Tab Alleman Jul 11 '17 at 14:50
  • Thank you for helping. Moving the declare and set tags worked but there is still only two more error messages, I think these are the last ones though. I have edited my original post to reflect this. Basically the error comes from the very first SELECT expression that comes before all the CASE expressions and in the order by expression at the very end – Ray Jul 11 '17 at 15:18