How to select previous month's date?

[Saaz]

Hi,

My job profile includes each day observation. I want to create new tables, which will contains the same data, but only the last previous observation from each month.


I used below code:


Select * into AB_CAD_M from AB_CAD where ObsDateCAD = ?

Do you aware about some method to select this last date from previous month using SQL query? Please help me on this.

[Modifier]

Please use below method:


Select P1.*
into AB_CAD_M
from AB_CAD as P1 inner join
(
select
dateadd(month,datediff(month,-1,ObsDateCAD ),-1) as Obs_Date_CAD1,
dateadd(month,datediff(month,-1,ObsDateCAD ),0) as Obs_Date_CAD2

from AB_CAD

group by ObsDateCAD

) as t2
on t1.ObsDateCAD >= P2.Obs_Date_CAD1 and P1.ObsDateCAD < P2.Obs_Date_CAD2

[kelfro]

If your problem not yet solved then please use below query:

where ObsDateCAD = (select max(ObsDateCAD) from CAD group by Year(ObsDateCAD),Month(ObsDateCAD))


//In above query the group by function group the records according to year and month

hopefully this will help you.

[Zecho]

Below query will definitely help you in your problem:

select colmn1, colmn2, ...
from
(select
row_num() over (partition by ObsDateCAD order by ObsDateCAD desc) as rownumber,
*
from CAD A1
where A1.ObsDateCAD=
(
select
max(ObsDateCAD) as ObsDateCAD
from CAD
where year(A1.ObsDateCAD)=year(ObsDateCAD)
and month(A1.ObsDateCAD)=month(ObsDateCAD)
)
)dt
where rownumber=1