Query that will display clients that have ordered one item more than once with the date of the first-CodePudding

I have 3 tables, -tblOrder (customerid, itemid, order_date, quantity)

tblItem (itemid, item, in_stock, price)
tblCustomer (customerid, city, state, ClientName)

How do I write the query to find the date for the first and last order? Can please someone help me?

CodePudding user response：

;with 
tblItem as (select 1 itemid, 1 item, 1 in_stock, 1 price)
,tblCustomer as(select 1 customerid, 1 city, 1 state, 1 ClientName union select 3 customerid, 1 city, 1 state, 1 ClientName)
,tblOrder as (select 1 customerid, 1 itemid, 1 order_date, 1 quantity
union select 1 customerid, 1 itemid, 5 order_date, 2 quantity
union select 1 customerid, 1 itemid, 7 order_date, 2 quantity
union select 3 customerid, 1 itemid, 8 order_date, 2 quantity
union select 3 customerid, 1 itemid, 9 order_date, 2 quantity
union select 3 customerid, 1 itemid, 1 order_date, 2 quantity)
--,counts as(
select c.customerid,i.itemid,count(1) total_count,min(o.order_date) mindate,max(o.order_date) as maxdate from tblOrder o
join tblItem i on o.itemid = i.itemid
join tblCustomer c on o.customerid = c.customerid
group by c.customerid,i.itemid
having count(1)>1

CodePudding user response：

Here's some code which will say if there a client has orders for which the total quantity of all orders for an item is greater than 1:

select o.customerId
--, c.ClientName
, o.itemId
--, i.Item
, min(o.order_date) firstOrdered
, max(o.order_date) lastOrdered
, sum(o.quantity) totalQuantity
from tblOrder o
--inner join tblCustomer c on c.customerid = o.customerid
--inner join tblItem i on i.itemid = o.itemid
group by o.customerId, o.itemId
--, c.ClientName, i.Item
having sum(o.quantity) > 1

The commented out lines can be included if you want to pull back details from the related item & customer tables; though only the order table is required to get what you'd asked for.

SQL Fiddle example code.