Prev: Oracle Trigger Late binding
Next: REQT: SENIOR JAVA CONSULTANT WITH STRONG UI
From: Malcolm Dew-Jones on 17 Apr 2008 14:26
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

This sounds like it should be super simple, but I can't think how to do
it using plain SQL.

I am going to display some rows, and I want to number them in the display,
like using rownum, except that the number only goes up when the row has
some property (I don't care if it displays or not when it doesn't go up).


To explain, imagine

select rownum , the_date , to_char(dates.the_date,'DY') DAY
from my_table
order by the_date

shows

1 1-Jan-2008 TUE
2 2-Jan-2008 WED
3 3-Jan-2008 THU
4 4-Jan-2008 FRI
5 5-Jan-2008 SAT
6 6-Jan-2008 SUN
7 7-Jan-2008 MON
8 8-Jan-2008 TUE

but I don't want to count the weekend. what I want to show would be the
following instead


1 1-Jan-2008 TUE
2 2-Jan-2008 WED
3 3-Jan-2008 THU
4 4-Jan-2008 FRI
5-Jan-2008 SAT
6-Jan-2008 SUN
5 7-Jan-2008 MON
6 8-Jan-2008 TUE

Looks simple, but how to do this in plain SQL?

Feedback welcome, thanks.

Malcolm
From: Ken Denny on 17 Apr 2008 13:40
I believe something like this will do it.

select r_num, the_date, to_char(dates.the_date,'DY') DAY
from (select rownum r_num, the_date
from (select the_date from my_table
where to_char(dates.the_date,'DY') IN
('MON','TUE','WED','THU','FRI')
order by the_date)
union
select null r_num, the_date from my_table
where to_char(dates.the_date,'DY') IN ('SAT','SUN'))
order by the_date;
From: Maxim Demenko on 17 Apr 2008 14:24
Malcolm Dew-Jones schrieb:
> Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
>
> This sounds like it should be super simple, but I can't think how to do
> it using plain SQL.
>
> I am going to display some rows, and I want to number them in the display,
> like using rownum, except that the number only goes up when the row has
> some property (I don't care if it displays or not when it doesn't go up).
>
>
> To explain, imagine
>
> select rownum , the_date , to_char(dates.the_date,'DY') DAY
> from my_table
> order by the_date
>
> shows
>
> 1 1-Jan-2008 TUE
> 2 2-Jan-2008 WED
> 3 3-Jan-2008 THU
> 4 4-Jan-2008 FRI
> 5 5-Jan-2008 SAT
> 6 6-Jan-2008 SUN
> 7 7-Jan-2008 MON
> 8 8-Jan-2008 TUE
>
> but I don't want to count the weekend. what I want to show would be the
> following instead
>
>
> 1 1-Jan-2008 TUE
> 2 2-Jan-2008 WED
> 3 3-Jan-2008 THU
> 4 4-Jan-2008 FRI
> 5-Jan-2008 SAT
> 6-Jan-2008 SUN
> 5 7-Jan-2008 MON
> 6 8-Jan-2008 TUE
>
> Looks simple, but how to do this in plain SQL?
>
> Feedback welcome, thanks.
>
> Malcolm

Alternatively you can achieve it with analytics (if you like to have nls
independent code, slightly more code is required)

SQL> alter session set nls_territory='AMERICA';

Session altered.

SQL> alter session set nls_date_language='AMERICAN';

Session altered.

SQL>
SQL> with my_table as (
2 select date '2008-01-01' + rownum -1 the_date
3 from dual connect by level <=10
4 )
5 select
6 case
7 when
8 to_char(the_date,'d') not in (1,7)
9 then row_number()
10 over(partition by case when to_char(the_date,'d') not in (1,7) then 1
11 end order by the_date)
12 end row_num,
13 the_date,
14 to_char(the_date,'DY') DAY
15 from my_table
16 order by the_date;

ROW_NUM THE_DATE DAY
---------- ------------------ ------------
1 01-JAN-08 TUE
2 02-JAN-08 WED
3 03-JAN-08 THU
4 04-JAN-08 FRI
05-JAN-08 SAT
06-JAN-08 SUN
5 07-JAN-08 MON
6 08-JAN-08 TUE
7 09-JAN-08 WED
8 10-JAN-08 THU


Best regards

Maxim
From: Charles Hooper on 17 Apr 2008 15:04
On Apr 17, 2:26 pm, yf...(a)vtn1.victoria.tc.ca (Malcolm Dew-Jones)
wrote:
> Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
>
> This sounds like it should be super simple, but I can't think how to do
> it using plain SQL.
>
> I am going to display some rows, and I want to number them in the display,
> like using rownum, except that the number only goes up when the row has
> some property (I don't care if it displays or not when it doesn't go up).
>
> To explain, imagine
>
>         select rownum  , the_date , to_char(dates.the_date,'DY') DAY
>         from my_table
>         order by the_date
>
> shows
>
>         1       1-Jan-2008      TUE
>         2       2-Jan-2008      WED
>         3       3-Jan-2008      THU
>         4       4-Jan-2008      FRI
>         5       5-Jan-2008      SAT
>         6       6-Jan-2008      SUN
>         7       7-Jan-2008      MON
>         8       8-Jan-2008      TUE
>
> but I don't want to count the weekend.  what I want to show would be the
> following instead
>
>         1       1-Jan-2008      TUE
>         2       2-Jan-2008      WED
>         3       3-Jan-2008      THU
>         4       4-Jan-2008      FRI
>                 5-Jan-2008      SAT
>                 6-Jan-2008      SUN
>         5       7-Jan-2008      MON
>         6       8-Jan-2008      TUE
>
> Looks simple, but how to do this in plain SQL?  
>
> Feedback welcome, thanks.
>
> Malcolm

