1

I have this query table,

CREATE TABLE BASE_Purchase
(
PurchaseId BIGINT,
PurchaseDate DATE NOT NULL,
SupplierName VARCHAR(255) NOT NULL,
Amount MONEY NOT NULL,
Outstanding MONEY NULL
PRIMARY KEY(PurchaseId)
)

I have the following result set from the table,

enter image description here

Above image result query ↑

SELECT PurchaseId, PurchaseDate, SupplierName, Amount, Outstanding
FROM BASE_Purchase
ORDER BY SupplierName, PurchaseId

I need the above should be result as follow, enter image description here

After retrieved the above result and i want to get the SUM of each outstanding

3
  • So, you want the row with the latest PurchaseDate for each SupplierName from your Results? Your tags indicate both SQL2008 and SQL2014. Does the solution have to run on both? Jun 19, 2019 at 19:09
  • Yes, First Table i have entered all my purchase details supplier wise, outstanding amount in a row is for the particular row date of outstanding. i want to get the result supplier wise their Last outstanding amount order of purchase id (display on my second image)
    – M.I.M.F
    Jun 19, 2019 at 19:23
  • How do you want to handle the situation where you have two rows for the same supplier and max purchasedate? Jun 19, 2019 at 19:24

1 Answer 1

1

Based on your images, something like this should run on SQL Server 2008.

(It's not completely tested because I didn't want to have to type all that data from your screenprints.)

;WITH _cte
AS (
    SELECT PurchaseId
        ,PurchaseDate
        ,SupplierName
        ,Amount
        ,Outstanding
    FROM BASE_Purchase
    )
SELECT PurchaseId
    ,PurchaseDate
    ,SupplierName
    ,Outstanding
FROM _cte c
WHERE PurchaseDate = (
        SELECT max(PurchaseDate)
        FROM _cte
        WHERE SupplierName = c.SupplierName
        )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.