Click here to Skip to main content
11,496,146 members (640 online)
The site is currently in read-only mode for maintenance. Posting of new items will be available again shortly.
See more: SQL-server-2005
select  distinct d.DispatchorderId DispatchId, d.[Despatch Order No],r.ReceivingOrderNo,  d.ManufacturerSerialNumber,d.Serial, r.DateReceived DateReceived, d.dateDispathced ,d.DepotTypeId
(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
Posted 10-Apr-13 22:07pm
Edited 11-Apr-13 3:31am

1 solution

You have 2 tables: VwDispatchedall, VwReceived. Why don't you use JOIN's[^]?
There is several types of JOIN's: LEFT[^], RIGHT[^], INNER[^], OUTER

Difference between them is shown here: Visual Representation of SQL Joins[^]

SELECT DESTINCT d.DispatchorderId DispatchId, d.[Despatch Order No],r.ReceivingOrderNo,  d.ManufacturerSerialNumber,d.Serial, r.DateReceived DateReceived, d.dateDispathced ,d.DepotTypeId
FROM VwReceived AS r INNER JOIN VwDispatchedall AS d ON r.ManufacturerSerialNumber = d.ManufacturerSerialNumber
WHERE d.Workcompleted > r.DateReceived AND r.DateReceived < d.DateDispatched
isi19 at 11-Apr-13 8:27am
The joins will return the exact same result as what i am getting
isi19 at 11-Apr-13 8:28am
500025 |20000 | 5000 | |1002 |2013-01-14 | 2013-03-14|
this line i need to exclude as this shows a received date after it had been received again
Maciej Los at 11-Apr-13 8:40am
I don't see the structure of your database. Update question (use "Improve question" widget) and post the structure of both tables and data types for each column, then i'll try to help you again.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 RyanDev 230
1 Sergey Alexandrovich Kryukov 114
2 Richard Deeming 100
3 OriginalGriff 89
4 Mostafa Asaduzzaman 59
0 Sergey Alexandrovich Kryukov 10,401
1 OriginalGriff 8,910
2 Sascha Lefèvre 3,899
3 Maciej Los 3,422
4 Richard Deeming 2,600

Advertise | Privacy | Mobile
Web03 | 2.8.150520.1 | Last Updated 11 Apr 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100