Oracle - Get data from past year (Dynamic)

Question

i have some question regarding about get data from database which need to fetch from past year ( dynamic , not hard-coded )

Table: BookingTable

bookingID   userID     Timebooked 
1             0003    10-May-2016
2             0001    10-May-2018
3             0001    10-Apr-2017
4             0001    10-Apr-2017
5             0003    10-Jan-2011
6             0006    10-Apr-2018
7             0003    10-Apr-2016
8             0006    10-Apr-2015
9             0001    10-Apr-2017

This is my code below

SELECT userID, COUNT(userID) AS count
FROM BookingTable 
GROUP BY userID
HAVING COUNT(userID) >2

Here is the result from my query above

userID  count
0001      4
0003      3

From the table i would like to add where the timebooked is from past year to current date.

Tim Biegeleisen · Accepted Answer · 2018-03-29T06:33:11.287

2

You may use sysdate - interval '1' year to represent today's date one year ago:

SELECT userID, COUNT(userID) AS count
FROM BookingTable 
WHERE Timebooked BETWEEN SYSDATE - INTERVAL '1' year AND SYSDATE
GROUP BY userID
HAVING COUNT(userID) > 2

As @Wernfried correctly pointed out below, the above would fail for February 29 in a leap year. Instead, use this workaround:

WHERE Timebooked BETWEEN ADD_MONTHS(SYSDATE, -12) AND SYSDATE

edited Mar 29 '18 at 06:33

answered Mar 29 '18 at 02:00

Tim Biegeleisen

502,043
27
286
360

Hello Time, after adding "WHERE Timebooked >= SYSDATE - INTERVAL '1' year" it will appear (NOT A GROUP BY expression) – whalesboy Mar 29 '18 at 02:04
Is `Timebooked` a date column? What is the type? – Tim Biegeleisen Mar 29 '18 at 02:05
DATE datatype with NOT NULL – whalesboy Mar 29 '18 at 02:06
Thank You for the help @Tim Biegeleisen , i just realized that I have some missing expression, everything is solved ! thanks for the help – whalesboy Mar 29 '18 at 02:12
1

`- INTERVAL '1' year` will fail on 29th of February. Consider to use `add_months(SYSDATE, -12)` – Wernfried Domscheit Mar 29 '18 at 06:24
No, [ADD_MONTHS](https://docs.oracle.com/database/121/SQLRF/functions011.htm#SQLRF00603) documentation says: *If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month.* `ADD_MONTHS(DATE '2016-02-29', -12)` returns `2015-02-28` – Wernfried Domscheit Mar 29 '18 at 06:33
@WernfriedDomscheit Thanks for this comment. You clearly know Oracle very well. – Tim Biegeleisen Mar 29 '18 at 06:33

Oracle - Get data from past year (Dynamic)

1 Answers1

Linked