11cZ4uVD

1DROP TABLE IF EXISTS [dbo].[UserActivity]
2IF OBJECT_ID('[dbo].[UserActivity]', 'U') IS NULL
3BEGIN
4    CREATE TABLE [dbo].[UserActivity] (
5        [UserId] [int] NOT NULL
6        ,[UserActivityId] [bigint] IDENTITY(1,1) NOT NULL
7        ,[ActivityTypeId] [tinyint] NOT NULL
8        ,[ActivityDate] [datetime2](0) NOT NULL CONSTRAINT [DF_UserActivity_ActivityDate] DEFAULT GETDATE()
9        ,CONSTRAINT [PK_UserActivity] PRIMARY KEY CLUSTERED ([UserActivityId] ASC)
10        ,INDEX [IX_UserActivity_UserId] NONCLUSTERED ([UserId] ASC)
11        ,INDEX [IX_UserActivity_ActivityTypeId] NONCLUSTERED ([ActivityTypeId] ASC)
12        ,INDEX [IX_UserActivity_ActivityDate] NONCLUSTERED ([ActivityDate] ASC)
13    )
14END;
15GO
16	
17DECLARE @UserId int = (SELECT ISNULL((SELECT TOP (1) [UserId] + 1 FROM [dbo].[UserActivity] ORDER BY [UserId] DESC), 1))
18;WITH [UserActivitySeed] AS (
19    SELECT
20        CONVERT(datetime2(0), '01/01/2018') AS 'ActivityDate'
21    UNION ALL
22    SELECT
23        DATEADD(minute, 4, [ActivityDate])
24    FROM
25        [UserActivitySeed]
26    WHERE
27        [ActivityDate] < '2018-04-01')
28INSERT INTO [dbo].[UserActivity] ([UserId], [ActivityTypeId], [ActivityDate])
29SELECT
30    @UserId
31    ,ABS(CHECKSUM(NEWID()) % 9) + 1
32    ,[ActivityDate]
33FROM
34    [UserActivitySeed] OPTION (MAXRECURSION 32767);
35
36GO 5
37
38ALTER INDEX ALL ON [dbo].[UserActivity] REBUILD;
39	
40DECLARE @UserId int = 1
41DECLARE @ForTypeId int = 3
42DECLARE @DurationInterval varchar(6) = 'hour'
43DECLARE @DurationIncrement int = 1
44
45SELECT
46    COUNT(UA.[UserActivityId]) AS 'ActivityTypeCount'
47FROM
48    [dbo].[UserActivity] UA
49WHERE
50    -- Exclude the @UserId and @ForTypeId predicates.
51    -- UA.[UserId] = @UserId
52    -- AND UA.[ActivityTypeId] = @ForTypeId
53    -- AND 
54    CASE
55        WHEN @DurationInterval IN ('year', 'yy', 'yyyy') THEN DATEDIFF(SECOND, UA.[ActivityDate], GETDATE()) / 3600.0 / 24.0 / 365.25
56        WHEN @DurationInterval IN ('month', 'mm', 'm') THEN DATEDIFF(SECOND, UA.[ActivityDate], GETDATE()) / 3600.0 / 24.0 / 365.25 * 12
57        WHEN @DurationInterval IN ('day', 'dd', 'd') THEN DATEDIFF(SECOND, UA.[ActivityDate], GETDATE()) / 3600.0 / 24.0
58        WHEN @DurationInterval IN ('hour', 'hh') THEN DATEDIFF(SECOND, UA.[ActivityDate], GETDATE()) / 3600.0
59        WHEN @DurationInterval IN ('minute', 'mi', 'n') THEN DATEDIFF(SECOND, UA.[ActivityDate], GETDATE()) / 60.0
60        WHEN @DurationInterval IN ('second', 'ss', 's') THEN DATEDIFF(SECOND, UA.[ActivityDate], GETDATE())
61    END < @DurationIncrement
62	
63DECLARE @UserId int = 1
64DECLARE @ForTypeId int = 3
65DECLARE @DurationInterval varchar(6) = 'hour'
66DECLARE @DurationIncrement int = 1
67
68SELECT
69    COUNT(UA.[UserActivityId]) AS 'ActivityTypeCount'
70FROM
71    [dbo].[UserActivity] UA
72WHERE
73    -- Exclude the @UserId and @ForTypeId predicates.
74    -- UA.[UserId] = @UserId
75    -- AND UA.[ActivityTypeId] = @ForTypeId
76    -- AND 
77    (
78        (@DurationInterval IN ('year', 'yy', 'yyyy') AND UA.[ActivityDate] > CONVERT(datetime2(0), DATEADD(YEAR, -@DurationIncrement, GETDATE())))
79        OR
80        (@DurationInterval IN ('month', 'mm', 'm') AND UA.[ActivityDate] > CONVERT(datetime2(0), DATEADD(MONTH, -@DurationIncrement, GETDATE())))
81        OR
82        (@DurationInterval IN ('day', 'dd', 'd') AND UA.[ActivityDate] > CONVERT(datetime2(0), DATEADD(DAY, -@DurationIncrement, GETDATE())))
83        OR
84        (@DurationInterval IN ('hour', 'hh') AND UA.[ActivityDate] > CONVERT(datetime2(0), DATEADD(HOUR, -@DurationIncrement, GETDATE())))
85        OR
86        (@DurationInterval IN ('minute', 'mi', 'n') AND UA.[ActivityDate] > CONVERT(datetime2(0), DATEADD(MINUTE, -@DurationIncrement, GETDATE())))
87        OR
88        (@DurationInterval IN ('second', 'ss', 's') AND UA.[ActivityDate] > CONVERT(datetime2(0), DATEADD(SECOND, -@DurationIncrement, GETDATE())))
89        )