SQL help

[dps]

Hello,

I am a novice in Oracle sql and would appreciate some assistance with a query.

I currently have a script

SELECT SUM(ad_len)/60,
FROM ad a
where prog_code = 'SM'
and brk_date > '30-Apr-04'
and brk_date < '01-Jun-04'
and brk_time BETWEEN &1 and &2

Rather then having to input values each time for each row to be retrieved (eg 0900 for 1 and 1000 for 2). Can the above query be best done by PL/SQL or indeed SQL?? A loop construct??

Thanks in advance.

 

[Turkbear]

Hi,
Use && instead of & - that way only prompts the first time..

Even better use the Accept/Prompt syntax:

PROMPT Enter Start Hour
PROMPT For example:  0900,1000,2100
PROMPT Enter Ending Hour
PROMPT For example:  0900,1000,2100

ACCEPT StTime PROMPT Start Hour: '
ACCEPT EndTime PROMPT End Hour: '

SELECT SUM(ad_len)/60, 
  FROM ad a
  where prog_code = 'SM'
  and   brk_date > '30-Apr-04'
  and brk_date < '01-Jun-04'
  and brk_time  BETWEEN &&StTime and &&EndTime;

Hope it helps...

 

[dps]

Yes of course that is very refreshing. But better still could not the values derived for brk_time, as a range of time (say 0900 - 1000) be supplied as input for each row to be retrieved, rather like a sub-query providing values in a loop to a parent one??

 

[SantaMufasa]

DPS,

I presume you want your code to occur/loop for each 1-hour block of hours throughout a 24-hour clock, regardless of "brk_date", correct? And since you are summing the "ad_len"s and dividing by 60, this implies grouping by your "brk_time". So, code to do what it appear you want could be as follows:

SELECT SUM(ad_len)/60 a, substr(to_char(brk_time),1,2) b
  FROM ad a
  where prog_code = 'SM'
  and   trunc(brk_date) between
	to_date('30-Apr-04','dd-Mon-rr') and
	to_date('01-Jun-04','dd-Mon-rr')
  group by substr(brk_time,1,2)
/

The depends upon "brk_time" being a consistently 4-character alphanumeric column; if it is numeric, then you must ensure that you less-than-4-char "brk_time" entries are left-padded with zeros.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:33 (21Jun04) UTC (aka "GMT" and "Zulu"), 10:33 (21Jun04) Mountain Time)

 

[dps]

Yes brk_time is numerical in datatype.

Yes it would be a requirement to the total time of breaks(brk_time) for month of May broken down for each 1 hour

brk_time ad_len/60
0900 - 1000 230
1000 - 1100 130
...
2200 - 2300 100

Total ads 460 for May

I guess I could throw in as parameters - the brk_time, to a function and then have a select statement within a loop call that function throwing in a brk_time (start) and brk_time(end).....something like that.


Thanks