CODEWITHSUNDEEP
sql-servergroup-byaggregate
Nick Knauer
Nick Knauer

Reputation: 4243

Aggregating in SQL

I have a table that looks like this: 

Conversion_Date     User_Name     Last_Date_Touch     Touch_Count
      7/15/2017             A           6/17/2017               1
      7/16/2017             B           6/24/2017               2
      7/19/2017             A           6/20/2017               1
      7/19/2017             C           6/29/2017               1

I want to get the sum of Touch_Count from the last 30 days for each Conversion_Date by User_Name in SQL.

The only thing I could think of at first was to figure out the time window for each conversion date going back 30 days and then I'm not sure how to aggregate this to get the final result.

First it was adding the 30 day window below: 

Conversion_Date     User_Name     Last_Date_Touch     Touch_Count   30_Days_Ago
      7/15/2017             A           6/17/2017               1     6/15/2017      
      7/16/2017             B           6/24/2017               2     6/16/2017
      7/19/2017             A           6/20/2017               1     6/19/2017
      7/19/2017             C           6/29/2017               1     6/19/2017

Then I need to aggregate it so that it looks like this: 

Conversion_Date     User_Name     Last_Date_Touch     Touch_Count     SUM(Last30daysbyconversiondate)
      7/15/2017             A           6/17/2017               1      5
      7/16/2017             B           6/24/2017               2      5
      7/19/2017             A           6/20/2017               1      4
      7/19/2017             C           6/29/2017               1      4

The last two were 4 because the last_date_touch value of 6/17/2017 was not in the 30 day window for 7/19/2017, so that touch_count was excluded making the total count 4 in this case for the last two rows.

Any help would be great if you know how to do this in SQL.

Thanks!

Upvotes: 2

Views: 80

Answers (1)

S3S
S3S

Reputation: 25152

This should be obtainable with a self join

select
    a.User_Name
    ,a.Conversion_Date
    ,sum(b.Touch_Count) as SumOver30Days
from
    SomeTable a
    left join
        SomeTable b on
        b.User_Name = a.User_name
        --and b.Conversion_Date <= a.Conversion_Date
        --and b.Conversion_Date >= dateadd(day,-30,a.Conversion_Date)
        and cast(b.Conversion_Date as date) <= cast(a.Conversion_Date as date) 
        and cast(b.Conversion_Date as date) >= cast(dateadd(day,-30,a.Conversion_Date) as date)
group by a.User_Name, a.Conversion_Date

Upvotes: 1

Related Questions