CODEWITHSUNDEEP
sql-servert-sql
João Cavaleiro
João Cavaleiro

Reputation: 3

SQL Server : order date and orders before

I have a simple table of Customers (customerID) and a table of Orders (orderID, orderdate)

I need to get a list of all customers that got an order exactly two weeks ago, and the count of all the orders that they had until that week.

SELECT c.id_customer
FROM customer AS c
JOIN order AS o ON o.customerID = c.customerID
WHERE o.delivery_date = DATEADD(Day,-14,GETDATE())

But I don't know how to get the count of orders until that week exactly.

Upvotes: 0

Views: 97

Answers (3)

KumarHarsh
KumarHarsh

Reputation: 5094

Try this with other sample data,

 Declare @DaysAgo int=14 --para to be pass in proc
    declare @To Datetime=DATEADD(second,-1,cast((DATEADD(Day, - (@DaysAgo-1), cast(GETDATE() as date)) ) as DATETIME))
   declare @From Datetime=cast((DATEADD(Day, - @DaysAgo, cast(GETDATE() as date)) ) as DATETIME)


    select c.id_customer, count(*) as order_count
    from customer as c
    join orders as o on o.customerID = c.customerID
    where o.delivery_date >= @From AND 
    o.delivery_date <= @To
    group by c.id_customer

Upvotes: 0

Suman Bhandari
Suman Bhandari

Reputation: 91

SELECT c.customerID,o.orderID, count(o.delivery_date)
FROM customer AS c
JOIN order AS o ON o.customerID = c.customerID
WHERE o.delivery_date = DATEADD(Day,-14,GETDATE())
group by c.customerID,o.orderID

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

You can use <= to get the rows and use having clause to check if there is a row present exactly on the given date:

select c.id_customer, count(*) as order_count
from customer as c
join orders as o on o.customerID = c.customerID
where o.delivery_date <= DATEADD(Day, - 14, GETDATE())
group by c.id_customer
having count(case when o.delivery_date = DATEADD(Day, -14, GETDATE()) then 1 end) > 0;

Also note that I changed table name order to orders as order is SQL reserved keyword and it is usually a bad idea to use keyword as table/column identifiers.

Upvotes: 1

Related Questions