October 13, 2016 at 9:48 am
Hi Folks
I'm trying to create a single table were as well as one consistent column, there are three columns of summarised date based data - 1 summarising values created 'this_week', one for 'past_4_weeks', and one 'cumulative' since beginning of the year.
Unfortunately the current query is taking nearly 2 minutes, doing an enormous case statement for each row, comparing date data against system date to determine whether that row should count or not, for each select column.
I have the gut feeling that doing this as three separate queries with specific where clauses limiting the dates returned on each, and then a cross-apply (or something similar?) would be a much faster approach (as well as much neater sql). Of course I'm posting because I don't understand this enough to work out a three table cross apply (or even if such is possible) - but there must be a more efficient way of doing this, any ideas guys?
I've not included the actual query because it is a large complex entity just dealing with the table relationships, irrespective of the date based aggregates - so I'm hoping somebody can give me an example with a just Table 1, Table 2, Table 3 and I'll try to work out how that would transfer to my table structure - if that is acceptable? (If not, I could try and cut it down to bare minimum and hope I could get it readable yet still close to the real query?)
October 14, 2016 at 11:43 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply