0

I have a whole bunch of tariffs, some work on weekends, some work on weekdays some on both. Sometimes I'll be querying on NOW() but sometimes I'll be querying on datetime column.

id   | Weekday | Weekend | Price
 1   | 1       | 0       | 0.04   
 2   | 0       | 1       | 0.02          

date
2020-04-15 00:00:00
2012-04-16 00:00:00

The date is from another table and is not related to the Price / days of week.

I know I can get the weekend dates by

SELECT * FROM tariff where EXTRACT(ISODOW FROM date) IN (6,7)

however I can't think of how I'd get rows that are either weekend / weekdays or both given a date.

** edit **

Updated the tables to show the dates are seperate. What I'm trying to get is the tariff that corresponds to the date in that table, whether it's on a week day or a weekend (or both but I can extrapolate that).

The weekend 1 is the tariff that is used for weekends, weekdays 1, all days is both.

Rudiger
  • 6,749
  • 13
  • 51
  • 102
  • Can you give detailed example of query inputs and outputs? – pifor Apr 16 '20 at 06:42
  • @pifor I've updated the question a bit. There's a lot more tables / data but trying to bring it down to the simplest problem I'm trying to solve. – Rudiger Apr 16 '20 at 09:15

1 Answers1

0

Cannot give you a query, supply anything to query. Nor can we be sure that the columns Weekday and Weekend mean as you didn't tell us. But if we take them as boolean indicator where 1 means desired may some thing like will work for you.

    select ...
      from ...
     where ...
       and (   (weekday = 1 and weekend =1) 
            or (weekday = 1 and extract(isodow from date) not in (6,7))
            or (weekend = 1 and extract(isodow from date)    in (6,7)) 
           ) ;
Belayer
  • 13,578
  • 2
  • 11
  • 22
  • sorry about that, I've updated the question a bit to clarify what `Weekday` and `Weekend` mean a bit. – Rudiger Apr 16 '20 at 09:19
  • Please see the help section [ask]. Modify your question to include table DDL (all) and sample data from them as [formatted text](https://senseful.github.io/text-table/) - **no images**, and the expected results of that data. Also include your current query or as much as you have. – Belayer Apr 16 '20 at 16:55
  • Theres about 10 tables and a whole heap of superfluous data that doesn't help with an answer. Looking at your answer I think that will work. – Rudiger Apr 16 '20 at 21:56
  • If you need it you can see [mcve]. Strip away any superfluous data (we won't even know it's gone). Good luck without it. – Belayer Apr 16 '20 at 21:59