A third option, do it with the SUM analytical function and DECODE:
SELECT
TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1) THE_DATE,
TO_CHAR(TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1),'DY') DAY
FROM
DUAL
CONNECT BY
LEVEL<=20;

THE_DATE DAY
--------- ---
01-JAN-08 TUE
02-JAN-08 WED
03-JAN-08 THU
04-JAN-08 FRI
...
19-JAN-08 SAT
20-JAN-08 SUN

Step 1:
SELECT
SUM(DECODE(DAY,'SAT',0,'SUN',0,1)) OVER (ORDER BY THE_DATE)
MY_COUNT,
THE_DATE,
DAY
FROM
(SELECT
TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1) THE_DATE,
TO_CHAR(TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1),'DY') DAY
FROM
DUAL
CONNECT BY
LEVEL<=20);

MY_COUNT THE_DATE DAY
---------- --------- ---
1 01-JAN-08 TUE
2 02-JAN-08 WED
3 03-JAN-08 THU
4 04-JAN-08 FRI
4 05-JAN-08 SAT
4 06-JAN-08 SUN
5 07-JAN-08 MON
6 08-JAN-08 TUE
7 09-JAN-08 WED
8 10-JAN-08 THU
9 11-JAN-08 FRI
9 12-JAN-08 SAT
9 13-JAN-08 SUN
10 14-JAN-08 MON
11 15-JAN-08 TUE
12 16-JAN-08 WED
13 17-JAN-08 THU
14 18-JAN-08 FRI
14 19-JAN-08 SAT
14 20-JAN-08 SUN

Now to remove the numbers that should not print, using a second
DECODE:
SELECT
DECODE(DAY,'SAT',NULL,'SUN',NULL,SUM(DECODE(DAY,'SAT',0,'SUN',0,1))
OVER (ORDER BY THE_DATE)) MY_COUNT,
THE_DATE,
DAY
FROM
(SELECT
TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1) THE_DATE,
TO_CHAR(TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1),'DY') DAY
FROM
DUAL
CONNECT BY
LEVEL<=20);

MY THE_DATE DAY
-- --------- ---
1 01-JAN-08 TUE
2 02-JAN-08 WED
3 03-JAN-08 THU
4 04-JAN-08 FRI
05-JAN-08 SAT
06-JAN-08 SUN
5 07-JAN-08 MON
6 08-JAN-08 TUE
7 09-JAN-08 WED
8 10-JAN-08 THU
9 11-JAN-08 FRI
12-JAN-08 SAT
13-JAN-08 SUN
10 14-JAN-08 MON
11 15-JAN-08 TUE
12 16-JAN-08 WED
13 17-JAN-08 THU
14 18-JAN-08 FRI
19-JAN-08 SAT
20-JAN-08 SUN

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
From: Malcolm Dew-Jones on 17 Apr 2008 16:16
Charles Hooper (hooperc2000(a)yahoo.com) wrote:
: On Apr 17, 2:26=A0pm, yf...(a)vtn1.victoria.tc.ca (Malcolm Dew-Jones)
: wrote:
: > Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
: >
: > This sounds like it should be super simple, but I can't think how to do
: > it using plain SQL.
: >
: > I am going to display some rows, and I want to number them in the display,=

: > like using rownum, except that the number only goes up when the row has
: > some property (I don't care if it displays or not when it doesn't go up).
: >
: > To explain, imagine
: >
: > =A0 =A0 =A0 =A0 select rownum =A0, the_date , to_char(dates.the_date,'DY')=
: DAY
: > =A0 =A0 =A0 =A0 from my_table
: > =A0 =A0 =A0 =A0 order by the_date
: >
: > shows
: >
: > =A0 =A0 =A0 =A0 1 =A0 =A0 =A0 1-Jan-2008 =A0 =A0 =A0TUE
: > =A0 =A0 =A0 =A0 2 =A0 =A0 =A0 2-Jan-2008 =A0 =A0 =A0WED
: > =A0 =A0 =A0 =A0 3 =A0 =A0 =A0 3-Jan-2008 =A0 =A0 =A0THU
: > =A0 =A0 =A0 =A0 4 =A0 =A0 =A0 4-Jan-2008 =A0 =A0 =A0FRI
: > =A0 =A0 =A0 =A0 5 =A0 =A0 =A0 5-Jan-2008 =A0 =A0 =A0SAT
: > =A0 =A0 =A0 =A0 6 =A0 =A0 =A0 6-Jan-2008 =A0 =A0 =A0SUN
: > =A0 =A0 =A0 =A0 7 =A0 =A0 =A0 7-Jan-2008 =A0 =A0 =A0MON
: > =A0 =A0 =A0 =A0 8 =A0 =A0 =A0 8-Jan-2008 =A0 =A0 =A0TUE
: >
: > but I don't want to count the weekend. =A0what I want to show would be the=

