Solved

T-sql - Not in

Posted on 2016-07-17
14
83 Views
Last Modified: 2016-09-27
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
Comment
Question by:ukerandi
  • 5
  • 3
  • 2
  • +3
14 Comments
 
LVL 68

Expert Comment

by:Qlemo
ID: 41716166
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
 
LVL 10

Author Comment

by:ukerandi
ID: 41716180
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
 
LVL 5

Accepted Solution

by:
Brian Chan earned 200 total points
ID: 41716251
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
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 100 total points
ID: 41716289
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
 
LVL 5

Expert Comment

by:Brian Chan
ID: 41716294
@Paul, Hehehe... I agree with you on that.

@ukerandi, can tell us if your data source has the [Order Date] field as datetime?
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 100 total points
ID: 41717448
@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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 10

Author Comment

by:ukerandi
ID: 41717968
@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
 
LVL 5

Assisted Solution

by:Brian Chan
Brian Chan earned 200 total points
ID: 41718047
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
 
LVL 5

Expert Comment

by:Brian Chan
ID: 41718050
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41718279
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
 
LVL 9

Assisted Solution

by:Valliappan AN
Valliappan AN earned 100 total points
ID: 41719299
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
 
LVL 5

Expert Comment

by:Brian Chan
ID: 41720038
@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
 
LVL 10

Author Comment

by:ukerandi
ID: 41738143
I will check the code
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now