how to send notification when user gets 20 new followers for past 2 hours in sql server?

Question

Hi i am create on store procedure for the UserFollowers. now i want to set the notification for the user followers.and in this notification when user gets 20 new followers for past 2 horus then send this type notification like 'abc, xyz and 18 other users followed you.' right now as par my logic in query just send one user is follow other user then send notification. so how can do that with this query any one know please let me know.

This is my query =>

    DECLARE @FollowerId int,
    DECLARE @UserId int
      INSERT INTO UserFollowers
            (
                FollowerId,
                UserId,
                IsFollowed
            )
        VALUES
            (
                @FollowerId,
                @UserId,
                1
            )

        EXEC AddNotification @UserId, @FollowerId, 1, 'User Follow', null, null, null

        SELECT @ActionUserName  = UserName FROM Users WHERE UserId = @FollowerId

        SELECT 'Followed'

        SELECT REPLACE(ISNULL(NTL.NotificationText,NT.NotificationText), '##UserName##', @ActionUserName) AS NotificationText,
               U.UserId,
               U.UserName                  
        FROM NotificationTexts NT
        LEFT JOIN NotificationTextsByLanguage NTL ON NT.NotificationTextId = NTL.NotificationTextId  
        INNER JOIN Users U ON U.UserId = @UserId
        INNER JOIN UserNotificationSetting UNS ON U.UserId = UNS.UserId
        INNER JOIN NotificationTypes NTYPE ON NT.NotificationTypeId = NTYPE.NotificationTypeId
        WHERE NT.NotificationTextId = 1 AND UNS.NotificationGroupTypeId = NTYPE.NotificationGroupTypeId

my current o/p is =>

abc followe you.

my expected o/p => abc, xyz and 18 other users followed you

any one how can do that with this query please let me know.

Did you tried WAITFOR DELAY inside while loop? – Jun 19 '17 at 06:30 — , Jun 19 '17 at 06:30
no i am not try can you please give me some hint please – coderwill Jun 19 '17 at 06:31 — coderwill, Jun 19 '17 at 06:31

score 0 · Answer 1 · 2017-06-19T06:44:13.860

0

I think you need to run a query after time interval using

WHILE 1=1
BEGIN
WAITFOR DELAY '02:00:00' -- Wait 2 hours 
--Do  check follower count here after time interval then notify
END

Here is how to run stored procedure after particular time interval Stored procedure after time interval

See here you need to add timestamp column inside user follower table check here Get records 10 min before system datetime in SQL

edited Jun 19 '17 at 06:44

answered Jun 19 '17 at 06:32

you mean here this query is waiting for the last 2 hrs? – coderwill Jun 19 '17 at 06:39
This query will trigger after every 2 hour. – Jun 19 '17 at 06:39
Did you use time stamp when user gets follower? – Jun 19 '17 at 06:40
and after 2 hours fire this query then suppose 20 count got then this query time 20 time it's run or one time? and i have add now one time stamp column in the table – coderwill Jun 19 '17 at 06:44
See here you need to add timestamp column inside user follower table so you can fetch records inserted before some interval check here https://stackoverflow.com/questions/4956973/get-records-10-min-before-system-datetime-in-sql – Jun 19 '17 at 06:44
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/147025/discussion-between-taha-sultan-temuri-and-coderwill). – Jun 19 '17 at 06:45

how to send notification when user gets 20 new followers for past 2 hours in sql server?

1 Answers1