For SQL Server 2005+ you can generate those 12 records very easily with a loop ar a recursive CTE. Here is an example of a recursive CTE:
DECLARE @Date DATETIME
SELECT @Date = '20120308 11:00:00'
;WITH Dates AS
(
SELECT DATEPART(HOUR,DATEADD(HOUR,-1,@Date)) [Hour],
DATEADD(HOUR,-1,@Date) [Date], 1 Num
UNION ALL
SELECT DATEPART(HOUR,DATEADD(HOUR,-1,[Date])),
DATEADD(HOUR,-1,[Date]), Num+1
FROM Dates
WHERE Num <= 11
)
SELECT [Hour], [Date]
FROM Dates
Then you just nedd to join it with your events table.
I think there's some misunderstanding about what you're attempting to do here.
Since your current design is to return all 24 rows from the base table, presumably all the supplementary fields are returned as well (to display in a grid, or something).
In order to fully aggregate the Value
column, all the supplementary columns cannot be included in the SELECT
list. Alternatively, if those columns are included in the GROUP BY
clause, the view would represent only a partial aggregation, as there would be one row for each unique combination of the columns in the GROUP BY
column list.
The only way I see something like this being useful is if the supplementary columns aren't included in the view, and there is some other process that requires only the daily aggregated values, without the base row data. Such a view could be defined like this:
CREATE TABLE [dbo].[BaseTbl]
(
ColRowID bigint NOT NULL,
AggregateID int NOT NULL,
Epoch int NOT NULL,
CustomerID int NOT NULL,
TypeID tinyint NOT NULL,
ErrorID smallint NOT NULL,
Value int NOT NULL,
PRIMARY KEY CLUSTERED(Epoch, CustomerId)
);
GO
CREATE VIEW [dbo].[ixvw_AggTbl]
WITH SCHEMABINDING
AS
SELECT
t.Epoch / 86400 AS EpochDay,
CustomerID,
TypeID,
SUM(t.Value) AS TotalValue,
COUNT_BIG(*) AS __RowCount
FROM [dbo].[BaseTbl] t
GROUP BY
t.Epoch / 86400,
CustomerID,
TypeID;
GO
CREATE UNIQUE CLUSTERED INDEX IX_ixvw_AggTbl
ON [dbo].[ixvw_AggTbl](EpochDay, CustomerID, TypeID);
Unfortunately, you can't go farther and convert the EpochDay
column to an actual date within the indexed view because DATEADD
is non-deterministic (see Aaron's comment below for why), so you'd have to convert it in the actual SELECT
query against the view. But that's not too difficult.
In any event, as I said before, I'm not sure how useful this would be for your specific application.
Best Answer
Given the data:
One approach is:
This is shown in the code below:
Output:
db<>fiddle demo
This produces a couple of extra periods compared with the output shown in the question, but it nevertheless looks correct to me.
If you prefer the (somewhat slow)
FORMAT
function, the following is equivalent: