Count with SQL SubQuery in From Clause Not Working

Question

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.

objectNotFound · Accepted Answer · 2016-07-19T23:53:48.330

1

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

edited Jul 19 '16 at 23:53

answered Jul 19 '16 at 23:49

objectNotFound

1,683
2
18
25

OMG -- I was on this for hours. Thank you SO SO much!!!! I didn't know you could put distinct in count. Your last solution is very elegant. Thank you. – Missy Jul 19 '16 at 23:53
1

@Missy you are most welcome ... just check the edits I added another alternative at the end which might work better. – objectNotFound Jul 19 '16 at 23:55

score 1 · Answer 2 · answered Jul 19 '16 at 23:51

The main problem is the lack of parentheses around the subquery.

Here are some fixes to the code:

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;

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

Changes:

Put the @tmprocdays = in the select statement itself. Your subquery is missing parentheses.
Added semicolons to the end of each line.
Removed the subquery, using count(distinct) instead.
Changed the date to YYYY-MM-DD format. This is at least an ISO standard.

The last three are cosmetic.

score 1 · Answer 3 · answered Jul 19 '16 at 23:51

The from clause must always contain either a table name, or in the case of a subquery it must be aliased:

If you add any identifier (word of letter) after your subquery it will work fine, eg:

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

Count with SQL SubQuery in From Clause Not Working

3 Answers3