sqlsql-serverscalar-subquery

Count with SQL SubQuery in From Clause Not Working

I am trying to get the number of workdays over a range of dates and assign it to a variable and I can't seem to get it. I have tried just my subquery and it works fine to get me the distinct dates but I need a count of them. Here is the code that I wish would work:

 declare @end date='2016/05/06' 
 declare @begin date = DATEADD(month, DATEDIFF(month, 0, @end), 0) 
 declare @begin31 date = DATEADD(MONTH, -1, @end)

 declare @tmprocdays int

  @tmprocdays = select count(*) from (select distinct WORKDATE from Daily where WORKDATE between @begin and @end)    <<<-----  NOT WORKING

The error says the problem is near the ) I've tried putting the while thing in parenthesis and that didn't work either.

Solution

  • try this 

    declare @end date='2016/05/06' 
 declare @begin date = DATEADD(month, DATEDIFF(month, 0, @end), 0) 
 declare @begin31 date = DATEADD(MONTH, -1, @end)

 declare @tmprocdays int

 set  @tmprocdays = select count(*) from (select distinct WORKDATE from Daily where WORKDATE between @begin and @end) a   <<<-----  NOT WORKING

    OR Replace the last 2 lines above to this:

    select @tmprocdays = count(*) 
from 
(
  select distinct WORKDATE from Daily where WORKDATE between @begin and @end
) a

    OR even better

      select  @tmprocdays = count(distinct WORKDATE) from Daily where WORKDATE between @begin and @end