Home » SQL & PL/SQL » SQL & PL/SQL » Average every 7 days
Average every 7 days [message #281077] Thu, 15 November 2007 11:48 Go to next message
speaker
Messages: 30
Registered: April 2006
Member
Hi,

i need to find average Value every 7 days between a given start date and end date

Userid is not unique ,, has has different reading during different dates

USERID   READING     R_DATE  
1          8.2        1/10/2007
1          4.2        10/10/2007
2          82.2       20/10/2007
2          3.2        2/11/2007
3          84.2       10/11/2007


Given the start and end dates between 1st Oct and 1st Nov

i need to find the average readings per user per week between 1st Oct and 1st Nov

eg:

USERID AVGTREADING R_DATE
1 10.2 7/10/2007
2 2.2 7/10/2007
1 2.2 14/10/2007


etc



Re: Average every 7 days [message #281082 is a reply to message #281077] Thu, 15 November 2007 12:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
'WW' date format model gives you the week you then you have to group by this and your id.

Regards
Michel
Re: Average every 7 days [message #281083 is a reply to message #281082] Thu, 15 November 2007 12:08 Go to previous messageGo to next message
speaker
Messages: 30
Registered: April 2006
Member
But how do i iterate between the given startdate and enddate..
Re: Average every 7 days [message #281086 is a reply to message #281083] Thu, 15 November 2007 12:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You use "where" and you group by week.
I don't understand the question.

Regards
Michel
Re: Average every 7 days [message #281089 is a reply to message #281077] Thu, 15 November 2007 12:41 Go to previous messageGo to next message
speaker
Messages: 30
Registered: April 2006
Member
I got your point...
i was confusing myself with complex queries of getting the result that a simple solution didint strike me..

thanks a lot..u saved my time..
Re: Average every 7 days [message #281096 is a reply to message #281089] Thu, 15 November 2007 13:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post your solution for other readers.

Regards
Michel
Re: Average every 7 days [message #281098 is a reply to message #281096] Thu, 15 November 2007 13:20 Go to previous messageGo to next message
speaker
Messages: 30
Registered: April 2006
Member
Hello
This was the query for finding average by week

SELECT   user_num, AVG (glucose_read), TRUNC (glucose_date, ''WW''),
             TRUNC (glucose_date, ''WW'') + 7
        FROM dmt_clinical_data
       WHERE glucose_date IS NOT NULL
         AND glucose_date <= :end_date
         AND TRUNC (glucose_date, ''WW'') + 7 < :end_Date
    GROUP BY user_num, TRUNC (glucose_date, ''WW'')
    ORDER BY TRUNC (glucose_date, ''WW''), user_num



But i am facing one more problem...
i have a requirement to fine average for every 2 weeks..now facing problem in this...

can u help

[Updated on: Thu, 15 November 2007 13:21]

Report message to a moderator

Re: Average every 7 days [message #281099 is a reply to message #281077] Thu, 15 November 2007 13:24 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>can u help
"U" is not a member of this forum.

YOU should NOT use IM-speak on this forum!

what happens if the "7" is replaced with"14"?

[Updated on: Thu, 15 November 2007 13:24] by Moderator

Report message to a moderator

Re: Average every 7 days [message #281102 is a reply to message #281099] Thu, 15 November 2007 13:40 Go to previous messageGo to next message
speaker
Messages: 30
Registered: April 2006
Member
This is the output i get when i find average for 1 week

UNUM   AVG	StartDate	EndDate
1	34	10/1/2007	10/8/2007
1	3.5	10/8/2007	10/15/2007
1	5	10/15/2007	10/22/2007
1	6.75	10/22/2007	10/29/2007



for average for 2 weeks
i already tried 14 instead of 7 but i am not getting it .
the second row should start from 10/16/2007
UNUM   AVG	StartDate	EndDate
1	34	10/1/2007	10/15/2007
1	3.5	10/8/2007	10/22/2007
1	5	10/15/2007	10/29/2007
Re: Average every 7 days [message #281103 is a reply to message #281102] Thu, 15 November 2007 13:50 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ for a week, it starts at day 0 and end at day +6 (not +7)
2/ for 2 weeks, it starts at day 0 and end at day +15 and you keep only one week out of 2, either even or odd (clue: see MOD function)

Regards
Michel
Previous Topic: Fetch special datas of any table in a procedure with ref cursor
Next Topic: Page Pagination with a Count (can this be done)
Goto Forum:
  


Current Time: Wed Dec 07 22:17:51 CST 2016

Total time taken to generate the page: 0.09206 seconds