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 : 06:32:38

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?

mapidea
Posting Yak Master

124 Posts

Posted - 2008-11-18 : 06:36:29

We can get the customer of the last one week by

WHERE CustomersOrders.DateOfOrder > DATEADD(dd, -7, GETDATE())

How can we find the customers who bought more than five products in last one week?

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-11-18 : 07:01:26

But how do u define last one week?
Does it mean the week that has just ended.If that is the case then this will get you the date range for the last ended week dates starting from monday to sunday.

select DATEADD(wk, DATEDIFF(wk,7, getdate())-1, 7)as startdate ,
DATEADD(dd,7,DATEADD(wk, DATEDIFF(wk, 6, getdate())-1, 6)) as enddate

mapidea
Posting Yak Master

124 Posts

Posted - 2008-11-18 : 07:02:51

We can get the customer of the last one week by

WHERE CustomersOrders.DateOfOrder > DATEADD(dd, -7, GETDATE())

How can we find the customers who bought more than five products in last one week?

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-11-18 : 07:12:19

Try this one

select cnt,DateOfOrder from

(
select count(productid)as cnt,Customer.CustomerID,CustomersOrders.DateOfOrder from OrderProduct
inner join Product on OrderProduct.ProductID=Product.ProductID
inner join CustomerOrder on OrderProduct.OrderID=CustomerOrder.OrderID
inner join Customer on CustomerOrder.CustomerID=Customer.CustomerID
group by Customer.CustomerID,CustomersOrders.DateOfOrder

)t where cnt>5 and ur date filters....

mapidea
Posting Yak Master

124 Posts

Posted - 2008-11-18 : 07:39:48

Your query gets the order in which the product bought are more than 5.

A customer could have placed three orders buying 2 products, 1 product and 3 product respectively in the span of one week.

How Can we get the Customers who have bought more than five products placing any number of orders?

Till now I have

SELECT Customers.CustomerID, Customers.CustomerName, OrderProduct.OrderID, COUNT(OrderProduct.OrderID) as ProductCount
FROM OrderProduct
INNER JOIN CustomersOrders ON CustomersOrders.OrderID = OrderProduct.OrderID
INNER JOIN Customers ON Customers.CustomerID = CustomersOrders.CustomerID
WHERE CustomersOrders.DateOfOrder > DATEADD(dd, -7, GETDATE())
GROUP BY OrderProduct.OrderID, Customers.CustomerID, Customers.CustomerName
HAVING COUNT(OrderProduct.OrderID) > 0

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-11-18 : 07:54:46

Please post some sample data.It is difficult to visualise without any data.

mapidea
Posting Yak Master

124 Posts

Posted - 2008-11-18 : 08:15:10

CustomerID CustomerName OrderID ProductCount
----------- -------------------------------------------------- ----------- ------------
1 Ji 1 5
2 Om 2 3
1 Ji 3 2
1 Ji 4 1
1 Ji 5 1
2 Om 6 2

(6 row(s) affected)

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-11-18 : 08:28:56

Try this

Select ProductCount,CustomerId,CustomerName from
(
Select sum(ProductCount)as ProductCount,CustomerId,CustomerName from your table
group by CustomerId,CustomerName
)t where ProductCount>5

If the above query also is not getting the result what you want then pls show the desired output from the sample data
you have provided.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 08:51:14

Didnt i provide you soultion here?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114648

mapidea
Posting Yak Master

124 Posts

Posted - 2008-11-18 : 09:13:41

Thanks ayamas I got the solution.

visakh16
There was some modification. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources