合同开始时的流失率&结束日期 [英] Churn Rate From Contract Start & End Dates

问题描述

我正在尝试计算客户的客户流失率，我所要做的就是合约开始&结束日期。

I am trying to calculate the churn rate of customer and all I have to play with is contract start & end dates.

当日期年份为9999时，这只意味着他们仍然是客户，我想只考虑拥有超过10年任期的帐户3个月（与公司一起工作3个月或更长时间）。

When the date year is 9999 it just means they are still a customer and I would like to consider account only who have a tenure of more than 3 months (as in have been with the company for 3 or more months).

CREATE TABLE #temp
(
 ACCOUNT varchar(20)NOT NULL
,CONTRACT_START_DATE DATETIME NOT NULL
,CONTRACT_END_DATE DATETIME NOT NULL
)
;


INSERT INTO #temp
VALUES('64074558792','20160729','20170805');
INSERT INTO #temp
VALUES('54654654664','20160810','20170110');
INSERT INTO #temp
VALUES('21454654764','20160112','20160812');
INSERT INTO #temp
VALUES('21654765134','20160101','20161231');
INSERT INTO #temp
VALUES('13214868794','20160811','99991231');
INSERT INTO #temp
VALUES ('88321546894','20160427','20160627');

SELECT *,
CASE WHEN CONTRACT_END_DATE <> '99991231' THEN DATEDIFF(DAY, CONTRACT_START_DATE,CONTRACT_END_DATE) ELSE null END AS TENURE
FROM #temp

推荐答案

你好DANII_PINK，

Hi DANII_PINK,

这是你想要的吗？

CREATE TABLE #temp
(
 ACCOUNT varchar(20)NOT NULL
,CONTRACT_START_DATE DATETIME NOT NULL
,CONTRACT_END_DATE DATETIME NOT NULL
)
;

INSERT INTO #temp
VALUES('64074558792','20160729','20170805');
INSERT INTO #temp
VALUES('54654654664','20160810','20170110');
INSERT INTO #temp
VALUES('21454654764','20160112','20160812');
INSERT INTO #temp
VALUES('21654765134','20160101','20161231');
INSERT INTO #temp
VALUES('13214868794','20160811','99991231');
INSERT INTO #temp
VALUES ('88321546894','20160427','20160627');

--Query
;WITH CTE AS
(
SELECT *,
--CASE WHEN CONTRACT_END_DATE <> '99991231' THEN DATEDIFF(DAY, CONTRACT_START_DATE,CONTRACT_END_DATE) ELSE null END AS TENURE
CASE WHEN CONTRACT_END_DATE <> '99991231' THEN DATEDIFF(MONTH, CONTRACT_START_DATE,CONTRACT_END_DATE) ELSE null END AS TENURE
FROM #temp
)
SELECT 
ACCOUNT,CONTRACT_START_DATE,CONTRACT_END_DATE
FROM CTE
WHERE TENURE>=3 OR TENURE IS NULL

最好的问候，

将

这篇关于合同开始时的流失率&结束日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！