SQL Server query of dimensions containing history of changes

Question

I have a dimension table in SQL Server that stores values with change history in the following format:

[Position_History]
Position          Person    Inserted     Deleted
Sales 1           Mark      2019-01-01   2019-02-01
Sales 2           Mark      2019-02-01   2019-05-01
Sales 3           Mark      2019-05-01   2020-07-01
Senior Developer  Peter     2019-01-01   2020-07-01
Junior Developer  John      2019-01-01   2019-04-01
Project Manager   John      2019-04-01   2020-07-01

Inserted = date of record insertion.

Deleted = date of record being updated.

When querying state of positions for a specific date a simple where query would suffice:

SELECT Position, Person FROM Position_History WHERE Inserted <= '2019-05-01' AND Deleted > '2019-05-01'

With result:

Position          Person
Sales 3           Mark 
Senior Developer  Peter
Project Manager   John

However I don't know which approach to use to create a query that would return me results for a list of dates. For example I would like to know state on 3 consecutive months - '2019-03-01', '2019-04-01', '2019-05-01'

The results I'm looking for would be:

Position          Person  Date
Sales 2           Mark    2019-03-01
Senior Developer  Peter   2019-03-01
Junior Developer  John    2019-03-01
Sales 2           Mark    2019-04-01
Senior Developer  Peter   2019-04-01
Project Manager   John    2019-04-01
Sales 3           Mark    2019-05-01
Senior Developer  Peter   2019-05-01
Project Manager   John    2019-05-01

I am looking for approach that would accept parameters dynamically as list of parameters or table, avoiding hardcoding union of x queries.

Thanks for help!

Do you want the state of play on only those exact three dates, or include every change on every date between those dates? — MatBailie, Jul 08 '20 at 12:14

allmhuran · Accepted Answer · 2020-07-08T12:22:23.413

If I've understood your request correctly, your issue is not with the logic of the query, but merely how to run that logic for multiple dates. That being the case, you can put the dates you want into a table and join to it:

create table #dates(ReportingDate date);
insert #dates 
values ('2019-03-01'),
       ('2019-04-01'),
       ('2019-05-01');

select ph.Position, ph.Person, dt.ReportingDate
from   dbo.Position_History ph
join   #dates               dt on ph.Inserted <= dt.ReportingDate
                                  and ph.Deleted > dt.ReportingDate

If you want to be able to pass this in from an application, you could use a table valued parameter, along with a function or stored procedure. For example:

create type dbo.ReportingDates as table(ReportingDate date);
create function dbo.PositionsAtDates(@dates dbo.ReportingDates readonly) 
returns table as return
(
   select ph.Position, ph.Person, dt.ReportingDate
   from   dbo.Position_History ph
   join   @dates               dt on ph.Inserted <= dt.ReportingDate
                                     and ph.Deleted > dt.ReportingDate
);

-- usage within SSMS:
declare @d dbo.ReportingDates;
insert @d values ('2020-06-01'), ('2020-07-01');
select * from dbo.PositionsAtDates(@d);

If you want to use this from, say, a C# application, you need to create a parameter with SqlDbType = SqlDbType.Structured, and you can set its value to a DataTable. See this thread or this MS documentation

For other ways to pass "arrays" around in SQL, see Erland Sommarskog's guide

Or, slightly tidier than unions... `INSERT INTO @d VALUES ('2020-06-01'),('2020-07-01'),('2020-08-01');` — MatBailie, Jul 08 '20 at 12:16

Shantanu Kher · Answer 2 · 2020-07-08T12:39:06.780

One possible approach I can think of is,

DB Fiddle - https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=e4d980ca238769feef5bbb49c451a23d

I have kept the date values hard coded for demonstration purpose, but in actual you can keep these values somewhere in a separate table. You can also replace between clause with >= AND < operators to include/ exclude records appropriately.

Sample SQL -

select mt.position, mt.person, dt.filter_date from (SELECT '2019-03-01'  as filter_date
UNION
SELECT '2019-04-01'
UNION 
Select '2019-05-01') dt
Join
(SELECT Position, Person, inserted, deleted FROM Position_History) mt
ON dt.filter_date between mt.inserted and mt.deleted;

Replace BETWEEN clause appropriately as shown below -

mt.inserted <= dt.filter_date and mt.deleted > dt.filter_date

SQL Server query of dimensions containing history of changes

2 Answers2