Combine multiple calcs using union

I have multiple calculations that use union and i am trying to combine them so i can load them into a new table on the same row.

Below is an example of the calculations, i will have 8 in total. I want to load the results to a new summary table via a stored proc but I am not sure on how to proceed in terms of combining/loading these calcs.

The summary table will contain 8 calc columns, and the name column and a load date. It will only ever contain 1 row as the data is deleted and reloaded throughout the day.

--current day

select Name, count(cast (id as int))  as current_day from (
    select Name,id from [Table1]
    where CONVERT(DATE, dte_Uploaded) = CONVERT(DATE, CURRENT_TIMESTAMP)
    union all
    select Name,id from from [Table2]
    where CONVERT(DATE, dte_Uploaded) = CONVERT(DATE, CURRENT_TIMESTAMP)

) x group by Name



--current week


select Name, count(cast (id as int))  as current week from (
    select Name,id from [Table1]
    where DATEDIFF(ww, dte_Uploaded, GETDATE()) = 0
    union all
    select Name,id from from [Table2]
    where DATEDIFF(ww, dte_Uploaded, GETDATE()) = 0


) x group by  Name


--current month


select Name, count(cast (id as int))   as current_month from (
    select Name,id from [Table1]
    where DATEDIFF(m, dte_Uploaded, GETDATE()) = 0
    union all
    select Name,id from from [Table2]
    where DATEDIFF(m, dte_Uploaded, GETDATE()) = 0


) x group by  Name

Solution

You might have better performance and improved readability if you calculate proper date ranges in advance and use conditional aggregation to calculate your counts.

When calculating date ranges, it is common to calculate an inclusive start date and an exclusive end date (00:00:00 at the start of the day afterwards). This nicely handles date/time fields without having to truncate potentially indexed column values.

Conditional aggregation combines an aggregate function (in this case COUNT()) with a CASE expression that either includes or excludes each value from the aggregation. For COUNT() the THEN 1 rows are counted, while the implicit ELSE NULL rows are not.

Try something like:

-- Day, week, and month ranges. (End dates are exclusive.)
DECLARE @today      DATE = CONVERT(DATE, GETDATE())
DECLARE @day_from   DATE = @today
DECLARE @day_to     DATE = DATEADD(day, 1, @day_from)
DECLARE @week_from  DATE = DATETRUNC(week, @today)
DECLARE @week_to    DATE = DATEADD(week, 1, @week_from)
DECLARE @month_from DATE = DATETRUNC(month, @today)
DECLARE @month_to   DATE = DATEADD(month, 1, @month_from)

-- Overall range (to limit initial data retrieval)
DECLARE @range_from DATE = LEAST(@week_from, @month_from)
DECLARE @range_to   DATE = GREATEST(@week_to, @month_to)

SELECT
    Name,
    COUNT(CASE WHEN dt >= @day_from   AND dt < @day_to   THEN 1 END) AS current_day,
    COUNT(CASE WHEN dt >= @week_from  AND dt < @week_to  THEN 1 END) AS current_week,
    COUNT(CASE WHEN dt >= @month_from AND dt < @month_to THEN 1 END) AS current_month
FROM (
    SELECT Name, dte_Uploaded AS dt
    FROM Table1
    WHERE dte_Uploaded >= @range_from AND dte_Uploaded < @range_to
    UNION ALL
    SELECT Name, dte_Uploaded AS dt
    FROM Table2
    WHERE dte_Uploaded >= @range_from AND dte_Uploaded < @range_to
) X
GROUP BY Name

If dte_Uploaded is indexed, the above will efficiently retrieve only those rows needed for the current calculation, rather than a less-efficient table scan.

If you are using older versions of SQL server that do not support the DATETRUNC(), LEAST(), and GREATEST() functions, the following alternate date calculations can be used:

-- Day, week, and month ranges. (End dates are exclusive.)
DECLARE @today      DATE = CONVERT(DATE, GETDATE())
DECLARE @day_from   DATE = @today
DECLARE @day_to     DATE = DATEADD(day, 1, @today)
DECLARE @week_from  DATE = DATEADD(day, 1 - DATEPART(weekday, @today), @today)
DECLARE @week_to    DATE = DATEADD(week, 1, @week_from)
DECLARE @month_from DATE = DATEADD(day, 1 - DAY(@today), @today)
DECLARE @month_to   DATE = DATEADD(month, 1, @month_from)

-- Overall range (to limit initial data retrieval)
DECLARE @range_from DATE = CASE WHEN @week_from < @month_from THEN @week_from ELSE @month_from END
DECLARE @range_to   DATE = CASE WHEN @week_to > @month_to THEN @week_to ELSE @month_to END

Depending how your site defines the start of a week, the week ranges might need to be tweaked.