Insert Dates in the Return from a Query Where There Is None

SQL query that returns all dates not used in a table

You're right — SQL does not make it easy to identify missing data. The usual technique is to join your sequence (with gaps) against a complete sequence, and select those elements in the latter sequence without a corresponding partner in your data.

So, @BenHoffstein's suggestion to maintain a permanent date table is a good one.

Short of that, you can dynamically create that date range with an integers table. Assuming the integers table has a column i with numbers at least 0 – 13, and that your table has its date column named datestamp:

   SELECT candidate_date AS missing
     FROM (SELECT CURRENT_DATE + INTERVAL i DAY AS candidate_date
             FROM integers
            WHERE i < 14) AS next_two_weeks
LEFT JOIN my_table ON candidate_date = datestamp
    WHERE datestamp is NULL;

In SQL, is there a way to show all dates even if the date doesn't have data points?

You need to generate all the rows for the months. One method uses a recursive CTE. Then rest is then left join and aggregation.

Let me assume you are using SQL Server:

with months as (
      select convert(date, '2019-01-01') as mon
      union all
      select dateadd(month, 1, mon)
      from months
      where mon < '2019-12-01'
     )
select Format(m.mon, 'MMM-yyyy') as year_month, 
       c.Customer,  
       count(t.customer) as SalesCount  
from months m cross join
     (select distinct customer from t) c left join
     t
     on t.transaction_date >= m.mon and
        t.transaction_date < dateadd(month, 1, mon) and
        t.customer = c.customer 
group by m.mon, c.customer  
order by c.ustomer, c.mon ;

Note the other changes to the query:

• Year/Month is not a valid column alias.
• This orders the rows chronologically. That is usually (always?) preferred over alphabetic sorting of months.

Write an SQLite query that returns all records with dates older than a certain value

Your insert statements seem to have a problem, because the date literals are just string literals, and so should be wrapped in single quotes:

INSERT INTO MyRestaurants (Name, Cuisine, Distance, LastVisit, Enjoy)
VALUES ('Dakshin', 'Indian', 5, '2019-01-13', 1);

INSERT INTO MyRestaurants (Name, Cuisine, Distance, LastVisit, Enjoy)
VALUES ('Cactus', 'Mexican', 6, '2018-01-04', 1);

Then, if you want records which did not occurr in the last 3 months you should use:

SELECT Name
FROM MyRestaurants
WHERE Enjoy = 1 AND LastVisit <= DATE('now', '-3 month')
ORDER BY Name;

Note that there is no formal date type in SQLite. Rather, dates are just stored as TEXT. You correctly used an ISO format for your date literals, which were correct, other than possibly that you didn't escape them in single quotes.

mysql query returns values outside of WHERE range when using BETWEEN or = & =

You are not comparing dates you are comparing int(6)'s.
If you are was going to compare dates stored at integers then it would make more sense to store them as most significant digit first as yymmdd.
The difference between 083115 and 080314 is only 1, which is a year.
If you think about it as how they would sort from smallest to largest and what they represent I think it will make sense.

PDO query does not return data when inserting date as variable

1. Your immediate problem is caused the fact that $dateInputQuery is unquoted. Date is a string literal and should be quoted. And even though you can easily add quotes around it you really shouldn't do this. See next point.

2. order is a reserved word in MySQL, therefore the table name should be put in backticks

   $data = $order->getAllOrders('`order`', "WHERE DATE(orderdate) = DATE('$dateInputQuery')", false, false);
                                 ^     ^                                 ^               ^
   

3. You're not leveraging parameter binding in query() function. Instead on top of it you're using query string interpolation leaving your code vulnerable to sql injections and diminishing the usage of prepared statements. When you use parameter binding you no longer need to quote parameter values.
4. Your sql query is not index-friendly. You shouldn't apply any functions (in your case DATE()) to the column you're searching on (orderdate). Instead you can rewrite your condition to apply necessary transformations/calculations to the arguments which are constants.
5. You should avoid using SELECT *. Read Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc and Why is using '*' to build a view bad?

That being said your query should look something like

$sql = "SELECT order_id, orderdate, ...
          FROM `order` 
         WHERE orderdate >= ? 
           AND orderdate <  ? + INTERVAL 1 DAY";

And you should execute it

$this->query($sql, array($dateInputQuery, $dateInputQuery));

Instead of passing whole clauses (e.g. WHERE) you should pass values

---

Related Topics