August 30, 2018 at 5:54 am
Hi
hi
I wish to create a table of data that links a CTE and a data customer table
I wish to see how customers that the company has in total, and how many have left
at each of the first of the month (from the CTE)
I have a customer table
ID, ActiveFrom, ActiveTo
1 2017 - 04 -01 2017 - 08 -23
2 2017 - 02 -01 Null
3 2017 - 02 -01 2017 - 09 -23
I have the following cte
Declare @DateFrom datetime, @DateTo Datetime
Set @DateFrom = '2017-04-01'
set @DateTo = '2018-03-31';
WITH CTE AS
(
SELECT @DateFrom AS cte_start_date
UNION ALL
SELECT DATEADD(MONTH, 1, cte_start_date)
FROM CTE
WHERE DATEADD(MONTH, 1, cte_start_date) <= @DateTo
)
I am having no luck
how do I link these together
please help
August 30, 2018 at 6:29 am
You did a good job of providing what you've tried, it's helpful if you put your sample data into a temp table script too. Here's one way, had to handle the NULL values by transforming into some future date (picked '12/31/2078' arbitrarily). Not fantastic, it'd be better if you could replace the NULLs with a date value first, and do away with the ISNULL() function in the WHERE.
So, since you have your CTE which is a table of dates by month, you want to bounce every record there against your data, so you want to CROSS JOIN. In this case, you need the date you're testing from the cte to land within the active date span for that ID. But, it's better to do the check for <= ActiveTo and >= ActiveFrom (See the following for examples: https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common)
Declare @DateFrom datetime, @DateTo Datetime
Set @DateFrom = '2017-04-01'
set @DateTo = '2018-03-31';
IF OBJECT_ID('Tempdb..#temp') IS NULL
BEGIN
CREATE TABLE #temp (ID int,ActiveFrom date,ActiveTo date);
INSERT INTO #temp (ID,ActiveFrom,ActiveTo)
SELECT 1, '4/1/2017','8/23/2017' UNION
SELECT 2, '2/1/2017', NULL UNION
SELECT 3, '2/1/2017', '9/23/2017';
END;
WITH CTE AS
(
SELECT @DateFrom AS cte_start_date
UNION ALL
SELECT DATEADD(MONTH, 1, cte_start_date)
FROM CTE
WHERE DATEADD(MONTH, 1, cte_start_date) <= @DateTo
)
SELECT COUNT(ID),cte_start_date
FROM #temp
CROSS JOIN CTE
WHERE cte_start_date <= ISNULL(ActiveTo,'12/31/2078')
AND cte_start_date >= ActiveFrom
GROUP BY cte_start_date
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
August 30, 2018 at 6:40 am
posting a follow-up to say that if you're going to group by month a lot, you might just want to create a permanent date table, so you don't have to use a CTE and DATEADD() to solve this problem. Plus, you can add things like end of month dates, quarters that the date belongs in to both calendar and fiscal years, etc.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply