September 15, 2016 at 8:30 am
Hello Experts ,
I need help with the following functionality.
I have 3 tables as listed below
Table 1 (Name : Invoice) with column's as below
----------------------------------
InvRef | Amount | Date
----------------------------------
456 | 785.5 | 01/09/2016
----------------------------------
457 | 900 | 22/09/2016
----------------------------------
Table 2 (Name : Sales) with column's as below (Relation with Invoice Table on InvRef column)
------------------------------------
InvRef | SalesDoc | Description
------------------------------------
456 | SD123 | ABCD
-------------------------------------
456 | SD456 | DEFG
-------------------------------------
457 | SD987 | ZXYH
-------------------------------------
457 | SD986 | RTUV
------------------------------------
Table 3 (Name : Equipment) with columns as below(Relation with Sales Table on SalesDoc column)
--------------------------------------
SalesDoc | EquipId | EquipName
--------------------------------------
SD123 | 101 | Cable
--------------------------------------
SD123 | 102 | Screen
--------------------------------------
SD456 | 201 | Printer
--------------------------------------
SD456 | 202 | Cartridge
--------------------------------------
SD987 | 301 | Key
--------------------------------------
SD987 | 302 | Lock
--------------------------------------
SD986 | 401 | Glass
--------------------------------------
SD986 | 402 | Plate
--------------------------------------
I need to get only top 1 row for all the records from Sales as well as Equipment table considering the Invoice as base
example as below
Output
------------------------------------------------------------------------------------------------
Invref | Amount | Date | SalesDoc | Description | EquipId | EquipName
-------------------------------------------------------------------------------------------------
456 | 785.5 | 01/09/2016 | SD123 | ABCD | 101 | Cable
--------------------------------------------------------------------------------------------------
457 | 900 | 22/09/2016 | SD987 | ZXYH | 301 | Key
Any help would be appreciated
September 15, 2016 at 8:40 am
Looks fairly simple - you're just joining all three tables together, and using a MAX or MIN to get the top sale per invoice. Is there any particular part of that you're struggling with? What have you already tried? And how do you decide which row from Sales to choose for each invoice?
John
September 15, 2016 at 8:49 am
A TOP(n) requires an order, which you haven't specified, but there is only one field that will give your specified results, so I have used that field.
SELECT InvRef, Amount, Date, SalesDoc, Description, EquipID, EquipName
FROM Invoice i
CROSS APPLY (
SELECT TOP (1) s.SalesDoc, s.[description], e.EquipID, e.EquipName
FROM Sales s
INNER JOIN Equipment e
ON s.SalesDoc = e.SalesDoc
WHERE s.InvRef = i.InvRef
ORDER BY e.EquipID
) se
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 15, 2016 at 8:53 am
John Mitchell-245523 (9/15/2016)
Looks fairly simple - you're just joining all three tables together, and using a MAX or MIN to get the top sale per invoice. Is there any particular part of that you're struggling with? What have you already tried? And how do you decide which row from Sales to choose for each invoice?John
The problem with a MIN/MAX is that it evaluates each column individually, where it looks like he wants to return the entire row based on the ordering, which is why I've used a CROSS APPLY.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply