Friday, 27 July 2018

Monthly data Extraction Logic using Oracle SQL

The following logic is used to get data from Database for monthly data extraction

For example if you want employee attendance data from DB for monthly then logic will be like shown in below

Select  Emp_attendance from Employee where Emp_attendance between add_months(trunc(sysdate,'mm'),-1) and last_day(add_months(trunc(sysdate,'mm'),-1)) ;

You can cross check whether the logic is getting correct dates or not by firing the below queries in sql developer or any tool you used to execute the queris

The below query will give you the first day date of  last month:
select add_months(trunc(sysdate,'mm'),-1) from dual;

The below query will give you the last day date of  last month:
select last_day(add_months(trunc(sysdate,'mm'),-1)) from DUAL;

No comments:

Post a Comment

If you Like my blog Spread it and help friends for whom this blog is useful for their career.