CODEWITHSUNDEEP
phpmysqlsqlsql-server
Armitage2k
Armitage2k

Reputation: 1254

SQL - get records within date range, incl. same day records

I am struggling with something (hopefully) simple here. I have an online logbook that I review on a daily basis. Each day has a number of logcases that I must review, hence I built a simple date-filter to screen each day's logcases on the main page.

Now, when loading the main page for the first time (meaning, with the default filter conditions), I want the page to show all logcases of the most recent DAY on file. So if for example today is the 10th of the month and the most recent case was submitted on the 8th, then I want to query ALL cases from the 8th.

If on the other hand somebody has already filed a logfile for the 10th, then I want to display all records from the 10th.

The problem here now is that the query does accurately identify the date if the last record, but the cutoff TIME seems to be midnight or 00:01, meaning it will not display any logfiles that were submitted on that day AFTER one minute past midnight! So if I want to see today's cases, I will only see those submitted between 00:00 to 00:01, making the whole filter useless.

Query

$sql = "SELECT *
FROM qci_dmlog_data
WHERE Incident_Date IN (
    SELECT MAX( Incident_Date )
    FROM qci_dmlog_data
    WHERE Holidex = '".$_SESSION['Holidex']."'
)
GROUP BY LogID ORDER BY Date DESC";

To sum it up, how can I filter records of the most recent DAY without caring about what TIME they were submitted?

Thanks!

Upvotes: 0

Views: 416

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

I believe you need to correlated the subquery on Holidex in addition to only using the date portion of the "date":

SELECT d.*
FROM qci_dmlog_data d
WHERE d.Holidex = '".$_SESSION['Holidex']."' AND
      DATE(d.Incident_Date) = (SELECT MAX(DATE(d2.Incident_Date))
                               FROM qci_dmlog_data d2
                               WHERE d2.Holidex = d.Holidex
                              );

This assumes that "most recent date" is per Holidex.

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You should look for the date part of incident date to get all the records from the most recent date.

SELECT *
FROM qci_dmlog_data
WHERE DATE(Incident_Date) IN (
    SELECT MAX(DATE(Incident_Date))
    FROM qci_dmlog_data
    WHERE Holidex = '".$_SESSION['Holidex']."'
)

Upvotes: 2

Related Questions