Sql Query List of Customers - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Sql Query List of Customers

Author

Topic

mapidea
Posting Yak Master

124 Posts

Posted - 2008-11-18 : 03:40:59

I have four tables

Customer Table
---------------------
CustomerID
CustomerName
Address1
Address2
City

CustomerOrder Table
----------------------
OrderID
CustomerID
DateOfOrder

Product Table
---------------
ProductID
ProductName
ProductDescription
ProductPrice

OrderProduct Table
-----------------
OrderID
ProductID
PriceofProduct

Sql Query to find

list of customers who bought more than five products in last one week?

Regards

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-11-18 : 04:02:30

What have you got so far?

- Lumbago

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 04:09:15

Seems like an assignment question.Have a look at JOINs and Aggregation in bokks online and try it out.

mapidea
Posting Yak Master

124 Posts

Posted - 2008-11-18 : 04:12:01

SELECT CustomerId, CustomerName, COUNT(OrderID) as total
FROM Customer INNER JOIN CustomerOrder ON Customer.CustomerId = CustomerOrder.CustomerId
GROUP BY CustomerId, CustomerName
HAVING COUNT(OrderID) > 5

How can we find the Customers for last one week?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-11-18 : 04:17:16

hey, there we go! Just add:

FROM ...
WHERE CustomerOrder.DateOfOrder > DATEADD(dd, -7, GETDATE())
GROUP BY ...

- Lumbago

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 04:17:59

[code]SELECT CustomerId, CustomerName, COUNT(OrderID) as total
FROM Customer INNER JOIN CustomerOrder ON Customer.CustomerId = CustomerOrder.CustomerId
WHERE CustomerOrder.DateOfOrder >= DATEADD(wk,DATEDIFF(wk,0,GETDATE()),-1)
AND CustomerOrder.DateOfOrder < DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
GROUP BY CustomerId, CustomerName
HAVING COUNT(OrderID) > 5
[/code]

mapidea
Posting Yak Master

124 Posts

Posted - 2008-11-18 : 04:32:15

Thanks a lot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 04:35:47

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources