Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2002
    Location
    India
    Posts
    40

    Unanswered: Specifying start and end day for week

    Hi all

    I have the following Query

    SELECT store_cd store_cd,
    (CASE
    WHEN trunc(to_date('&datethru') - to_Date('&datefrom')) < 8 THEN to_char(Interval, 'D')
    WHEN ceil(months_between('&datethru' , '&datefrom')) < = 1 THEN 'Wk'|| to_char(Interval, 'W')
    WHEN ceil(months_between('&datethru' , '&datefrom')) < 4 THEN to_char(Interval, 'Mon')
    ELSE 'Qtr'|| to_char(Interval, 'Q')
    END) Interval,
    SUM(A) A
    FROM Test
    GROUP BY store_cd,
    (CASE
    WHEN trunc(to_date('&datethru') - to_Date('&datefrom')) < 8 THEN to_char(Interval, 'D')
    WHEN ceil(months_between('&datethru' , '&datefrom')) < = 1 THEN 'Wk'|| to_char(Interval, 'W')
    WHEN ceil(months_between('&datethru' , '&datefrom')) < 4 THEN to_char(Interval, 'Mon')
    ELSE 'Qtr'|| to_char(Interval, 'Q')
    END)
    ORDER BY store_cd;


    It displays the data based on datefrom and datethru parameters.

    We have some problem with this.
    I want to say to this Query start week from Monday and End week on Sunday
    So that if in any of the months 1st falls in Saturday ...it has to calculate week total
    from Sat to sunday and next week total from Monday to sunday like that.

    Now it is calculating first 7days as week one , next 7 days as week two like that.

    So help me to get solution for this ASAP

    It i have to do in SQL , so plz suggest in SQL and also in PL/SQL if possible

    thanks
    Suryadevara

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    1) You can create a function:
    Code:
    Create Or Replace Function Datethru
          (Datefrom Date) 
    Return Date Is
    Begin
      If To_Char(Datefrom,'W') = 1 Then
        Return (Datefrom + 7 - To_Char(Datefrom,'D'));
      End If;
      If Trunc(Months_Between(Datefrom, Datefrom + 7)) = 1 Then
        Return Last_Day(Fromdate);
      End If;
      Return (Datefrom + 6);
    End;
    2) OR use a decode statement:
    Code:
     decode(To_Char(Datefrom,'W'),1,(Datefrom + 7 - To_Char(Datefrom,'D'))
    ,decode(Trunc(Months_Between(Datefrom, Datefrom + 7)),1,Last_Day(Fromdate)
    ,Datefrom + 6))
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jun 2002
    Location
    India
    Posts
    40
    Thanks for u r mail , but it is not working as per my requirement.

    To b clear once again this is what i am trying to get.

    Please create one table and insert values from 01-JAN-2000 to 16-JAN-2000

    create table VTest(VDate Date,VValue Number(2))

    Insert into Vtest values('10-JAN-2000','11')

    Create another table

    create table FT(VDate Date,VDate1 date)

    Insert into FT values('01-JAN-2000','31-JAN-2000') just this value is enough..

    In this date period (01-JAN-2000 to 16-JAN-2000)as per my requirement there are 3 weeks.

    01-JAN-2000 to 02-JAN-2000 as Week1
    03-JAN-2000 to 09-JAN-2000 as Week2
    10-JAN-2000 to 16-JAN-2000 as Week3
    as i have to consider week start day as Monday and end day as Sunday.

    So can u plz let me know is there any way to work on this and get the result.

    this is the statement which i was using ...but not working According to my requirement...

    Help me to get result ASAP

    SELECT Sum(Vvalue),
    (CASE
    WHEN trunc(to_date(A.Vdate1) - to_Date(A.Vdate)) < 8 THEN to_char(B.Vdate, 'D')
    WHEN ceil(months_between(A.Vdate1 , A.Vdate)) < = 1 THEN
    (Case
    When to_char(B.Vdate,'D') > '2' then to_char(B.Vdate, 'W')
    end)
    WHEN ceil(months_between(A.Vdate1 , A.Vdate)) < 4 THEN to_char(B.Vdate, 'Mon')
    ELSE 'Qtr'|| to_char(B.Vdate, 'Q')
    END)Interval
    FROM FT A, VTest B
    GROUP BY
    (CASE
    WHEN trunc(to_date(A.Vdate1) - to_Date(A.Vdate)) < 8 THEN to_char(B.Vdate, 'D')
    WHEN ceil(months_between(A.Vdate1 , A.Vdate)) < = 1 THEN
    (Case
    When to_char(B.Vdate,'D') > '2' then to_char(B.Vdate, 'W')
    End)
    WHEN ceil(months_between(A.Vdate1, A.Vdate)) < 4 THEN to_char(B.Vdate, 'Mon')
    ELSE 'Qtr'|| to_char(B.Vdate, 'Q')
    END)

    Thx....
    Suryadevara

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs up

    OK, with a little tweeking the function works:
    Code:
    SQL> Create Or Replace Function Datethru
      2        (Datefrom Date)
      3  Return Date Is
      4  Begin
      5    If To_Char(Datefrom,'W') = 1 Then
      6      Return (Datefrom + 8 - To_Char(Datefrom,'D'));
      7    End If;
      8    If (To_Char(Datefrom+7,'MM')-To_Char(Datefrom,'MM')) > 0 Then
      9      Return Last_Day(Datefrom);
     10    End If;
     11    Return (Datefrom + 6);
     12  End;
     13  /
    
    Function created.
    
    SQL> Drop Table Vtest
      2  /
    
    Table dropped.
    
    SQL> Create Table Vtest(Vdate Date,Vvalue Number(3))
      2  /
    
    Table created.
    
    SQL> Insert Into Vtest
      2    Select To_Date('10-Jan-2000')+(Rownum-1)
      3         , Rownum + 10
      4      From All_Objects Where Rownum < 32
      5  /
    
    31 rows created.
    
    SQL> Commit
      2  /
    
    Commit complete.
    
    SQL> Drop Table Ft
      2  /
    
    Table dropped.
    
    SQL> Create Table Ft(Vdate Date,Vdate1 Date)
      2  /
    
    Table created.
    
    SQL> Insert Into Ft Values('01-Jan-2000','31-Jan-2000')
      2  /
    
    1 row created.
    
    SQL> Commit
      2  /
    
    Commit complete.
    
    SQL> Set Serverout On Size 1000000;
    SQL> Declare
      2  Beg_Dt Date;
      3  End_Dt Date;
      4  Beg_Wk Date;
      5  End_Wk Date;
      6  Week_No Number(2):=0;
      7  Begin
      8    Select Vdate, Vdate1
      9      Into Beg_Dt, End_Dt
     10      From Ft;
     11    Beg_Wk:=Beg_Dt;
     12    Week_No :=1;
     13    While Beg_Wk <= End_Dt Loop
     14      End_Wk:=Datethru(Beg_Wk);
     15      Dbms_Output.Put_Line('Week '||Week_No||' From '
     16          ||Beg_Wk||' Through '||End_Wk);
     17      Week_No:=Week_No + 1;
     18      Beg_Wk:=End_Wk+1;
     19    End Loop;
     20  End;
     21  /
    Week 1 From 01-JAN-2000 Through 02-JAN-2000
    Week 2 From 03-JAN-2000 Through 09-JAN-2000
    Week 3 From 10-JAN-2000 Through 16-JAN-2000
    Week 4 From 17-JAN-2000 Through 23-JAN-2000
    Week 5 From 24-JAN-2000 Through 30-JAN-2000
    Week 6 From 31-JAN-2000 Through 31-JAN-2000
    
    PL/SQL procedure successfully completed.
    
    SQL>
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •