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.
I think you are attempting to get a list of tables that contain rows referenced in another table. I think you want to encapsulate this into a re-usable object, such as a stored procedure.
I created a simple schema to test your code, as such:
USE TempDB;
CREATE TABLE dbo.T1
(
ID INT NOT NULL PRIMARY KEY CLUSTERED
, SomeData VARCHAR(255)
);
CREATE TABLE dbo.T2
(
ID INT NOT NULL FOREIGN KEY REFERENCES dbo.T1(ID)
, SomeOtherData VARCHAR(255)
);
INSERT INTO dbo.T1 VALUES (1, 'Test');
INSERT INTO dbo.T2 VALUES (1, 'Test2');
The following shows the table T2
as output, which I think is what you are attempting to do.
CREATE PROCEDURE dbo.GetReferencedTables
(
@TableName SYSNAME
, @RowID INT
)
AS
BEGIN
DECLARE @OutputTable TABLE
(
TableName SYSNAME
);
DECLARE @Command nvarchar(max);
SELECT @Command = isnull(@Command + ' union all ', '') + '
SELECT ''' + object_name(parent_object_id) + '''
WHERE EXISTS(SELECT * FROM ' + object_name(parent_object_id)
+ ' WHERE ' + col.name+ ' = ' + cast(@RowId as varchar) + ')'
FROM sys.foreign_key_columns fkc
JOIN sys.columns col ON
fkc.parent_object_id = col.object_id AND fkc.parent_column_id = col.column_id
WHERE OBJECT_NAME(referenced_object_id) = @TableName;
--Totally unnecessary to put the results into a table variable
--unless you want to further manipulate or use the results
INSERT INTO @OutputTable
EXEC sp_executesql @Command;
SELECT * /* NEVER use SELECT * unless you're me.
see http://bit.ly/1nRziYq for reasons.
*/
FROM @OutputTable
ORDER BY TableName;
END
GO
EXEC dbo.GetReferencedTables 'T1',1;
Output:
Best Answer
I can share the conceptual answer to this problem, you will have to fill in correct syntax for query as per your database engine.
This query can give you record occurrences in one single interval of time.
For running on single query on multiple instances, you can use this concept to find occurrences in multiple intervals.