sql - joining select statements? -

i inner join in query don´t know how summarize 2 select statements 1 query.

this first query:

 select            machine           , eventdefinition         , duration         , sum(duration) on (partition 1) total         , duration/sum(duration) on (partition 1)*100 distribution         ,case when eventcategory < 0.05 'not incl' else 'ok' end under3min       (             select      systemname machine     , eventdefinition     , eventdate     , eventstartdatetime     , isnull(eventenddatetime, getdate()) eventenddatetime     , sum(cast(eventenddatetime - eventstartdatetime float))*24 duration     ,sum(case when customevent = 'without stop' cast(eventenddatetime - eventstartdatetime float)*24 else 0 end) eventcategory         tdatacategory                           eventdate >= @startdatetime         , eventdate <= @enddatetime         , systemname = '201' group systemname, eventdefinition, eventstartdatetime,  eventenddatetime, eventdefinition, eventdate, customevent     ) t     customevent <> 'without stop'          or (customevent = 'without stop' , t.eventcategory >=0.05)      group eventdefinition             , duration         ,machine         ,eventcategory 

output: , second query:

   select datavalue = case when prod = 0 0 else isnull(100.0 / prod * scrap, 0) end,      value = goodunits/theoreticalunits *100,          (         select intervaldate datevalue, intervaldateweek datum, tsystem.name name, productname product, teamname team,     sum(case when issuename in ('a1', 'a2') calculationunitsinitial else 0 end) scrap,     sum(case when issuename = 'prod' calculationunitsinitial else 0 end) prod,     sum(goodunits)   goodunits, sum(theoreticalunits) theoreticalunits,  tcount inner join tsystem on tcount.systemid = tsystem.id     intervaldate >=  dateadd(wk, datediff(wk, 1, getdate()), 0)   , intervaldate <= dateadd(wk, datediff(wk, 0, getdate()), 0) , ((datepart(dw, intervaldate) + @@datefirst) % 7) not in (0,1)     , tsystem.name = '201'      group intervaldate, tsystem.name, intervaldateweek, productname, teamname     ) s 

output:

i tried add in query 2 select statements. if output wrong. don´t know how should join 2 queries.

i calculation: distribution * (1 - value)

distribution first query , value second query

i assume first , second result set has joined based on a.machine=b.name

try this,

select a.distribution * (1 - b.value) (     select machine         ,eventdefinition         ,duration         ,sum(duration) on (partition 1) total         ,duration / sum(duration) on (partition 1) * 100 distribution         ,case              when eventcategory < 0.05                 'not incl'             else 'ok'             end under3min     (         select systemname machine             ,eventdefinition             ,eventdate             ,eventstartdatetime             ,isnull(eventenddatetime, getdate()) eventenddatetime             ,sum(cast(eventenddatetime - eventstartdatetime float)) * 24 duration             ,sum(case                      when customevent = 'without stop'                         cast(eventenddatetime - eventstartdatetime float) * 24                     else 0                     end) eventcategory         tdatacategory         eventdate >= @startdatetime             , eventdate <= @enddatetime             , systemname = '201'         group systemname             ,eventdefinition             ,eventstartdatetime             ,eventenddatetime             ,eventdefinition             ,eventdate             ,customevent         ) t     customevent <> 'without stop'         or (             customevent = 'without stop'             , t.eventcategory >= 0.05             )     group eventdefinition         ,duration         ,machine         ,eventcategory     ) inner join (     select datavalue = case              when prod = 0                 0             else isnull(100.0 / prod * scrap, 0)             end         ,value = goodunits / theoreticalunits * 100         ,name     (         select intervaldate datevalue             ,intervaldateweek datum             ,tsystem.name name             ,productname product             ,teamname team             ,sum(case                      when issuename in (                             'a1'                             ,'a2'                             )                         calculationunitsinitial                     else 0                     end) scrap             ,sum(case                      when issuename = 'prod'                         calculationunitsinitial                     else 0                     end) prod             ,sum(goodunits) goodunits             ,sum(theoreticalunits) theoreticalunits             ,         tcount         inner join tsystem on tcount.systemid = tsystem.id         intervaldate >= dateadd(wk, datediff(wk, 1, getdate()), 0)             , intervaldate <= dateadd(wk, datediff(wk, 0, getdate()), 0)             , ((datepart(dw, intervaldate) + @@datefirst) % 7) not in (                 0                 ,1                 )             , tsystem.name = '201'         group intervaldate             ,tsystem.name             ,intervaldateweek             ,productname             ,teamname         ) s     ) b on a.machine = b.name