CODEWITHSUNDEEP
sql-server-2008t-sql
Murtaza Mandvi
Murtaza Mandvi

Reputation: 10998

Aggregate columns into yearly/monthly/weekly

I have a Table :

Table A
-------
SubscriberId int
ContentId int
SendDate DateTime

Sample Data
------------
1            190159      2012-11-07 00:00:00.000
1            190160      2012-11-07 00:00:00.000
1            190160      2012-11-07 00:00:00.000
1            190160      2012-11-07 00:00:00.000
1            190160      2012-11-07 00:00:00.000
1            190160      2012-11-07 00:00:00.000
1            190160      2012-11-07 00:00:00.000
1            190160      2012-11-19 00:00:00.000
2            190160      2012-11-19 00:00:00.000
2            190160      2012-10-19 00:00:00.000
2            190160      2012-10-01 00:00:00.000
2            190160      2011-10-01 00:00:00.000
2            190160      2011-10-01 00:00:00.000
2            190160      2011-10-01 00:00:00.000
2            190160      2011-10-19 00:00:00.000
2            190160      2011-10-18 00:00:00.000
2            190160      2011-10-17 00:00:00.000
2            190161      2011-10-20 00:00:00.000
2            190161      2011-10-01 01:01:01.000

The above table is a flat table and is not normalized (and I intend to keep it that way)

The output I need is that for Each SubscriberId I need to count the number of ContentIds for the Senddate YEARLY, MONTHLY and WEEKLY and format them in a particular way:

Subscriber ID    YEARLYValidData
1                190159,1|190160,7
2                190160,9|190161,2

The Above result is a partial result i.e. I have got the result to be working for YEARLY data, however In the second Column I need MONTHLYValiddata (i.e. content that falls in 30 days from GetDate()) and I also need WEEKLYValidData (i.e. content that falls in 7 days from GetDate())

Below query gives me desired results but each of them separately:

DECLARE NumberOfDays int = 365
SELECT  MAIN.SubscriberId,
            STUFF((
                SELECT '|' + 
                (CAST(SUB.ContentId AS nvarchar(10)) + ',' + 
                 CAST(Count(ContentId) as nvarchar(100))) 
                FROM TABLEA SUB
                Where
                SUB.SubscriberId = MAIN.SubscriberId
                GROUP BY ContentId
                FOR XML PATH('')
                ), 1, 1, '' )
            AS [Result]
            FROM  TABLEA MAIN
            WHERE 
            DATEADD(DAY, DATEDIFF(DAY, 0, SendDate), 0) >= 
            DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) - @NumberOfDays

How do I get the below result in a single Select statement:

Subscriber ID    YEARLYValidData       MONTHLYValidData         WEEKLYValidData
1                190159,1|190160,7     190159,1|190160,7        190159,1|190160,7       
2                190160,9|190161,2     190160,9|190161,2        190160,1

I know I could use TEMP Table (or Table Variable) and keep on inputting Yearly / monthly / weekly data but I would like to use something more efficient , if possible?

Upvotes: 0

Views: 357

Answers (1)

Nick.Mc
Nick.Mc

Reputation: 19194

I don't quite understand, but this might get you a little further along the path, even if it is not the final answer:

SELECT  C.Interval,
        MAIN.SubscriberId,
        STUFF((
            SELECT '|' + 
            (CAST(SUB.ContentId AS nvarchar(10)) + ',' + 
             CAST(Count(ContentId) as nvarchar(100))) 
            FROM TABLEA SUB
            Where
            SUB.SubscriberId = MAIN.SubscriberId
            GROUP BY ContentId
            FOR XML PATH('')
            ), 1, 1, '' )
        AS [Result]
        FROM  TABLEA MAIN
        CROSS JOIN
        (    SELECT 356 As Days, 'Year' As Interval
             UNION ALL 
             SELECT 30 Days, 'Month' As Interval
             UNION ALL 
             SELECT 7 Days, 'Week' As Interval
        ) C
        WHERE 
        DATEADD(DAY, DATEDIFF(DAY, 0, SendDate), 0) >= 
        DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) - C.Days

Upvotes: 1

Related Questions