select distinct d.DispatchorderId DispatchId, d.[Despatch Order No],r.ReceivingOrderNo, d.ManufacturerSerialNumber,d.Serial, r.DateReceived DateReceived, d.dateDispathced ,d.DepotTypeId
from
(select distinct ManufacturerSerialNumber,workcompleted DateReceived,[Despatch Order No] ReceivingOrderNo from VwReceived)r,
(select distinct DispatchorderId, ManufacturerSerialNumber, Serial ,dateDispathced,[Despatch Order No],DepotTypeId from VwDispatchedall)d
where r.ManufacturerSerialNumber = d.ManufacturerSerialNumber collate database_default and d.Workcompleted > r.DateReceived
Hi Above is my sql query that i am running to get all the received dates vs despatched dates.
Problem i am having it was joining each received date with each dispatched date, so i added the exclusion of where dateDispathced > DateReceived helped partially, problem i am now having is if there is any received dates < one date dispatched it will show that record as well
below is one example on a ManufacturerSerialNumber
manufactserial| Serial|Dispatched OrderNO| ReceivingOrderNo |DateReceived | DateDispatched
500025 |20000 | 4555 | |1002 |2013-01-14 | 2013-02-11|
500025 |20000 | 5000 | |1002 |2013-01-14 | 2013-03-14|
500025 |20000 | 5000 | |1689 |2013-03-01 | 2013-03-14|
I do not want the second line to show as that is not a valid Received vs Dispatched.
The second line shows a the date received as 2013-01-14 to the dispatch date of 2013-03-14
which is incorrect as the correct data is represented on the next line where the date received is 2013-03-01 and dispatched date is 2013-03-14 so basicly i need to show one received date to one dispatch date