mercredi 15 juin 2016

Left Join / OR - [SQL ACCESS]

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