none
TSQL- SendLog Query

    Question

  • Hi,

    I have a requirement wherein I need to calculate measures based on the emailaddress and the sentdate. 

    Below is the sample T-SQL :

    CREATE TABLE #SendEmailLog
    ( id INT IDENTITY (1,1),
      Email varchar(128) NOT NULL,
      SentDate datetime NOT NULL)
      
      INSERT INTO #SendEmailLog VALUES ('arc@gmail.com','2013-01-01')
      INSERT INTO #SendEmailLog VALUES ('arc@gmail.com','2013-04-01')
      INSERT INTO #SendEmailLog VALUES ('arc@gmail.com','2013-05-10')
      INSERT INTO #SendEmailLog VALUES ('arc@gmail.com','2013-05-12')
      INSERT INTO #SendEmailLog VALUES ('arc@gmail.com','2013-06-30')
      INSERT INTO #SendEmailLog VALUES ('arc@gmail.com','2013-07-15')
      
      --SELECT * FROM #SendEmailLog
      
      INSERT INTO #SendEmailLog VALUES ('localaps@gmail.com','2012-01-01')
      INSERT INTO #SendEmailLog VALUES ('localaps@gmail.com','2012-04-01')
      INSERT INTO #SendEmailLog VALUES ('localaps@gmail.com','2012-05-10')
      INSERT INTO #SendEmailLog VALUES ('localaps@gmail.com','2013-05-12')
      INSERT INTO #SendEmailLog VALUES ('localaps@gmail.com','2013-06-30')
      INSERT INTO #SendEmailLog VALUES ('localaps@gmail.com','2013-07-15')
      INSERT INTO #SendEmailLog VALUES ('localaps@gmail.com','2013-07-16')

    For Each Unique Email Address I need to know:

    • EmailAddress
    • No Of Emails sent
    • DaysSinceLastEmail
    • DaysSinceFirstEmail
    • NumOfEmailSentInLast30Days
    • NumOfEmailSent0To23Months ( Num Of Emails Sent in Last 23 Months )
    • MinDaysBetweenEmails 
    • MaxDaysBetweenEmails
    • MinDaysBetweenEmails0to11Months ( Past 11 Months )
    • MaxDaysBetweenEmails0to11Months ( Past 11 Months )

    Please guide as to how to get to these measures. 

    Thanks


    EVA05

    Tuesday, September 24, 2013 6:52 PM

Answers

  • Pretty complex:

    SELECT	O.Email,
    		COUNT(*) AS NoOfEmails,
    		DATEDIFF(DAY, MIN(O.SentDate), GETDATE()) AS DaysSinceFirstEmail,
    		DATEDIFF(DAY, MAX(O.SentDate), GETDATE()) AS DaysSinceLastEmail,
    		( 
    			SELECT	COUNT(*) 
    			FROM	#SendEmailLog I 
    			WHERE	I.Email = O.Email
    					AND I.SentDate > DATEADD(DAY, -30, GETDATE())
    		) AS NumOfEmailSentInLast30Days,
    		( 
    			SELECT	COUNT(*) 
    			FROM	#SendEmailLog I 
    			WHERE	I.Email = O.Email
    					AND I.SentDate > DATEADD(MONTH, -11, GETDATE())
    		) AS MinDaysBetweenEmails0to11Months,
    		( 
    			SELECT	COUNT(*) 
    			FROM	#SendEmailLog I 
    			WHERE	I.Email = O.Email
    					AND I.SentDate > DATEADD(MONTH, -23, GETDATE())
    		) AS NumOfEmailSent0To23Months,
    		(
    			SELECT	MIN(SentDateDiff)
    			FROM	(
    						SELECT	DATEDIFF(DAY, LAG(I.SentDate, 1, 0) OVER ( PARTITION BY I.Email ORDER BY I.SentDate) , I.SentDate) AS SentDateDiff			
    						FROM	#SendEmailLog I
    						WHERE	I.Email = O.Email
    					) Q1
    		) MinDaysBetweenEmails,
    		(
    			SELECT	MAX(SentDateDiff)
    			FROM	(
    						SELECT	I.Email, DATEDIFF(DAY, LAG(I.SentDate, 1, GETDATE()) OVER ( PARTITION BY I.Email ORDER BY I.SentDate) , I.SentDate) AS SentDateDiff
    						FROM	#SendEmailLog I
    						WHERE	I.Email = O.Email
    					) Q2
    		) MaxDaysBetweenEmails
    FROM	#SendEmailLog O	
    GROUP BY O.Email;
    
    
    

    • Marked as answer by eva05 Wednesday, September 25, 2013 1:07 PM
    • Unmarked as answer by eva05 Wednesday, September 25, 2013 1:15 PM
    • Marked as answer by eva05 Wednesday, September 25, 2013 1:44 PM
    Tuesday, September 24, 2013 7:33 PM
  • They are correlated sub-queries. Thus the condition WHERE I.Email = O.Email. The I and O are table aliases for the Inner and Outer table. And AND I.SentDate > DATEADD(DAY, -30, GETDATE()) simply test whether the SentDate is not past the given interval. DATEADD() is used as it returns a DATETIME value.
    • Edited by Stefan HoffmannMVP Wednesday, September 25, 2013 2:14 PM
    • Marked as answer by eva05 Wednesday, September 25, 2013 2:31 PM
    Wednesday, September 25, 2013 1:57 PM

