Missy Missy - 4 months ago 10
SQL Question

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.

Answer

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