Help get each HOUR from a date() for SQL filtering
Posted: Mon Mar 06, 2006 9:25 am
I am currently writing some very bulky code to select each hour from a POSTED date to a page. I want to be able to generate SQL to say how many parts were made between each hour of a particular shift, there are 3 shifts. The user selects a date and a shift (Day, Night, Afternoon) from a drop down list which gets posted to a results page.
I am managing the dates as such:
then I isolate the shifts into 3 different groups (Night Shift Shown):
I pass this through a query and print out a report of how many parts were made for that shift:
Now they have asked for a breakdown by each hour, and my code is becoming unmanageable. I am creating 2 variables for every hour group:
and then I am creating 8 SQL statements per shift, etc. etc.
There has to be a better way. I would like to write a single query to handle the 8 hour breaks. Any suggestions ???
Thanks,
Justin
I am managing the dates as such:
Code: Select all
If (!isset($_POST['PickDate'])) {
$PickDate = date("l, F d, Y");
} else {
$PickDate = date("l, F d, Y", $_POST['PickDate']);
}
If (!isset($_POST['PickShift'])) {
$PickShift = "Night Shift";
} else {
$PickShift = $_POST['PickShift'];
}Code: Select all
$NightShiftStart = date("Y-m-d H:i:s", mktime(23,00,00,$PickMonth, $PickDay-1, $PickYear));
$NightShiftEnd = date("Y-m-d H:i:s", mktime(06,59,59,$PickMonth, $PickDay, $PickYear));Code: Select all
$NightShiftCountSQL = "SELECT DCM_ID AS DCM, COUNT(ShiftCount) AS COUNT
FROM ester_perpart
WHERE TS BETWEEN '$NightShiftStart' AND '$NightShiftEnd'
GROUP BY DCM_ID
";Code: Select all
$CurrentShiftHour1Start = date("Y-m-d H:i:s", mktime(23,00,00, $PickMonth, $PickDay-1, $PickYear));
$CurrentShiftHour1End = date("Y-m-d H:i:s", mktime(23,59,59, $PickMonth, $PickDay-1, $PickYear));There has to be a better way. I would like to write a single query to handle the 8 hour breaks. Any suggestions ???
Thanks,
Justin