Query System Versioned Table

[G12Consult]

I need to query a system versioned table which will group by date but I don't know what to GROUP BY

 SELECT COUNT(*) 'UNPROCESSED' 
FROM [PROCESSING].[xxx].[MASTER_xxx_FUTURE_STATE_BUILD] 
FOR SYSTEM_TIME BETWEEN '2019-01-09 16:59:00.000' AND '2019-01-14 16:59:00.000' 
WHERE DM_RecordStatus = 0]SELECT COUNT(*) 'UNPROCESSED'
GROUP BY ??

so the code above without the group by gives me a total of 148752

What I want is something like:

09/01/2019 45500
10/01/2018 28242
11/01/2018 45421
...

 

[TamarGranor]

Try CONVERT(date, SYSTEM_TIME) as a field and your GROUP BY expression.

Tamar

 

[G12Consult]

Tried that and gave me an invalid column name on the system_time

 

[dhookom]

If this is SQL Server, try:

SELECT Convert(Date,SYSTEM_TIME) [SysDate], COUNT(*) [UNPROCESSED]
FROM [PROCESSING].[xxx].[MASTER_xxx_FUTURE_STATE_BUILD] 
WHERE SYSTEM_TIME BETWEEN '2019-01-09 16:59:00.000' AND '2019-01-14 16:59:00.000' 
AND DM_RecordStatus = 0
GROUP BY Convert(Date,SYSTEM_TIME)

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[G12Consult]

Same issue. invalid column name 'SYSTEM_TIME'

 

[dhookom]

G12Consult,
Your first post had the column SYSTEM_TIME. Where did it come from?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[G12Consult]

I used a website that provided that code and it worked (without the group by). I do not have a column in my table that references that name.

Website = Link

 

[Olaf Doschke]

Well, the docs tell you can only use such FOR SYSTEM_TIME clauses, it's not a real column, it's metadata SQL Server knows about records of temporal tables from transaction logs.

So you need a real date or datetime column in your table which is set with GetDate() or Convert(date,GetDate()) and group by that. You can't group by SYSTEM_TIME.

PS:

https://docs.microsoft.com/en-us/sq...al-table-usage-scenarios?view=sql-server-2017

suggests, that you must have datetime2 columns to define PERIOD FOR SYSTEM_TIME.
So, if you let SSMS script the CREATE TABLE statement for your temporal table, what does it tell?

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

Going by the tutorials, here's how to group all data by dates in a temporal table:

1. generating the table

CREATE TABLE test   
(    
     id int identity(1,1) NOT NULL PRIMARY KEY CLUSTERED  
   , atext varchar(50) NOT NULL  
   , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL  
   , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL  
   , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)     
)    
WITH (SYSTEM_VERSIONING = ON);

2. creating some data (and history data)

insert into test (atext) values ('hello'),('world')
-- making history
update test set atext ='bye' where atext='hello'

3. Selecting data

--only current data (just as usual, that's all data in the main table)
select * from test 
-- all data including history data
select * from test for system_time ALL
---grouping by dates:
select convert(date,SysStartTime) as DataOfDate, count(*) from test for system_time ALL group by convert(date,SysStartTime)
--or by id and date:
select id, convert(date,SysStartTime) as DataOfDate, count(*) from test for system_time ALL group by id,convert(date,SysStartTime)

SYSTEM_TIME can only be used to filter data for a period. Notice the highlighted remark, to make sargeable (fast) queries you need to know the time period you query for in UTC:

...filters in form of <period column> {< | > | =, ...} date_condition [highlight #FCE94F]AT TIME ZONE 'UTC'[/highlight].

That also means if you want to group by dates in your time zone, you better have an additional column with a simple datetime not bound to a time zone or bound to your time zone.
The main ingredient to get all data in current state and historical states is to include a clause FOR SYSTEM_TIME ALL, you can also use this clause to filter for a period of say last month, last quarter or year, just remember this is in UTC time zone, always.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name