Selecting values from tables with Exclusions

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

SQL

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 21:07pm

isi19

Updated 11-Apr-13 2:31am

v5

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2013-04-11T01:01:00

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[^]

SQL

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

Posted 11-Apr-13 1:01am

Maciej Los

Comments

isi19 11-Apr-13 8:27am

The joins will return the exact same result as what i am getting

isi19 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 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.