Results 1 to 10 of 10
  1. #1
    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. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Lightbulb

    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
      Beg_Wk(1):=Last_Day(Add_Months(Datefrom,-1))+1;
      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
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    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. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    no, not possible.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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. #6
    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(paysover (partition by weekpayments
    from 
    (
      
    select 
        to_char
    ((fecpago-1),'IW'week,
        
    fecpago pays
      from 
        pagos

    why wouldn't that work? Am I missing something?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

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

    Cool

    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
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

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

  9. #9
    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. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by LKBrwn_DBA
    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
    Thanks for spotting that - I just realised I used MOD when I meant "integer divide" and came back to change it!

Posting Permissions

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