Datediff SQL-server query for statistical data with count & group by

Question

So what I'm trying to attain is to count how many Users trigger EventCode 90 relative to when they last recieved a Notification.

Source tables are the following:

ServiceOne

UserNr         RegisteredUntil            NotificationMonth
532091985      2016-05-15 00:00:00.000    5
950628185      2016-03-15 00:00:00.000    3
561007126      2016-09-15 00:00:00.000    9

Notifications

UserNr         NotificationNr     NotificationDate            Service
532091985      134567             2013-04-16 00:00:00.000     1
532091985      153468             2014-04-15 00:00:00.000     1
950628185      235481             2014-02-17 00:00:00.000     1
561007126      354812             2012-08-15 00:00:00.000     1

EventLog

Time                        EventCode      UserNr
2012-12-19 00:00:00.000     90             561007126
2014-05-02 00:00:00.000     90             120456873
2009-08-24 00:00:00.000     90             935187423

The table I want is something like this:

CancMonth CancAmount
0         49091
1         53564
2         14308

What I have so far is

Select Max(datediff(month, I.NotificationDate, E.Time)  ) as CancMonth
,Count(datediff(month, I.NotificationDate, E.Time)  ) as CancAmount

From ServiceOne P, Eventlog E, Notifications N
Where P.UserNr=E.UserNr
AND P.UserNr=N.UserNr
AND E.EventCode = 90 --EventCode 90 is both flagging for deregistration and manual deregistration
AND N.Service=1
AND P.Status In (0,4) -- 0 is not registered and 4 is flagged for deregistration
AND datediff(month, N.NotificationDate, E.Time ) < 13 --Notifications are sent once a year
AND N.NotificationDate < E.Time

Group By datediff(month, N.NotificationDate, E.Time )
Order By CancMonth

I did a count on how many total records this gave and it returns about 35 000 more than I have passive and flagged users in ServiceOne.

Help is much appreciated since this has given me a massive headache the last couple of days.

EDIT: I added my source-tables and all possibly usable columns with some random sample-data

I did post an answer suggesting to use a join instead, I removed it as it seemed obvious. Have you tried joins? Do you have time to create a sqlfiddle so we can see the table structure and sample data — n34_panda, May 23 '14 at 09:19
I did see your answer and added some sample data to my question. With the left joins are, unfortunately, out of the question. The one you suggested has been working for 23 minutes now and is still not done. As to an sqlfiddle, I'm not entirely sure what that even is — cg_and, May 23 '14 at 09:47
Do you have any indexes on these tables? Your existing code uses inner joins but you might need to use outer joins if that what your logic requires. — Nick.Mc, May 23 '14 at 09:47
I do have indexes on all of the tables. Sorry for being as inexperienced as I am but what separates outer joins from inner ones? Regardless, Jithin below gave a wonderful answer that solved my problem. — cg_and, May 23 '14 at 11:29
joins: http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-join — n34_panda, May 23 '14 at 11:48

score 0 · Accepted Answer · answered May 23 '14 at 10:46

0

Is this What you are looking for?

--I assue that Latest NotificationDate has Largest NotificationNr

SELECT      MAX(DATEDIFF(MONTH, I.NotificationDate, E.Time)) AS CancMonth,
            COUNT(DATEDIFF(MONTH, I.NotificationDate, E.Time)) AS CancAmount
FROM        ServiceOne P 
JOIN        Eventlog E ON P.UserNr  =E.UserNr 
JOIN        (
            SELECT  N.*
            FROM    Notifications N
            JOIN    (
            SELECT  UserNr,
                    MAX(NotificationDate) NotificationDate,
                    MAX(NotificationNr) NotificationNr        
            FROM    Notifications) LU 
    ON      N.UserNr = LU.UserNr 
    AND     N.NotificationDate = LU.NotificationDate 
    AND     N.NotificationNr = LU.NotificationNr
            ) N ON P.UserNr = N.UserNr 
WHERE       E.EventCode = 90 
AND         N.Service=1
AND         P.Status In (0,4)
AND         DATEDIFF(MONTH, N.NotificationDate, E.Time ) < 13
AND         N.NotificationDate < E.Time
GROUP BY    DATEDIFF(MONTH, N.NotificationDate, E.Time )
ORDER BY    CancMonth

answered May 23 '14 at 10:46

Jithin Shaji

5,893
5
25
47

This was exactly what I was looking for, thank you for your help! On a small sidenote, how come this query was as fast as it was? It is huge with nested joins where it pulls together tables with hundreds of thousands of records. Yet it completed in 5 seconds. Intriguing. – cg_and May 23 '14 at 11:24
If NotificationDate and NotificationNr increments in parallel, you just need to take MAX(NotificationNr) alone.. – Jithin Shaji May 23 '14 at 11:27
I did notice that and took the liberty of removing NotificationNr altogether from the query since all I really need from Notifications are Date and UserNr to pair – cg_and May 23 '14 at 11:31
Just one thing I forgot to mention about your code as well. For it to work you need to add Group by CustomerNr in the LU Select subquery – cg_and May 23 '14 at 12:24

Datediff SQL-server query for statistical data with count & group by

1 Answers1