locked
How to select a max within a max... I want query to return most recent date when day, month, and year are seperate columns RRS feed

  • Question

  • I am trying to automate this query to automatically return data for the most recent data:

    SELECT [Year],[Month],[Day],[Hour], SUM([CountOfTrans])

      FROM [dbo].[HourlyPassiveAgg]

      WHERE Year = 2018  --This is what I want to make dynamic to get most recent so I don't have to change this

      AND Month = 7      --This is what I want to make dynamic to get most recent so I don't have to change this

      GROUP BY [Year],[Month],[Day],[Hour]

      ORDER BY [Year],[Month],[Day],[Hour];

    Table Def:

    CREATE TABLE [dbo].[HourlyPassiveAgg](
    [Name] [nvarchar](150) NOT NULL,
    [DeviceId] [varchar](50) NOT NULL,
    [Year] [int] NOT NULL,
    [Month] [int] NOT NULL,
    [Day] [int] NOT NULL,
    [Hour] [int] NOT NULL,
    [CountOfTrans] [int] NOT NULL
    )

    Thursday, July 19, 2018 12:56 PM

Answers

  • use parameters for that

    like below

    DECLARE @Year int, @Month int
    
    SELECT TOP 1 @Year = [Year],
    @Month =[Month]
    FROM dbo.HourlyPassiveAgg
    ORDER BY [Year] DESC, [Month] DESC
    
    
    SELECT [Year],[Month],[Day],[Hour], SUM([CountOfTrans])
    
      FROM [dbo].[HourlyPassiveAgg]
    
      WHERE Year = @Year  --This is what I want to make dynamic to get most recent so I don't have to change this
    
      AND Month = @Month      --This is what I want to make dynamic to get most recent so I don't have to change this
    
      GROUP BY [Year],[Month],[Day],[Hour]
    
      ORDER BY [Year],[Month],[Day],[Hour];
    


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, July 19, 2018 1:14 PM

All replies

  • use parameters for that

    like below

    DECLARE @Year int, @Month int
    
    SELECT TOP 1 @Year = [Year],
    @Month =[Month]
    FROM dbo.HourlyPassiveAgg
    ORDER BY [Year] DESC, [Month] DESC
    
    
    SELECT [Year],[Month],[Day],[Hour], SUM([CountOfTrans])
    
      FROM [dbo].[HourlyPassiveAgg]
    
      WHERE Year = @Year  --This is what I want to make dynamic to get most recent so I don't have to change this
    
      AND Month = @Month      --This is what I want to make dynamic to get most recent so I don't have to change this
    
      GROUP BY [Year],[Month],[Day],[Hour]
    
      ORDER BY [Year],[Month],[Day],[Hour];
    


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, July 19, 2018 1:14 PM
  • Thanks you!  I love it when I overlook the obvious... Much appreciated.
    Thursday, July 19, 2018 2:44 PM