SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sql Max query


Sql Max query

Author
Message
DD-533246
DD-533246
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 69
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
drew.allen
drew.allen
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63767 Visits: 16959
DD-533246 - Thursday, February 1, 2018 12:52 PM
SQL 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 49


Table B gives which tasks can be performed together or parallel.
Table B

TestTask Parallel Tests
T104 T105
T104 T106
T104 T107
T104 T108
T104 T109

E.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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Joe Torre
Joe Torre
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7723 Visits: 1389
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.
DD-533246
DD-533246
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 69
Joe Torre - Thursday, February 1, 2018 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.

i have added insert script

DD-533246
DD-533246
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 69
drew.allen - Thursday, February 1, 2018 3:13 PM
DD-533246 - Thursday, February 1, 2018 12:52 PM
SQL 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 49


Table B gives which tasks can be performed together or parallel.
Table B

TestTask Parallel Tests
T104 T105
T104 T106
T104 T107
T104 T108
T104 T109

E.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

i have added insert script

Joe Torre
Joe Torre
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7723 Visits: 1389
What have you tried and what is the desired output?
DD-533246
DD-533246
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 69
Joe Torre - Monday, February 5, 2018 2:23 PM
What 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
drew.allen
drew.allen
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63767 Visits: 16959
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search