Aaron Bertrand posted a challenge:
We’re going to use the AdventureWorks sample database (get your copy here), where the folks in marketing requested a list of users to e-mail a new promotional campaign. The customers need to meet at least one of the following criteria:
- last placed an order more than a year ago
- placed 3 or more orders in the past year
- have ordered from a specific category in the past two weeks
These criteria don’t have to make sense! They just need to make the query a little bit more complex than your average CRUD operations.
First, we’re going to update
Sales.SalesOrderHeaderto modern times so that dates make sense relative to today. We only care aboutOrderDatehere, but there are check constraints that protect a couple of other columns (as well as a trigger that sometimes fails on db<>fiddle but that I have no energy to troubleshoot):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DISABLE TRIGGER Sales.uSalesOrderHeader ON Sales.SalesOrderHeader; GO DECLARE @months int; SELECT @months = DATEDIFF(MONTH, MAX(OrderDate), GETDATE()) FROM Sales.SalesOrderHeader; UPDATE Sales.SalesOrderHeader SET DueDate = DATEADD(MONTH, @months-1, DueDate); UPDATE Sales.SalesOrderHeader SET ShipDate = DATEADD(MONTH, @months-1, ShipDate); UPDATE Sales.SalesOrderHeader SET OrderDate = DATEADD(MONTH, @months-1, OrderDate); GO ENABLE TRIGGER Sales.uSalesOrderHeader ON Sales.SalesOrderHeader; |
This stored procedure that someone wrote will now return data (without the update, it would be hard to write predictable queries based on, say, some offset from GETDATE()).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
create proc sp_find_customers(@paramIntCategoryId INT) as select customerid, firstname, emailaddress from ( -- last placed an order more than a year ago select distinct customerid, firstname, emailaddress from sales.customer a (nolock), person.person b(nolock), person.emailaddress c(nolock) where personid = b.businessentityid and b.businessentityid = c.businessentityid and (select max(orderdate) from sales.salesorderheader (nolock) where customerid = a.customerid) < dateadd(yyyy,-1,convert(DATE,getdate())) union -- placed at least 3 orders in the past year select distinct customerid, firstname, emailaddress from person.person p (nolock) join person.emailaddress e (nolock) on p.businessentityid = e.businessentityid join sales.customer c (nolock) on personid = p.businessentityid where customerid in (select customerid from sales.salesorderheader (nolock) where orderdate between dateadd(yy, -1, convert(DATE,getdate())) and getdate() group by customerid having count(*) >= 3) union -- ordered within specified category in past two weeks select distinct customerid, firstname, emailaddress from person.emailaddress em (nolock) join person.person pp with (nolock) on em.businessentityid=pp.businessentityid join sales.customer cu (nolock) on personid = pp.businessentityid where customerid in (select top (2147483647) customerid from sales.salesorderheader oh (nolock) join sales.salesorderdetail od (nolock) on oh.salesorderid = od.salesorderid and datediff(day, oh.orderdate, convert(DATE,getdate())) <= 14 join production.product pr (nolock) on od.productid = pr.productid inner join production.productsubcategory AS sc (nolock) on pr.productsubcategoryid = sc.productsubcategoryid and sc.productcategoryid = @paramIntCategoryId order by customerid) ) x order by 1 |
Aaron’s answers are over here. I’ve turned off comments on this blog post because if you have any questions or thoughts, you should post ’em on Aaron’s blog post. The only reason I’m posting this here is that I bet a lot of y’all aren’t subscribed to Simple Talk, and I wanted to break this challenge up into two parts – the code to review, and the answers. Do not look at the answers until you’re done with your code review. Have fun!
