Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Blog
Exam_3_Final
--Sql Server 2014 Express Edition --Batches are separated by 'go' CREATE TABLE Users( id INT NOT NULL IDENTITY PRIMARY KEY , Name varchar(100), Email varchar(100), Sub_Date datetime, Unsub_Date datetime); GO INSERT INTO Users(Name, Email, Sub_Date, Unsub_Date) VALUES ('NANDAN','NANDAN@CHANDAN.COM','2019-09-01','2018-10-26'), ('PHALLE','NANDAN@CHANDAN.COM','2019-08-01', NULL), ('NEERAD','4CHAWANIGHODEPE@NANDANMERELODEPE','2019-11-01','2019-12-26'), ('HAGAL','MEHAT_HAGAL@GMAIL.COM','2019-10-11','2019-11-11') GO SELECT * FROM Users; GO CREATE PROCEDURE report AS BEGIN SELECT MONTH(Sub_Date) AS MONTH, COUNT(*) SUBSCRIPTIONS, COUNT(*) OVER () AS TOTAL_USERS INTO #STARTUSER FROM Users GROUP BY MONTH(Sub_Date) SELECT MONTH(Unsub_Date) AS MONTH, COUNT(*) UNSUBSCRIPTION INTO #ENDUSER FROM Users GROUP BY MONTH(Unsub_Date) SELECT U.MONTH MONTH, U.SUBSCRIPTIONS STARTING_SUBSCRIPTION, P.UNSUBSCRIPTION ENDING_SUBSCRIPTION, (U.TOTAL_USERS - ISNULL(( P.UNSUBSCRIPTION), 0 )) AS RETENTION FROM #STARTUSER U FULL JOIN #ENDUSER P ON U.MONTH = P.MONTH where U.SUBSCRIPTIONS is not NULL ORDER BY U.MONTH END GO EXEC report;
run
|
edit
|
history
|
help
0
Please
log in
to post a comment.
index fragmentation and how to deal with it
Suikwan tests
DGDFGDFG
food_plan
hhhhhhhhhhh
New_1
bc150200055
sql-server-storing-wildcards-in-data
FirstTable
Mc170404738
Please log in to post a comment.