Re: SQL: how to use DATE to select last two weeks when the year changes (2006/2007)

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 2 Jan 2007 12:51:28 -0800
Message-ID: <1167771088.914607.304980@n51g2000cwc.googlegroups.com>

joe1977_at_tlen.pl wrote:
> how can one select or group records for last two weeks?
> I used to do it (just for one year):
>
> TO_CHAR(CREATED, 'IW') >= (TO_CHAR(SYSDATE, 'IW')-2)
>
> but now I need to add a year to that query.
>
> Basicaly, I need last 2 weeks of data, even though the year changed
> from 2006 to 2007.
>
>
>
>
> ps. Happy New Year :)

Here is something to experiment with (substitute 2007-01-01 with the first day of the first week of the year): SELECT
  (TO_DATE('2007-01-01', 'YYYY-MM-DD') + (ROWNUM - 1) * 14) PERIOD_START,
  (TO_DATE('2007-01-01', 'YYYY-MM-DD') + (ROWNUM - 1) * 14)+13 PERIOD_END,
  ROWNUM CHECK_PERIOD
FROM
  DUAL
CONNECT BY
  LEVEL <=52;

    PERIOD_START PERIOD_END CHECK_PERIOD

==================== ==================== ============
01-JAN-2007 00:00:00 14-JAN-2007 00:00:00	     1
15-JAN-2007 00:00:00 28-JAN-2007 00:00:00	     2
29-JAN-2007 00:00:00 11-FEB-2007 00:00:00	     3
12-FEB-2007 00:00:00 25-FEB-2007 00:00:00	     4
26-FEB-2007 00:00:00 11-MAR-2007 00:00:00	     5
12-MAR-2007 00:00:00 25-MAR-2007 00:00:00	     6
26-MAR-2007 00:00:00 08-APR-2007 00:00:00	     7
09-APR-2007 00:00:00 22-APR-2007 00:00:00	     8
23-APR-2007 00:00:00 06-MAY-2007 00:00:00	     9
07-MAY-2007 00:00:00 20-MAY-2007 00:00:00	    10
21-MAY-2007 00:00:00 03-JUN-2007 00:00:00	    11
04-JUN-2007 00:00:00 17-JUN-2007 00:00:00	    12
18-JUN-2007 00:00:00 01-JUL-2007 00:00:00	    13
02-JUL-2007 00:00:00 15-JUL-2007 00:00:00	    14
16-JUL-2007 00:00:00 29-JUL-2007 00:00:00	    15
30-JUL-2007 00:00:00 12-AUG-2007 00:00:00	    16
13-AUG-2007 00:00:00 26-AUG-2007 00:00:00	    17
27-AUG-2007 00:00:00 09-SEP-2007 00:00:00	    18
10-SEP-2007 00:00:00 23-SEP-2007 00:00:00	    19
24-SEP-2007 00:00:00 07-OCT-2007 00:00:00	    20
08-OCT-2007 00:00:00 21-OCT-2007 00:00:00	    21
22-OCT-2007 00:00:00 04-NOV-2007 00:00:00	    22
05-NOV-2007 00:00:00 18-NOV-2007 00:00:00	    23
19-NOV-2007 00:00:00 02-DEC-2007 00:00:00	    24
03-DEC-2007 00:00:00 16-DEC-2007 00:00:00	    25
17-DEC-2007 00:00:00 30-DEC-2007 00:00:00	    26
31-DEC-2007 00:00:00 13-JAN-2008 00:00:00	    27
14-JAN-2008 00:00:00 27-JAN-2008 00:00:00	    28
28-JAN-2008 00:00:00 10-FEB-2008 00:00:00	    29
11-FEB-2008 00:00:00 24-FEB-2008 00:00:00	    30
25-FEB-2008 00:00:00 09-MAR-2008 00:00:00	    31
10-MAR-2008 00:00:00 23-MAR-2008 00:00:00	    32
24-MAR-2008 00:00:00 06-APR-2008 00:00:00	    33
07-APR-2008 00:00:00 20-APR-2008 00:00:00	    34
21-APR-2008 00:00:00 04-MAY-2008 00:00:00	    35
05-MAY-2008 00:00:00 18-MAY-2008 00:00:00	    36
19-MAY-2008 00:00:00 01-JUN-2008 00:00:00	    37
02-JUN-2008 00:00:00 15-JUN-2008 00:00:00	    38
16-JUN-2008 00:00:00 29-JUN-2008 00:00:00	    39
30-JUN-2008 00:00:00 13-JUL-2008 00:00:00	    40
14-JUL-2008 00:00:00 27-JUL-2008 00:00:00	    41
28-JUL-2008 00:00:00 10-AUG-2008 00:00:00	    42
11-AUG-2008 00:00:00 24-AUG-2008 00:00:00	    43
25-AUG-2008 00:00:00 07-SEP-2008 00:00:00	    44
08-SEP-2008 00:00:00 21-SEP-2008 00:00:00	    45
22-SEP-2008 00:00:00 05-OCT-2008 00:00:00	    46
06-OCT-2008 00:00:00 19-OCT-2008 00:00:00	    47
20-OCT-2008 00:00:00 02-NOV-2008 00:00:00	    48
03-NOV-2008 00:00:00 16-NOV-2008 00:00:00	    49
17-NOV-2008 00:00:00 30-NOV-2008 00:00:00	    50
01-DEC-2008 00:00:00 14-DEC-2008 00:00:00	    51
15-DEC-2008 00:00:00 28-DEC-2008 00:00:00	    52

