How to plast last and first column info per row in MS SQL script

We a data purchase table that we are trying to have all info in the same product row.  That is, we are trying to place in the same Product displayed the following columns:

- first purchase
- last purchase
- last provider
- last contract
- total purchase in costs to-Date
- total purchase in qty to-Date

The desired result is:
Product   First Purch Date  Last Purch Date  Last Provider   Last Contract   Total Purch (Costs) Total Purch (Qty)
0069-001  2003-1031         2004-1112        TWA             Contract-02     4551.86             21

Open in new window

rayluvsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rayluvsAuthor Commented:
here's is the data:

-- Create Table
    DECLARE @Sales TABLE(
                     Product VARCHAR(20),
                     Provider VARCHAR(20),
                     cDate VARCHAR(20),
                     Cost FLOAT,
                     Qty INT,
                     cContract VARCHAR(20))

-- Insert data
    INSERT INTO @Sales SELECT '0069-001','IBM','2003-1031', 151.86,1,'Contract-01' 
    INSERT INTO @Sales SELECT '0069-001','TWA','2004-1112', 220,20,'Contract-02'

-- Display individual results
    SELECT * FROM @sales z WHERE z.Product='0069-001' ORDER BY 1 DESC -- All
    SELECT top 1  z.cDate 'FIRST PURCH DATE', z.cContract, z.Product, z.Provider, z.Cost, z.Qty  FROM @sales z WHERE z.Product='0069-001' ORDER BY 1 asc -- first purchase
    SELECT top 1  z.cDate 'LAST PURCH DATE', z.cContract, z.Product, z.Provider, z.Cost, z.Qty FROM @sales z WHERE z.Product='0069-001' ORDER BY 1 DESC -- last purchase
    SELECT top 1  z.Provider 'LAST PROVIDER', z.Product, z.cDate, z.Cost, z.Qty, z.cContract FROM @sales z WHERE z.Product='0069-001' ORDER BY 3 DESC -- last provider
    SELECT top 1  z.cContract 'LAST CONTRACT', z.Provider , z.Product, z.cDate, z.Cost, z.Qty FROM @sales z WHERE z.Product='0069-001' ORDER BY 4 desc -- last contract
    SELECT sum(z.Qty*z.Cost) 'PURCH TO-DATE IN COSTS', z.Product FROM @sales z WHERE z.Product='0069-001' GROUP BY z.Product -- total purchase in costs to-Date
    SELECT sum(z.Qty) 'PURCH TO-DATE IN QTY', z.Product FROM @sales z WHERE z.Product='0069-001' GROUP BY z.Product -- total purchase in qty to-Date

Open in new window

0
Scott PletcherSenior DBACommented:
SELECT
    MIN(s.cDate) AS [First Purch Date],
    MAX(s.cDate) AS [Last Purch Date],
    MAX(sLast.Provider) AS [Last Provider],
    MAX(sLast.cContract) AS [Last Contract],
    SUM(s.Cost * S.Qty) AS [Total Purch (Costs)],
    SUM(s.Qty) AS [Total Purch (Qty)]
FROM @Sales s
INNER JOIN (
    SELECT
        Product, Provider, cContract,
        ROW_NUMBER() OVER(PARTITION BY Product ORDER BY cDate DESC) AS row_num
    FROM @Sales    
) AS sLast ON
    sLast.row_num = 1 AND
    sLast.Product = s.Product
GROUP BY
    s.Product
ORDER BY
    s.Product
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rayluvsAuthor Commented:
Hi Thanx!  Worked excellently!

Can you explain a bit on the how the way you correctly displayed the columns                     LastPurchDate, LastProvider, LastContract, TotalPurchCosts and TotalPurchQty (using MIN,MAX, ROW NUMBER and PARTITION)?

Please we greatly appreciate you assistance, but we would appreciate even more an explanation.

Again, Thanx Lots!!
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Scott PletcherSenior DBACommented:
Sure.  This (sub)query:

   SELECT
        Product, Provider, cContract,
        ROW_NUMBER() OVER(PARTITION BY Product ORDER BY cDate DESC) AS row_num
    FROM @Sales    

lists the Product, Provider and cContract columns for all rows, like any SELECT.

The ROW_NUMBER() functions generates a new column very similar to an IDENTITY value.  The PARTITION BY clause, which is optional, "tells" SQL where to break/reset the numbering.  The ORDER BY clause, which is required, naturally tells SQL how to sort the data before assigning the row number.  Thus, you force SQL to sort and number some rows separately from the main columns in the SELECT.

For example, if you have this data:
Product, Provider, cContract, cDate ( 'item1', 'vendor1', 'contract1', '20131013' )
Product, Provider, cContract, cDate ( 'item1', 'vendor2', 'contract2', '20131014' )
Product, Provider, cContract, cDate ( 'item1', 'vendor3', 'contract3', '20131012' )
Product, Provider, cContract, cDate ( 'item2', 'vendor3', 'contract4', '20131011' )
Product, Provider, cContract, cDate ( 'item2', 'vendor4', 'contract5', '20131013' )

The output would be:
Product, Provider, cContract, cDate, row_num ('item1', 'vendor1', 'contract1', '20131013', 2)
Product, Provider, cContract, cDate, row_num ('item1', 'vendor2', 'contract2', '20131014', 1)
Product, Provider, cContract, cDate, row_num ('item1', 'vendor3', 'contract3', '20131012', 3)
Product, Provider, cContract, cDate, row_num ('item2', 'vendor3', 'contract4', '20131011', 2)
Product, Provider, cContract, cDate, row_num ('item2', 'vendor4', 'contract5', '20131013', 1)

Because of the sort, the highest cDate will always have ROW_NUMBER/row_num = 1.

The WHERE clause then restricts the final results of the query to only rows with row_num = 1, i.e., the highest cDate, i.e. the last order :-) .

If the PARTITION BY was not in the ROW_NUMBER(), the results would be:
Product, Provider, cContract, cDate ( 'item1', 'vendor1', 'contract1', '20131013', 2 )
Product, Provider, cContract, cDate ( 'item1', 'vendor2', 'contract2', '20131014',1 )
Product, Provider, cContract, cDate ( 'item1', 'vendor3', 'contract3', '20131012', 4 )
Product, Provider, cContract, cDate ( 'item2', 'vendor3', 'contract4', '20131011', 5 )
Product, Provider, cContract, cDate ( 'item2', 'vendor4', 'contract5', '20131013', 3 )
--NOTE: since there are two rows with an identical sort key of 20131013, it is random which will be #2 and which will be #3.
0
rayluvsAuthor Commented:
Excellent!!! Thank you very much!!

As for MIN and MAX, it's obvious, retreive the MINimum and the MAXimum row (if we not correct, please advice).
0
Scott PletcherSenior DBACommented:
Yes, exactly: the standard MIN/MAX/SUM work as always.

This:
    MAX(sLast.Provider) AS [Last Provider],
    MAX(sLast.cContract) AS [Last Contract],

looks a little odd, since we know the WHERE clause restricts the sLast subquery results to only one row, with row_num = 1.

The MAX() is required because of the GROUP BY in the outer query.  We can't use "sLast.Provider" just by itself, because SQL won't/can't assume there will be only value.  For example, if we try this:

SELECT
    MIN(s.cDate) AS [First Purch Date],
    MAX(s.cDate) AS [Last Purch Date],
    sLast.Provider AS [Last Provider],
    sLast.cContract AS [Last Contract],
    SUM(s.Cost * S.Qty) AS [Total Purch (Costs)],
    SUM(s.Qty) AS [Total Purch (Qty)]
FROM @Sales s
...
GROUP BY ...

We get an error message:
"Column 'sLast.Provider' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

So we have to put the MAX() around the sLast columns, even though we know there will only ever be a single value because of the way we've coded the query.  But obviously it doesn't hurt the results -- the MAX() of a single value will always be that value :-) .
0
rayluvsAuthor Commented:
Thank you very much! Wish we could give you more than 500 point! Excellent info!

Proceed to close
0
rayluvsAuthor Commented:
Thanx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.