Post reply

Multiple date periods aggregated in one table or query

  • October 14, 2016 at 11:43 am

    #1906645

    Typically a single query works best in that case, since the ytd query will by definition include the other data. Not sure what your definition of "week" is, but here's the general idea:

    SELECT

    SUM(CASE WHEN column_datetime >= DATEADD(DAY, -6, today_at_midnight) THEN value ELSE 0 END) AS this_week,

    SUM(CASE WHEN column_datetime >= DATEADD(DAY, -27, today_at_midnight) THEN value ELSE 0 END) AS past_4_weeks,

    SUM(value) AS cumulative

    FROM ...

    CROSS APPLY (

    SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AS today_at_midnight

    ) AS assign_alias_names

    WHERE column_datetime >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AND

    column_datetime <= GETDATE()

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing post 1 (of 2 total)

You must be logged in to reply to this topic. Login to reply