SQL queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL queries

Author

Topic

mlbwhf
Starting Member

3 Posts

Posted - 2012-10-28 : 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 */

sathiesh2005
Yak Posting Veteran

85 Posts

Posted - 2012-10-29 : 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

mlbwhf
Starting Member

3 Posts

Posted - 2012-10-29 : 16:32:47

thanks Satish

mlbwhf
Starting Member

3 Posts

Posted - 2012-11-04 : 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%

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-05 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources