Question:

if I have the following table of data

ProductID,StockOnSite,StockOffsite 1 83 81 1 98 85 1 112 101 2 81 85 2 115 83 2 115 101

What I need to do is get the Highest StockOnSite per ProductID( calculating the StockDifference ) record and concatinate StockOnSite with StockOffsite to create a column StockInfo

output needed

ProductID,StockOnSite,StockOffsite,StockDifference,StockInfo 1 98 85 13 98/85 2 115 83 32 115/83

SQL I have come up with

select ProductID ,StockOnSite ,StockOffsite ,StockDifference = max(StockOnSite - StockOffsite) from Product group by ProductID, StockOnSite, StockOffsite

I don't know where to go from here ?

Thanks

Below is query to build table and data:

CREATE TABLE Products ( ProductID int NOT NULL, StockOnSite int NOT NULL, StockOffsite int NOT NULL ) insert into table Products(ProductID, StockOnSite, StockOffsite) values(1,83,81) insert into table Products(ProductID, StockOnSite, StockOffsite) values(1,98,85) insert into table Products(ProductID, StockOnSite, StockOffsite) values(1,112,101) insert into table Products(ProductID, StockOnSite, StockOffsite) values(2,81,85) insert into table Products(ProductID, StockOnSite, StockOffsite) values(2,115,83) insert into table Products(ProductID, StockOnSite, StockOffsite) values(2,115,101)

select ProductID ,StockOnSite ,StockOffsite ,StockOnSite - StockOffsite ,cast(Stockonsite as varchar(10))+'/' +cast(StockOffsite as varchar(10)) from Products pr1 where StockOnSite - StockOffsite = (Select max(StockOnSite - StockOffsite) from Products pr2 where pr1.ProductID = pr2.ProductID ) group by ProductID, StockOnSite, StockOffsite

This should do the trick.<br /> The query needs to track the product so as not to get confused if the same stock difference occurs in more than 1 product:

select p.ProductID ,p.StockOnSite ,p.StockOffsite ,(p.StockOnSite - p.StockOffsite) StockDifference ,concat(p.StockOnSite,'/',p.StockOffsite) StockInfo from Products p where concat(ProductID,'-',(StockOnSite - StockOffsite)) = ( select concat(ProductID,'-',max((StockOnSite - StockOffsite))) from Products p2 where p2.ProductID = p.ProductID group by ProductID ) ;