Home » SQL & PL/SQL » SQL & PL/SQL » group the data on weeks starting from sunday
group the data on weeks starting from sunday [message #383268] Wed, 28 January 2009 01:00 Go to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
is this possible to group the data on week.?
Re: group the data on weeks starting from sunday [message #383271 is a reply to message #383268] Wed, 28 January 2009 01:07 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
yes, no, maybe, huh?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you
Re: group the data on weeks starting from sunday [message #383273 is a reply to message #383268] Wed, 28 January 2009 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you have no date information in your data it is not possible.

Regards
Michel
Re: group the data on weeks starting from sunday [message #383279 is a reply to message #383273] Wed, 28 January 2009 01:21 Go to previous messageGo to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
ya date is availabe at which we want to group the data.

we will have some range of data like 05-jan-2009 to 25-jan-2009.
in this range we need to group the data on weeks.

but i want like if range start from wednesday then wed-sat would be one group.
second group from sun-sat and so on until last group.

i tried it myself but i m having a problem with it. it always end the week at thursday.

Like

SELECT COUNT(*),TO_CHAR( sched_Date, 'W' ),MIN(sched_date),MAX(sched_date) FROM mytable
WHERE sched_date BETWEEN '03-jan-2009' AND '28-jan-2009'
GROUP BY TO_CHAR( sched_Date, 'W' )
ORDER BY TO_CHAR( sched_Date, 'W' )

[Updated on: Wed, 28 January 2009 01:24]

Report message to a moderator

Re: group the data on weeks starting from sunday [message #383280 is a reply to message #383279] Wed, 28 January 2009 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: group the data on weeks starting from sunday [message #383285 is a reply to message #383268] Wed, 28 January 2009 01:44 Go to previous messageGo to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
yea here it is.

CREATE TABLE abc ( emp_id VARCHAR2(10), duty_Date DATE);
	   
DECLARE
BEGIN
FOR i IN 1..5 LOOP
FOR j IN 1..11 LOOP
INSERT INTO abc VALUES (i,SYSDATE-j);
END LOOP;
END LOOP;
END ;



----------------

now if i provide the date range like 17-jan to 26-jan then out put i want like

count	min(date)	max(date)
5	17-Jan-2009	17-Jan-2009
35	18-Jan-2009	24-Jan-2009
10	25-Jan-2009	26-Jan-2009


drop table abc;

thanks
Re: group the data on weeks starting from sunday [message #383289 is a reply to message #383285] Wed, 28 January 2009 02:10 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Would the 'IW' format mask (instead of 'W') do any good?
SQL> select count(*) cnt, to_char(duty_date, 'iw') week,
  2    min(duty_date) min_date, max(duty_date) max_date
  3  from abc
  4  where duty_date between to_date('17.01.2009', 'dd.mm.yyyy')
  5                      and to_date('26.01.2009', 'dd.mm.yyyy')
  6  group by to_char(duty_date, 'iw')
  7  order by to_char(duty_date, 'iw');

       CNT WEEK       MIN_DATE   MAX_DATE
---------- ---------- ---------- ----------
        10 03         17.01.2009 18.01.2009
        35 04         19.01.2009 25.01.2009
         5 05         26.01.2009 26.01.2009

SQL>
Re: group the data on weeks starting from sunday [message #383295 is a reply to message #383289] Wed, 28 January 2009 02:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If I understand the example the groups are from Sunday to Saturday.
Unfortunatly the default format models neither work with ISO week (IW which starts on Monday) nor with common week (W or WW which start on first day of the month or the year).

In this case, IW seems to be the smarter one to make the group changing the value for Sunday.

Regards
Michel
Re: group the data on weeks starting from sunday [message #383335 is a reply to message #383295] Wed, 28 January 2009 06:07 Go to previous messageGo to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
but 'IW' group on MON to SUN but i required it for SUN to SAT? is this possible some how?
Re: group the data on weeks starting from sunday [message #383343 is a reply to message #383335] Wed, 28 January 2009 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just a matter of a small adjustment. Check if it is Sunday and change the value of week.
Try it and if you can't do it post what you tried.

Regards
Michel
Re: group the data on weeks starting from sunday [message #383346 is a reply to message #383335] Wed, 28 January 2009 06:36 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
kashifchughtai wrote on Wed, 28 January 2009 13:07
but 'IW' group on MON to SUN but i required it for SUN to SAT? is this possible some how?

A simple date arithmetic could achieve it too:
- add one day to the date (so SUN becomes MON, ..., SAT becomes SUN)
- truncate the result to ISO week (MON)
- subtract one day from the result (SUN)
That's all.
Re: group the data on weeks starting from sunday [message #383357 is a reply to message #383285] Wed, 28 January 2009 08:22 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
kashifchughtai wrote on Wed, 28 January 2009 02:44

WHERE sched_date BETWEEN '03-jan-2009' AND '28-jan-2009'



Littlefoot fixed your code by using the proper TO_DATE function. Relying on implicit conversion is a lazy habit that will come back and bite you one day.

kashifchughtai wrote on Wed, 28 January 2009 02:44

CREATE TABLE abc ( emp_id VARCHAR2(10), duty_Date DATE);
...
FOR i IN 1..5 LOOP
INSERT INTO abc VALUES (i,SYSDATE-j);




This one is no good either. i is a number and first column in your table is a varchar2. Additionally, you should name your columns explicitly as adding a column to the table would make your code fail.

You really should address these items as you are just starting out. Bad habits die hard.
Re: group the data on weeks starting from sunday [message #383624 is a reply to message #383357] Thu, 29 January 2009 06:03 Go to previous message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
i got it...thanks alot fot the help and suggestions.Smile
Previous Topic: PLSQL script not collecting temp table fields - variables problem?
Next Topic: Wheter FORALL works for DML operations only
Goto Forum:
  


Current Time: Wed Dec 07 06:51:46 CST 2016

Total time taken to generate the page: 0.33624 seconds