52457

SQL get the highest value and concatenate

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)

Answer1:

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

Answer2:

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 ) ;

Recommend

  • showing data in one row (from multiple rows)
  • Format specific text in Outlook
  • Replace last two characters in column
  • RxJava debounce by arbitrary value
  • pyodbc doesn't report sql server error
  • D3 get axis values on zoom event
  • C: Incompatible pointer type initializing
  • Get history of file changes from TFS to implement custom “blame”-behaviour of exceptions
  • Blackberry - Custom EditField Cursor
  • Test if a set exists before trying to drop it
  • D3 nodes and links from JSON with nested arrays of children
  • How to create a file in java without a extension
  • Alert pop up with LWUIT
  • NHibernate Validation Localization with S#arp Architecture
  • ilmerge with a PFX file
  • PHPUnit_Framework_TestCase class is not available. Fix… - Makegood , Eclipse
  • Installing Hadoop, Java Exception about illegal characters at index 7?
  • Java Scanner input dilemma. Automatically inputs without allowing user to type
  • Adding a button at the bottom of a table view
  • C# - Is there a limit to the size of an httpWebRequest stream?
  • Read text file and split every line in MSBuild
  • PHP - How to update data to MySQL when click a radio button
  • req.body is undefined - nodejs
  • Counter field in MS Access, how to generate?
  • Get object from AWS S3 as a stream
  • Java applet as stand-alone Windows application?
  • MySQL WHERE-condition in procedure ignored
  • Convert array of 8 bytes to signed long in C++
  • Adding custom controls to a full screen movie
  • How to get next/previous record number?
  • Comma separated Values
  • Error creating VM instance in Google Compute Engine
  • Hits per day in Google Big Query
  • -fvisibility=hidden not passed by compiler for Debug builds
  • how does django model after text[] in postgresql [duplicate]
  • embed rChart in Markdown
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • How can I use `wmic` in a Windows PE script?
  • Unable to use reactive element in my shiny app
  • Net Present Value in Excel for Grouped Recurring CF