: > following instead
: >
: > =A0 =A0 =A0 =A0 1 =A0 =A0 =A0 1-Jan-2008 =A0 =A0 =A0TUE
: > =A0 =A0 =A0 =A0 2 =A0 =A0 =A0 2-Jan-2008 =A0 =A0 =A0WED
: > =A0 =A0 =A0 =A0 3 =A0 =A0 =A0 3-Jan-2008 =A0 =A0 =A0THU
: > =A0 =A0 =A0 =A0 4 =A0 =A0 =A0 4-Jan-2008 =A0 =A0 =A0FRI
: > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 5-Jan-2008 =A0 =A0 =A0SAT
: > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 6-Jan-2008 =A0 =A0 =A0SUN
: > =A0 =A0 =A0 =A0 5 =A0 =A0 =A0 7-Jan-2008 =A0 =A0 =A0MON
: > =A0 =A0 =A0 =A0 6 =A0 =A0 =A0 8-Jan-2008 =A0 =A0 =A0TUE
: >
: > Looks simple, but how to do this in plain SQL? =A0
: >
: > Feedback welcome, thanks.
: >
: > Malcolm

: A third option, do it with the SUM analytical function and DECODE:
: SELECT
: TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1) THE_DATE,
: TO_CHAR(TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1),'DY') DAY
: FROM
: DUAL
: CONNECT BY
: LEVEL<=3D20;

: THE_DATE DAY
: --------- ---
: 01-JAN-08 TUE
: 02-JAN-08 WED
: 03-JAN-08 THU
: 04-JAN-08 FRI
: =2E..
: 19-JAN-08 SAT
: 20-JAN-08 SUN

: Step 1:
: SELECT
: SUM(DECODE(DAY,'SAT',0,'SUN',0,1)) OVER (ORDER BY THE_DATE)
: MY_COUNT,
: THE_DATE,
: DAY
: FROM
: (SELECT
: TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1) THE_DATE,
: TO_CHAR(TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1),'DY') DAY
: FROM
: DUAL
: CONNECT BY
: LEVEL<=3D20);

: MY_COUNT THE_DATE DAY
: ---------- --------- ---
: 1 01-JAN-08 TUE
: 2 02-JAN-08 WED
: 3 03-JAN-08 THU
: 4 04-JAN-08 FRI
: 4 05-JAN-08 SAT
: 4 06-JAN-08 SUN
: 5 07-JAN-08 MON
: 6 08-JAN-08 TUE
: 7 09-JAN-08 WED
: 8 10-JAN-08 THU
: 9 11-JAN-08 FRI
: 9 12-JAN-08 SAT
: 9 13-JAN-08 SUN
: 10 14-JAN-08 MON
: 11 15-JAN-08 TUE
: 12 16-JAN-08 WED
: 13 17-JAN-08 THU
: 14 18-JAN-08 FRI
: 14 19-JAN-08 SAT
: 14 20-JAN-08 SUN

: Now to remove the numbers that should not print, using a second
: DECODE:
: SELECT
: DECODE(DAY,'SAT',NULL,'SUN',NULL,SUM(DECODE(DAY,'SAT',0,'SUN',0,1))
: OVER (ORDER BY THE_DATE)) MY_COUNT,
: THE_DATE,
: DAY
: FROM
: (SELECT
: TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1) THE_DATE,
: TO_CHAR(TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1),'DY') DAY
: FROM
: DUAL
: CONNECT BY
: LEVEL<=3D20);

: MY THE_DATE DAY
: -- --------- ---
: 1 01-JAN-08 TUE
: 2 02-JAN-08 WED
: 3 03-JAN-08 THU
: 4 04-JAN-08 FRI
: 05-JAN-08 SAT
: 06-JAN-08 SUN
: 5 07-JAN-08 MON
: 6 08-JAN-08 TUE
: 7 09-JAN-08 WED
: 8 10-JAN-08 THU
: 9 11-JAN-08 FRI
: 12-JAN-08 SAT
: 13-JAN-08 SUN
: 10 14-JAN-08 MON
: 11 15-JAN-08 TUE
: 12 16-JAN-08 WED
: 13 17-JAN-08 THU
: 14 18-JAN-08 FRI
: 19-JAN-08 SAT
: 20-JAN-08 SUN

: Charles Hooper
: IT Manager/Oracle DBA
: K&M Machine-Fabricating, Inc.

Thanks all, a few good ideas here, thanks.
 | 
Pages: 1
Prev: Oracle Trigger Late binding
Next: REQT: SENIOR JAVA CONSULTANT WITH STRONG UI