SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL queries
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mlbwhf
Starting Member

Oman
3 Posts

Posted - 10/28/2012 :  02:10:12  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
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
Go to Top of Page

mlbwhf
Starting Member

Oman
3 Posts

Posted - 10/29/2012 :  16:32:47  Show Profile  Reply with Quote
thanks Satish
Go to Top of Page

mlbwhf
Starting Member

Oman
3 Posts

Posted - 11/04/2012 :  11:57:54  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 11/05/2012 :  00:58:37  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000