July 4, 2017 at 3:42 am
I have different tables that work with child-parent hierarchy, this is easy with just a "parent" column that indicates to which parent each item belongs (in same table). But now I want to achieve this with a history in time. So if at a certain moment in time an item moves from one parent to another, this will be traceable. To do this of course I need to have a separate table that holds the child-parent relation in time. I created these tables for testing
create table eq_sets (set_id int identity(1,1) primary key
, set_name varchar(50))
create table eq_parts (part_id int identity(1,1) primary key
, part_name varchar(50))
--table with part-set (child-parent) relations, with date for history purpose
create table parts_in_set (id int identity(1,1) primary key
, part_id int default(0) not null
, set_id int default(0) not null
, parts_in_set_date smalldatetime)
insert into eq_sets (set_name) values ('Set A'), ('Set B'), ('Set C'), ('Set D'), ('Set E')
insert into eq_parts (part_name) values ('Part 1'), ('Part 2'), ('Part 3'), ('Part 4'), ('Part 5'), ('Part 6'), ('Part 7'), ('Part 8'
), ('Part 9'), ('Part 10'), ('Part 11'), ('Part 12'), ('Part 13'), ('Part 14'), ('Part 15')
--assign parts on date 2017-1-1
insert into parts_in_set (part_id, set_id, parts_in_set_date) values
--assign parts 1, 2, 3 to set A
(1, 1, '2017-1-1'), (2, 1, '2017-1-1'), (3, 1, '2017-1-1')
--assign parts 4, 5, 6 to set B
, (4, 2, '2017-1-1'), (5, 2, '2017-1-1'), (6, 2, '2017-1-1')
--assign parts 7, 8, 9 to set C
, (7, 3, '2017-1-1'), (8, 3, '2017-1-1'), (9, 3, '2017-1-1')
--assign parts 10, 11, 12 to set D
, (10, 4, '2017-1-1'), (11, 4, '2017-1-1'), (12, 4, '2017-1-1')
--assign parts 13, 14, 15 to set E
, (13, 5, '2017-1-1'), (14, 5, '2017-1-1'), (15, 5, '2017-1-1')
--on 2017-7-1 part 4 is moved to set A
insert into parts_in_set (part_id, set_id, parts_in_set_date) values (4, 1, '2017-7-1')
In this sample I create 5 sets (these will be the parents) and 15 parts (these will be the children).
Initially each set will have 3 parts (Set A will contain item 1, 2 and 3) but on 2017-7-1 part 4 is moved to set A.
The goal is to retrieve the parts of a set on a specific date.
If I require the parts of set A on 2017-2-1 this should give 1-2-3 as a result, but if I require it on 2017-7-4 it should give 1-2-3-4 because item 4 moved to set A on 2017-7-1
The same for set B, if I query it for 2017-2-1 it should give 4-5-6 but on 2017-7-4 it should give only 5-6 because part 4 moved on 2017-7-1...
My knowledge of sql is too limited to achieve this, so any help is really appreciated... thanks!
July 4, 2017 at 3:58 am
Couple of ways you could do this. Firstly, you could use APPLY and use a SELECT statement, and do something like this:DECLARE @Date date = '20170106';
SELECT s.set_name, p.part_name, ps.parts_in_set_date
FROM eq_parts p
CROSS APPLY
(SELECT TOP 1 *
FROM parts_in_set ca
WHERE ca.part_id = p.part_id
AND ca.parts_in_set_date <= @Date
ORDER BY ca.parts_in_set_date DESC) ps
JOIN eq_sets s ON ps.set_id = s.set_id;
Otherwise, you could instead create a table valued function:--Create the your new function
CREATE FUNCTION dbo.current_parts_in_set (@part_id int, @Date date)
RETURNS TABLE
AS RETURN
SELECT TOP 1 *
FROM parts_in_set ps
WHERE ps.part_id = @part_id
AND ps.parts_in_set_date <= @Date
ORDER BY ps.parts_in_set_date DESC;
GO
--Now test your new function
DECLARE @Date date = '20170107';
SELECT s.set_name, p.part_name, cps.parts_in_set_date
FROM eq_parts p
CROSS APPLY dbo.current_parts_in_set (p.part_id, @Date) cps
JOIN eq_sets s ON cps.set_id = s.set_id;
Edit: Note that I have used date, rather than datetime, as all your dates have '00:00:00' as the time. If your data includes actual times as well then you'll need to adjust the parameter/variable.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 4, 2017 at 4:52 am
This is exactly what I needed... I tested it and it works as it should, thanks!
Small question, I notice that you write the date as 20170101 where as I always write it as 2017-01-01 in queries...
Is it better like you do it? Does this has advantages?
July 4, 2017 at 5:02 am
marc.corbeel - Tuesday, July 4, 2017 4:52 AMThis is exactly what I needed... I tested it and it works as it should, thanks!Small question, I notice that you write the date as 20170101 where as I always write it as 2017-01-01 in queries...
Is it better like you do it? Does this has advantages?
Main reason is because of languages when working with datetime, which just means when I write dates it follows over. For example, on British English:SELECT CAST('20170113 00:00:00.000' AS datetime); --works fine
SELECT CAST('2017-01-13 00:00:00.000' AS datetime); --Fails, conversion error
For some reason, even on British English, '2017-01-13 00:00:00.000' is read as the 1st day of the 13th month. 20170113 works fine though.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 4, 2017 at 5:37 am
Can I make it a bit more complex now?
In my sample I made two table, one with the sets and one with the parts. In my real live application this is one table, with a column indicating the parent (but this will be changed now to the table with the date history). I now would like to change this so that not only an item belongs to a parent, but another item can be a part of that item. This means that a set can have items, but some of these parts can be sets as well. In the example I made the same move on 20170701, and moved part 4 from set A to set B.
So again, on any given date I want to know the full contense of a set, with all parts, and parts of these parts etc...
Can this be done?
create table items (item_id int identity(1,1) primary key
, item_name varchar(50))
--table with part-set (child-parent) relations, with date for history purpose
create table parts_in_set (id int identity(1,1) primary key
, part_id int default(0) not null
, set_id int default(0) not null
, parts_in_set_date smalldatetime)
--add 2 sets
insert into items (item_name) values
--will be id 1
('Set A')
--will be id 2
, ('Set B')
--add 10 parts
insert into items (item_name) values
--will be id 3
('Part 1')
--will be id 4
, ('Part 2')
--will be id 5
, ('Part 3')
--will be id 6
, ('Part 4')
--will be id 7
, ('Part 5')
--will be id 8
, ('Part 6')
--etc...
, ('Part 7'), ('Part 8'), ('Part 9'), ('Part 10')
--assign parts on date 2017-1-1
insert into parts_in_set (part_id, set_id, parts_in_set_date) values
--assign parts 1, 2, 3 to set A
(3, 1, '2017-1-1'), (4, 1, '2017-1-1'), (5, 1, '2017-1-1')
--assign parts 4, 5, 6 to set B
, (6, 2, '2017-1-1'), (7, 2, '2017-1-1'), (8, 2, '2017-1-1')
--assign parts 7, 8, 9 to part 4
, (9, 7, '2017-1-1'), (10, 7, '2017-1-1'), (11, 7, '2017-1-1')
--on 2017-7-1 part 4 is moved to set A
insert into parts_in_set (part_id, set_id, parts_in_set_date) values (7, 1, '2017-7-1')
July 4, 2017 at 5:47 am
marc.corbeel - Tuesday, July 4, 2017 4:52 AMThis is exactly what I needed... I tested it and it works as it should, thanks!Small question, I notice that you write the date as 20170101 where as I always write it as 2017-01-01 in queries...
Is it better like you do it? Does this has advantages?
Take a read here for a more detailed answer to your question.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 4, 2017 at 6:21 am
So to be clear: I need the parts of set A on any given date, and parts of those parts, etc... with a full path name, delimited with backslash like the parts for Set A on 2017-07-04
Set A\Part1
Set A\Part2
Set A\Part3
Set A\Part4
Set A\Part4\Part7
Set A\Part4\Part8
Set A\Part4\Part9
July 4, 2017 at 6:57 am
Correction: the sample setup is here
create table items (item_id int identity(1,1) primary key
, item_name varchar(50))
--table with part-set (child-parent) relations, with date for history purpose
create table parts_in_set (id int identity(1,1) primary key
, part_id int default(0) not null
, set_id int default(0) not null
, parts_in_set_date smalldatetime)
--add 2 sets
insert into items (item_name) values
('Set A') --will be id 1
, ('Set B') --will be id 2
--add 10 parts
insert into items (item_name) values
('Part 1') --will be id 3
, ('Part 2') --will be id 4
, ('Part 3') --will be id 5
, ('Part 4') --will be id 6
, ('Part 5') --will be id 7
, ('Part 6') --will be id 8
--etc...
, ('Part 7'), ('Part 8'), ('Part 9'), ('Part 10')
--assign parts on date 2017-1-1
insert into parts_in_set (part_id, set_id, parts_in_set_date) values
--assign parts 1, 2, 3 to set A
(3, 1, '2017-1-1'), (4, 1, '2017-1-1'), (5, 1, '2017-1-1')
--assign parts 4, 5, 6 to set B
, (6, 2, '2017-1-1'), (7, 2, '2017-1-1'), (8, 2, '2017-1-1')
--assign parts 7, 8, 9 to part 4
, (9, 6, '2017-1-1'), (10, 6, '2017-1-1'), (11, 6, '2017-1-1')
--on 2017-7-1 part 4 is moved to set A
insert into parts_in_set (part_id, set_id, parts_in_set_date) values (6, 1, '2017-7-1')
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply