Retention

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[_sqlm_retention]
-- Add the parameters for the stored procedure here
--input
@InStartDate datetime,
@InDay int,

--output
@OutTotal bigint = 0 OUTPUT,
@OutTotalRetention bigint = 0 OUTPUT,
@OutRetention float = 0.0 OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here



declare @qry nvarchar(max) = N''

declare @datetime_s datetime = convert(nchar(10), @InStartDate, 23)
declare @daycnt int = @InDay
declare @datetime_e datetime = dateadd(day, @daycnt, @datetime_s)

if @daycnt > 10
return

declare @year_s nchar(4) = REPLICATE ('0', 4-LEN (DATEPART(year, @datetime_s))) + convert(nvarchar(4), DATEPART(year, @datetime_s))
declare @month_s nchar(2) = REPLICATE ('0', 2-LEN (DATEPART(month, @datetime_s))) + convert(nvarchar(2), DATEPART(month, @datetime_s))

declare @year_e nchar(4) = REPLICATE ('0', 4-LEN (DATEPART(year, @datetime_e))) + convert(nvarchar(4), DATEPART(year, @datetime_e))
declare @month_e nchar(2) = REPLICATE ('0', 2-LEN (DATEPART(month, @datetime_e))) + convert(nvarchar(2), DATEPART(month, @datetime_e))


declare @retention bigint = 0
declare @total_cnt bigint = 0

set @qry = '
select @total_cnt = count(t_.AccountUnique) from
(
select AccountUnique
from log_commander_tbl_' + @year_s + @month_s + ' with(nolock)
where WriteDate >= ''' + convert(nvarchar(30), @datetime_s) + ''' and WriteDate < dateadd(day, 1, ''' + convert(nvarchar(30), @datetime_s) + ''')
group by AccountUnique
) as t_'



EXEC SP_EXECUTESQL @qry, N'@total_cnt bigint OUTPUT', @total_cnt = @total_cnt OUTPUT


if( DATEPART(month, @datetime_s) = DATEPART(month, @datetime_e))
begin
set @qry = '
select @retention = count(rr_.AccountUnique) from
(
select r_.AccountUnique as AccountUnique from
(
select convert(nchar(10), WriteDate, 23) as [Date], AccountUnique
from log_commander_tbl_' + @year_s + @month_s + ' with(nolock)
where WriteDate >= ''' + convert(nvarchar(30), @datetime_s) + ''' and WriteDate < ''' + convert(nvarchar(30), @datetime_e) + '''
group by AccountUnique, convert(nchar(10), WriteDate, 23)
) as r_ group by r_.AccountUnique having count(r_.AccountUnique) = ' + convert(nchar(5), @daycnt) + '
) as rr_'
end
else
begin
set @qry = '
select @retention = count(rr_.AccountUnique) from
(
select r_.AccountUnique as AccountUnique from
(
select convert(nchar(10), WriteDate, 23) as [Date], AccountUnique
from log_commander_tbl_' + @year_s + @month_s + ' with(nolock)
where WriteDate >= ''' + convert(nvarchar(30), @datetime_s) + ''' and WriteDate < ''' + convert(nvarchar(30), @datetime_e) + '''
group by AccountUnique, convert(nchar(10), WriteDate, 23) union all
select convert(nchar(10), WriteDate, 23) as [Date], AccountUnique
from log_commander_tbl_' + @year_e + @month_e + ' with(nolock)
where WriteDate >= ''' + convert(nvarchar(30), @datetime_s) + ''' and WriteDate < ''' + convert(nvarchar(30), @datetime_e) + '''
group by AccountUnique, convert(nchar(10), WriteDate, 23)
) as r_ group by r_.AccountUnique having count(r_.AccountUnique) = ' + convert(nchar(5), @daycnt) + '
) as rr_
'
end


EXEC SP_EXECUTESQL @qry, N'@retention bigint OUTPUT', @retention = @retention OUTPUT

set @OutTotalRetention = @retention
set @OutTotal = @total_cnt
set @OutRetention = convert(float, @retention) * 100.0 / convert(float, @total_cnt)

END