Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Exam_Final
CREATE TABLE Users( id int IDENTITY PRIMARY KEY NOT NULL, Name varchar(100), Email varchar(100), Sub_Date datetime, Unsub_Date datetime ); go INSERT INTO Users (Name,Email,Sub_Date,Unsub_Date) VALUES ('Neerad','nrdnandan@gmail.com','2019-09-01','2018-01-01'),('MehtaJi','mehatchuttad@gmail.com','2019-08-10',NULL), ('MRS','nrdnandan@gmail.com','2019-09-01','2019-09-01'), ('SimanG','simanbewara@gmail.com','2018-05-10','2019-01-05'),('Xing','wangx@gmail.com','2020-01-01',NULL); GO CREATE PROCEDURE MONTHLY_REPORT AS BEGIN DECLARE @STARTDATE DATETIME DECLARE @ENDDATE DATETIME SET @STARTDATE = '01 JANUARY 2019'; SET @ENDDATE = '31 DECEMBER 2019'; WITH CTE_Months (dates) AS ( SELECT @STARTDATE dates UNION ALL SELECT DATEADD(MONTH,1,dates) FROM CTE_Months WHERE DATEADD(MONTH,1,dates) < @ENDDATE ), CTE_A (MONTHS, PEOPLESTARTED) AS ( SELECT MONTH(SUB_DATE),COUNT(*) FROM USERS GROUP BY MONTH(SUB_DATE) ), CTE_B (MONTHS, PEOPLEENDEDTHISMONTH) AS ( SELECT MONTH(Unsub_Date), COUNT(*) FROM USERS GROUP BY MONTH(Unsub_Date) ) SELECT MONTH(M.dates) MONTHS, ((CAST(PEOPLEENDEDTHISMONTH AS float) / CAST(PEOPLESTARTED AS float)) * 100) RETENTION_RATE FROM CTE_A A RIGHT JOIN CTE_Months M ON MONTH(M.dates) = A.MONTHS LEFT JOIN CTE_B B ON MONTH(M.dates) = B.MONTHS END GO EXEC MONTHLY_REPORT;
run
|
edit
|
history
|
help
0
RBT QA
CTE reqursive query
b
Campeonato2DLuizFelipe
String between two indexes
reee
dbms pracs
sql 7
6
Branch