little sql question

[pando]

Hi

I have this SQL causing me some trouble these few days. I have following table with data such as invented

day            calls        
20020901       130
20020902       129
20020903       128
20020904       127
20020905       126
20020906       125
20020907       124
20020908       123
20020909       122
20020910       121
20020911       120
20020912       119
20020913       118
20020914       117
20020915       116
20020916       115
20020917       114
20020918       113
20020919       112
20020920       111
20020921       110
20020922       109
20020923       108
20020924       107
20020925       106
20020926       105
20020927       104
20020928       103
20020929       102
20020930       101
20021001       123
20021002       101
20021003       102
20021004       103
20021005       104
20021006       105
20021007       106
20021008       107

now I need to add a third column which calculates the average calls for that same week day in last month

For example if today is Monday 7th of October then I should see

day            calls     average_on_september_mondays
20021007       106       115

I get 115 because I add the calls from 20020902, 20020909, 20020916, 20020923, 20020930 then divided by 5 (5 Mondays on September)

Does anyone know how to calculate this on fly? I guess I can use lag/lead functions or some dodgy case statement but cant see how to start though

[marist89]

Hmmm, where's that thinking cap when I need it...


When you want the "average calls for that same week day in last month", would you expect the same number for 20021014 as you get for 20021007?

Is "day" a DATE or NUMBER?

[pando]

day is date sorry, forgot to put that

it's trunc of date so they are all

2002monthday 00:00:00

I mean week days from Monday tu Sunday

if today is Monday then I have to find the deviation of number of today's calls with last months Mondays average, to find the deviation I have to first calculate the average

then to find deviation would be as follows:
20021007 is Monday so 20021014, I will have to take those
day's number of calls and compare with September's Mondays average calls. 20021008 will compare with September's Tuesday average calls, 1009 with Wednesdays and so on

Finding deviation is easy so I am not too worry about it, the problems is to add a third average column which must correspond to each respective week day.

I also forgot to mention that September does not need a third column (I can leave it null) because I dont have any augost data to calculate last month's week days average. This table will grow and contains data from all months from October

[stecal]

I'm quitting my job so I can work on this problem. I'll get back to you real soon.

[stecal]

Okay, it's not the best table in terms of normalization, but to make the data collection a lot easier, why not add a column that reflects the day of the week (day number 1-7). That takes of the "how many Monday's were there last month" question - your select is based on where day_of_week = 1 (or 2).

[pando]

what i do to calculate the average of week days is something like

select to_char(day, 'YYYYMM-DAY') week_days, 
       avg(calls) average_week_Day_calls
from call
group by to_char(day, 'YYYYMM-DAY');

WEEK_DAYS        AVERAGE_WEEK_DAY_CALLS
---------------- ----------------------
200209-FRIDAY                     114.5
200209-MONDAY                       115
200209-SATURDAY                   113.5
200209-SUNDAY                       116
200209-THURSDAY                   115.5
200209-TUESDAY                    117.5
200209-WEDNESDAY                  116.5
200210-FRIDAY                       103
200210-MONDAY                       106
200210-SATURDAY                     104
200210-SUNDAY                       105
200210-THURSDAY                     102
200210-TUESDAY                      115
200210-WEDNESDAY                    101

to get last months weekdays average

select to_char(day, 'YYYYMM-DAY') week_days,
       avg(calls) average_week_Day_calls
  from call
 where day between last_day(add_months(trunc(sysdate), - 2)) + 1
               and last_day(add_months(trunc(sysdate), - 1))
group by to_char(day, 'YYYYMM-DAY')

WEEK_DAYS        AVERAGE_WEEK_DAY_CALLS
---------------- ----------------------
200209-FRIDAY                     114.5
200209-MONDAY                       115
200209-SATURDAY                   113.5
200209-SUNDAY                       116
200209-THURSDAY                   115.5
200209-TUESDAY                    117.5
200209-WEDNESDAY                  116.5

But from there I am stucked

btw, who wants to give this a shot I paste the code to generate the data

create table call
(day date,
 calls number);

begin
  for i in reverse 1..30
  loop
    insert into call values(trunc(sysdate, 'mm')-i, i+100);
  end loop;
end;
/

begin
  for i in 0..7
  loop
    insert into call values(trunc(sysdate, 'mm')+i, i+100);
  end loop;
end;
/

update call set calls = 123
where trunc(day) = to_date('20021001', 'YYYYMMDD');

commit;

[Edited by pando on 10-10-2002 at 05:39 PM]

[jmodic]

THis one is not using analytical (windowing) functions, but it seems that it returns what you want:

SELECT day, calls,
      (SELECT AVG(CALLS) FROM my_table
       WHERE day BETWEEN ADD_MONTHS(TRUNC(mt.day,'MM'),-1) AND TRUNC(mt.day,'MM')-1
       AND TO_CHAR(day,'D') = TO_CHAR(mt.day,'D')
      ) AS avg_same_DOW_previous_month 
FROM my_table mt;

[pando]

oh yes, that's what I had been thinking as well but since I am not pretty good with these scalar correlated queries I was stucked there too

I will give this a try tomorrow with the real table

cheers

[marist89]

That jmodic... I was just working on something like this...

[stecal]

Maybe I can get my job back.