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;

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;

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;

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;

Malcolm Dew-Jones schrieb:
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

Malcolm Dew-Jones schrieb:
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

Malcolm Dew-Jones schrieb:
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

Malcolm Dew-Jones schrieb:
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

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

On Apr 17, 2:26*pm, yf... (AT) vtn1 (DOT) victoria.tc.ca (Malcolm Dew-Jones)
wrote:
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,'S AT',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.