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

    Unanswered: Prob with date field to display month ,week wise data

    I have two date columns in one table
    D1,D2 and other columns V3,V4 from this i want to display the data month wise, Week wise,Quater wise, day wise...
    So how can i go about this..

    Use will give the input values for D1 and D2...
    D1 = 01-jan-2001,D2 = 31 -jan-2001
    if this is the input , i have to display the result for one month only ...if not 22-jan-03 , 05-feb-2003..
    i have to display for two months, as the dates fall in two months.

    Please advise me on this ASAP
    Suryadevara

  2. #2
    Join Date
    Feb 2004
    Location
    India
    Posts
    22
    select * from a where to_date(to_char(a,'mm/yy'),'mm/yy')
    between to_date(to_char(to_date('&d1','dd/mm/yy'),'mm/yy'),'mm/yy')
    and to_date(to_char(to_date('&d2','dd/mm/yy'),'mm/yy'),'mm/yy')
    Regards,
    Jigar Bhavsar

  3. #3
    Join Date
    Jun 2002
    Location
    India
    Posts
    40
    thanks for your reply , but it doesn't help me,

    For further info these r the details.

    Table A has this data

    WR ORDTPCD ITMTPCD DELDOC
    --------- ---------- ---------- ----------
    01-JAN-01 SAL PKG 1
    02-JAN-01 SAL PKG 1
    03-JAN-01 SAL PKG 1
    04-JAN-01 SAL PKG 1
    05-JAN-01 SAL PKG 1
    06-JAN-01 SAL PKG 1
    01-FEB-01 SAL PKG 1
    02-FEB-01 SAL PKG 1
    01-MAR-01 SAL PKG 1
    02-MAR-01 SAL PKG 1

    Table B has this data

    PCTS UNITS
    ---------- ----------
    2 10

    What i am looking for is that...

    if the user gives the input for begin date and end date as
    01-jan-2001 , 03-mar-2001...

    I want to display total for SAL Type for Jan month,feb month,mar month as the give dates fall in 3 months...So advice and help me on this ASAP
    Suryadevara

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    WHERE wr BETWEEN TO_DATE('01-jan-2001','DD-MON-YYYY') AND TO_DATE('31-mar-2001','DD-MON-YYYY')

  5. #5
    Join Date
    Jun 2002
    Location
    India
    Posts
    40
    it's not working for my result

    What i am looking for is that, we have to use some logic in Group by clause....as i want to data by month wise...like for jan , feb, mar...

    from that data which i gave
    Suryadevara

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    OK, I see. TRUNC(date,'MM') will truncate dates to the 1st of the month, so you can group by that.

  7. #7
    Join Date
    Jun 2002
    Location
    India
    Posts
    40
    thanks once again..but still i have the prob with the Query...

    i gave the following query
    SQL> select wr,
    2 sum(pcts*units)
    3 from
    4 jt1 ,jt2
    5 where
    6 wr between '01-jan-2001' and '03-mar-2001'
    7 group by pcts,units,ordtpcd,
    8 wr;

    WR SUM(PCTS*UNITS)
    --------- ---------------
    01-JAN-01 20
    02-JAN-01 20
    03-JAN-01 20
    04-JAN-01 20
    05-JAN-01 20
    06-JAN-01 20
    01-FEB-01 20
    02-FEB-01 20
    01-MAR-01 20
    02-MAR-01 20


    If i give the above Query it displays that result, what i am looking is that
    some what like this, if i remove wr from select list and group by list...

    i want to see the result like this..

    sum(....)
    ---------
    120...this is for total jan month of 2001
    40 ...this is for total Feb month of 2001
    40 ...this is for total Mar month of 2001

    so please help me with total Query .....
    Suryadevara

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    what are you joining by?

    analytics.

    PHP Code:
    select distinct  
    sum
    (pcts*unitsover (partition by to_char(wr,'Mon YYYY')) total,
    to_char(wr,'Mon YYYY'month
    from 
     jt1 
    ,jt2
    where 
     wr between 
    '01-jan-2001' and '03-mar-2001'
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Jun 2002
    Location
    India
    Posts
    40
    thanks...it's working for now....
    Suryadevara

Posting Permissions

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