How to get a row with maximum value, and it corresponds to two other tables?

Question

For some reason, my client does not track what goes out of his store, what he does is counting the products he has every time, some products daily weekly and monthly, anyway, I have three tables that look 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 product count along with Qty, in other words, I want the products to be suspended for the account (I check whether the last account was more than a week or never been counted) as

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

What I came up with is

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 cannot add Qty

, , PRODUCT 1000 , COUNT , 3 10 , Count.

PS: Count -

Answer 1

"OUTER APPLY" .

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

, .

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

Answer 3

In my opinion, using the GROUP BY clause was unnecessary and problematic, you can use the subquery to select the last count date. Also, to eliminate confusion, first perform an INNER JOIN between Stock and Count (see Using brackets), and then go to the CORRECT WORK with products

USE StoTrackerBasic
GO

SELECT P.idPro, P.ProName, R.CountDate AS LastCount, S.Qty
    FROM (Stock as S INNER JOIN (
        SELECT TOP 1 * FROM Count ORDER BY CountDate DESC
    ) R ON R.idCount = S.idCount)
    RIGHT JOIN Product P 
      ON S.idProduct = P.idProduct
GROUP BY P.ProName, P.ProCode
ORDER BY P.ProName