SQL: I want to join a secondary query and i want to get only the latest record (there is a date field in that table.
Query 1 (Consists all the invoices of our customers and their debtors):
Clientnr
Debtornr
Invoicenr (unique)
Amount
..
..
Query 2 (Consists of all the log records, but for each debtor/client combination there can be a lot of records, i need the latest (latest date of highest idLog):
idLog
Clientnr
Debtornr
ActivityDate
ActivityTime
Description (this is the field i need)
In both of the tables i use several WHERE statements to filer the results...
I can't get it to work. I always get duplicated records for each table....
The big problem is that i can only join the tables using clientnr and debtornr. Not on an unique id... and i can't change the tables because it is from software we don't own.
I hope someone has an answer...
Thanks
Peter
ADDED: Query where i need to add another table with multiple records, as explained above...:
--------------------------------------------------------------------------------------------------------
SELECT Client.ClientNr AS Klantnr, Client.ClientName AS Klantnaam, Debtor.DebtorNr AS Debiteurnr, Debtor.DebtorName AS DebiteurNaam,
Invoice.InvoiceNr AS Factuurnr, Invoice.InvoiceDate AS FactuurDatum, Invoice.VervalDatum AS Vervaldatum, Invoice.OpenstaandBedrag,
Invoice.DaysOpen AS DagenVervallen, CASE WHEN Invoice.BlockUnblock = 1 THEN 'JA' ELSE 'NEE' END AS GeblokkeerdJN,
Invoice.InvoiceBlocked AS BlokkeerOmschrijving, CASE WHEN Invoice.Dispute = 'Dispuut' THEN 'JA' ELSE 'NEE' END AS DispuutJN,
CASE WHEN Invoice.Dispute = 'Dispuut' THEN Dispuuttekst.TextFree ELSE 'nvt' END AS DispuutOmschrijving,
CASE WHEN Invoice.BlockUnblock = 1 THEN 'CLIENT' WHEN Invoice.Dispute = 'Dispuut' THEN 'CLIENT' ELSE 'CF' END AS ActieLigtBij
FROM Debtor INNER JOIN
Client ON Debtor.ClientNr = Client.ClientNr INNER JOIN
Invoice ON Debtor.ClientNr = Invoice.ClientNr AND Debtor.DebtorNr = Invoice.DebtorNr LEFT OUTER JOIN
(SELECT ClientNr, DebtorNr, TextFree
FROM Dispute) AS Dispuuttekst ON Debtor.ClientNr = Dispuuttekst.ClientNr AND Debtor.DebtorNr = Dispuuttekst.DebtorNr
WHERE (Client.ClientNr = 5267 OR
Client.ClientNr = 5395 OR
Client.ClientNr = 5292) AND (Invoice.OpenstaandBedrag 30)
ORDER BY DebiteurNaam
----------------------------------------------------------------------------------------------------
QUERY 2 (With multiple records):
SELECT
ClientNr,
DebtorNr,
ActivityDate,
ActivityTime,
Administrator,
ActivityType,
Description,
AgendaDate,
AgendaAction,
AgendaDescription,
Subject,
Type,
IdLog
FROM
LogFile
WHERE
Service = 'OUT'
AND type = 'Activity'
AND ActivityType ''
AND Activitytype 'specdeb'
=================================================================================================================
ADDED (2):
Results from the seperate queries:
-----------------------------------------------------------------------------------------------------------------
Results Query1 (copy paste into different text editor to see the right format):
-----------------------------------------------------------------------------------------------------------------
Clientnr ClientName DebtorNr DebtorName InvoiceNr InvoiceDate ExpireDate Value AmountDue Blocked BlockedComment Dispute DisputeComment Action
5267 TPF 95348 Abv 1123017 2012-07-26 00:00:00.000 2012-08-25 00:00:00.000 3442,38 118 J Niet manen NEE nvt CLIENT
5267 TPF 102797 Avof 1124214 2012-10-11 00:00:00.000 2012-11-10 00:00:00.000 531,26 41 J Niet manen NEE nvt CLIENT
5267 TPF 99098 BB 20112339 2011-07-28 00:00:00.000 2011-09-11 00:00:00.000 327,6 467 N NEE nvt CF
5267 TPF 109559 BTO 1121891 2012-05-16 00:00:00.000 2012-05-30 00:00:00.000 256 205 N NEE nvt CF
5267 TPF 119814 BPM 4120449 2012-09-13 00:00:00.000 2012-10-13 00:00:00.000 -286 69 N NEE nvt CF
5395 APB 108808 CFK 20121900 2012-10-04 00:00:00.000 2012-11-03 00:00:00.000 133,83 48 N NEE nvt CF
5395 APB 108808 CFK 20121901 2012-10-04 00:00:00.000 2012-11-03 00:00:00.000 333,44 48 N NEE nvt CF
5395 APB 108808 CFK 20121902 2012-10-04 00:00:00.000 2012-11-03 00:00:00.000 333,44 48 N NEE nvt CF
5395 APB 108808 CFK 20121903 2012-10-04 00:00:00.000 2012-11-03 00:00:00.000 333,44 48 N NEE nvt CF
--------------------------------------------------------------------------------------------------------------
results Query2 (copy paste into different text editor to see the right format):
the first 2 records are from 1 client/debtor combination, so i only need the desciption of the latest record (activitydate)...
Hope someone can help me...
- Follow via:
- RSS
- Email Alert
Question
0
Votes
I want to join to a secondary table, i want to get only the latest record
Updated - 21st Dec
Answers (3)
0
Votes
A hint
Here is something I wrote within a WHERE clause for a similar problem (I wanted the latest address supplied)
and spraddr_from_date = (select max(b.spraddr_from_date)
from spraddr b
where b.spraddr_pidm = pebempl_pidm
and b.spraddr_atyp_code = 'PR'
and b.spraddr_status_ind is null
and b.spraddr_from_date = sysdate)
You, I think, need to do something a little more complicated (you need to nest yet further) with the date and IDLog. Succes!
and spraddr_from_date = (select max(b.spraddr_from_date)
from spraddr b
where b.spraddr_pidm = pebempl_pidm
and b.spraddr_atyp_code = 'PR'
and b.spraddr_status_ind is null
and b.spraddr_from_date = sysdate)
You, I think, need to do something a little more complicated (you need to nest yet further) with the date and IDLog. Succes!
21st Dec
0
Votes
Another Hint
Have you considered GROUP BY for your second query?
Select Field1, Field2, Field3, max(ActivityDate) as 'AliasedDate'
From Table
GROUP BY Field1, Field2, Field3
Select Field1, Field2, Field3, max(ActivityDate) as 'AliasedDate'
From Table
GROUP BY Field1, Field2, Field3
21st Dec
0
Votes
Or
SELECT TOP 1 ......
ORDER BY activitydate DESC
ORDER BY activitydate DESC
21st Dec
