What I'm looking to do is quantify the total value of purchases and the number of months in which a purchase was made within three different timeframes by account. I only want to look at accounts who made a purchase between 1-1-2020 and 4-1-2021.

I'm wondering if there is a more streamlined way to pull in the fields I'm creating using CASE WHEN below (maybe through a series of queries to create the calculations and the left joining?). This query is taking extremely long to pull back, so I'd like to enhance this code where I can. All of my code and desired output is listed below. Thank you!

Creating a temporary table to pull account numbers:

DROP TABLE IF EXISTS #accounts

SELECT DISTINCT s.account_no, c.code, c.code_desc
INTO #accounts
FROM sales AS s
LEFT JOIN customer AS c ON s.account_no = c.account_no
WHERE s.tran_date BETWEEN '2020-01-01' AND '2021-04-01'
GROUP BY s.account_no, c.code, c.code_desc;

Confirming row counts:

SELECT COUNT (*)
FROM #accounts
ORDER BY account_no;

Creating Sales and Sales period count columns for three timeframes:

SELECT 
    s.account_no, c.code, c.code_desc
    SUM(CASE 
            WHEN s.tran_date BETWEEN '2020-01-01' AND '2021-04-01' 
                THEN VALUE_USD 
        END) AS Total_Spend_Pre,
    SUM(CASE 
            WHEN s.tran_date BETWEEN '2021-04-01' AND '2022-03-31' 
                THEN VALUE_USD 
        END) Total_Spend_During,
    SUM(CASE 
            WHEN s.tran_date > '2022-04-01' 
                THEN VALUE_USD 
        END) Total_Spend_Post,
    COUNT(DISTINCT CASE WHEN s.tran_date BETWEEN '2020-01-01' AND '2021-04-01' THEN CONCAT(s.bk_month, s.bk_year) END) Pre_Periods,
    COUNT(DISTINCT CASE WHEN s.tran_date BETWEEN '2021-04-01' AND '2022-03-31' THEN CONCAT(s.bk_month, s.bk_year) END) During_Periods,
    COUNT(DISTINCT CASE WHEN s.tran_date > '2022-04-01' THEN CONCAT(s.bk_month, s.bk_year) END) Post_Periods
FROM 
    sales AS s
LEFT JOIN 
    customer AS c ON s.account_no = c.account_no
WHERE 
    c.account_no IN (SELECT DISTINCT account_no
                     FROM #accounts)
GROUP BY 
    s.account_no, c.code, c.code_desc;

Desired output:

CodePudding user response：

You may use your date ranges to join with dataset, and 'Tag' your result like below, this will result in 3 rows, for each group. If you need them in a single row, have PIVOTE over it

;With DateRanges AS (
    SELECT CAST('2020-01-01' AS DATE) StartDate, CAST('2021-04-01' AS DATE) EndDate, 'Pre' Tag UNION
    SELECT '2021-04-01', '2022-03-31', 'During' UNION
    SELECT '2022-04-01', Null, 'Post' 
)
SELECT s.account_no, c.code, c.code_desc, d.Tag, 
SUM(VALUE_USD) AS Total_Spend,
COUNT(DISTINCT CONCAT(s.bk_month, s.bk_year)) RecordCount
FROM sales as s
LEFT JOIN customer as c
INNER JOIN DateRanges D ON s.tran_date BETWEEN D.StartDate AND ISNULL(D.EndDate,s.tran_date)
ON s.account_no = c.account_no
WHERE c.account_no IN (SELECT DISTINCT account_no FROM #accounts)
GROUP BY s.account_no, c.code, c.code_desc;

CodePudding user response：

with [cte_accountActivityPeriods] as (
select [PeriodOrdinal] = 1, [PeriodName] = 'Total Spend Pre', [PeriodStart] = convert(date,'2020-01-01',23) , [PeriodFinish] = convert(date,'2021-03-31',23) union
select [PeriodOrdinal] = 2, [PeriodName] = 'Total Spend During', [PeriodStart] = convert(date,'2021-04-01',23) , [PeriodFinish] = convert(date,'2022-03-31',23) union
select [PeriodOrdinal] = 3, [PeriodName] = 'Total Spend Post', [PeriodStart] = convert(date,'2022-04-01',23) , [PeriodFinish] = convert(date,'9999-12-31',23) 
)
, [cte_allsalesForActivityPeriod]
SELECT s.account_no,  bk_month, bk_year, [PeriodOrdinal], s.tran_date, s.value_usd
FROM sales as s
cross join [cte_accountActivityPeriods] 
  on s.[tran_date] between [cte_ActivityPeriods].[PeriodStart] and [cte_ActivityPeriods].[PeriodFinish]
)
, [cte_uniqueAccounts] as ( /*Unique and qualifying Accounts*/
select distinct account_no from [cte_allsalesForActivityPeriod]
inner join #accounts accs on accs.[account_no] = [cte_allsalesForActivityPeriod].[account_no]
)
, [cte_AllSalesAggregatedByPeriod] as (
select account_no,  [PeriodOrdinal], bk_month, bk_year, [PeriodTotalSpend] = sum([value_usd])
  from [cte_allsalesForActivityPeriod]
  group by s.account_no, [PeriodOrdinal],  bk_month, bk_year
)
, [cte_PeriodAnalysis] as (
select account_no,  [PeriodOrdinal], [ActivePeriods] = count(distinct concat(bk_month, bk_year))
  from [cte_AllSalesAggregatedByPeriod]
  group by s.account_no,  [PeriodOrdinal]
)
, [cte_pivot_clumsily] as (
/* Aggregations already done - so simple pivot */
select [cte_uniqueAccounts].[account_no]
     , [Total_Spend_Pre] = case when [SaleVal].[PeriodOrdinal] in (1) then [SaleVal].[PeriodTotalSpend] else 0 end
     , [Total_Spend_During] = case when [SaleVal].[PeriodOrdinal] in (2) then [SaleVal].[PeriodTotalSpend] else 0 end
     , [Total_Spend_Post] = case when [SaleVal].[PeriodOrdinal] in (3) then [SaleVal].[PeriodTotalSpend] else 0 end
     , [Pre_Periods] = case when [SalePrd].[PeriodOrdinal] in (1) then [SalePrd].[ActivePeriods] else 0 end
     , [Post_Periods] = case when [SalePrd].[PeriodOrdinal] in (3) then [SalePrd].[ActivePeriods] else 0 end
  from [cte_uniqueAccounts] 
  left join [cte_AllSalesAggregatedByPeriod] [SaleVal] on [SaleVal].[account_no] = [cte_uniqueAccounts].[account_no]
  left join [cte_PeriodAnalysis] [SalePrd] on [SalePrd].[account_no] = [cte_uniqueAccounts].[account_no]
)
select c.code, c.code_desc, [cte_pivot_clumsily].*
from [cte_pivot_clumsily] 
LEFT JOIN customer as c
ON [cte_pivot_clumsily].account_no = c.account_no