All replies

  • THis is little different from a previous question of yours

    previous post, similar statistics

    Tuesday, September 24, 2013 7:10 PM
  • Pretty complex:

    SELECT	O.Email,
    		COUNT(*) AS NoOfEmails,
    		DATEDIFF(DAY, MIN(O.SentDate), GETDATE()) AS DaysSinceFirstEmail,
    		DATEDIFF(DAY, MAX(O.SentDate), GETDATE()) AS DaysSinceLastEmail,
    		( 
    			SELECT	COUNT(*) 
    			FROM	#SendEmailLog I 
    			WHERE	I.Email = O.Email
    					AND I.SentDate > DATEADD(DAY, -30, GETDATE())
    		) AS NumOfEmailSentInLast30Days,
    		( 
    			SELECT	COUNT(*) 
    			FROM	#SendEmailLog I 
    			WHERE	I.Email = O.Email
    					AND I.SentDate > DATEADD(MONTH, -11, GETDATE())
    		) AS MinDaysBetweenEmails0to11Months,
    		( 
    			SELECT	COUNT(*) 
    			FROM	#SendEmailLog I 
    			WHERE	I.Email = O.Email
    					AND I.SentDate > DATEADD(MONTH, -23, GETDATE())
    		) AS NumOfEmailSent0To23Months,
    		(
    			SELECT	MIN(SentDateDiff)
    			FROM	(
    						SELECT	DATEDIFF(DAY, LAG(I.SentDate, 1, 0) OVER ( PARTITION BY I.Email ORDER BY I.SentDate) , I.SentDate) AS SentDateDiff			
    						FROM	#SendEmailLog I
    						WHERE	I.Email = O.Email
    					) Q1
    		) MinDaysBetweenEmails,
    		(
    			SELECT	MAX(SentDateDiff)
    			FROM	(
    						SELECT	I.Email, DATEDIFF(DAY, LAG(I.SentDate, 1, GETDATE()) OVER ( PARTITION BY I.Email ORDER BY I.SentDate) , I.SentDate) AS SentDateDiff
    						FROM	#SendEmailLog I
    						WHERE	I.Email = O.Email
    					) Q2
    		) MaxDaysBetweenEmails
    FROM	#SendEmailLog O	
    GROUP BY O.Email;
    
    
    

    • Marked as answer by eva05 Wednesday, September 25, 2013 1:07 PM
    • Unmarked as answer by eva05 Wednesday, September 25, 2013 1:15 PM
    • Marked as answer by eva05 Wednesday, September 25, 2013 1:44 PM
    Tuesday, September 24, 2013 7:33 PM
  •  

    Thanks Stefan Hoffmann


    EVA05

    Wednesday, September 25, 2013 1:09 PM
  • Just for curiosity: Why have you marked and then unmarked my post? Is it wrong?
    Wednesday, September 25, 2013 1:21 PM
  • Hello Stefan Hoffmann.,

    Thank you for the prompt response. In the above query can you guide as to how to get 

    • MinDaysBetweenEmails0to11Months  ( Minimum number of Days Between Emails Sent from 0 to 11 months) 

    Thanks


    EVA05

    Wednesday, September 25, 2013 1:31 PM
  •  Hello Stefan Hoffmann

    Thank you for the response. Your query is working perfect. 

    I am looking at getting guidance on one of the measures :

    • MinDaysBetweenEmails0to11Months  ( Minimum number of Days Between Emails Sent from 0 to 11 months) 

    Thanks


    EVA05

    Wednesday, September 25, 2013 1:39 PM
  • They are correlated sub-queries. Thus the condition WHERE I.Email = O.Email. The I and O are table aliases for the Inner and Outer table. And AND I.SentDate > DATEADD(DAY, -30, GETDATE()) simply test whether the SentDate is not past the given interval. DATEADD() is used as it returns a DATETIME value.
    • Edited by Stefan HoffmannMVP Wednesday, September 25, 2013 2:14 PM
    • Marked as answer by eva05 Wednesday, September 25, 2013 2:31 PM
    Wednesday, September 25, 2013 1:57 PM
  • Hi  Stefan,

     I am trying to structure the query( as shown below) so that I get output for the below measure 

    • MinDaysBetweenEmails0to11Months  ( Minimum number of Days Between Emails Sent from 0 to 11 months) 

    ( SELECT Min(SentDateDiff) FROM ( SELECT I.Email, DATEDIFF(DAY, LAG(I.SentDate, 1, GETDATE()) OVER ( PARTITION BY I.Email ORDER BY I.SentDate) , I.SentDate) AS SentDateDiff FROM #SendEmailLog I WHERE I.Email = O.Email

    and i.sentdate>DATEADD(MONTH, -11, GETDATE()) 

    and ) Q2 ) MixDaysBetweenEmails0to11months

    I am getting inaccurate results if I use the above tweaked code. Can you guide where is the catch.

    Thanks


    EVA05

    Wednesday, September 25, 2013 8:26 PM
  • The easiest approach would be another sub-query instead of FROM#SendEmailLog I:

    FROM ( SELECT * FROM #SendEmailLog WHERE SentDate >DATEADD(..)) I

    Wednesday, September 25, 2013 8:41 PM