Select records from previous week based on current date

Database
Enthusiast

Select records from previous week based on current date


I need to automate some queries that will run batched at specific days, such as Monday. In this I need to select records for the previous week, weeks for us start Monday and end Sunday. Temporal calculations are prety confusing for me.

Is there a way to specify the previous 7 day period based on the current date, so that I wont need to modify a date range each time the query is executed?
5 REPLIES
Junior Supporter

Re: Select records from previous week based on current date

... WHERE WHATEVER_YOUR_DATE_IS >= DATE - 7 ???

Cheers.

Carlos.
Enthusiast

Re: Select records from previous week based on current date

Select * From MyTab MyT
Where MyT.Dt in
(Select D.Calendar_Date From Sys_Calendar.Calendar D -- all days of last week
Join
(Select Max(C.Calendar_Date) As ST_Monday -- The Monday of last week
From Sys_Calendar.Calendar C
Where C.Day_Of_Week = 2
And C.Calendar_Date LE
(Select Max(A.Calendar_Date)
From Sys_Calendar.Calendar A -- One week before current date
Join Sys_Calendar.Calendar B -- Current Date
On B.Calendar_Date = Current_Date
And A.Day_Of_Week = B.Day_Of_Week
And A.Week_Of_Calendar LT B.Week_Of_Calendar ) ) As LastMon
On D.Calendar_Date Between LastMon.St_MOnday and LastMon.ST_Monday + 6
)
;
Enthusiast

Re: Select records from previous week based on current date

Jimm, thanks so much. This works beautifully!
Enthusiast

Re: Select records from previous week based on current date

BTW, could I trouble you for some help on the syntax? I also need to do the same for a few more iterations, such as Last month, month to date, and week to date.
Enthusiast

Re: Select records from previous week based on current date

Week To Date
------------------
Select * From MyTab MyT
Where MyT.Dt in
(Select D.Calendar_Date From Sys_Calendar.Calendar D
Join
(Select Max(C.Calendar_Date) As ST_Monday -- The Monday of this week
From Sys_Calendar.Calendar C
Where C.Day_Of_Week = 2
And C.Calendar_Date LE Current_Date ) As ThisMon
On D.Calendar_Date Between ThisMon.St_MOnday and Current_Date
)
;

Last Month
--------------
Select * From MyTab MyT
Where MyT.Dt in
(Select D.Calendar_Date From Sys_Calendar.Calendar D
Where D.Calendar_Date Between Cast((Add_Months(Current_Date, - 1)/100 * 100) + 1 As Date)
And Current_Date - Extract(Day From Current_Date)
)
;

Month To Date
------------------
Select * From MyTab MyT
Where MyT.Dt in
(Select D.Calendar_Date From Sys_Calendar.Calendar D
Where D.Calendar_Date Between Cast((Current_Date / 100 * 100) + 1 As Date)
And Current_Date - 1
)
;

********** Note that month to date does not include today.
********** Remove the -1 from current date if you want it to.