how to get row with max value and it matches from two other tables?

Question

For some reasons my client doesnt keep track of what goes out from his store, what he does is count the products he has each time, some products daily others weekly and some monthly, anyway i have three tables that looks like this

PRODUCT
------------------
| idPro | ProName|
------------------
|  1    | P1     |
|  2    | P2     |
|  3    | P3     |
------------------

STOCK
-------------------------
| idPro | idCount | Qty |
-------------------------
|   1   |   1     | 10  |
|   2   |   2     | 30  |
|   1   |   4     | 60  |
|   2   |   5     | 10  |
-------------------------

Count
------------------
| idCount | Date |
------------------
|  1      | 1100 |
|  2      | 1109 |
|  3      | 1505 |
|  4      | 1806 |
------------------

I want to get the last count of a product along with the Qty, in other words i want to get the products on hold for the count (i check if the last count was over a week or has never been count) something like

----------------------------
|ProName | LastCount | Qty |
----------------------------
|  1     | 1505      | 60  |
|  2     | 1806      | 10  |
|  3     | NULL      | NULL|
----------------------------

What i came up with is this

USE StoTrackerBasic
GO

SELECT P.ProCode, P.ProName, MAX(R.CountDate) LastCount
    FROM Product P 
    LEFT JOIN Stock as S ON P.idProduct = S.idProduct
    INNER JOIN Count R ON R.idCount = S.idCount
    --  WHERE R.CountDate > 100 OR R.CountDate IS NULL
GROUP BY P.ProName, P.ProCode
ORDER BY P.ProName

but Can't add the Qty

Also i guess it is important to mention that table PRODUCT may have over 1000 product and the COUNT table may have a triple since the client might perform 3 or 10 counts a day involving different products, so my query i guess checks for every product every row on the Count table.

PS : The Date on the table Count is a timestamp

Answer 1

You can use "OUTER APPLY" for gather last related item.

DECLARE @Product TABLE ( idProduct INT , ProName VARCHAR(50) )
INSERT INTO @Product VALUES( 1, 'P1'), (2, 'P2'), (3, 'P3')

DECLARE @Stock TABLE ( idProduct INT,  idCount INT,  Qty INT )
INSERT INTO @Stock VALUES (1, 1, 10), (2, 2, 30), (1, 4, 60), (2, 5,10)

DECLARE @Count TABLE ( idCount INT , CountDate VARCHAR(20) )
INSERT INTO @Count  VALUES (1, 1100), (2, 1109), (3, 1505), (4, 1806)

SELECT  P.idProduct ProID, P.ProName, A.CountDate LastCount, A.Qty LastQty
FROM @Product P 
OUTER APPLY(    
        SELECT TOP 1 R.CountDate, S.Qty 
        FROM 
            @Stock as S 
            INNER JOIN @Count R ON R.idCount = S.idCount
        WHERE 
            P.idProduct = S.idProduct
        ORDER BY 
            R.CountDate DESC
        ) AS A
ORDER BY P.ProName

If the Stock table has multiple records then additional order by predicate should be added to the outer query according to priority.

Answer 2

;WITH cteRowRum (rowNum,idPro,idCount,Qty) AS(
    Select row_number()over(Order by idCount) as rowNum,idPro,idCount,Qty
    From Stock)

Select C.idPro,B.Date,A.Qty
From ProductTBl as C
Left Join
(
    Select *
    From
    (
        Select *, row_Number()Over(Partition by idPro Order by rowNum desc) as SelectedRows
        From cteRowRum
    ) as A
    Where SelectedRows = 1
) as A
On C.idPro = A.idPro
Left Join
CountTbl as B
On A.rowNum=B.idCount