0

First I'll explain the concept of the tables..
I have 3 tables, A, B and C

table A contains the old and new license numbers and also the date when the driver changed a new license

table A contains 

fdate                      |   oldLicense (varchar)  |  newLicense  (varchar)  
12/14/2013 4:16:16 PM      |   2                     |  3

Table B records the license number and car ID, also the first day when they used the car. Notice that on 12/14/2013, a new license number is inserted which means that the drivers were changed on a car (because the old one cannot drive anymore or have died).

table B contains

ID_Car    |    licenseNumber  | fDate
44        |    2              | 12/9/2013 4:16:16 AM   
44        |    3              | 12/14/2013 4:16:16 PM

Table C is the maintenance made on the car. Notice that it has dates, it should reflect when the drivers are changed.
Eg, on 12/9/2013 the driver's license is still 2, and on 12/15/2013 the license is already 3.

table C contains

Invoice   |   ID_Car   |   fDate
00989     |   44       |   12/9/2013 5:00:00 AM
10100     |   44       |   12/9/2013 6:00:00 AM
32323     |   44       |   12/15/2013 12:00:00 AM

Desired output:

C.Invoice |  A.Licenses ( old and new?)
00989     |  2  
10100     |  2
32323     |  3

What I have tried so far is:

SELECT B.fInvoice, C.fLicenseNew 
FROM tblCarFranchise A
LEFT JOIN tblCarMaintenance B ON A.ID_Car = B.ID_Car
LEFT JOIN tblTaxiDriversRelation C ON A.fLicenseNumber = C.fLicenseNew

Output:

00989     |  NULL  
10100     |  NULL
32323     |  NULL
00989     |  3  
10100     |  3
32323     |  3

I dont really know how should I start this, this might be the most complex query that I faced so far, also the rows are somehow duplicated (a total of 6 rows instead of 3 rows)

Help T_T

UPDATE: @Szymon thnx for helping, also to kumarch1
on the image, license number 222333 = 2 (on the question) and 13213113 = 3 Added tables

Improve this question
1
  • check answer defined below Commented Dec 17, 2013 at 2:22

2 Answers 2

Reset to default
1

After clarification, this will be your query. It finds the change of licence based on date.

select C.fInvoice, B.fLicenseNumber 
from tblCarMaintenance C
inner join tblCarFranchise B
on C.ID_Car = B.ID_Car
and C.fDate >= B.fdate
and C.fDate < isnull((select top 1 fDate from tblCarFranchise M where M.fdate > B.fdate
              and M.ID_Car = B.ID_Car), '20991231')

SQL Fiddle demo

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

can you tell me what '20991231' means on the query?
Date in the distant future. I'm matching invoices to be between the changes of licences and this is how I find the last licence (it's between last change and year 2099.
oh okay thanks :) it's working on the fiddle, but I'm still tweaking the code to work on sql server and on my code. I'll mark this answered as soon as I get it :)
I have already edited the question.. thank you so much for the help and sorry for the bother.. I have highlighted the used fields :(( i.sstatic.net/mhwXb.png
Updated the answer. I made a wrong assumptions that licences are consecutive. Now I compare based on dates. Remove some of your comments as well, let's clean that post up a bit.
0

keep your data in a temp temple and retrieve data from temp table without null

Select temp.fInvoice ,temp.fLicenseNew from( SELECT B.fInvoice as fInvoice , C.fLicenseNew as  fLicenseNew 
FROM tblCarFranchise A
LEFT JOIN tblCarMaintenance B ON A.ID_Car = B.ID_Car
LEFT JOIN tblTaxiDriversRelation C ON A.fLicenseNumber = C.fLicenseNew) temp
where temp.fLicenseNew != null
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.