• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 132
  • Last Modified:

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

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]

Open in new window


Can any one show me where is the mistake
0
ukerandi
Asked:
ukerandi
  • 5
  • 3
  • 2
  • +3
5 Solutions
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Do you want a list of:
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.
0
 
ukerandiAuthor Commented:
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
0
 
Brian ChanDBACommented:
hi ukerandi,

Try this below and see if this is what you are looking for between the item and the Customer:

-- Code updated - Let make the datetime input more dynamic
DECLARE 
	@StartDate varchar(10) = CONVERT(varchar(10),dateadd (yy, -1, getdate()),126),
	@EndDate varchar(10) =	CONVERT(varchar(10),getdate(),126);

WITH PastYearSales_item
AS (
	SELECT DISTINCT Item
	FROM   CustomerOrdersView
	WHERE
		[Order Date]>=@StartDate
		AND [Order Date]<=@EndDate
)	
, PastYearSales_Customer
AS (
	SELECT DISTINCT [Customer Name]
	FROM   CustomerOrdersView
	WHERE 
		[Order Date]>=@StartDate
		AND [Order Date]<=@EndDate
)
, PastYearSales_sales
AS (
	SELECT DISTINCT Item,[Customer Name]
	FROM   CustomerOrdersView
	WHERE 
		[Order Date]>=@StartDate
		AND [Order Date]<=@EndDate
)
, PastYearSales_s
AS (
	SELECT DISTINCT [Customer Name],[sales Person],[Order Date]
	FROM   CustomerOrdersView
	WHERE 
		[Order Date]>=CONVERT(varchar(10),dateadd (yy, -1, getdate()),126)
		AND [Order Date]<=CONVERT(varchar(10),getdate(),126)

)
SELECT *
FROM PastYearSales_item, PastYearSales_Customer
except
SELECT *
FROM PastYearSales_sales

Open in new window


The only thing that is not covered yet is the [sales Person]. can you confirm if that is a one to one relationship? ie. any given one customer is assigned to only one sales person.

Best regards
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
PaulCommented:
YIKES! Don't compare date or datetime columns to varchar variables

WHERE  [Order Date]>=CONVERT(varchar(10),dateadd (yy, -1, getdate()),126)
AND [Order Date]<=CONVERT(varchar(10),getdate(),126)

You are IMPLICITLY CONVERTING ON EVERY ROW and this is NOT a good thing to do.

to "remove time" from getdate() just cast it to date, try this
          select   dateadd (yy, -1, cast(getdate() as date)) st_dt , cast(getdate() as date) end_dt


WHERE  [Order Date] >= dateadd (yy, -1, cast(getdate() as date))
AND [Order Date]< cast(getdate() as date)

nb: I recommend you use >= and < 
      (i.e. only one equal sign)
0
 
Brian ChanDBACommented:
@Paul, Hehehe... I agree with you on that.

@ukerandi, can tell us if your data source has the [Order Date] field as datetime?
0
 
Scott PletcherSenior DBACommented:
@Paul; @bpnchan

I deeply disagree.  Indeed the opposite is true: you should as a rule use varchar to compare against date/datetime/time columns to make sure that no implicit column conversion is ever required, as long as you know that the char value will be valid.  Most notably, as in this case, where you've converted the varchar value yourself from a known valid date/time value.  You should, however, use format 112 (YYYYMMDD) rather than 126, or any other format with editing chars, because any format with delims could be misinterpreted under different SQL settings.

That's because SQL will implicitly convert the varchar value to match the date/datetime/time column, not the other way around.  Indeed, SQL would need to implicitly convert the column only if you used a higher precedence data type than the column data type, such as comparing a datetime2 value to a datetime column.  [Admittedly, the SQL optimizer now seems to automatically "downgrade" date/datetime values on its own if needed to prevent an implicit column conversion, but that did not happen in earlier versions of SQL and may not happen on other dbms's, so using "advanced data types" for comparison could be dangerous to performance.  Stick to varchar when possible and safe.]

For example, if you have a datetime column and want to select, say, June of 2016, you should code it like this:
WHERE order_date>= '20160601' AND order_date < '20160701'
and not like this:
WHERE order_date >= CAST('20160601' AS datetime) AND order_date < CAST('20160701' AS datetime)
Because, say the column later changes to a date only, for whatever reason.  Since datetime has a higher precedence, you could be forcing a highly undesirable implicit column version by explicitly coding a datetime value.
0
 
ukerandiAuthor Commented:
@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.
0
 
Brian ChanDBACommented:
Hi ukerandi,

Please try the following code.

Please note that I have made an assumption with the Sales person with  customer assignment relationship. I assumed that the latest order date from a given customer will associate with the current sales person assigned to them. In that case that will take care of the scenario that if a new sales person assigned to the customer within the current year. So, please verify with your business.
 

-- Code updated - Let make the datetime input more dynamic
DECLARE 
	@StartDate varchar(10) = CONVERT(varchar(10),dateadd (yy, -1, getdate()),126),
	@EndDate varchar(10) =	CONVERT(varchar(10),getdate(),126);

WITH PastYearSales_item
AS (
	SELECT DISTINCT Item
	FROM   CustomerOrdersView
	WHERE
		[Order Date]>=@StartDate
		AND [Order Date]<=@EndDate
)	
, PastYearSales_Customer
AS (
	SELECT DISTINCT [Customer Name]
	FROM   CustomerOrdersView
	WHERE 
		[Order Date]>=@StartDate
		AND [Order Date]<=@EndDate
)
, PastYearSales_sales
AS (
	SELECT DISTINCT Item,[Customer Name]
	FROM   CustomerOrdersView
	WHERE 
		[Order Date]>=@StartDate
		AND [Order Date]<=@EndDate
)
, PastYearSales_salesPerson
AS (
	SELECT COV.[Customer Name],COV.[sales Person]
	FROM   CustomerOrdersView COV
		INNER JOIN (SELECT [Customer Name],Max([Order Date]) as [Order Date]
					FROM   CustomerOrdersView
					WHERE 
						[Order Date]>=@StartDate
						AND [Order Date]<=@EndDate
					group by [Customer Name]) T1
				ON COV.[Customer Name] = T1.[Customer Name] AND COV.[Order Date] = T1.[Order Date]
)
SELECT 
	t3.Item
	,t3.[Customer Name]
	,t2.[sales Person]
FROM  PastYearSales_salesPerson t2
Join (
			SELECT *
			FROM PastYearSales_item, PastYearSales_Customer
			except
			SELECT *
			FROM PastYearSales_sales
	) t3
	on t2.[Customer Name] = t3.[Customer Name]
order by t3.[Customer Name],t3.Item

Open in new window

0
 
Brian ChanDBACommented:
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();

Open in new window

0
 
PaulCommented:
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.
0
 
Valliappan ANSenior Tech ConsultantCommented:
Adding to bpnchan and others' comments here, You may also try this:  

WITH A
AS
(
SELECT 
	[sales Person]
	,[Customer Name]
FROM CustomerOrdersView
GROUP BY [sales Person]
	,[Customer Name]
),
B AS
(
SELECT Item
	,[item Description]
FROM CustomerOrdersView
GROUP BY Item, [item Description]
)
SELECT * FROM A, B 
WHERE NOT EXISTS (SELECT 1 FROM CustomerOrdersView C WHERE C.[sales person] = A.[sales person] AND C.[Customer Name]=A.[Customer Name] AND C.[Item] = A.[Item] AND C.[Order Date] BETWEEN CONVERT(VARCHAR(10), dateadd(yy, - 1, getdate()), 112)
	AND CONVERT(VARCHAR(10), getdate(), 112) )

Open in new window


Let me know if any issues.  hth.
0
 
Brian ChanDBACommented:
@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.
0
 
ukerandiAuthor Commented:
I will check the code
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 5
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now