February 27, 2017 at 2:41 am
Hi
how do I use the only clause and date together as follows
a customer can many orders from departments in one day...
but how do I find out if a Customer (CustomerID) only on day orders from (ORDERS Table) the Department 'Catering'
also as another trick , get this date (when it happened) from the OrderDate column too
please help
February 27, 2017 at 3:04 am
Sorry, but I'm not sure i understand your goal here. Are you:
A. Looking for a customers that have only ordered on a specific day (no other odays), and only ordered from the Catering Department
B. Looking for customers that ordered from the Catering Department on a specific day
C Looking for customers that only ordered from the Catering Department on a specific day
D. Look for Customers that have only ordered from the Catering Department, once
-D ii. And get the date they ordered
E. Look for customers that have only ordered from the Catering Department
-E ii. And get the dates they have ordered
F,. Other..?
Have a look at the link in my signature on how to ask questions on SSC, as we'll need to at least know your table layout so we can create appropriate JOINs for you. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 27, 2017 at 3:14 am
Hi
I have something like this
CustomerID OrderID OrderDate Department
1 abc1 01/01/2001 09:00 Home
1 abc2 01/01/2001 09:30 Children
1 abc3 01/01/2001 09:35 Home
1 abc4 01/01/2001 10:50 Catering
1 abc5 02/01/2001 09:30 Catering
1 abc6 02/01/2001 09:35 Home
1 abc7 02/01/2001 09:40 Catering
1 abc8 02/01/2001 10:05 Children
1 abc9 05/01/2001 09:05 Catering ...this is the line I am after
2 abc10 01/01/2001 09:00 Home
2 abc11 01/01/2001 09:30 Catering
I am looking to get the date as 05/01/2001 , and Customer ID 1
please help
February 27, 2017 at 3:33 am
You should really provide your sample data as shown in my signature. I've done this for you this time,.
Couple of solution, one using a subquery, the other using GROUPING. The GROUP statement is quicker, but included the subquery solution anyway so you can see different answers.CREATE TABLE #Sample (SampleID INT IDENTITY(1,1),
CustomerID INT,
OrderID VARCHAR(5),
OrderDate DATETIME,
Department VARCHAR(20));
GO
INSERT INTO #Sample(CustomerID, OrderID, OrderDate, Department)
VALUES
(1,'abc1','20010101 09:00','Home'), --Supply your dates in a universal format
(1,'abc2','20010101 09:30','Children'),
(1,'abc3','20010101 09:35','Home'),
(1,'abc4','20010101 10:50','Catering'),
(1,'abc5','20010102 09:30','Catering'),
(1,'abc6','20010102 09:35','Home'),
(1,'abc7','20010102 09:40','Catering'),
(1,'abc8','20010102 10:05','Children'),
(1,'abc9','20010105 09:05','Catering'), --this is the line I am after
(2,'abc10','20010101 09:00','Home'),
(2,'abc11','20010101 09:30','Catering');
GO
SELECT *
FROM #Sample;
--Sub Query
SELECT DISTINCT
S.CustomerID, CAST(OrderDate AS date)
FROM #Sample S
WHERE S.Department = 'Catering'
AND S.CustomerID NOT IN (SELECT sq.CustomerID
FROM #Sample sq
WHERE CAST(sq.OrderDate AS date) = CAST(S.OrderDate AS date)
AND sq.Department != 'Catering');
--GROUP BY
SELECT S.CustomerID, CAST(OrderDate AS date)
FROM #Sample S
GROUP BY S.CustomerID, CAST(OrderDate AS date)
HAVING COUNT(DISTINCT S.Department) = 1
AND MAX(CASE WHEN S.Department = 'Catering' THEN 1 ELSE 0 END) = 1;
GO
DROP TABLE #Sample;
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 27, 2017 at 4:20 am
Everything is much simpler, really
SELECT Whatever
FROM orders
where Orderdate >= @OrderdateOnly
and OrderDate < dateadd(dd, 1, @OrderdateOnly)
_____________
Code for TallyGenerator
February 27, 2017 at 4:23 am
Sergiy - Monday, February 27, 2017 4:20 AMEverything is much simpler, really
SELECT Whatever
FROM orders
where Orderdate >= @OrderdateOnly
and OrderDate < dateadd(dd, 1, @OrderdateOnly)
I'm not sure that meets the OP's goal. From their description: "but how do I find out if a Customer (CustomerID) only on day orders from (ORDERS Table) the Department 'Catering'", I believe they are implying that they only want details of the customer and the day that they only ordered from catering, not all orders on a specific day.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 27, 2017 at 6:01 pm
Thom A - Monday, February 27, 2017 4:23 AMThom A - Monday, February 27, 2017 4:23 AMSergiy - Monday, February 27, 2017 4:20 AMEverything is much simpler, really
SELECT Whatever
FROM orders
where Orderdate >= @OrderdateOnly
and OrderDate < dateadd(dd, 1, @OrderdateOnly)I'm not sure that meets the OP's goal. From their description: "but how do I find out if a Customer (CustomerID) only on day orders from (ORDERS Table) the Department 'Catering'", I believe they are implying that they only want details of the customer and the day that they only ordered from catering, not all orders on a specific day.
I'm not sure that meets the OP's goal. From their description: "but how do I find out if a Customer (CustomerID) only on day orders from (ORDERS Table) the Department 'Catering'", I believe they are implying that they only want details of the customer and the day that they only ordered from catering, not all orders on a specific day.
That would be an additional filter:AND Department = 'Catering'
_____________
Code for TallyGenerator
February 28, 2017 at 1:52 am
Sergiy - Monday, February 27, 2017 6:01 PMThat would be an additional filter:AND Department = 'Catering'
And if the Customer ordered from a different department that day, they would still be included in your result set. The OP did not want that.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 1, 2017 at 9:40 am
Out of curiosity. Is this a homework question?
I remember seeing something really similar months ago.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply