CODEWITHSUNDEEP
sql-serversql-server-2008
user3025605
user3025605

Reputation: 323

create sql query to fetch repeat column values within time frame

Can someone help me with this query? I want to get the result of all the customer_id which repeats more than once in 24hrs

SELECT 
    O.Order_No, O.Customer_ID, O.DateOrdered, O.IPAddress,
    C.FirstName, C.LastName, CD.nameoncard
FROM 
    Order_No O
INNER JOIN 
    CardData CD ON O.card_id = CD.id
INNER JOIN 
    Customers C ON O.customer_id = C.customer_id   
ORDER BY
    O.order_no desc

adding more details.. so suppose order with customer id xx was placed on 04/23 2:30 pm and again 2nd order was placed with same customer Id xx on same day 04/23 5:30 pm.

i want the query to return me customer Id xx

Thanks

Upvotes: 0

Views: 162

Answers (2)

APH
APH

Reputation: 4154

Self-join:

SELECT distinct O.Customer_ID
FROM 
Order_No O
inner join Order_No o2
on o.customerID = o2.customerID 
    and datediff(hour, o.DateOrdered, o2.DateOrdered) between 0 and 24
and o.Order_No <> o2.Order_No

This will return all customer_IDs that have ever placed more than one order in any 24 hour period.

Edited to add the join criteria that the matching records should not be the same record. Should return customers who placed two different orders at the same time, but not customers who placed only one order.

Upvotes: 0

Nizam
Nizam

Reputation: 4699

select Customer_ID, CAST(DateOrdered as Date) DateOrdered, count(*) QTDE
from Order_No
group by Customer_ID, CAST(DateOrdered as Date)
having count(*) > 1

To get the customers who have orders issued after the first one, then you could use the following query:

select distinct A.Customer_ID
from Order_No A
inner join (select Customer_ID, min(DateOrdered) DateOrdered from Order_No group by Customer_ID ) B
  on A.Customer_ID = B.Customer_ID
  and A.DateOrdered - B.DateOrdered <= 1
  and A.DateOrdered > B.DateOrdered

SQL Fiddle

To get all customers that have ANY TIME more than one order issued in period less or equal than 24h

select distinct A.Customer_ID
from Order_No A
inner join Order_No B
  on A.Customer_ID = B.Customer_ID
  and A.DateOrdered > B.DateOrdered
  and A.DateOrdered - B.DateOrdered <= 1

SQL Fiddle

Upvotes: 2

Related Questions