I try to manage a delivery team. That's why, i want to display a table . The first line will be completed by day. And the following rows will be filled with the states and their durations . The row "date" must contain all days of the month . Even if nobody works.
- First table (typeState) -
deliveryStatusDuration //State Time (seconds) INTEGER
deliveryStatus //Type Status INTEGER
startStatus //Start of the state DATE
endStatus //End of the state DATE
deliveryCarNum //deliverer identifier INTEGER
- Second table (dateTable) -
dateInt // Just an integer (from 1 to 31) INTEGER
My SQL Request :
SELECT SUM(deliveryStatusDuration) AS DURATION, deliveryStatus, datetable.dateInt
FROM datetable
LEFT JOIN typeState ON datetable.dateInt = DAY(typeState.startStatus)
WHERE deliveryCarNum=:CarNum
AND startStatus >= :DateStart
AND endStatus <= :DateEnd
AND (typeState.startStatus Is Null)
OR (typeState.deliveryStatus Is Null)
OR datetable.dateInt <= DAY(:DateEnd)
GROUP BY datetable.dateInt, deliveryStatus
The problem Is that whatever the month used, all information is displayed . I think "OR" destroy my "AND", so I don't have those days when a status begins. For exemple, if a delivery man begins to deliver at May 4, 2016 and ends May 8, 2016; my request doesn't appear August 4. : 1 2 3 5 6 7 8 9 10...31
Aucun commentaire:
Enregistrer un commentaire