CODEWITHSUNDEEP
sqlsql-serverjoin
user1648738
user1648738

Reputation: 41

SQL, get values from second table based on minvalue of grouped selection

I have a query below were i list all transactions that hasnt been nulled, the objective is get to a list with all opened transactionID (represents a customerorder) and its invoicenumber. 

Table 1
transactionID
bookdate
cost
year

Table 2
transactionID
invoice
year

SELECT 1.transactionID, Sum(cost), 1.Max(bookdate), 2.invoice
FROM 1
LEFT JOIN 2
ON 1.transactionID = 2. transactionID AND 1.year = 2.year
GROUP BY 1.transactionID, 2.invoice
HAVING (Sum(cost)<>0)

My problem is that the transactionID is reused every year, that's why I need to check that the year corresponds between the actual transactionID and the invoices in table 2.

Every transactionID has several transactions with different bookingdates. This mean that one transaction could have happened in 2011 and one in 2012. I want the query to look for the year that corresponds to the earliest bookingdate per open transactionID.

For example:

Table 1

1 | 20120101 | -20  | 2012
2 | 20120501 | -100 | 2012
2 | 20110501 | 100  | 2012
1 | 20110801 | 50   | 2011

Table 2

1 | invoice X   | 2012
2 | invoice Y   | 2012
1 | invoice old | 2011

The result should be

1 | 30 USD | Invoice old

Upvotes: 3

Views: 106

Answers (2)

Andriy M
Andriy M

Reputation: 77687

If you are using SQL Server 2005 or later version, you could use window functions like this:

WITH summedAndRanked AS (
  SELECT
    [1].transactionID,
    [2].invoice,
    totalCost    = SUM(cost)     OVER (PARTITION BY [1].transactionID),
    lastBookDate = MAX(bookdate) OVER (PARTITION BY [1].transactionID),
    rnk          = DENSE_RANK()  OVER (PARTITION BY [1].transactionID ORDER BY [1].year),
  FROM [1]
    LEFT JOIN [2]
      ON [1].transactionID = [2].transactionID
     AND [1].year = [2].year
)
SELECT DISTINCT
  TransactionID,
  totalCost,
  lastBookDate,
  invoice
FROM countedAndRanked
WHERE totalCost <> 0
  AND rnk = 1
;

Upvotes: 1

podiluska
podiluska

Reputation: 51494

I think you're nearly there - try

SELECT 1.transactionID, 1.Year, Sum(cost), Max(1.bookdate), 2.invoice 
FROM 1 
LEFT JOIN 2 
ON 1.transactionID = 2. transactionID AND 1.year = 2.year 
GROUP BY 1.transactionID, 1.Year, 2.invoice
HAVING (Sum(cost)<>0)

Upvotes: 0

Related Questions