Group data by week

pts.
Tags:
Oracle
Hi, I would like to group my data by weeks( and the user gives the from to Through date). But the problem is the week in this report starts from Friday and ends on thirsday. for instance if the user gives 1/3/06 to 1/12/06 then I want to display my data like this 1/3/05 - 1/5/05 Sum of my fileds average of mydata .... 1/6/05 - 1/12/05 Sum of my fileds average of mydata .... 1/13/05 Sum of ..........................

Answer Wiki

Thanks. We'll let you know when a new response is added.

Please state whether you’re using DB2, Oracle, or something else.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Smitha80
    We using oracle 8i..
    0 pointsBadges:
    report
  • SheldonLinker
    In Oracle, the difference between any two dates is the number of days between them. For example, TO_DATE('1-Jan-2006') - TO_DATE('1-Dec-2005') is 31. So to begin a week on Friday, pick a friday arbitrarily in the past. For instance, 5-Jan-1990. Subtracting this from a date gives the number of days since that day. Dividing by 7 then gives the number of weeks since that date. Grouping by that groups into weeks. Thus, you can use: SELECT whatever FROM whatever WHERE whatever GROUP BY TRUNC((dateField-TO_DATE('5-Jan-1990'))/7) --- Sheldon Linker (sol@linker.com) Linker Systems, Inc. (www.linkersystems.com) 800-315-1174 (+1-949-552-1904)
    30 pointsBadges:
    report
  • Kojakhu
    Hi In Oracle i'd use an appropriate format string in the to_char function, like a combination of yyyy and ww: SELECT TO_CHAR(t.date_field, 'YYYY,WW') week , SUM(t.value) sumval , AVG(t.value) avgval FROM my_table t WHERE t.date_field BETWEEN :date_lo AND :date_hi GROUP BY TO_CHAR(t.date_field, 'YYYY,WW') links (8.1.7): TO_CHAR: http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/function.htm#79294 Date Format Elements: http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/sql_elem.htm#34926
    0 pointsBadges:
    report
  • Smitha80
    [...] Address: http://itknowledgeexchange.techtarget.com/itanswers/group-data-by-week/ (0) Comments Read [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following