February 9, 2019 at 9:19 am
As per this block
DECLARE @t1 table (
id uniqueidentifier PRIMARY KEY,
activity_id int,
plan_id int,
parent int,
predecessor int,
dept int,
item_name varchar(255),
task_type varchar(10),
planned_start_date date,
planned_end_date date,
actual_start_date date,
actual_end_date date,
CHECK (planned_start_date <= planned_end_date),
CHECK (actual_start_date <= actual_end_date)
)
insert into @t1 select NEWID(), 1000, 1, null, null, 1, 'Wash Car', 'project', null, null, null, null
insert into @t1 select NEWID(), 1010, 1, 1000, null, 1, 'Wash Hood', 'task', '2019-01-01', '2019-01-10', '2019-01-01', '2019-01-11'
insert into @t1 select NEWID(), 1020, 1, 1000, 1010, 1, 'Wash Door', 'task', '2019-01-10', '2019-01-15', '2019-01-11', '2019-01-18'
insert into @t1 select NEWID(), 1030, 1, 1000, 1020, 1, 'Wash Trunk', 'task', '2019-01-16', '2019-01-26', '2019-01-19', '2019-01-28'
insert into @t1 select NEWID(), 1040, 1, 1000, 1030, 1, 'Wash Windows', 'task', '2019-01-27', '2019-01-31', '2019-01-29', '2019-02-05'
insert into @t1 select NEWID(), 1050, 1, 1000, 1040, 1, 'Wash Wheels', 'task', '2019-02-01', '2019-02-04', '2019-02-06', null -- was all going well until active start conflicts with line 14...
insert into @t1 select NEWID(), 1000, 2, null, null, 1, 'Wash Truck', 'project', null, null, null, null
insert into @t1 select NEWID(), 1010, 2, 1000, null, 1, 'Wash Hood', 'task', '2019-02-05', '2019-02-10', '2019-02-06', null
insert into @t1 select NEWID(), 1020, 2, 1000, 1010, 1, 'Wash Door', 'task', '2019-02-11', '2019-02-12', null, null
insert into @t1 select NEWID(), 1030, 2, 1000, 1020, 1, 'Wash Back Hatch', 'task', '2019-02-13', '2019-02-14', null, null
insert into @t1 select NEWID(), 1040, 2, 1000, 1030, 1, 'Wash Windows', 'task', '2019-02-15', '2019-02-20', null, null
insert into @t1 select NEWID(), 1050, 2, 1000, 1040, 1, 'Wash Wheels', 'task', '2019-02-21', '2019-02-28', null, null
insert into @t1 select NEWID(), 1000, 3, null, null, 1, 'Wash Boat', 'project', null, null, null, null
insert into @t1 select NEWID(), 1010, 3, 1000, null, 2, 'Vacuum Carpets', 'task', '2019-02-17', '2019-02-20', null, null -- vacuum dept has a task in this project
insert into @t1 select NEWID(), 1020, 3, 1000, 1010, 1, 'Wash Sides', 'task', '2019-02-21', '2019-02-26', null, null
insert into @t1 select NEWID(), 1030, 3, 1000, 1020, 1, 'Wash Console', 'task', '2019-02-27', '2019-02-28', null, null
insert into @t1 select NEWID(), 1040, 3, 1000, 1030, 1, 'Wash Windows', 'task', '2019-03-01', '2019-03-15', null, null
insert into @t1 select NEWID(), 1050, 3, 1000, 1040, 1, 'Wash Seat Vinyl', 'task', '2019-03-15', '2019-03-25', null, null
insert into @t1 select NEWID(), 1000, 4, null, null, 1, 'Wash Yacht', 'project', null, null, null, null
insert into @t1 select NEWID(), 1010, 4, 1000, null, 2, 'Vacuum Carpets', 'task', '2019-02-10', '2019-02-18', null, null -- vacuum dept double booked for part of this
select * from @t1 order by plan_id, planned_start_date -- really need to sort by which ever one starts first and keep parent together with children ; not sure how...
-- activity numbers look like duplicates but we are trying to standardize on what these numbers mean ; so wash trunk is always 1030 ; for future reporting and data science
-- parent and predecessor are actually UUID as well but simplifying for this example; real table has proper constraints for them
-- it is ok to overlap when in same plan_id as project managers may anticipate people finishing tasks quicker than planned
-- so will ignore conflict checking in same plan
-- project dates should get updated based on children min and max planned_start is earliest child, planned_end latest planned child ; same for actuals as they get filled in
-- overlap rules ; dept tasks cannot be overlapping from one project to another as long as they are not finished yet
-- need to know not only if they are in conflict (overlap) but for each task which other tasks are conflicting with it to visually report this on web application or PowerBi report
-- it is preferred / fine to return the conflicting tasks in new calculated column and comma separated if more than one conflicts
I am looking for the proper sql to find date ranges that overlap based on the comments listed above. I have seen examples using inner joins of task tale to itself and also using WHERE EXISTS as well.
Any help or insight much appreciated.
P.S. I an a sql dba and web developer so my data modelling / science chops are lacking so if this is easy for most I totally understand and accept my shame. 🙂
February 9, 2019 at 11:13 am
Came up with this so far. Any thoughts? Not sure it is the right way.
select * from @t1 as t1
where exists (
select 1 from @t1 as t2
where t2.plan_id!=t1.plan_id
and t2.task_type='task'
and COALESCE(t1.actual_start_date, t1.planned_start_date) <= COALESCE(t2.actual_end_date, t2.planned_end_date) and COALESCE(t2.actual_start_date, t2.planned_start_date) <= COALESCE(t1.actual_end_date, t1.planned_end_date)
and t2.dept = t1.dept
and t2.actual_end_date is null
) and t1.task_type='task' and actual_end_date is null
order by plan_id, planned_start_date
Still no idea how to actually do the computed column and have that comma separated list of tasks it conflicts with
February 9, 2019 at 9:01 pm
ronwcraig - Saturday, February 9, 2019 9:19 AMAs per this block
DECLARE @t1 table (
id uniqueidentifier PRIMARY KEY,
activity_id int,
plan_id int,
parent int,
predecessor int,
dept int,
item_name varchar(255),
task_type varchar(10),
planned_start_date date,
planned_end_date date,
actual_start_date date,
actual_end_date date,
CHECK (planned_start_date <= planned_end_date),
CHECK (actual_start_date <= actual_end_date)
)insert into @t1 select NEWID(), 1000, 1, null, null, 1, 'Wash Car', 'project', null, null, null, null
insert into @t1 select NEWID(), 1010, 1, 1000, null, 1, 'Wash Hood', 'task', '2019-01-01', '2019-01-10', '2019-01-01', '2019-01-11'
insert into @t1 select NEWID(), 1020, 1, 1000, 1010, 1, 'Wash Door', 'task', '2019-01-10', '2019-01-15', '2019-01-11', '2019-01-18'
insert into @t1 select NEWID(), 1030, 1, 1000, 1020, 1, 'Wash Trunk', 'task', '2019-01-16', '2019-01-26', '2019-01-19', '2019-01-28'
insert into @t1 select NEWID(), 1040, 1, 1000, 1030, 1, 'Wash Windows', 'task', '2019-01-27', '2019-01-31', '2019-01-29', '2019-02-05'
insert into @t1 select NEWID(), 1050, 1, 1000, 1040, 1, 'Wash Wheels', 'task', '2019-02-01', '2019-02-04', '2019-02-06', null -- was all going well until active start conflicts with line 14...insert into @t1 select NEWID(), 1000, 2, null, null, 1, 'Wash Truck', 'project', null, null, null, null
insert into @t1 select NEWID(), 1010, 2, 1000, null, 1, 'Wash Hood', 'task', '2019-02-05', '2019-02-10', '2019-02-06', null
insert into @t1 select NEWID(), 1020, 2, 1000, 1010, 1, 'Wash Door', 'task', '2019-02-11', '2019-02-12', null, null
insert into @t1 select NEWID(), 1030, 2, 1000, 1020, 1, 'Wash Back Hatch', 'task', '2019-02-13', '2019-02-14', null, null
insert into @t1 select NEWID(), 1040, 2, 1000, 1030, 1, 'Wash Windows', 'task', '2019-02-15', '2019-02-20', null, null
insert into @t1 select NEWID(), 1050, 2, 1000, 1040, 1, 'Wash Wheels', 'task', '2019-02-21', '2019-02-28', null, nullinsert into @t1 select NEWID(), 1000, 3, null, null, 1, 'Wash Boat', 'project', null, null, null, null
insert into @t1 select NEWID(), 1010, 3, 1000, null, 2, 'Vacuum Carpets', 'task', '2019-02-17', '2019-02-20', null, null -- vacuum dept has a task in this project
insert into @t1 select NEWID(), 1020, 3, 1000, 1010, 1, 'Wash Sides', 'task', '2019-02-21', '2019-02-26', null, null
insert into @t1 select NEWID(), 1030, 3, 1000, 1020, 1, 'Wash Console', 'task', '2019-02-27', '2019-02-28', null, null
insert into @t1 select NEWID(), 1040, 3, 1000, 1030, 1, 'Wash Windows', 'task', '2019-03-01', '2019-03-15', null, null
insert into @t1 select NEWID(), 1050, 3, 1000, 1040, 1, 'Wash Seat Vinyl', 'task', '2019-03-15', '2019-03-25', null, nullinsert into @t1 select NEWID(), 1000, 4, null, null, 1, 'Wash Yacht', 'project', null, null, null, null
insert into @t1 select NEWID(), 1010, 4, 1000, null, 2, 'Vacuum Carpets', 'task', '2019-02-10', '2019-02-18', null, null -- vacuum dept double booked for part of thisselect * from @t1 order by plan_id, planned_start_date -- really need to sort by which ever one starts first and keep parent together with children ; not sure how...
-- activity numbers look like duplicates but we are trying to standardize on what these numbers mean ; so wash trunk is always 1030 ; for future reporting and data science
-- parent and predecessor are actually UUID as well but simplifying for this example; real table has proper constraints for them
-- it is ok to overlap when in same plan_id as project managers may anticipate people finishing tasks quicker than planned
-- so will ignore conflict checking in same plan-- project dates should get updated based on children min and max planned_start is earliest child, planned_end latest planned child ; same for actuals as they get filled in
-- overlap rules ; dept tasks cannot be overlapping from one project to another as long as they are not finished yet
-- need to know not only if they are in conflict (overlap) but for each task which other tasks are conflicting with it to visually report this on web application or PowerBi report
-- it is preferred / fine to return the conflicting tasks in new calculated column and comma separated if more than one conflictsI am looking for the proper sql to find date ranges that overlap based on the comments listed above. I have seen examples using inner joins of task tale to itself and also using WHERE EXISTS as well.
Any help or insight much appreciated.
P.S. I an a sql dba and web developer so my data modelling / science chops are lacking so if this is easy for most I totally understand and accept my shame. 🙂
Please post DDL and follow ANSI/ISO standards when asking for help.
February 10, 2019 at 6:33 am
I posted the declare above the inserts. Sorry if you mean more. Have to admit I don’t what else to post. This is all I know. I changed to declare so people could just copy paste but that is my schema mostly. Apologize for my newbishness
February 12, 2019 at 10:55 am
ronwcraig - Sunday, February 10, 2019 6:33 AMI posted the declare above the inserts. Sorry if you mean more. Have to admit I don’t what else to post. This is all I know. I changed to declare so people could just copy paste but that is my schema mostly. Apologize for my newbishness
Please pay no attention to Mr. Celko. He doesn't follow the standards he so often glorifies while castigating everyone/everything that violate them, even when it's the most practical solution. You may be a newbie, but don't ever let that stop you from asking questions. Just an FYI, but forget about a computed column. That's not realistic, as it requires information from other rows. SQL Server will NOT be letting that happen. Given your sample data, does your posted "Came up with this so far. ..." code get you the solution for that sample data? Verify it and let us know...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply