Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

T-sql - Not in

Posted on 2016-07-17
14
Medium Priority
?
121 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +3
14 Comments
 
LVL 71

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 800 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 400 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 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
 
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 800 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 49

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 400 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

Automating Terraform w Jenkins & AWS CodeCommit

How to configure Jenkins and CodeCommit to allow users to easily create and destroy infrastructure using Terraform code.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

715 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