How to use HAVING with MAX on dates.

  • paul 69259

    SSCrazy

    Points: 2890

    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

  • Thomas Rushton

    SSC-Insane

    Points: 22648

    Try AND rather than OR in your HAVING clause?

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • paul 69259

    SSCrazy

    Points: 2890

    Yes, I just noticed the OR, I have changed that to AND now but it still gives the same error

  • Thomas Rushton

    SSC-Insane

    Points: 22648

    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

  • paul 69259

    SSCrazy

    Points: 2890

    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.,

  • Thomas Rushton

    SSC-Insane

    Points: 22648

    You changed the WHERE clause as well?

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • paul 69259

    SSCrazy

    Points: 2890

    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

  • Thomas Rushton

    SSC-Insane

    Points: 22648

    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

  • @Taps

    SSC Eights!

    Points: 868

    Hi
    Don't think we can use alias in having  clause
    Thanks

  • paul 69259

    SSCrazy

    Points: 2890

    Yes, orderdateandtime is a datetime field.

  • maroon-78

    Ten Centuries

    Points: 1014

    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?

  • paul 69259

    SSCrazy

    Points: 2890

    I have tried it with and without the quotes. Without the quotes it says invalid column name

  • John Mitchell-245523

    SSC Guru

    Points: 148761

    paul 69259 - Thursday, January 11, 2018 7:42 AM

    I 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

  • maroon-78

    Ten Centuries

    Points: 1014

    that is correct, Thanks John

  • Lynn Pettis

    SSC Guru

    Points: 442337

    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 23 total)

You must be logged in to reply to this topic. Login to reply