customers who dont have orders in last 1 year and also customers , who does not placed orders

Question

1.00/5 (4 votes)

See more:

customers who dont have orders in last 1 year and also customers who does not placed orders

customer tbl : customerid, name ,email
orders tbl: customerid , orderdate , ordernumber

Posted 26-Aug-14 2:07am

venky1988

Updated 26-Aug-14 4:29am

coded007

v2

Add a Solution

Comments

Thanks7872 26-Aug-14 8:19am

Read this question your self assuming you don't know anything about DB,Application etc.

Read it again. Dont you think this doesn't make any sense? Nothing at all?

venky1988 26-Aug-14 8:31am

can you please let me what are you able to understand with my question , so that i can provide some info to you

Sanchayeeta 26-Aug-14 8:37am

Question is not clear. Explain properly your question, if possible explain with example.

venky1988 26-Aug-14 8:44am

customerId customername lastOrderdate

1 ram 08/01/2013
2 ravi 07/02/2013
3 krishna
4 venky 06/01/2012

in the above customerid 1, 2 and 4 don't have orders in last 1 year but they have orders earlier and customer id 3 don't have any order in orders table

W∴ Balboos, GHB 26-Aug-14 8:45am

I may sound a bit harsh, but the way your question was submitted it would appear that you've not done anything with this yourself.

2 solutions

Solution 2

Hi,

From your above info only i can write below query. Try this...

SQL

SELECT customerid, name from customer c, order o
where c.customerid = o.customerid
and c.customerid not in (select distinct customerid from order where orderdate between getdate() and getdate() - 365)

Hope this will give you required output.

Cheers

Posted 26-Aug-14 2:49am

Magic Wonder

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Gupta Poonam · Accepted Answer · 2014-08-26T02:55:00

Solution 3

Try this..

SQL

select distinct c.customerid,c.name,c.email,o.ordernumber
from Orders o
right join Customers c on o.customerid=c.customerid
where DATEDIFF(DAY,o.orderdate,GETDATE())>365 or o.orderdate IS Null

Posted 26-Aug-14 2:55am

Gupta Poonam

v2

Comments

venky1988 27-Aug-14 4:45am

Hi With your Query I got the solution., Thank you ....

select customer.customerid, Customer.name,Customer.email,max(ord.orderdate) as 'lastorderdate'
from customer
left join orders ord on customer.customerid=ord.customerid
where datediff(dd,convert(varchar(10),(select max(orderdate) from orders where customerid = ord.customerid),101),'08/30/2014')>365 )

or (ord.orderdate is null)

group by customer.customerid, Customer.name,Customer.email
order by customerid desc