CODEWITHSUNDEEP
sqlsql-server
Shelar
Shelar

Reputation: 46

Combine 2 queries in single query

I am using MSSQL server 2008

I want to combine 2 queries in single query

SELECT Id,GroupName,Status,StartDateTime 
from MasterSchedule  
Where Project='Sample' AND SubProject='Sample'
 AND StartDateTime between '2016-04-25' AND '2016-04-27'


SELECT sum(DATEDIFF(SECOND, CONVERT(NVARCHAR, StartTime,100) , CONVERT(NVARCHAR, EndTime,100))) as totaltime
from MasterScheduleDetails 
where MasterScheduleId = Id

Schema of database is MasterSchedule

Id          Project                                                                                                                                                                                                                                                         SubProject                                                                                                                                                                                                                                                      GroupName                                          StartDateTime           Status                                             Time                                               CreatedDate                                        BrowserName                                        RunPauseStatus
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- ----------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
22          Sample                                                                                                                                                                                                                                                          Sample                                                                                                                                                                                                                                                          TEST_01                                            2016-04-26 16:03:59.000 Completed                                          04/26/2016 16:03:18                                04/26/2016                                         eZscript;IE                                        NotStarted

MasterScheduleDetails Table
Id          MasterScheduleId SuiteId     Status                                                                                                                                                                                                                                                           RunOn                                              StartTime                                                                                                                                                                                                                                                        SequenceNo  RunSuiteId                                                                                                                                                                                                                                                       EndTime                                                                                                                                                                                                                                                          XmlReportId                                        TestDescription
----------- ---------------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
43          22               1           Pass                                                                                                                                                                                                                                                             192.168.1.139                                      04/26/2016 16:05:09                                                                                                                                                                                                                                              0           1                                                                                                                                                                                                                                                                04/26/2016 16:05:41                                                                                                                                                                                                                                              GTSABHIJIT_abhijit.shelar_04262016160509           BPW
44          22               2           Pass                                                                                                                                                                                                                                                             192.168.1.139                                      04/26/2016 16:06:11                                                                                                                                                                                                                                              1           2                                                                                                                                                                                                                                                                04/26/2016 16:06:43                                                                                                                                                                                                                                              GTSABHIJIT_abhijit.shelar_04262016160611           BPW1

Currently I am using for loop and get result

Final result require is 

Id,GroupName,Status,StartDateTime,totaltime
22,TEST_01,Completed,2016-04-26 16:03:59.000,64

Upvotes: 0

Views: 57

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Is this what you want -- replacing the id in the second query with the ids from the first?

SELECT sum(DATEDIFF(SECOND, StartTime, EndTime)) as totaltime
from MasterScheduleDetails 
where MasterScheduleId in (SELECT Id
                           from MasterSchedule  
                           Where Project = 'Sample' AND SubProject = 'Sample' AND
                                 StartDateTime between '2016-04-25' AND '2016-04-27'
                          );

Note: This fixes the conversions to nvarchar(). DATEDIFF() operates on date/datetime values, not on strings. It is absurd to convert a valid date/time to a string, just so it can be implicitly converted back to a date/time.

Upvotes: 1

Related Questions