More experimentation:
(Today)
SELECT

  TRUNC(NEXT_DAY(SYSDATE,'MONDAY'))-7 PERIOD_START,
  TRUNC(NEXT_DAY(SYSDATE,'MONDAY'))+6 PERIOD_END,
  ((TRUNC(NEXT_DAY(SYSDATE,'MONDAY'))-7)-(TO_DATE('2007-01-01',

'YYYY-MM-DD')))/7+1 CHECK_PERIOD
FROM
  DUAL;     PERIOD_START PERIOD_END CHECK_PERIOD

==================== ==================== ============
01-JAN-2007 00:00:00 14-JAN-2007 00:00:00	     1

(Result from 12 days in the future):
SELECT

  TRUNC(NEXT_DAY(SYSDATE+12,'MONDAY'))-7 PERIOD_START,
  TRUNC(NEXT_DAY(SYSDATE+12,'MONDAY'))+6 PERIOD_END,
  ((TRUNC(NEXT_DAY(SYSDATE+12,'MONDAY'))-7)-(TO_DATE('2007-01-01',

'YYYY-MM-DD')))/14+1 CHECK_PERIOD
FROM
  DUAL;     PERIOD_START PERIOD_END CHECK_PERIOD

==================== ==================== ============
08-JAN-2007 00:00:00 21-JAN-2007 00:00:00	   1.5

(Result from 14 days in the future):
SELECT

  TRUNC(NEXT_DAY(SYSDATE+14,'MONDAY'))-7 PERIOD_START,
  TRUNC(NEXT_DAY(SYSDATE+14,'MONDAY'))+6 PERIOD_END,
  ((TRUNC(NEXT_DAY(SYSDATE+14,'MONDAY'))-7)-(TO_DATE('2007-01-01',

'YYYY-MM-DD')))/14+1 CHECK_PERIOD
FROM
  DUAL;     PERIOD_START PERIOD_END CHECK_PERIOD

==================== ==================== ============
15-JAN-2007 00:00:00 28-JAN-2007 00:00:00	     2

And finally:

SELECT
  TRUNC(NEXT_DAY(SYSDATE,'MONDAY')-7)-13 MONDAY_MINUS_13_DAYS,   TRUNC(SYSDATE) TODAY
FROM
  DUAL; MONDAY_MINUS_13_DAYS TODAY

==================== ====================

19-DEC-2006 00:00:00 02-JAN-2007 00:00:00 This seems to imply that:
  TO_CHAR(CREATED, 'IW') >= (TO_CHAR(SYSDATE, 'IW')-2) Becomes:
  CREATED >= (TRUNC(NEXT_DAY(SYSDATE,'MONDAY')-7)-13) Verify, of course.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Jan 02 2007 - 14:51:28 CST