0

Hi,

Below is code to gather information from a beg and end date but hwo would I generate information on each day of the week. Sunday - Saturday for a set of specific date ranges?

Select
Count(b.max)
From 
lm.dw a,
lm.bw b
Where
(a.Start_dt <= getdate() and a.End_dt >= getdate())
and (b.pic_x= a.pic_x);

Select
Count(b.max)
From 
lm.bw b;
2
Contributors
1
Reply
2
Views
12 Years
Discussion Span
Last Post by jim mcnamara
0

Your specifications are kinda fuzzy - I used PL/SQL to get what I thought you wanted - one weeks worth of data. The &1 thing is for interactive input.
If you saved this as "someday.sql" then usage in sqlplus would be

@someday 01-NOV-2005
SET SERVEROUT ON SIZE 1000000
spool t.lis
DECLARE
    mydate DATE:='&1';
    endit NUMBER(2):=1;


cursor getbydate is
    select count(*) cnt , TRUNC(datefld) fld 
    from mytable
    where
        datefld>=mydate and datefld<=mydate+6
    group by TRUNC(datefld);
    

BEGIN
  dbms_output.enable(1000000);
  for x in getbydate
  loop
    dbms_output.put_line(
         to_char(x.cnt,'999999') || '  ' || 
         rpad(TO_CHAR(x.fld,'DAY'),10) || ' ' || x.fld );
  end loop;
END;
/
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.