Friday, February 2, 2024

How to find Minimum and Maximum value across multiple Columns in SQL Server

How to return Minimum and Maximum value across multiple Columns in SQL Server
Scenario:
Suppose we have a sample Products table with Price values across multiple Years as shown below.
/* Create a sample table */
CREATE TABLE [dbo].[tbl_Products]
  (
    [ProdID] [int] NOT NULL,
    [ProdName] [varchar](255) NOT NULL,
    [YR2010] [int] NULL,
    [2011] [int] NULL,
    [YR2012] [int] NULL,
    [2013] [int] NULL,
    [YR2014] [int] NULL,
    [2015] [int] NULL,
  ) ON [PRIMARY]
GO

/* Insert sample records */
INSERT INTO [dbo].[tbl_Products]
  ([ProdID], [ProdName], [YR2010], [2011], [YR2012], [2013], [YR2014], [2015])
  VALUES (121, 'Apple',25, NULL,35,15,20,10), (122,'Mango',35,15, NULL,45,15,25)

SELECT * FROM [dbo].[tbl_Products];


Now we can find the Minimum and Maximum value for each Product across the multiple Year columns using the below Query:
/* Returning Minimum and Maximum values across Multiple Columns */
SELECT [ProdID],[ProdName],
    (
      SELECT MIN(Val)
      FROM ( VALUES ([YR2010]),([2011]),([YR2012]),([2013]),([YR2014]),([2015]))
      AS tbl_Prod(Val)
    ) MinVal,
    (
    SELECT MAX(Val)
      FROM ( VALUES ([YR2010]),([2011]),([YR2012]),([2013]),([YR2014]),([2015]))
      AS tbl_Prod(Val)
    ) MaxVal
FROM [dbo].[tbl_Products]

Result:

We can understand the above query with below simple example:
SELECT *
  FROM ( VALUES (1),(NULL),(3),(17),(5),(13)) AS tbl(Col1)
Result:

SELECT MAX(Val) MaxVal, MIN(Val) MinVal
  FROM ( VALUES (7),(NULL),(3),(17),(5),(13)) AS tbl(Val)

Result:

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog