Results 1 to 12 of 12

Thread: Dates

  1. #1
    Join Date
    Apr 2013
    Posts
    3

    Red face Unanswered: Dates

    I am working on my query to get a total cost for each client. I tried to calculate the rent and add discount 40% for each one but in order to give each one discount I need to imply a role saying that for each client to rented the video over 10 days, there will discount the 40% , less than that the total cost should appear without discount


    SELECT CCLIENTNO, SUM(RENTV-0.2) TOTAL
    FROM CLIENT C, RENT_AG R
    WHERE C.CLIENT_NO = R.CLIENT_NO
    GROUP BY CLIENTNO
    HAVING SUM(RENTV - 0.2) ( HERE SHOULD BE SUBQUERY ASKS FOR THE START DATE OF THE RENT AND THE END DATE );

    Any help please!!

  2. #2
    Join Date
    Apr 2013
    Posts
    3

    Red face Dates

    I am working on my query to get a total cost for each client. I tried to calculate the rent and add discount 40% for each one but in order to give each one discount I need to imply a role saying that for each client to rented the video over 10 days, there will discount the 40% , less than that the total cost should appear without discount


    SELECT CCLIENTNO, SUM(RENTV-0.2) TOTAL
    FROM CLIENT C, RENT_AG R
    WHERE C.CLIENT_NO = R.CLIENT_NO
    GROUP BY CLIENTNO
    HAVING SUM(RENTV - 0.2) ( HERE SHOULD BE SUBQUERY ASKS FOR THE START DATE OF THE RENT AND THE END DATE );

    Any help please!!

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Any chance for you to provide a test case (CREATE TABLE and INSERT INTO several sample records)? That will be input; show us what is output supposed to look like.

  4. #4
    Join Date
    Apr 2013
    Posts
    3

    Thank you for the reply

    CLIENTNO TOTALCOST
    ----------- ----------
    7777 14.79
    5555 9.79
    0987 7.79
    1238 9.79
    8765 11.79
    7654 19.79


    The total cost should consider when the rent started and when it finished
    if the rent is over 10 days then the discount is made, otherwise there is no discount on the cost of the video !


    START_D END_D
    ---------- ----------------
    02-JAN-03 20-APR-03
    09-DEC-03 06-JUN-03
    01-JUN-03 02-JUN-03
    10-AUG-03 15-AUG-03
    07-NOV-02 17-NOV-03
    15-DEC-02 20-DEC-03

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Chris, are you using Oracle, Sybase, or another database engine? Different database engines handle date processing using different syntax. Reposting your question until you get a response is a great way to get a response quickly when your assignment is due tomorrow, but if you can't get the response to work then you've wasted your time and ours on a wild goose chase.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    How is the first bunch of records related to the second one?

    Quote Originally Posted by Chris
    if the rent is over 10 days
    When subtracting two dates, the result is the number of DAYS between them (which can (and probably will) be a decimal number).

    Deciding whether you should calculate the discount can be done using some kind of the IF-THEN-ELSE statement. In SQL, it can be done with CASE or DECODE.

    (It appears that you didn't get me seriously about a test case. It is difficult to write any kind of code if you don't supply such information).

  7. #7
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Quote Originally Posted by Christech82 View Post
    The total cost should consider when the rent started and when it finished
    if the rent is over 10 days then the discount is made, otherwise there is no discount on the cost of the video
    This is an example using the data you posted:
    Code:
    create table client_info( 
      clientno             number( 10,0 ) not null,
      cost                 number( 11,2 ) not null,
      start_d              date not null,
      end_d                date null )
    
    select clientno, cost, start_d, end_d,
           end_d - start_d as days,
           case
             when end_d - start_d > 10 then cost - round( ( cost * .40 ),2 )
             else cost
           end as total_cost
     from  client_info
    
    clientno cost  start_d    end_d      days total_cost
        7777 14.79 2003-01-02 2003-04-20  108       8.87
       55555  9.79 2002-12-09 2003-06-06  179       5.87
         987  7.79 2003-06-01 2003-06-02    1       7.79
        1238  9.79 2003-08-10 2003-08-15    5       9.79
        8765 11.79 2002-11-07 2003-11-17  375       7.07
        7654 19.79 2002-12-15 2003-12-20  370      11.87

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Unfortunately, you miss the RENT_AG table (which exists in OP's initial post).

  9. #9
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Show some sample data for your tables and your desired result using the sample data

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The OP (ChrisTech82) seems to be having problems and accidentally re-posting this question repeatedly in different forums. I'm going to start to combine those new threads with this one to keep things consolidated and consistent.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Feb 2012
    Posts
    133
    can you elaborate please

  12. #12
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    My post was in reply to a question - seems the original post disappeared

Posting Permissions

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