January 20, 2016 at 7:49 am
I'm trying to find all customers who have placed an order but there isn't a callback after their last order date.
My current query:
SELECT dbo.[Order].CustomerId, MAX(OrderDate) AS OrderDate, NextCallbackDate, UserName
FROM dbo.[Order]
LEFT OUTER JOIN (SELECT MAX(CallbackDate) NextCallbackDate, CustomerID
FROM AccountCallbacks
GROUP By CustomerId
) callbacks ON callbacks.CustomerID = dbo.[Order].CustomerID
LEFT OUTER JOIN dbo.aspnet_Users users ON dbo.[Order].UserID = users.UserId
WHERE (PaymentTypeID IN (2, 3, 4, 6, 8))
AND OrderDate >= NextCallbackDate
GROUP BY dbo.[Order].CustomerID, dbo.[Order].OrderDate,callbacks.NextCallbackDate, UserName
ORDER BY dbo.[Order].CustomerID
Tables:
AccountCallBacks:
[CallbackID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [uniqueidentifier] NOT NULL,
[CustomerID] [int] NOT NULL,
[Created] [datetime] NOT NULL,
[CallbackDate] [date] NOT NULL,
[Enabled] [bit] NOT NULL,
[CallbackTimeID] [int] NULL,
[GaryCust] [bit] NULL,
[NotInterestedReasonID] [int] NULL
Order Table:
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[CustomerID] [int] NULL,
[UserID] [uniqueidentifier] NULL,
[OrderDate] [datetime] NOT NULL,
[PaymentTypeID] [int] NULL,
[PaymentStatusID] [int] NOT NULL,
[PaymentDate] [datetime] NULL,
[TransactionRef] [varchar](50) NULL
And the aspnet_Users table is the usual .net membership users table
My query doesn't give me what I'm expecting, one of the rows of data for a particular CustomerID isn't in the result set. There should always be at least one AccountCallbacks.CallbackDate for every CustomerID as I'm joining on the dbo.[Order] table and they wouldn't be in there without first ever being in the AccountCallbacks table.
Feel free to ask any other info, help is greatly appreciated.
January 20, 2016 at 8:01 am
Thanks for posting the CREATE TABLE for two tables. Can you please add the third? Because "the usual .net membership users table" may be obvious to you, but it isn't to me.
And then can you please also post some sample data (as INSERT statements) with a few rows of representative data? Make sure to select data that demonstrates the problem. Add an overview of the expected results, and the actual results you are seeing. Seeing the actual input and the expected output usually explains the issue way better than any abstract description will.
January 20, 2016 at 8:18 am
You really need to get in the habit of using aliases and prefixing every column in your queries with that alias. It makes it a LOT easier to figure out what column is from what table. I have to ask why you are using LEFT joins if you are 100% certain you have rows in the table. Your where predicate "OrderDate >= NextCallbackDate" has changed your left join to an inner join.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 20, 2016 at 8:50 am
Apologies to both! I used a left outer join during my fiddling trying to figure out why some records were not showing up..
aspnet_Users table:
[dbo].[aspnet_Users](
[ApplicationId] [uniqueidentifier] NOT NULL,
[UserId] [uniqueidentifier] NOT NULL,
[UserName] [nvarchar](256) NOT NULL,
[LoweredUserName] [nvarchar](256) NOT NULL,
[MobileAlias] [nvarchar](16) NULL,
[IsAnonymous] [bit] NOT NULL,
[LastActivityDate] [datetime] NOT NULL,
Example incorrect data:
CustomerId OrderDate NextCallbackDate UserName
531 2015-01-14 14:09:43.3802015-01-14 jessica
1034 2015-02-05 11:58:23.1172015-02-05 naz
1323 2015-01-07 16:46:41.4572014-12-21 kath
1328 2014-12-09 10:12:12.1472014-12-09 kath
1370 2015-04-14 14:24:38.8302015-04-14 angelo
1599 2015-03-26 13:35:03.0002015-03-26 NULL
1599 2015-03-26 13:41:45.5902015-03-26 jessica
1657 2015-04-09 11:31:25.8172015-04-09 kath
1692 2015-02-09 15:46:56.6672015-02-06 angelo
When I say these are incorrect, they're actually correct because the 'NextCallbackDate' is before their last OrderDate, or what I believe is their last (or the same as) OrderDate.
I'm working on getting some other data and will create some insert statements - a missing customer/record is only noticed if they actually call the company and place an order! This customer meets the criteria in my where clause as far as I can see, PaymentTypeID matches along with their NextcallbackDate being the same or before their last OrderDate.
Thanks,
Michael
January 20, 2016 at 8:56 am
I am confused now. Do you still need help or did you figure it out? If you need help I am not understanding what the problem is.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 20, 2016 at 9:07 am
Hi,
Yes, I do need help.
My issue is:
I want a list of CustomerID, UserName, LastOrderDate, NextCallbackDate for all customers who have an record in the Order table but their last callback date in the AccountCallbacks table was either the same time or before their last order date, meaning a customer has ordered but their isn't a future callback date in the AccountCallbacks table for that CustomerID.
I thought my query was correct but a customer will randomly call the office to place an order and it is at that point it's realised they didn't have a future callback date set, it was just by luck they called to place their order, which means my query isn't doing what I think it's doing.
I hope I'm making sense!
Thanks,
Michael
January 20, 2016 at 9:30 am
michaeleaton 36224 (1/20/2016)
I'm working on getting some other data and will create some insert statements
Good. Because I notice that after reading your descriptions I am almost tempted to slide into guessing mode, and that almost always backfires.
I will wait until you post the INSERT statements and expected output, so that I can compare that to the results from your attempted query - seeing the actual error will probably help me see the error in your current query, and propose a fix.
January 20, 2016 at 12:30 pm
i'm betting Sean Lange's suggestion that your left join converted to inner is your core problem:
modify your join criteria like this, doe sthat solve the issue?
SELECT
ord.CustomerId,
MAX(ordOrderDate) AS OrderDate,
callbacks.NextCallbackDate,
users.UserName
FROM dbo.[Order] ord
LEFT OUTER JOIN (SELECT MAX(CallbackDate) NextCallbackDate, CustomerID
FROM AccountCallbacks
GROUP By CustomerId
) callbacks
[highlight="#ffff11"] ON callbacks.CustomerID = ord.CustomerID
AND OrderDate >= NextCallbackDate[/highlight]
LEFT OUTER JOIN dbo.aspnet_Users users ON ord.UserID = users.UserId
WHERE (PaymentTypeID IN (2, 3, 4, 6, 8))
GROUP BY o.CustomerID, o.OrderDate,callbacks.NextCallbackDate, UserName
ORDER BY dbo.[Order].CustomerID
Lowell
January 20, 2016 at 2:24 pm
Hi Lowel,
Thanks for that, but I get the exact same result set as my original query.
Here are my create scripts.
https://www.dropbox.com/s/bh2ynmlsyt9o8z5/sqlScripts.zip?dl=0
I'll try explaining what I'm after again.
I want a list of Customers (Fields: CustomerID, UserName (from 'LastOrderDate' UserID field in Order table), LastOrderDate, NextCallbackDate (this could be in the past)) where since their last order based on date was after or the same day as their last callback in the AccountCallbacks table.
I hope I've made sense this time!
Thanks,
Michael
January 20, 2016 at 2:47 pm
michaeleaton 36224 (1/20/2016)
Hi Lowel,Thanks for that, but I get the exact same result set as my original query.
Here are my create scripts.
https://www.dropbox.com/s/bh2ynmlsyt9o8z5/sqlScripts.zip?dl=0
I'll try explaining what I'm after again.
I want a list of Customers (Fields: CustomerID, UserName (from 'LastOrderDate' UserID field in Order table), LastOrderDate, NextCallbackDate (this could be in the past)) where since their last order based on date was after or the same day as their last callback in the AccountCallbacks table.
I hope I've made sense this time!
Thanks,
Michael
WOW!!!! That is a LOT of sample data. Could probably save yourself some headaches in the future and not post 30,000 rows for each table. Aside from some foreign keys that don't work due to missing tables this sample was easy to work with.
The challenge is that we have no idea what you are really looking for or what the output should be based on your sample data. Doing my best to interpret what you want I think it is as simple as this query.
select o.CustomerID
, u.UserName
, MAX(o.OrderDate) as LastOrderDate
, MAX(ac.CallbackDate) as NextCallbackDate
from Orders o
join aspnet_Users u on u.UserId = o.UserID
left join AccountCallbacks ac on ac.UserID = o.UserID and o.CustomerID = ac.CustomerID
group by o.CustomerID
, u.UserName
order by u.UserName
If that is in fact not what you are looking for we may need to pare down the sample data to 1 or 2 customers and figure our what it is you really want.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 20, 2016 at 3:18 pm
michaeleaton 36224 (1/20/2016)
I'm trying to find all customers who have placed an order but there isn't a callback after their last order date.There should always be at least one AccountCallbacks.CallbackDate for every CustomerID as I'm joining on the dbo.[Order] table and they wouldn't be in there without first ever being in the AccountCallbacks table.
am not sure if I have this correct, but looking at the sample data you provided, it appears that you have orders without a customerID and also orders that do not have any AccountCallBack rows.......??
probably misunderstood....<grin>
SELECT *
FROM [order]
WHERE(CustomerID IS NULL);
SELECT O.OrderID,
O.CustomerID,
AC.CustomerID AS AC_Missing
FROM [Order] AS O
LEFT OUTER JOIN AccountCallbacks AS AC ON O.CustomerID = AC.CustomerID
WHERE(AC.CustomerID IS NULL)
AND (NOT(O.CustomerID IS NULL));
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply