| Author |
 Topic  |
|
|
mlbwhf
Starting Member
Oman
3 Posts |
 Posted - 10/28/2012 : 02:10:12
|
I have the following tables:
Customers (Cust_ID, Cust_name, Cust_address, Cust_email, Type)
Suppliers (Sup_ID, Sup_name, Sup_address, Sup_email)
Equipment (Equip_ID, Equip_name, Equip_brand, Sup_ID, weekedays_rate, weekend_rate, Delivery_time, Category, Qty_in_stock)/ *Sup_ID referencing Supplier.Sup_ID*/
Complain (Comp_ID, hire_ID,Equip_ID, Equip_name, Cust_ID, comp_date,Comp_action) /*hire_ID refrencing Hire.hire_ID* Equip_ID referencing Equipmnet.Equip_ID* Equip_name referencing Equipmnet.Equip_name *Cus_ID referencing Customers.Cus_ID*/
Rent (Rent_ID, Cust_ID, Equip_ID, Equip_name, Rent_start, exp_return_date, act_return_date, weekday_num, weekend_num,hire_ income)/ *Cus_ID referencing Customers.Cus_ID* Equip_ID referencing Equipmnet.Equip_ID* Equip_name referencing Equipmnet.Equip_name */
|
Edited by - mlbwhf on 11/04/2012 12:01:17
|
|
|
sathiesh2005
Yak Posting Veteran
India
85 Posts |
Posted - 10/29/2012 : 09:11:12
|
Hi Try the below. If this is not your requirement, post it clearly with some sample data.
SELECT S.Sup_name, Sup_address AS address,
SUM(E.Qty_in_stock) AS quantity
FROM Suppliers S, Equipment E
WHERE S.Sup_ID = E.Sup_ID
GROUP BY E.Sup_ID, S.Sup_name, Sup_address
ORDER BY S.Sup_name;
----------------------------------------------
SELECT E.category, SUM(E.Qty_in_stock) AS quantity,
COUNT(R.*)AS rent
FROM Equipment E, Rent R
WHERE E.Category = ‘Access Equipment’
AND R.exp_return_date > getdate()
AND E.Equip_ID =R.Equip_ID
GROUP BY E.Category;
@Sathiesh |
Edited by - sathiesh2005 on 10/29/2012 09:12:45 |
 |
|
|
mlbwhf
Starting Member
Oman
3 Posts |
Posted - 10/29/2012 : 16:32:47
|
| thanks Satish |
|
|
|
mlbwhf
Starting Member
Oman
3 Posts |
Posted - 11/04/2012 : 11:57:54
|
and i have this querey to calculate income need to produce the following queries:
Code:
SELECT Equipment.Category, ([Equipment]![weekday_rate]*[Rent]![weekdays_num])+([Rent]![weekend_num]*[Equipment]![weekdend_rate])+(([Equipment]![weekday_rate]*[Rent]![weekdays_num])+([Rent]![weekend_num]*[Equipment]![weekdend_rate])*0.2) AS Income
FROM Equipment INNER JOIN Rent ON Equipment.Equip_ID = Rent.Equip_ID
WHERE (((Rent.act_return_date) Between [Enter the Starting Date] And [Enter the Ending Date]));
the only thing i want to check the user category and give him a discount depending on the type of user which can be chosen from a drop down under Customers table, the categories and discountare:
Personal no discount
Silver 10%
Gold 25%
Diamond 40% |
Edited by - mlbwhf on 11/04/2012 12:00:57 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 11/05/2012 : 00:58:37
|
quote:
the only thing i want to check the user category and give him a discount depending on the type of user which can be chosen from a drop down under Customers table, the categories and discountare:
Personal no discount
Silver 10%
Gold 25%
Diamond 40%
use CASE statement as follows:
CASE Equipment.Category WHEN 'Personal' THEN 0%
WHEN 'Silver' THEN 10%
WHEN 'Gold' THEN 25%
WHEN 'Diamond' THEN 40%
end as Discount
--
Chandu |
|
|
| |
Topic |
|
|
|
SQL queries
