# Thread: Specifying start and end day for week

1. Registered User
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

2. Registered User
Join Date
Jun 2003
Location
West Palm Beach, FL
Posts
2,713
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))```

3. Registered User
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....

4. Registered User
Join Date
Jun 2003
Location
West Palm Beach, FL
Posts
2,713
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>```

#### Posting Permissions

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