January 11, 2018 at 5:01 am
Hi,
I need to do a report for sales reps showing the customers that haven;t ordered between a set of chosen dates.
I need to break the dates down. If, for instance, the 1st October 2017 to 31 December 2017 was chosen I need to list the customers and the months they didn’t order between those dates.
I can find the customers that didn’t order by just checking which account ID’s are not in the orders table between the 2 dates. I currently find the last order date by using the MAX command but I am trying to use HAVING with the MAX command so that it only looks between the selected dates.
I am getting a conversion failed converting date and/or time from character string error.
Can someone help me out with the correct syntax please?
This is my code st the moment...........
SELECT
dbo.Sales.SALESDESCRIPTION,
dbo.Groups.GROUPDESCRIPTION,
dbo.Account.ACCOUNTNAME ,
dbo.Account.ACCOUNTNUMBER,
max(dbo.orders.ORDERDATEANDTIME) as 'LAST_ORDER_DATE',
DATENAME (MONTH,dbo.Orders.ORDERDATEANDTIME) as MONTH,
(datepart(mm,dbo.Orders.ORDERDATEANDTIME)) AS MONTH_NUMBER
FROM
dbo.Account
LEFT OUTER JOIN
dbo.Orders ON dbo.Account.ACCOUNTID = dbo.Orders.ACCOUNTID
LEFT OUTER JOIN
dbo.Sales ON dbo.Account.SALESID = dbo.Sales.SALESID
LEFT OUTER JOIN
dbo.Groups ON dbo.Account.GROUPID = dbo.Groups.GROUPID
where
dbo.account.ACCOUNTID
not in
(SELECT dbo.orders.ACCOUNTID FROM dbo.orders
where dbo.orders.ORDERDATEANDTIME >= convert(datetime,'01/06/2017',103)
and dbo.orders.ORDERDATEANDTIME < convert(datetime,'30/09/2017',103))
group by
dbo.Sales.SALESDESCRIPTION,
dbo.Groups.GROUPDESCRIPTION,
dbo.account.ACCOUNTNAME,
dbo.account.ACCOUNTNUMBER,
dbo.account.ACCOUNTID,
dbo.Orders.ORDERDATEANDTIME
HAVING
('LAST_ORDER_DATE' >= convert(datetime,'01/06/2017',103))
AND
('LAST_ORDER_DATE' < convert(datetime,'30/09/2017',103))
ORDER BY dbo.Sales.SALESDESCRIPTION ASC
January 11, 2018 at 5:16 am
Try AND rather than OR in your HAVING clause?
Thomas Rushton
blog: https://thelonedba.wordpress.com
January 11, 2018 at 5:19 am
Yes, I just noticed the OR, I have changed that to AND now but it still gives the same error
January 11, 2018 at 5:27 am
Missed that bit, sorry.
This is always a problem when converting between strings and dates, and is avoided by using ISO 8601 format date strings, putting the year at the front. Give that a go.
In your HAVING clause, that would make it:
HAVING ('LAST_ORDER_DATE' >= convert(datetime,'20170601',103))
AND ('LAST_ORDER_DATE' < convert(datetime,'20170930',103))
Incidentally, if you are doing this, then you're going to lose everything that was done on 30th September - perhaps that's intended, but perhaps you want to use 20171001...
Thomas Rushton
blog: https://thelonedba.wordpress.com
January 11, 2018 at 5:39 am
Thank you Thomas.
In the actual query, it's in SSRS, I use variables for the dates so the < part of the query is the variable + 1 to ensure the upper date is included. I just manually enter the dates for testing.
I have tried changing it to the iso 8601 date but it still gives the same error.,
January 11, 2018 at 5:48 am
You changed the WHERE clause as well?
Thomas Rushton
blog: https://thelonedba.wordpress.com
January 11, 2018 at 5:54 am
Yes,
This is how it looks now and I still get the same error.
SELECT
dbo.Sales.SALESDESCRIPTION,
dbo.Groups.GROUPDESCRIPTION,
dbo.Account.ACCOUNTNAME,
dbo.Account.ACCOUNTNUMBER,
max(dbo.orders.ORDERDATEANDTIME) as 'LAST_ORDER_DATE',
DATENAME (MONTH,dbo.Orders.ORDERDATEANDTIME) as MONTH,
(datepart(mm,dbo.Orders.ORDERDATEANDTIME)) AS MONTH_NUMBER
FROM
dbo.Account
LEFT OUTER JOIN
dbo.Orders ON dbo.Account.ACCOUNTID = dbo.Orders.ACCOUNTID
LEFT OUTER JOIN
dbo.Sales ON dbo.Account.SALESID = dbo.Sales.SALESID
LEFT OUTER JOIN
dbo.Groups ON dbo.Account.GROUPID = dbo.Groups.GROUPID
where
dbo.account.ACCOUNTID
not in
(SELECT dbo.orders.ACCOUNTID FROM dbo.orders
where dbo.orders.ORDERDATEANDTIME >= convert(datetime,'20170601',103)
and dbo.orders.ORDERDATEANDTIME < convert(datetime,'20170930',103))
group by
dbo.Sales.SALESDESCRIPTION,
dbo.Groups.GROUPDESCRIPTION,
dbo.account.ACCOUNTNAME,
dbo.account.ACCOUNTNUMBER,
dbo.account.ACCOUNTID,
dbo.Orders.ORDERDATEANDTIME
HAVING
('LAST_ORDER_DATE' >= convert(datetime,'20170601',103))
AND
('LAST_ORDER_DATE' < convert(datetime,'20170930',103))
ORDER BY dbo.Sales.SALESDESCRIPTION ASC
January 11, 2018 at 6:39 am
OK, so the next thing is for you to show us the schema - what are your table definitions? Is the field "ORDERDATEANDTIME" being stored as a datetime data type?
Thomas Rushton
blog: https://thelonedba.wordpress.com
January 11, 2018 at 6:48 am
Hi
Don't think we can use alias in having clause
Thanks
January 11, 2018 at 6:57 am
Yes, orderdateandtime is a datetime field.
January 11, 2018 at 7:15 am
why are you using: ( 'LAST_ORDER_DATE')
('LAST_ORDER_DATE' >= convert(datetime,'20170601',103))
AND
('LAST_ORDER_DATE' < convert(datetime,'20170930',103))
I would have thought it would be: (LAST_ORDER_DATE)
(LAST_ORDER_DATE >= convert(datetime,'20170601',103))
AND
(LAST_ORDER_DATE < convert(datetime,'20170930',103))
is it possible that 'LAST_ORDER_DATE' cannot be converted to a datetime?
January 11, 2018 at 7:42 am
I have tried it with and without the quotes. Without the quotes it says invalid column name
January 11, 2018 at 7:46 am
paul 69259 - Thursday, January 11, 2018 7:42 AMI have tried it with and without the quotes. Without the quotes it says invalid column name
That's because, as @Taps said, you can't use a column alias in a HAVING clause. It's because the HAVING clause is evaluated before the SELECT list and so the alias doesn't exist at evaluation time. You need to change it for the original expression: max(dbo.orders.ORDERDATEANDTIME).
John
January 11, 2018 at 12:11 pm
that is correct, Thanks John
January 11, 2018 at 1:55 pm
Just one thing I would like to point out. The use of 3-part naming in the SELECT column list has been deprecated. You really should be using table aliases. I have modified your original code so you can see what this may look like.
SELECT
[sal].[SALESDESCRIPTION]
, [grp].[GROUPDESCRIPTION]
, [acc].[ACCOUNTNAME]
, [acc].[ACCOUNTNUMBER]
, MAX([ord].[ORDERDATEANDTIME]) AS [LAST_ORDER_DATE]
, DATENAME(MONTH, [ord].[ORDERDATEANDTIME]) AS [MONTH]
, (DATEPART(mm, [ord].[ORDERDATEANDTIME])) AS [MONTH_NUMBER]
FROM
[dbo].[Account] [acc]
LEFT OUTER JOIN [dbo].[Orders] [ord]
ON [acc].[ACCOUNTID] = [ord].[ACCOUNTID]
LEFT OUTER JOIN [dbo].[Sales] [sal]
ON [acc].[SALESID] = [sal].[SALESID]
LEFT OUTER JOIN [dbo].[Groups] [grp]
ON [acc].[GROUPID] = [grp].[GROUPID]
WHERE
[acc].[ACCOUNTID] NOT IN (
SELECT
[ord].[ACCOUNTID]
FROM
[dbo].[Orders] [ord]
WHERE
[ord].[ORDERDATEANDTIME] >= CONVERT(DATETIME, '01/06/2017', 103)
AND [ord].[ORDERDATEANDTIME] < CONVERT(DATETIME, '30/09/2017', 103)
)
GROUP BY
[sal].[SALESDESCRIPTION]
, [grp].[GROUPDESCRIPTION]
, [acc].[ACCOUNTNAME]
, [acc].[ACCOUNTNUMBER]
, [acc].[ACCOUNTID]
, [ord].[ORDERDATEANDTIME]
HAVING
(MAX([ord].[ORDERDATEANDTIME]) >= CONVERT(DATETIME, '01/06/2017', 103))
AND (MAX([ord].[ORDERDATEANDTIME]) < CONVERT(DATETIME, '30/09/2017', 103))
ORDER BY
[sal].[SALESDESCRIPTION] ASC;
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply