-2

i have a table and the columns like

Start_date timestamp,
end_date   timestamp,
id number,
cost number(10,2).

And the data which i inserted into the table 'll be like this

1,'2013-02-03 00:00:00','2013-02-03 00:00:00',75*0.06
1,'2013-02-04 00:00:00','2013-02-04 00:00:00',75*0.06
1,'2013-02-05 00:00:00','2013-02-05 00:00:00',75*0.06
1,'2013-02-06 00:00:00','2013-02-06 00:00:00',75*0.06
1,'2013-02-07 00:00:00','2013-02-07 00:00:00',75*0.06
1,'2013-02-08 00:00:00','2013-02-08 00:00:00',75*0.06
1,'2013-02-09 00:00:00','2013-02-09 00:00:00',75*0.06

and now i want to group the columns Start_date and end_date by Sunday to Saturday.

can you please help me on this.

Thanks in Advance.

Dileep
  • 624
  • 3
  • 10
  • 20
  • `week(start_date)` and `week(end_date)` gives you week number; from there you can group – जलजनक Feb 12 '13 at 17:16
  • @Dileep - Add some sample data and desired output. I personally do not understand what does it mean group by Sun to Sat... You can order by dates or week numbers or days etc... – Art Feb 12 '13 at 17:17
  • @DoSparKot when i Tried like this select week(start_date),week(end_date) from table1; it is throwing error – Dileep Feb 12 '13 at 17:22
  • @Art sample data 'll be like this 1,'2012-12-23 00:00:00','2012-12-23 00:00:00',null,null,343*0.06 – Dileep Feb 12 '13 at 17:26
  • update the post with table schema and sample data. use `show create table tablename` – जलजनक Feb 12 '13 at 17:28
  • 2
    One row of sample data and no expected output doesn't help us much. How do you group by two different columns? Particularly when those two columns fall in different weeks? – Justin Cave Feb 12 '13 at 17:29
  • @Dileep - please post your expected answer too – Fathah Rehman P Feb 12 '13 at 17:34
  • @Dileep- Question is bit difficult to understand. I created one useful query. But its not fully correct. I still have confusion in your question, especially in the " i want to group the columns Start_date and end_date" part . Please add expected result too so i can tell you correct query. – Fathah Rehman P Feb 12 '13 at 17:38

1 Answers1

0

This is the best I can offer you as your question is impossible to understand as well as that sample you provided. ISO week table for 2013:

SELECT start_date  -- 1/1/2013 --
 , TRUNC(start_date, 'iw')                  wk_starts  
 , TRUNC(start_date, 'iw') + 7 - 1/86400    wk_ends
 , TO_NUMBER (TO_CHAR (start_date, 'IW'))   ISO_wk#_iw  
 , TO_CHAR(start_date, 'DAY')               wk_day
FROM
 (
  SELECT TRUNC(SYSDATE, 'YEAR')-1 + LEVEL AS start_date
   FROM dual
 CONNECT BY LEVEL <= 
 (
  SELECT TRUNC(ADD_MONTHS (SYSDATE, 12), 'Y')-TRUNC(SYSDATE, 'Y') "Num of Days in 2013"   
    FROM dual
 )
)
/

START_DATE    WK_STARTS    WK_ENDS            ISO_WK#   WK_DAY
----------------------------------------------------------------------
1/1/2013    12/31/2012    1/6/2013 11:59:59 PM    1    TUESDAY  
1/2/2013    12/31/2012    1/6/2013 11:59:59 PM    1    WEDNESDAY
.....
1/7/2013    1/7/2013    1/13/2013 11:59:59 PM    2    MONDAY   
1/8/2013    1/7/2013    1/13/2013 11:59:59 PM    2    TUESDAY
.....

You can add any other formats for your date and order by it I guess...

Art
  • 5,616
  • 1
  • 20
  • 22
  • the problem with query is i can't use Trunc because i am using Netezza DB – Dileep Feb 12 '13 at 17:38
  • @Dileep - What can you use? ROUND() may work... If TRUNC() is the problem then you need to find equivalent in Netezza. I never heard of it. You have Oracle tags in your question, so, I assumed Oracle query would help, give some idea... – Art Feb 12 '13 at 17:39
  • most of the oracle functions ll use, but we c't use some of those like TRUNC,Level,etc.. – Dileep Feb 12 '13 at 17:56
  • i need the exact o/p which u have provided, but when i tried ur query with the respective changes in Netezza it's throwing error found "START_DATE" (at char 130) expecting `USING' or `')'' or `','' – Dileep Feb 12 '13 at 18:03
  • @Dileep - very sorry, cannot help you with Netezza... I used LEVEL only to build table on the fly. You do not need to use that-you already have table with data. Try finding equivalents to Oracle TRUNC/ROUND functions in Netezza. – Art Feb 12 '13 at 18:08