SQLTeam.com | Weblogs | Forums

Need help in building sql query which has complex logic

sql2008

#1

below is my insert Script
drop table Temp_TestMethods
go
drop table Temp_ParallelMethods
go

create table Temp_TestMethods(
Id int IDENTITY (1,1),
TestMethod varchar(50),
EstimatedTime int
)

insert into Temp_TestMethods (TestMethod,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)
insert into Temp_TestMethods values('TX',180)

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
I need to calculate total Estimatedtime in for Tests 'T1O3' and 'T102' and T109 and 'TX'
considering all parallel test to be beforemd at the same time. this should be only 1 record.
Test TX does not have any parallel methods so it will be addition to the rest of parallel test with max estimated time,


#2

Hi

I was able to do it like this ..

Please check and let me know if this is fine

Thanks

Query Solution

SELECT sum1,
sum2,
sum3,
overallsum
FROM (SELECT a.abc,
a.testmethod,
Sum(a.estimatedtime)
OVER(
partition BY abc)
AS 'OverAllSum',
'sum'
+ Cast(Row_number() OVER(partition BY abc ORDER BY abc ) AS
VARCHAR(10))
AS xyz
FROM (SELECT 'abc' AS abc,
testmethod,
estimatedtime
FROM temp_testmethods
WHERE testmethod IN ( 'T1O3', 'T102', 'T109', 'TX' )) a) Temp
PIVOT ( Max(testmethod)
FOR xyz IN (sum1,
sum2,
sum3) ) piv

Create Data

drop table Temp_TestMethods
go
drop table Temp_ParallelMethods
go

create table Temp_TestMethods(
Id int IDENTITY (1,1),
TestMethod varchar(50),
EstimatedTime int
)

insert into Temp_TestMethods (TestMethod,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)
insert into Temp_TestMethods values('TX',180)

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)


#3

Reddy thanks looking into this really appreciate it , i think you need to look for parallel test methods table also
as Test T102 and T109 can be performed parallel , so both test will take max of (T102,T109) = 65 so total result should be TX + Max(T02,T109) = 180+ 65 = 245.

look at this query result , T102 and T109 are parallel Tests based on Temp_ParallelMethods table, so we should just take 65 which is max for both of tests and then add that to Test TX , result should be 180+ 65 = 245.

SELECT 'abc' AS abc,
A.TestMethod , MAX(A.estimatedTIme) as EstimatedTime
FROM Temp_TestMethods A
left outer JOIN Temp_ParallelMethods B ON B.parallelMethodid = A.ID
WHERE testmethod IN ( 'T1O3', 'T102', 'T109', 'TX' )
group by A.TestMethod


#4

Hi

Its very easy to include that logic

do you want me to do it

thanks


#5

yes please


#6

Hi

working on it

please give me some time ..probably 4 to 5 hrs
have other things

thanks


#7

Hi

I was able to do it ..

Please check and let me know if this is correct

Thanks
Sorry for the delay

Script

SELECT sum1,
sum2,
sum3,
overallsum
FROM (SELECT a.abc,
a.testmethod,
Max(a.estimatedtime)
OVER(
partition BY abc)
AS 'OverAllSum',
'sum'
+ Cast(Row_number() OVER(partition BY abc ORDER BY abc ) AS
VARCHAR(10))
AS xyz
FROM (SELECT 'abc' AS abc,
testmethod,
(SELECT Max(estimatedtime)
FROM temp_testmethods A
LEFT OUTER JOIN temp_parallelmethods B
ON B.parallelmethodid = A.id
WHERE testmethod IN ( 'T1O3', 'T102', 'T109' ))
+ (SELECT estimatedtime
FROM temp_testmethods A
LEFT OUTER JOIN temp_parallelmethods B
ON B.parallelmethodid = A.id
WHERE testmethod IN ( 'TX' )) estimatedtime
FROM temp_testmethods
WHERE testmethod IN ( 'T1O3', 'T102', 'T109', 'TX' )) a) Temp
PIVOT ( Max(testmethod)
FOR xyz IN (sum1,
sum2,
sum3) ) piv

Create Data

drop table Temp_TestMethods
go
drop table Temp_ParallelMethods
go

create table Temp_TestMethods(
Id int IDENTITY (1,1),
TestMethod varchar(50),
EstimatedTime int
)

insert into Temp_TestMethods (TestMethod,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)
insert into Temp_TestMethods values('TX',180)

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)


#8

Thanks looks promising, exploring your solution. thanks for feedback.


#9

Hi

I was able to do it by watching and following you tube video

Thanks
Hopefully this will help you in future

Good luck