Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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
 
                     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
Posted 10-Apr-13 22:07pm
isi19463
Edited 11-Apr-13 3:31am
v5

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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
  Permalink  
Comments
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 Marcin Kozub 295
1 OriginalGriff 273
2 Richard MacCutchan 229
3 Praneet Nadkar 197
4 Sergey Alexandrovich Kryukov 190
0 OriginalGriff 7,913
1 Sergey Alexandrovich Kryukov 7,232
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,865


Advertise | Privacy | Mobile
Web01 | 2.8.1411023.1 | Last Updated 11 Apr 2013
Copyright © CodeProject, 1999-2014
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