June 5, 2017 at 11:21 am
Hi guys!
SELECT *
FROM Spine S
CROSS
APPLY
(
SELECT *
FROM PersonTemporalTable
FOR SYSTEM_TIME AS OF S.DateTimeValueOfInterest
) CA
It would be much simpler if I left out the "why" of it and stuck to the "what" and "how" 🙂
I need to be be able to query a Temporal table for record state at specific points in time - were the syntax above legal, it would do precisely what I am after.
In this case, the table Spine contains a list of DATETIME values of interest.
Is this simply a matter of not using the correct syntax or is it a limitation?
Besides going with dynamic SQL, do you guys have any other suggestions I could try?
Thanks in advance!
***********************************
Edit
***********************************
This is what I've done since - open to better ideas
SELECT *
FROM Spine SP
JOIN PetTemporal FOR SYSTEM_TIME ALL PT ON SP.SpineDT >= PT.ValidFrom AND SP.SpineDT < PT.ValidTo
JOIN PersonTemporal FOR SYSTEM_TIME ALL PS ON SP.SpineDT >= PS.ValidFrom AND SP.SpineDT < PS.ValidTo
AND PS.PersonID = PT.PersonID
***********************************
DDL
Unfortunately because of the whole temporal thing it'll be easier if you just write simple inserts separated by 5sec?
Yes, I could write a look with DELAY, but (*&^*&^% I'm tired, going to bed
***********************************CREATE TABLE [dbo].[PersonTemporal](
[PersonID] [int] NOT NULL,
[Name] [varchar](100) NOT NULL,
[ValidFrom] [datetime2](2) GENERATED ALWAYS AS ROW START NOT NULL,
[ValidTo] [datetime2](2) GENERATED ALWAYS AS ROW END NOT NULL,
PRIMARY KEY CLUSTERED
(
[PersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
) ON [PRIMARY]
WITH
(
SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[PersonHistory] )
)
GO
CREATE TABLE [dbo].[PetTemporal](
[PetID] [int] NOT NULL,
[PersonID] [int] NOT NULL,
[Name] [varchar](100) NOT NULL,
[ValidFrom] [datetime2](2) GENERATED ALWAYS AS ROW START NOT NULL,
[ValidTo] [datetime2](2) GENERATED ALWAYS AS ROW END NOT NULL,
PRIMARY KEY CLUSTERED
(
[PetID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
) ON [PRIMARY]
WITH
(
SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[PetHistory] )
)
;WITH Spine
(
SELECT SpineDT = ValidFrom
FROM PersonTemporal
FOR SYSTEM_TIME ALL
WHERE ValidFrom > @From
UNION
SELECT SpineDT = ValidFrom
FROM PetTemporal
FOR SYSTEM_TIME ALL
WHERE ValidFrom > @From
)
June 5, 2017 at 1:27 pm
I think you want to do this the other way. From what I know, which isn't a ton on the Temporal side, you want to join the tables first, then use the AS OF syntax.
If you include more DDL and some sample data/results, I can help more. The view example might help here: https://docs.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table
June 5, 2017 at 1:42 pm
Steve Jones - SSC Editor - Monday, June 5, 2017 1:27 PMI think you want to do this the other way. From what I know, which isn't a ton on the Temporal side, you want to join the tables first, then use the AS OF syntax.If you include more DDL and some sample data/results, I can help more. The view example might help here: https://docs.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table
Hi Steve,
DDL is up but I've run out of energy and f*&ks
The data really is 5 arb inserts in each table seperated at 5min intervals
AFAIK, one can't insert 5 rows in one transaction and later modify the system datetimes - so it needs delay
Understand if you also don't have the time or energy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply