asked on

Extract the TOP 1 of each item within the Table in Ms Sql

We have a sales table with where an invoice can have 2 type of transactions: ‘Invoice’ and ‘PreQtrInvoice’. The table consists of a document number representing the invoice number for the sales and a type for the Items sold.

We are trying to display only the first ‘PreQtrInvoice’of an Item within each document number (like TOP 1 sorting by ‘PreQtrInvoice’). If the item has no ‘PreQtrInvoice’ within its document number, then include in the list the first Invoice type

The data is:
Data:

DocNumber Product DocType        StockQy
Doc1      Prod-A  PreQtrInvoice  1
Doc1      Prod-A  Invoice        3
Doc1      Prod-B  PreQtrInvoice  5
Doc1      Prod-B  PreQtrInvoice  5
Doc1      Prod-B  Invoice        1
Doc1      Prod-C  Invoice        1
Doc2      Prod-D  Invoice        2
Doc2      Prod-E  Invoice        2
Doc3      Prod-F  PreQtrInvoice  1
Doc3      Prod-G  Invoice        7
Doc3      Prod-F  Invoice        3

Open in new window

The expected result is:

DocNumber Product DocType        StockQy
Doc1      Prod-A  PreQtrInvoice  1
Doc1      Prod-B  PreQtrInvoice  5
Doc1      Prod-C  Invoice        1
Doc2      Prod-D  Invoice        2
Doc2      Prod-E  Invoice        2
Doc3      Prod-F  PreQtrInvoice  1
Doc3      Prod-G  Invoice        7

Open in new window

Upto now we see 2 separate script. One to extract the TOP 1 of ‘PreQtrInvoice’ and another to display items with ‘‘PreQtrInvoice’ and display the first one of their document number.

jana

ASKER

Here is a sample of the data:

-- Create Table
    DECLARE @Sales TABLE(
                     DocNumber VARCHAR(20),
                     Product VARCHAR(20),
                     DocType VARCHAR(20),
                     StockQy INT)
-- Insert data
   INSERT INTO @Sales SELECT 'Doc1','Prod-A','PreQtrInvoice', 1
   INSERT INTO @Sales SELECT 'Doc1','Prod-A','Invoice', 3
   INSERT INTO @Sales SELECT 'Doc1','Prod-B','PreQtrInvoice', 5
   INSERT INTO @Sales SELECT 'Doc1','Prod-B','PreQtrInvoice', 5
   INSERT INTO @Sales SELECT 'Doc1','Prod-B','Invoice', 1
   INSERT INTO @Sales SELECT 'Doc1','Prod-C','Invoice', 1
   INSERT INTO @Sales SELECT 'Doc2','Prod-D','Invoice', 2
   INSERT INTO @Sales SELECT 'Doc2','Prod-E','Invoice', 2
   INSERT INTO @Sales SELECT 'Doc3','Prod-F','PreQtrInvoice', 1
   INSERT INTO @Sales SELECT 'Doc3','Prod-G','Invoice', 7
   INSERT INTO @Sales SELECT 'Doc3','Prod-F','Invoice', 3

-- Display Table
   SELECT * from @Sales
   ORDER BY DocNumber, Product asc

Open in new window

Below is the results of the above marking the lines the script we are working trying to accomplish:

DocNumber            Product              DocType              StockQy
-------------------- -------------------- -------------------- -----------
Doc1                 Prod-A               PreQtrInvoice        1  < ---- DISPLAY THIS LINE
Doc1                 Prod-A               Invoice              3
Doc1                 Prod-B               PreQtrInvoice        5  < ---- DISPLAY THIS LINE
Doc1                 Prod-B               PreQtrInvoice        5
Doc1                 Prod-B               Invoice              1
Doc1                 Prod-C               Invoice              1  < ---- DISPLAY THIS LINE
Doc2                 Prod-D               Invoice              2  < ---- DISPLAY THIS LINE
Doc2                 Prod-E               Invoice              2  < ---- DISPLAY THIS LINE
Doc3                 Prod-F               PreQtrInvoice        1  < ---- DISPLAY THIS LINE
Doc3                 Prod-F               Invoice              3
Doc3                 Prod-G               Invoice              7  < ---- DISPLAY THIS LINE

Open in new window

Christopher Gordon

See attach sample using row counter to sort invoices. Note: SQL 2008 syntax used for populating table.
Invoice-Sample-SQL.sql

ASKER CERTIFIED SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jana

ASKER

gohord, we're getting a syntax error at the first "('Doc1',".

BriCrowe, it worked excellently. How can we display only the result? When adding your portion, it does a full table display, then the result.

Here is the last portion of the script after adding your lines:

-- Display Table
   SELECT * from @Sales
   ;WITH cteInvoice (DocNumber, Product, DocType, StockQy, RowNumber)
AS
(
      SELECT DocNumber, Product, DocType, StockQy,
            ROW_NUMBER() OVER (PARTITION BY Product ORDER BY DocType DESC) AS RowNumber
      FROM @Sales
)
SELECT * FROM cteInvoice
WHERE RowNumber  = 1
ORDER BY DocNumber, Product asc

Open in new window

SOLUTION