sql server - Only one expression can be specified in the select list when the subquery is not introduced with EXISTS nested case statements -

i trying create query gets number of hours event open below query. using case statements because needs take account count weekdays. step in process overall goal hours days. example if the days more 1 count days , multiply 8.. if less 1 datediff hours , hours day.. appreciated!

but getting following error:

only 1 expression can specified in select list when subquery not introduced exists.

declare @workdays int select creationdatetime,         closeddatetime,datediff(dd, creationdatetime, closeddatetime)+1,        case when (datediff(dd, creationdatetime, closeddatetime)+1 > 1)             (                   select creationdatetime ,closeddatetime,                      ((datediff(dd, creationdatetime, closeddatetime)+1)                       -(datediff(wk, creationdatetime, closeddatetime) * 2)                       -(case when datename(dw, creationdatetime)                         = 'sunday' 1 else 0 end)                       -(case when datename(dw, closeddatetime)                          = 'saturday' 1 else 0 end)                   )*8 workdayhours                   table.ofevents closeddatetime not null) end table.ofevents closeddatetime not null 

as error said, cannot select more 1 column value. when doing select statement:

select a, b, c ... 

each expression a, b, c, represents column of data returned. column composed of values have data type integer or double or varchar.

so when do:

select a, b, (select c, d ...) ... 

you saying "i want column of as, column of bs, , column of ..." oops. broke sql because doesn't know how put multiple values in single cell that. (well, sql variants know how that, not simple.)

what want is:

select a, b, (select c ...), (select d ...) etc. 

so, means this:

declare @workdays int    select creationdatetime        , closeddatetime        , datediff(dd, creationdatetime, closeddatetime)+1        , case when (datediff(dd, creationdatetime, closeddatetime)+1 > 1)                       (select creationdatetime               workdayhours               table.ofevents closeddatetime not null)           end        , case when (datediff(dd, creationdatetime, closeddatetime)+1 > 1)                       (select closeddatetime               workdayhours               table.ofevents closeddatetime not null)           end        , case when (datediff(dd, creationdatetime, closeddatetime)+1 > 1)           ( (datediff(dd, creationdatetime, closeddatetime)+1 )                 -(datediff(wk, creationdatetime, closeddatetime) * 2)                 -(case when datename(dw, creationdatetime) = 'sunday' 1 else 0 end)                 -(case when datename(dw, closeddatetime) = 'saturday' 1 else 0 end))*8               workdayhours               table.ofevents closeddatetime not null)           end     table.ofevents closeddatetime not null 

see how each subquery selects single value?

but, still not see why need select "creationdatetime" , "closedatetime", can remove columns.

one last piece of advice, because see in people's sql related questions. ask yourself, deeply, thoroughly, why want sql something. why did type:

   (select creationdatetime ,closeddatetime,       ( (datediff(dd, creationdatetime, closeddatetime)+1 ) 

why creationdatetime there right before closedatetime, mean? sql declarative language, write down intent , job of database produce data matches intent. unlike programming languages imperative, c. in c, write down how something. makes understanding sql difficult people understand imperative languages. so, ask yourself, why did create subquery, goal?

if answer not suffice, please edit question clarify want result query look like. that's important. if don't tell expect query return, how can expect tell database that, or expect tell how tell database that?