Member Avatar for djbsabkcb

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
  • 96 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by jim mcnamara
Member Avatar for jim mcnamara

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;
/
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.