From Promotion table get max value by date

Hi,

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]

data is given below.

INSERT INTO [UserDB].[dbo].[Promotion1]

([Prom_No]

,[Prom_Date]

,[Prom_EmpName]

,[Prom_PromDesig]

,[Prom_PromSal]

,[Prom_LastDesig]

,[Prom_LastSal])

VALUES

,'2016-01-01'

,'alex'

,'HR2'

,200

,'HR1'

,100)

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)

The result should be below.

Prom_No	Prom_Date	Prom_EmpName	Prom_PromDesig	Prom_PromSal	Prom_LastDesig	Prom_LastSal
2	01-06-2016	alex	HR3	400	HR2	200

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Hari Haran8y ago
SELECT MAX(PROM_No) FROM Promotion1 WHERE Prom_Date IN(
SELECT MAX(Prom_Date) From Promotion1)
0
Saikat Mekdar8y ago
Hi Basit,

Your ans is...

select * from Promotion1o where CAST(o.Prom_Date as date)=cast((select MAX(Prom_Date) from Promotion1)as DATE)
0
Chetan Ranpariya8y ago
Show us what query you tried and what issue you faced with that query.
+1