January 16, 2018 at 8:51 pm
Several things.
1. Always use NOT EXISTS instead of NOT IN.
2. Always use aliases and make sure you don't use identical aliases for 2or more tables mentioned in the query.
If the alias [ord] is used for [dbo].[Orders] in outer query then you have to think of another one for [dbo].[Orders] in NOT IN (or NOT EXISTS) part.
3. Ditch HAVING clause altogether. It does not serve any purpose, except for adding extra work for the engine and slowing down the query.
Accounts which do not have orders between 1/06/2017 and 30/09/2017 cannot possibly have [Last_Order_Date] between those dates.
4. It seems you're missing the sale for the last day of the quarter. To check for all Sep sales you need to use = CONVERT(DATETIME, '01/06/2017', 103)
AND [qo].[ORDERDATEANDTIME] < CONVERT(DATETIME, '01/10/2017', 103)
)
GROUP BY
[sal].[SALESDESCRIPTION]
, [grp].[GROUPDESCRIPTION]
, [acc].[ACCOUNTNAME]
, [acc].[ACCOUNTNUMBER]
, [acc].[ACCOUNTID]
, DATENAME(MONTH, [ord].[ORDERDATEANDTIME])
, (DATEPART(mm, [ord].[ORDERDATEANDTIME]))
ORDER BY
[sal].[SALESDESCRIPTION] ASC
[/code]
_____________
Code for TallyGenerator
January 17, 2018 at 6:37 am
Thank you all very much for your time and replies.
I have asked the customer to mock up a report to show me exactly what they want and expect because they are basically asking me to report on something that doesn't exist i.e. customers that haven't ordered between a set of dates. At the moment the best I can do is give them the customers and their last order dates, I don't see what else I can give them.
Thank you
Paul.
January 17, 2018 at 9:25 am
paul 69259 - Wednesday, January 17, 2018 6:36 AMThank you all very much for your time and replies.I have asked the customer to mock up a report to show me exactly what they want and expect because they are basically asking me to report on something that doesn't exist i.e. customers that haven't ordered between a set of dates. At the moment the best I can do is give them the customers and their last order dates, I don't see what else I can give them.
Thank you
Paul.
I would say something like this would be a start:
SELECT
*
FROM
dbo.Customers cus
WHERE
NOT EXISTS(SELECT
1
FROM
dbo.OrderHeader oh
WHERE
oh.CustomerNumber = cus.CustomerNumber
AND oh.OrderDate >= @StartDate
AND oh.OrderDate < @EndDate); -- @EndDate is the date after the last date of the range searched
January 18, 2018 at 5:47 am
for clarification please.......if you use these dates 1st October 2017 to 31 December 2017 for your report and a customer has orders in Oct and Dec but not in November....do you just want to return the customer and Nov 17?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 18, 2018 at 5:57 am
@ J Livingston SQL
Thanks for your reply. It's ok, I have talked it over with the customer and I have been able to modify a report he already had to show the data he needed. When he said customers that haven't ordered between a certain date he really meant he wanted to retrieve all the orders for the different "TYPES" he has so that he could see if there were any zero values there and then follow them up with sales/
Got there in the end haha.
Thank you
Paul.
January 18, 2018 at 6:59 am
paul 69259 - Thursday, January 18, 2018 5:57 AM@ J Livingston SQLThanks for your reply. It's ok, I have talked it over with the customer and I have been able to modify a report he already had to show the data he needed. When he said customers that haven't ordered between a certain date he really meant he wanted to retrieve all the orders for the different "TYPES" he has so that he could see if there were any zero values there and then follow them up with sales/
Got there in the end haha.
Thank you
Paul.
ok ...glad to see you have solved it.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 18, 2018 at 10:29 am
>> I need to do a report for sales reps showing the customers that have not ordered between a set of chosen dates. I need to break the dates down. If, for instance, 2017-10-01 to 2017-12-31 was chosen I need to list the customers and the months they didn’t order between those dates. <<
Let’s start off basic problems. You fail to post any DDL, so we have to guess at all the keys, all the constraints and all the references among the tables. Are you able to program under these conditions?
Then you don’t understand how temporal data works in SQL. First of all, the only format allowed for display in the ANSI/ISO standards is “yyyy-mm-dd”, and not the local dialect that you’re using. Then you’re still using the old Sybase convert function and treating dates as if they were COBOL strings in the 1950s.
Finally, since we don’t have any DDL, I can only guess, that sales and orders are actually the same data kept in two separate tables. That is, keeping with your COBOL model, your using SQL to mimic decks of punch cards or sequential files that move an order record (or punch card) to a sale when it status changes. Is this is why you have so many outer joins? In a relational model, the order would have an order_status as it moves from an order, to a sale, to a shipment, possibly to a return, etc.
Finally, the purpose of SQL is to fetch data. It doesn’t do things like pull out the month name or any of that stuff. Sybase options were put in for the COBOL programmers, such as DATENAME, because they couldn’t conceive of a tiered architecture in which things like names would be done in the presentation layer, never in the database layer.
>> I can find the customers that didn’t order and orders by just checking which account_id’s are not in the Orders table between the two dates. I currently find the last order_date by using the MAX() function but I am trying to use HAVING with the MAX() command so that it only looks between the selected dates. <<
Where did the MAX() come from? It was not in the specs. Try this with set-oriented CTE instead of weird OUTER JOINs.
WITH No_Orders_in_Range (account_nbr)
AS
(SELECT A.account_nbr
FROM Accounts AS A
EXCEPT
SELECT O.account_nbr
FROM Orders AS O
WHERE O.order_date BETWEEN '2017-06-01' AND '2017-09-30')
SELECT ..;
>> I am getting a conversion failed converting date and/or time from character string error. <<
Why are you doing this at all? We have a DATE data type. It looks like you’re actually carrying what should be a simple DATE is a full timestamp (the datetime and datetime2 proprietary datatypes are called TIMESTAMP in standard SQL). I also see that you have only one account; that’s what a singular table name means. Because tables model sets, their name should be collective nouns or plurals instead.
Please post DDL and follow ANSI/ISO standards when asking for help.
January 18, 2018 at 10:35 am
jcelko212 32090 - Thursday, January 18, 2018 10:29 AM>> I need to do a report for sales reps showing the customers that have not ordered between a set of chosen dates. I need to break the dates down. If, for instance, 2017-10-01 to 2017-12-31 was chosen I need to list the customers and the months they didn’t order between those dates. <<Let’s start off basic problems. You fail to post any DDL, so we have to guess at all the keys, all the constraints and all the references among the tables. Are you able to program under these conditions?
Then you don’t understand how temporal data works in SQL. First of all, the only format allowed for display in the ANSI/ISO standards is “yyyy-mm-ddâ€, and not the local dialect that you’re using. Then you’re still using the old Sybase convert function and treating dates as if they were COBOL strings in the 1950s.
Finally, since we don’t have any DDL, I can only guess, that sales and orders are actually the same data kept in two separate tables. That is, keeping with your COBOL model, your using SQL to mimic decks of punch cards or sequential files that move an order record (or punch card) to a sale when it status changes. Is this is why you have so many outer joins? In a relational model, the order would have an order_status as it moves from an order, to a sale, to a shipment, possibly to a return, etc.
Finally, the purpose of SQL is to fetch data. It doesn’t do things like pull out the month name or any of that stuff. Sybase options were put in for the COBOL programmers, such as DATENAME, because they couldn’t conceive of a tiered architecture in which things like names would be done in the presentation layer, never in the database layer.
>> I can find the customers that didn’t order and orders by just checking which account_id’s are not in the Orders table between the two dates. I currently find the last order_date by using the MAX() function but I am trying to use HAVING with the MAX() command so that it only looks between the selected dates. <<
Where did the MAX() come from? It was not in the specs. Try this with set-oriented CTE instead of weird OUTER JOINs.
WITH No_Orders_in_Range (account_nbr)
AS
(SELECT A.account_nbr
FROM Accounts AS A
EXCEPT
SELECT O.account_nbr
FROM Orders AS O
WHERE O.order_date BETWEEN '2017-06-01' AND '2017-09-30')SELECT ..;
>> I am getting a conversion failed converting date and/or time from character string error. <<
Why are you doing this at all? We have a DATE data type. It looks like you’re actually carrying what should be a simple DATE is a full timestamp (the datetime and datetime2 proprietary datatypes are called TIMESTAMP in standard SQL). I also see that you have only one account; that’s what a singular table name means. Because tables model sets, their name should be collective nouns or plurals instead.
Please, Mr. Celko, stop disparaging COBOL programmers. It is getting so old and tired.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply