1. Registered User
Join Date
Apr 2013
Posts
3

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 );

2. Registered User
Join Date
Apr 2013
Posts
3

## 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 );

3. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,113
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. Registered User
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

6. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,113
How is the first bunch of records related to the second one?

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. Registered User
Join Date
Feb 2006
Posts
175
Originally Posted by Christech82
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. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,113
Unfortunately, you miss the RENT_AG table (which exists in OP's initial post).

9. Registered User
Join Date
May 2005
Location
South Africa
Posts
1,369
Show some sample data for your tables and your desired result using the sample data

10. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

11. Registered User
Join Date
Feb 2012
Posts
133

12. Registered User
Join Date
May 2005
Location
South Africa
Posts
1,369