fetcth aggregate data

Question

fetcth aggregate data

Abhas

SSC Enthusiast

Points: 181
More actions
April 6, 2020 at 10:34 am

#3740674

Hi All,

I am having table where below list of columns
OrderNo
ActualHours
PlanedHours
calMonth

now i want to find MIN and MAX ActualHours ,PlanedHours,calMonth alongwith ActualHours and
PlanedHours ,ActualHours for MAX calMonth.

how can fetch this?

thanks
Abhas.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734605 More actions · Answer 1

If this is SQL 2019, you can use window functions. This might help you. Your requirement isn't clear. Here is some sample data and a query. Not sure if this is what you need.

  CREATE TABLE OrderHourList (
  OrderNo INT NOT NULL CONSTRAINT OrderHourListPK PRIMARY KEY
  , ActualHours SMALLINT
  , PlannedHours SMALLINT
  , CalMonth VARCHAR(6)
  )
  GO
  INSERT dbo.OrderHourList
      (OrderNo, ActualHours, PlannedHours, CalMonth)
  VALUES
      (1, 10, 10, '202001')
   ,   (2, 10, 20, '202001')
   ,   (3, 20, 40, '202001')
   ,   (4, 20, 15, '202002')
   ,   (5, 20, 10, '202002')
   ,   (6,  5, 10, '202003')
   ,   (7, 40, 50, '202003')
   ,   (8, 10, 20, '202003')
   ,   (9, 20, 10, '202003')
   ,   (10, 30, 5, '202003')
GO

WITH cteMax(calmonth)
AS
( SELECT MAX(calmonth)
 FROM dbo.OrderHourList AS ohl
)
SELECT ohl.CalMonth
     , MAX(ohl.ActualHours) OVER (ORDER BY (select NULL)) AS MaxActual
     , MIN(ohl.ActualHours) OVER (ORDER BY (select NULL)) AS MinActual
     , MAX(ohl.PlannedHours) OVER (ORDER BY (select NULL)) AS MaxPlanned
     , MIN(ohl.PlannedHours) OVER (ORDER BY (select NULL)) AS MinPlanned
 , ohl.ActualHours
 , ohl.PlannedHours
 FROM dbo.OrderHourList AS ohl
 INNER JOIN cteMax ON cteMax.calmonth = ohl.CalMonth

Abhas SSC Enthusiast Points: 181 More actions · Answer 2

Thank you Steve ,

this helped me. Than you so much.

Regards,

Abaso Jadhav

Steve Jones - SSC Editor SSC Guru Points: 734605 More actions · Answer 3

You are welcome. If you have more issues, please post back. Including code makes this easier for us, both with setup and seeing your expected results.