T-sql - Not in
Hi, I have View,its showing all the Customer orders details.I need to find in that view(the view all the necessary fields available) any customers didn't order product past 12 months according to sales person
For example didn't order any product
ProductID customer Name Sales Person Name
1234 EEEE Mr.Ryan
I have write following query but it's not look like working
Can any one show me where is the mistake
For example didn't order any product
ProductID customer Name Sales Person Name
1234 EEEE Mr.Ryan
I have write following query but it's not look like working
select Item,[item Description],[sales Person],[Customer Name]
from CustomerOrdersView
WHERE
[Order Date]>=CONVERT(varchar(10),dateadd (yy, -1, getdate()),126)
AND [Order Date]<=CONVERT(varchar(10),getdate(),126)
AND Item NOT IN
(select Item
from CustomerOrdersView
WHERE [Order Date]>=CONVERT(varchar(10),dateadd (yy, -1, getdate()),126)
AND [Order Date]<=CONVERT(varchar(10),getdate(),126)
AND [Total Ordered]>0
Group by Item)
Group by Item,[item Description],[sales Person],[Customer Name]Can any one show me where is the mistake
ASKER
1. customers not ordering any items within the last year? YES
2. items not getting ordered by anyone within the last year? YES
Please find attached excel file. Red colour showing expected output
CustomerOrders.xlsx
2. items not getting ordered by anyone within the last year? YES
Please find attached excel file. Red colour showing expected output
CustomerOrders.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@Paul, Hehehe... I agree with you on that.
@ukerandi, can tell us if your data source has the [Order Date] field as datetime?
@ukerandi, can tell us if your data source has the [Order Date] field as datetime?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@bpnchan :can tell us if your data source has the [Order Date] field as datetime?
Yes
can you confirm if that is a one to one relationship? ie. any given one customer is assigned to only one sales person.
Yes, the one Sales person assign to one customer.
Yes
can you confirm if that is a one to one relationship? ie. any given one customer is assigned to only one sales person.
Yes, the one Sales person assign to one customer.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
By the way, if you are sure that the [Order Date] is datetime, I would change the variable declare section into the following:
-- Code updated - Let make the datetime input more dynamic
DECLARE
@StartDate datetime = dateadd (yy, -1, getdate())
,@EndDate datetime = getdate();
Scott was quite right to correct my earlier statement.
That statement is wrong. Apologies.
I shall endeavour to stop attempting to answer in a train on a phone.
That statement is wrong. Apologies.
I shall endeavour to stop attempting to answer in a train on a phone.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@Scott - Thanks for the sharing. I have never thought of that, and think datetime itself would be the best way to regulate the input data is validated as date time.
ASKER
I will check the code
1. customers not ordering any items within the last year?
2. items not getting ordered by anyone within the last year?
3. customers and items they did not order within the last year?
Neither your description nor the query really make sense to me. Best to show us some source data (a few rows only), and the expected result.