February 1, 2018 at 12:52 pm
SQL server 2012 :
Need help with building query for max.
drop table Temp_TestMethods
go
drop table Temp_ParallelMethods
go
create table Temp_TestMethods(
Id int IDENTITY (1,1),
TestMetod varchar(50),
EstimatedTime int
)
insert into Temp_TestMethods (TestMetod,EstimatedTime) values('T102',65)
insert into Temp_TestMethods values('T103',90)
insert into Temp_TestMethods values('T109',45)
insert into Temp_TestMethods values('T9',40)
insert into Temp_TestMethods values('T10',60)
insert into Temp_TestMethods values('T202',80)
insert into Temp_TestMethods values('T201',70)
insert into Temp_TestMethods values('T203',35)
create table Temp_ParallelMethods(
TESTMethodID int,
parallelMethodId int
)
insert into Temp_ParallelMethods values(1,2)
insert into Temp_ParallelMethods values(1,3)
insert into Temp_ParallelMethods values(1,4)
insert into Temp_ParallelMethods values(1,5)
insert into Temp_ParallelMethods values(2,1)
insert into Temp_ParallelMethods values(2,3)
insert into Temp_ParallelMethods values(2,4)
insert into Temp_ParallelMethods values(2,5)
insert into Temp_ParallelMethods values(3,2)
insert into Temp_ParallelMethods values(3,1)
insert into Temp_ParallelMethods values(3,4)
insert into Temp_ParallelMethods values(3,5)
insert into Temp_ParallelMethods values(4,2)
insert into Temp_ParallelMethods values(4,3)
insert into Temp_ParallelMethods values(4,1)
insert into Temp_ParallelMethods values(4,5)
insert into Temp_ParallelMethods values(5,2)
insert into Temp_ParallelMethods values(5,3)
insert into Temp_ParallelMethods values(5,4)
insert into Temp_ParallelMethods values(5,1)
insert into Temp_ParallelMethods values(6,7)
insert into Temp_ParallelMethods values(6,8)
insert into Temp_ParallelMethods values(7,6)
insert into Temp_ParallelMethods values(7,8)
insert into Temp_ParallelMethods values(8,6)
insert into Temp_ParallelMethods values(8,7)
select * from Temp_TestMethods
select * from Temp_ParallelMethods
now i need to calculate Estimatedtime for Test 'T1O3' and 'T102' and T109.
Remember we need to consider Parallel test methods and get max time for calculating overall time.
Thanks much appreciated looking into this
February 1, 2018 at 3:13 pm
DD-533246 - Thursday, February 1, 2018 12:52 PMSQL server 2012 :
Need help with building query for max.
Table A has a record Ex: T103 and how much time it takes to complete a task.Table A
TestTask Estimated Days
T 103 90
T 104 40
T 105 50
T 106 50
T 107 45
T 108 45
T 109 49Table B gives which tasks can be performed together or parallel.
Table BTestTask Parallel Tests
T104 T105
T104 T106
T104 T107
T104 T108
T104 T109E.g. Test T103 has timing of 90 days
Test T104 has timing of 40 days
Test T105 has timing of 50 days.
Test T106 has timing of 50 days.
Test T107 has timing of 45 days.
Test T108 has timing of 46 days.
Test T109 has timing of 49 days.
T104 can be run parallel with T105 & 106 &107 &108 &109
how i should write sql query to get Total days for all Records like below
Total Timing = Timefor T103 + Max( all parallel tests)
Total Timing = 90 +max(40,50,50,45,46,49) = 140 days
Thanks
I don't see where it says that T105 can be run in parallel with T106, even though both can be run in parallel with T104. Can we assume that being able to run in parallel is a transitive property?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 1, 2018 at 3:13 pm
Please post a script that creates a table, temp table or table variable with insert statement(s) of sample data, what you've tried and desired output.
February 3, 2018 at 5:31 pm
Joe Torre - Thursday, February 1, 2018 3:13 PMPlease post a script that creates a table, temp table or table variable with insert statement(s) of sample data, what you've tried and desired output.
i have added insert script
February 3, 2018 at 5:31 pm
drew.allen - Thursday, February 1, 2018 3:13 PMDD-533246 - Thursday, February 1, 2018 12:52 PMSQL server 2012 :
Need help with building query for max.
Table A has a record Ex: T103 and how much time it takes to complete a task.Table A
TestTask Estimated Days
T 103 90
T 104 40
T 105 50
T 106 50
T 107 45
T 108 45
T 109 49Table B gives which tasks can be performed together or parallel.
Table BTestTask Parallel Tests
T104 T105
T104 T106
T104 T107
T104 T108
T104 T109E.g. Test T103 has timing of 90 days
Test T104 has timing of 40 days
Test T105 has timing of 50 days.
Test T106 has timing of 50 days.
Test T107 has timing of 45 days.
Test T108 has timing of 46 days.
Test T109 has timing of 49 days.
T104 can be run parallel with T105 & 106 &107 &108 &109
how i should write sql query to get Total days for all Records like below
Total Timing = Timefor T103 + Max( all parallel tests)
Total Timing = 90 +max(40,50,50,45,46,49) = 140 days
ThanksI don't see where it says that T105 can be run in parallel with T106, even though both can be run in parallel with T104. Can we assume that being able to run in parallel is a transitive property?
Drew
i have added insert script
February 5, 2018 at 2:23 pm
What have you tried and what is the desired output?
February 6, 2018 at 11:56 am
Joe Torre - Monday, February 5, 2018 2:23 PMWhat have you tried and what is the desired output?
i tried below query, but this does not work with all scenarios. it always get Max values for test which does not have parallel test , rather it should SUM for different tests.SELECT SUM(vbn.new) as NewestimatedTime from
(
SELECT CASE WHEN xyz.parallelMethodId = 0 THEN sum(xyz.estimatedTIme)
when xyz.parallelMethodId =1 THEN Max(xyz.estimatedTIme)
end as new
from(
SELECT CASE WHEN abc.parallelMethodId IS NULL THEN 0
WHEN abc.parallelMethodId IS NOT NULL THEN 1
END as parallelMethodId,abc.estimatedTIme
from(SELECT testmethod, Max(estimatedtime) as estimatedTIme , b.parallelMethodId FROM temp_testmethods A
LEFT OUTER JOIN temp_parallelmethods B ON B.parallelmethodid = A.id
WHERE testmethod IN ( 'T103', 'T102', 'T109','TX','TX1' )
group by testmethod,b.parallelMethodId) abc) xyz
group by xyz.parallelMethodId) vbn
February 6, 2018 at 12:57 pm
I looked at this, and it looks like your data is too clean to be representative, because your sets are disjoint and your subsets are complete. That is, if you define a set A as every test that can be run in parallel with test a, then every test within that set can be run in parallel with any other test in that set and cannot be run in parallel with any test that is not within that set.
In the real world, I would expect things to be much messier. For example, say tests A and B use the same set of equipment, so they cannot be run in parallel, but test C uses a different set of equipment, so it can be run in parallel with either or both A and B.
In this case,
Set A = {A, C}
Set B = {B, C}
Set C = {A, B, C}
Sets A and B are not disjoint, because they are not the same, but they both include {C}. Set C is not complete, because it includes tests A and B which cannot be run in parallel with each other.
I came up with a solution that works only if the sets are disjoint and the subsets are complete. I haven't posted it, because I don't think that it will work for your date, because I don't think your data will match that criteria.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply