How to create a stored procedure to combine multiple results
Here is the structure of the table:
TABLE TestTable
(
[Id] [uniqueidentifier] NOT NULL,
[Quality] [tinyint] NULL,
[UtcTimeStamp] [datetime2](7) NOT NULL,
[Value] [varbinary](max) NULL
)
I want to achieve: for a specified time range ( from
- to
) for UtcTimeStamp
, first I will query all data located in the time range. Then keep asking for data after the time range to
following this rule:
- R0:
Quality
has three meanings: 1, 2, 3 - R1: If the first data
Quality
afterto
is 3, just compare this result with the results above the range. - R2: If the first data
Quality
afterto
is 2, keep querying the next row (after thatUtcTimeStamp
), whichQuality
is 3. Finally, combine these two query results with the results above the range. - R3: If the first data
Quality
afterto
is 1, keep querying the next lines (after thatUtcTimeStamp
), whichQuality
are 2 and 3. Finally, combine these three query results with the above range of results.
All of the above job contains one stored procedure and the best performance possible.
I am not very good at stored procedure syntax and tried to store some intermediate variables and use the syntax IF
ELSE
to combine all the results, just failed to get a sentence.
UPDATED
Here is demo content:
For R1:
Database like this
Id(fake) UtcTimeStamp(fake) Quality Value 1s-.. 1 1 0x... 1s-.. 2 2 0x... 1s-.. 3 2 0x... 1s-.. 4 3 0x... 1s-.. 5 3 0x... 1s-.. 6 2 0x...
If I hope to ask for a time range of 1 to 4, then the results should be:
Id(fake) UtcTimeStamp(fake) Quality Value 1s-.. 1 1 0x... 1s-.. 2 2 0x... 1s-.. 3 2 0x... 1s-.. 4 3 0x... 1s-.. 5 3 0x...
For R2:
Database like this
Id(fake) UtcTimeStamp(fake) Quality Value 1s-.. 1 1 0x... 1s-.. 2 2 0x... 1s-.. 3 2 0x... 1s-.. 4 3 0x... 1s-.. 5 2 0x... 1s-.. 6 2 0x... 1s-.. 7 1 0x... 1s-.. 8 3 0x...
If I hope to ask for a time range of 1 to 4, then the results should be:
Id(fake) UtcTimeStamp(fake) Quality Value 1s-.. 1 1 0x... 1s-.. 2 2 0x... 1s-.. 3 2 0x... 1s-.. 4 3 0x... 1s-.. 5 2 0x... 1s-.. 8 3 0x...
For R3:
Database like this
Id(fake) UtcTimeStamp(fake) Quality Value 1s-.. 1 1 0x... 1s-.. 2 2 0x... 1s-.. 3 2 0x... 1s-.. 4 3 0x... 1s-.. 5 1 0x... 1s-.. 6 2 0x... 1s-.. 7 1 0x... 1s-.. 8 3 0x...
If I hope to ask for a time range of 1 to 4, then the results should be:
Id(fake) UtcTimeStamp(fake) Quality Value 1s-.. 1 1 0x... 1s-.. 2 2 0x... 1s-.. 3 2 0x... 1s-.. 4 3 0x... 1s-.. 5 1 0x... 1s-.. 6 2 0x... 1s-.. 8 3 0x...
The procedure should look something like this:
CREATE PROCEDURE [ProcedureName] @Id as uniqueidentifier, @StartTime as datetime2, @EndTime as datetime2 AS ...
source to share
Ok, here is my solution:
CREATE PROCEDURE [ProcedureName] @Id as uniqueidentifier, @StartTime as datetime2, @EndTime as datetime2
AS BEGIN
DECLARE @NextRow TABLE
(
[Id] [uniqueidentifier] NOT NULL,
[Quality] [tinyint] NULL,
[UtcTimeStamp] [datetime2](7) NOT NULL,
[Value] [varbinary](max) NULL
)
DECLARE @BoundRows TABLE
(
[Id] [uniqueidentifier] NOT NULL,
[Quality] [tinyint] NULL,
[UtcTimeStamp] [datetime2](7) NOT NULL,
[Value] [varbinary](max) NULL
)
INSERT INTO @NextRow SELECT top 1 * from [JerryTest].[dbo].[TestTable]
where Id = @Id
AND UtcTimeStamp > @EndTime
IF((SELECT TOP 1 Quality FROM @NextRow) = 3)
begin
INSERT INTO @BoundRows SELECT * FROM @NextRow
end
ELSE IF((SELECT TOP 1 Quality FROM @NextRow) = 2)
begin
INSERT INTO @BoundRows SELECT * FROM @NextRow
INSERT INTo @BoundRows SELECT TOP 1 * FROM [JerryTest].[dbo].[TestTable]
WHERE Id = @Id
AND UtcTimeStamp > @EndTime
AND Quality = 3
end
ELSE IF((SELECT TOP 1 Quality FROM @NextRow) = 1)
begin
INSERT INTO @BoundRows SELECT * FROM @NextRow
INSERT INTO @BoundRows SELECT TOP 1 * FROM [JerryTest].[dbo].[TestTable]
WHERE Id = @Id
AND UtcTimeStamp > @EndTime
AND Quality = 2
INSERT INTO @BoundRows SELECT TOP 1 * FROM [JerryTest].[dbo].[TestTable]
WHERE Id = @Id
AND UtcTimeStamp > @EndTime
AND Quality = 3
end
(SELECT * FROM [JerryTest].[dbo].[TestTable]
WHERE Id = @Id
AND UtcTimeStamp >= @StartTime
AND UtcTimeStamp <= @EndTime)
UNION ALL
SELECT * FROM @BoundRows
END
Hope this helps you.
source to share
You can try the following. I will try to generate demo data first:
-- Create demo data
CREATE TABLE dbo.temp
(
[Id] [uniqueidentifier] NOT NULL,
[Quality] [tinyint] NULL,
[UtcTimeStamp] [datetime2](7) NOT NULL,
[Value] [varbinary](max) NULL
)
INSERT INTO dbo.temp(id, quality,UtcTimeStamp, value)
SELECT NEWID() as id, NTILE(3) OVER(ORDER BY object_id) as quality,
DATEADD(day,-NTILE(3) OVER(ORDER BY object_id),GETUTCDATE()) as UtcTimeStamp,
HASHBYTES(N'SHA1',CONVERT(nvarchar(36),NEWID())) as value
FROM sys.all_objects
You can try this procedure to get everything:
-- Doing the stuff inside the proc
CREATE PROCEDURE dbo.yourProcedure
@from datetime2, @to datetime2
AS BEGIN
;WITH cte AS(
-- Prepare data base for the second part of the query
SELECT t.id, t.Quality, t.UtcTimeStamp, t.Value, ROW_NUMBER() OVER(order by t.UtcTimeStamp) as rn
FROM dbo.temp as t
WHERE t.UtcTimeStamp > @to
)
-- Get all data based inside the range of @from and @to
SELECT t.id, t.Quality, t.UtcTimeStamp, t.Value
FROM dbo.temp as t
WHERE t.UtcTimeStamp BETWEEN @from AND @to
UNION ALL -- You can use union all, as you the following records won't already be in the above result set
-- Add all rows which are behind the @to date (prepared in the cte)
SELECT t.id, t.Quality, t.UtcTimeStamp, t.Value
FROM cte as t
WHERE (t.Quality = 3 AND t.rn = 1) -- The first row, if it quality 3
-- All rows until quality 3, if the first row is quality 2
OR ((t.quality = 2 and t.rn = 1) AND (t.rn <= (SELECT TOP 1 rn FROM cte WHERE quality = 3 ORDER By rn)))
-- All rows until quality 2 or 3, if the first row is quality 1
OR ((t.quality = 1 and t.rn = 1) AND (t.rn <= (SELECT TOP 1 rn FROM cte WHERE quality IN(2,3) ORDER By rn)))
END
GO
Then call the result:
DECLARE @from datetime2 = DATEADD(day,-3,GETUTCDATE()), @to datetime2 = DATEADD(day,-2,GETUTCDATE())
SELECT @from, @to, * FROM dbo.temp
EXEC dbo.yourProcedure @from = @from, @to = @to
GO
Remember to clean up afterwards.
-- Cleanup
DROP TABLE dbo.temp
source to share