Add Where-Condition to Joined View with Group By

Question

I am currently building a website where people can place orders so it's kinda like an online shop. I want to create a view where i can easily see which users have not placed an order for more than 90 days.

For this I have created this MS SQL View:

SELECT
    dbo.AspNetUsers.Id,
    dbo.AspNetUsers.FirstName,
    dbo.AspNetUsers.LastName,
    dbo.AspNetUsers.Email,
    dbo.AspNetUsers.Street,
    dbo.AspNetUsers.SignupDate,
    MAX(dbo.Orders.OrderDate) AS OrderDate 
FROM
    dbo.AspNetUsers 
INNER JOIN
    dbo.Orders 
        ON dbo.AspNetUsers.Id = dbo.Orders.OrderUserId 
GROUP BY
    dbo.AspNetUsers.Id,
    dbo.AspNetUsers.FirstName,
    dbo.AspNetUsers.LastName,
    dbo.AspNetUsers.Email,
    dbo.AspNetUsers.Street,
    dbo.AspNetUsers.SignupDate

Now this piece of code is still lacking the where-condition that checks if the date is older than 90 days. WHERE dbo.Orders.OrderDate < DATEADD(day, -90, GETDATE()). I tried adding it before the group by but this does not work (It should only check if the most recent order is older than 90 days and this would check if there is any order date that is older than 90 days).

So where can I put this condition to achieve the desired result?

score 4 · Accepted Answer · answered Jul 31 '19 at 06:04

4

Check aggregate conditions with having instead of where

GROUP BY ...
HAVING MAX(dbo.Orders.OrderDate) < DATEADD(day, -90, GETDATE())

answered Jul 31 '19 at 06:04

juergen d

201,996
37
293
362

Thank's for the code example. It seems to work now! – Tim Gerhard Jul 31 '19 at 06:08

score 1 · Answer 2 · answered Jul 31 '19 at 06:04

1

please try to use having clause

answered Jul 31 '19 at 06:04

Ahmed Emad

265
2
4

Add Where-Condition to Joined View with Group By

2 Answers2