# Thread: How many payments per Week

1. Registered User
Join Date
May 2004
Location
Dominican Republic
Posts
721

## Unanswered: How many payments per Week

Hi. I am trying to find a way to count all the payments per week. The problem is, I need to count each week starting MONDAY, thus I can't use to_char(datefield,'w') to find that.

I am currently using the above query, so If anyone know an easier and more elegant way of doing it, please lit a light:

Code:
```SELECT week, COUNT(*) FROM (
SELECT (CASE WHEN (trunc(fecpago) >= x.w1) AND (trunc(fecpago) < x.w2) THEN 'W1'
WHEN (trunc(fecpago) >= x.w2) AND (trunc(fecpago) < x.w3) THEN 'W2'
WHEN (trunc(fecpago) >= x.w3) AND (trunc(fecpago) < x.w4) THEN 'W3'
WHEN (trunc(fecpago) >= x.w4) AND (trunc(fecpago) < x.w5) THEN 'W4'
WHEN (trunc(fecpago) >= x.w5) THEN 'W5'
END) AS week
FROM (
SELECT trunc(to_date('01-'||to_char(fecpago,'mm-yyyy'),'dd-mm-yyyy')) w1,
trunc(next_day(to_date('01-'||to_char(fecpago,'mm-yyyy'),'dd-mm-yyyy'),'Monday')) w2,
trunc(next_day(next_day(to_date('01-'||to_char(fecpago,'mm-yyyy'),'dd-mm-yyyy'),'Monday')+1,'Monday')) w3,
trunc(next_day(next_day(next_day(to_date('01-'||to_char(fecpago,'mm-yyyy'),'dd-mm-yyyy'),'Monday')+1,'Monday')+1,'Monday')) w4,
trunc(next_day(next_day(next_day(next_day(to_date('01-'||to_char(fecpago,'mm-yyyy'),'dd-mm-yyyy'),'Monday')+1,'Monday')+1,'Monday')+1,'Monday')) w5
FROM pagos
WHERE codigo = 2297927
) x, pagos p
WHERE trunc(fecpago) BETWEEN to_date('01-06-2004','dd-mm-yyyy') AND to_date('25-06-2004','dd-mm-yyyy')
AND origen NOT IN ('R','D')
AND hora_elm IS NULL)
GROUP BY week```
I will explain it. Here, fecpago is a datefield on table PAGOS (payments). On the first query, I am using this payment code to only identify one row which in the same month of the payments, the other fields (origen, hora_elm) are just there for a particular purpose, so they shouldn't matter.

Thanks!

2. Registered User
Join Date
Jun 2003
Location
West Palm Beach, FL
Posts
2,713
Ok now I undestand. Try creating a function like this:
Code:
```Create Or Replace Function Semana_Pago
(Datefrom Date)
Return Varchar2 Is
Type Dt_Type Is Table Of Date
Index By Binary_Integer;
Beg_Wk Dt_Type;
End_Wk Dt_Type;
I Pls_Integer:=1;
Begin
While Beg_Wk(I) <= Last_Day(Datefrom)
Loop
If To_Char(Beg_Wk(I),'W') = 1 Then
End_Wk(I):= (Beg_Wk(I) + 8 - To_Char(Beg_Wk(I),'D'));
Else
If (To_Char(Beg_Wk(I)+7,'MM')-To_Char(Beg_Wk(I),'MM')) > 0 Then
End_Wk(I):= Last_Day(Beg_Wk(I));
Else
End_Wk(I):= Beg_Wk(I) + 6;
End If;
End If;
If Datefrom >= Beg_Wk(I) And Datefrom <= End_Wk(I) Then
Return iTo_Char(Datefrom,'MM')||'-W'||I;
End If;
I := I + 1;
Beg_Wk(I):=End_Wk(I-1)+1;
End Loop;
Raise_Application_Error(-20000,'!Error: Semana_Pago Bad Date:'||Datefrom);
End;
/
show error```
And then re-write your select like this:
Code:
```SELECT semana_pago(fecpago) week, COUNT(semana_pago(fecpago)) "#Pagos"
FROM pagos
WHERE trunc(fecpago)
BETWEEN to_date('01-06-2004','dd-mm-yyyy')
AND to_date('25-06-2004','dd-mm-yyyy')
AND origen NOT IN ('R','D')
AND hora_elm IS NULL
GROUP BY week```

3. Registered User
Join Date
May 2004
Location
Dominican Republic
Posts
721
Woa, all that to get this only ?

Isn't there a variable or something where I can SET the first day of a WEEK so to_char(fecpago,'w') can work out ?

4. Registered User
Join Date
Jun 2003
Location
West Palm Beach, FL
Posts
2,713
no, not possible.

5. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
I think all you need is something like this to get the week number within the month:

mod( trunc(fecpago) - (trunc(fecpago,'MM')-1, 'SUN')-6, 7) + 1

I don't have access to Oracle right now, so there may be a mistake in there, but this is what it does:

trunc(fecpago) = the date in question
trunc(fecpago,'MM') = 1st day of that month
next_day (trunc(fecpago,'MM')-1, 'SUN') = 1st Sunday in that month
next_day (trunc(fecpago,'MM')-1, 'SUN')-6 = start of week that ends on 1st Sunday

It may need a slight adjustment depending on your rules...

6. Registered User
Join Date
Jul 2003
Posts
2,296
All you really are concerned about are Sundays right?
Why not just -1 day from every date?

PHP Code:
``` select distinct    week,    count(pays) over (partition by week) payments from (   select      to_char((fecpago-1),'IW') week,     fecpago pays   from      pagos)  ```
why wouldn't that work? Am I missing something?

7. Registered User
Join Date
Jun 2003
Location
West Palm Beach, FL
Posts
2,713
andrewst is on the right track, but it is something more like this:
Code:
` floor((trunc(fecpago)-Trunc(TRUNC(fecpago,'Mon'),'Day'))/7-0.01)+1`

8. Registered User
Join Date
May 2004
Location
Dominican Republic
Posts
721
Thanks a lot ALL!.. you guys really impress me.

9. Registered User
Join Date
May 2004
Location
Dominican Republic
Posts
721
Hi Guys, one last question though. I have been analyzing a litle bit the query, but couldn't find what actually does:

a) Trunc(TRUNC(fecpago,'Mon'),'Day')

Say.. TRUNC(FECPAGO,'MON') returns 01-JUN-04 and TRUNC(TRUNC(FECPAGO,'MON'),'DAY') returns 30-MAY-04.. and I wonder, why the difference in date is I'm inputting the same date ?

10. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
` floor((trunc(fecpago)-Trunc(TRUNC(fecpago,'Mon'),'Day'))/7-0.01)+1`