August 24, 2011 at 10:37 am
Hey folks, I'm relatively new to writing complex SQL statements and have a business reporting challenge that I'm stuck on, was hoping someone could provide some direction or advice.
The business need is a list of all current maintenance customers who have not let their maintenance agreement lapse over the last 5 years. The only identifier I have to determine if a customer has not lapsed is if they have purchased a maintenance product during the year in question.
I'm able to query and get a list all customers who have ordered a maintenance product, but I'm having problems transitioning to the next step of comparing the last 5 years and returning a single row for a single customer IF they do in fact have each of those 5 orders.
Hope that makes some kind of sense.
This is what I've done so far, just created a CTE to grab the date, product name and account name from the various tables that contain that data. I've played around with the select statement afterwards but keep coming up shy of the end result. Any help is appreciated!
USE companydatabase;
WITH SalesDetailsCTE (SoDate, PName, ActName)
AS
(
SELECT so.SubmitDate, p.Name, a.Name
FROM dbo.salesorderdetailbase AS sod
JOIN dbo.salesorderbase AS so
ON so.SalesOrderID = sod.SalesOrderID
LEFT JOIN dbo.AccountBase AS a
on a.AccountID = so.AccountID
LEFT JOIN dbo.ProductBase AS p
on sod.ProductID = p.ProductID
Where p.Name LIKE '%Maint%' AND a.Name IS NOT NULL AND DATEPART(yyyy, so.SubmitDate) Between '2006' AND '2011'
)
Select ActName, DATEPART(yyyy, SoDate) AS [Year]
FROM SalesDetailsCTE
GROUP BY SoDate, ActName
ORDER BY ActName, SoDate DESC
August 24, 2011 at 11:13 am
Adding a Row_Number partitioned by the Year(SubmitDate) order by SubmitDate in the CTE and in the final select selecting values <= 5 on Row Number will get you there.. BUT, this is just my shot in the dark.
Can you please provide sample data of your table and the table structure (in readily consumable format , as in CREATe TAble and INSERT INTO TABLE script) so that we can give u a test answer ?
August 24, 2011 at 11:43 am
I will get the requested scripts up for you later today, thanks!
August 24, 2011 at 11:46 am
I think you want
Select ActName, PName
FROM SalesDetailsCTE
GROUP BY ActName, PName
HAVING COUNT(DISTINCT DATEPART(yyyy, SoDate)) = 5
(this says for each customer and product, show me the customers that have bought a maintenance agreement in 5 different years between 2006 and 2011)
However I notice you have 6 years of data in your CTE, so a customer might have not bought in 2007, but would still show up. Also, unless salesOrderDetailBase or accountBase includes only current customers, you're missing that part of the requirement.
If you have situations where a customer bought maintenance agreements in January and December of the same year, you'll have to remove the DISTINCT keywords.
If you have a situation where some customers buy maintenance agreements, say, monthly you'll need to account for that.
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
August 25, 2011 at 4:22 am
I could not test it, as you did not include any DDL or test data, but this should do the trick.
with cteMaintenanceSales as (
-- Collect all sales where a maintenance product was included.
select so.AccountID, so.SubmitDate
from dbo.SalesOrderBase so
where exists (
select *
from dbo.SalesOrderDetailBase sod
inner join dbo.ProductBase AS p ON sod.ProductID = p.ProductID and p.Name LIKE '%Maint%'
where sod.SalesOrderID = so.SalesOrderID
)
)
,cteOneToFive as (
-- Number 'generator' from 1 to 5, for the 5 year range.
select 1 as n union all select 2 union all select 3 union all select 4 union all select 5
)
-- For active accounts (i.e. have bought at least 1 maintenance product this year)
-- show their history for the last 5 years.
select a.Name,
x.thisyear,
x.lastyear,
x.lastlastyear,
x.lastlastlastyear,
x.lastlastlastlastyear,
case when not (x.thisyear = 0 or x.lastyear = 0 or x.lastlastyear = 0 or x.lastlastlastyear = 0 or x.lastlastlastlastyear = 0)
then 'We have winner!'
else 'Sorry, you don''t apply for the bonus.'
end
from (
-- Calculate the reporting date as January 1st of the current year.
select dateadd(year, datediff(year, 0, getdate()), 0) as dt
) rpt
cross join dbo.AccountBase as a
cross apply (
-- Collect for this account the sales history for maintenance
-- products over the last 5 years.
select isnull(sum(case when c.n = 1 then 1 else 0 end), 0) as thisyear
,isnull(sum(case when c.n = 2 then 1 else 0 end), 0) as lastyear
,isnull(sum(case when c.n = 3 then 1 else 0 end), 0) as lastlastyear
,isnull(sum(case when c.n = 4 then 1 else 0 end), 0) as lastlastlastyear
,isnull(sum(case when c.n = 5 then 1 else 0 end), 0) as lastlastlastlastyear
from cteOneToFive c
inner join cteMaintenanceSales ms on (ms.SubmitDate >= dateadd(year, 1 - n, rpt.dt) and ms.SubmitDate < dateadd(year, 2 - n, rpt.dt))
where ms.AccounID = a.AccountID
) x
where x.thisyear > 0 -- Only the currently active accounts.
August 25, 2011 at 4:33 am
Again, you'll have to test it, but I think this will be faster on larger data sets.
with cteMaintenanceSales as (
-- Collect all sales where a maintenance product was included.
select so.AccountID, so.SubmitDate
from dbo.SalesOrderBase so
where exists (
select *
from dbo.SalesOrderDetailBase sod
inner join dbo.ProductBase AS p ON sod.ProductID = p.ProductID and p.Name LIKE '%Maint%'
where sod.SalesOrderID = so.SalesOrderID
)
)
,cteOneToFive as (
-- Number 'generator' from 1 to 5, for the 5 year range.
select 1 as n union all select 2 union all select 3 union all select 4 union all select 5
)
-- For active accounts (i.e. have bought at least 1 maintenance product this year)
-- show their history for the last 5 years.
select a.Name,
x.thisyear,
x.lastyear,
x.lastlastyear,
x.lastlastlastyear,
x.lastlastlastlastyear,
case when not (x.thisyear = 0 or x.lastyear = 0 or x.lastlastyear = 0 or x.lastlastlastyear = 0 or x.lastlastlastlastyear = 0)
then 'We have winner!'
else 'Sorry, you don''t apply for the bonus.'
end
from (
-- Calculate the reporting date as January 1st of the current year.
select dateadd(year, datediff(year, 0, getdate()), 0) as dt
) rpt
cross join dbo.AccountBase as a
inner join (
select ms.AccountID
,isnull(sum(case when c.n = 1 then 1 else 0 end), 0) as thisyear
,isnull(sum(case when c.n = 2 then 1 else 0 end), 0) as lastyear
,isnull(sum(case when c.n = 3 then 1 else 0 end), 0) as lastlastyear
,isnull(sum(case when c.n = 4 then 1 else 0 end), 0) as lastlastlastyear
,isnull(sum(case when c.n = 5 then 1 else 0 end), 0) as lastlastlastlastyear
from cteOneToFive c
inner join cteMaintenanceSales ms on (ms.SubmitDate >= dateadd(year, 1 - n, rpt.dt) and ms.SubmitDate < dateadd(year, 2 - n, rpt.dt))
group by ms.AccountID
) x on x.AccountID = a.AccountID
where x.thisyear > 0 -- Only the currently active accounts.
August 25, 2011 at 12:17 pm
Thanks a ton for the responses. Sorry I'm in the middle of a week long sql training class and was up studying till 1am last night going over everything that day and doing the labs again as I'm really trying to know this stuff....so much to learn!
I will make sure to post any future request along with sample data and table creation scripts.
Your code works beautifully, and thanks for the comments. I think I understand about 90% of it but some of the logic is a bit out of my reach (need to read up on cross apply).
Thanks again!
August 26, 2011 at 10:40 am
weitzera (8/24/2011)
I think you want
Select ActName, PNameFROM SalesDetailsCTE
GROUP BY ActName, PName
HAVING COUNT(DISTINCT DATEPART(yyyy, SoDate)) = 5
(this says for each customer and product, show me the customers that have bought a maintenance agreement in 5 different years between 2006 and 2011)
However I notice you have 6 years of data in your CTE, so a customer might have not bought in 2007, but would still show up. Also, unless salesOrderDetailBase or accountBase includes only current customers, you're missing that part of the requirement.
If you have situations where a customer bought maintenance agreements in January and December of the same year, you'll have to remove the DISTINCT keywords.
If you have a situation where some customers buy maintenance agreements, say, monthly you'll need to account for that.
I'm not sure that's true. If an account was active every other year for the last ten years instead of being fully active for the last 5 years, I believe your code would still return the non-continuous account.
--Jeff Moden
August 26, 2011 at 11:18 am
If an account was active every other year for the last ten years instead of being fully active for the last 5 years, I believe your code would still return the non-continuous account.
THE CTE provided limits the number of years to look at, so that's not true. However it is true that the code depends on that restriction being in the CTE. Note the following comment from my original post:
However I notice you have 6 years of data in your CTE, so a customer might have not bought in 2007, but would still show up. Also, unless salesOrderDetailBase or accountBase includes only current customers, you're missing that part of the requirement.
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
August 26, 2011 at 11:41 am
Ah... apologies. I didn't see that you'd left the CTE out of your code.
--Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply