From Promotion table get max value by date

1

Hi,
From Promotion table get max value by date

I have promotion table.

CREATE TABLE [dbo].[Promotion1](
	[Prom_No] [numeric](18, 0) NULL,
	[Prom_Date] [date] NULL,
	[Prom_EmpName] [nvarchar](50) NULL,
	[Prom_PromDesig] [nvarchar](50) NULL,
	[Prom_PromSal] [numeric](18, 0) NULL,
	[Prom_LastDesig] [nvarchar](50) NULL,
	[Prom_LastSal] [numeric](18, 0) NULL
) ON [PRIMARY]

GO

data is given below.

INSERT INTO [UserDB].[dbo].[Promotion1]
           ([Prom_No]
           ,[Prom_Date]
           ,[Prom_EmpName]
           ,[Prom_PromDesig]
           ,[Prom_PromSal]
           ,[Prom_LastDesig]
           ,[Prom_LastSal])
     VALUES
           (1
           ,'2016-01-01'
           ,'alex'
           ,'HR2'
           ,200
           ,'HR1'
           ,100)
GO

INSERT INTO [UserDB].[dbo].[Promotion1]
           ([Prom_No]
           ,[Prom_Date]
           ,[Prom_EmpName]
           ,[Prom_PromDesig]
           ,[Prom_PromSal]
           ,[Prom_LastDesig]
           ,[Prom_LastSal])
     VALUES
           (2,
           '2016-06-01'
           ,'alex'
           ,'HR3'
           ,400
           ,'HR2'
           ,200)
GO

the result looking for is.

image

Thanks
Basit.

2

(Not Applicable to this q).