CODEWITHSUNDEEP
sqlsql-server
RMBPMK
RMBPMK

Reputation: 95

How to get the count of records comparing days between two dates in SQL?

declare @UserId varchar(30);
declare @StartDate datetime;
declare @EndDate datetime;

set @UserId = 'E2';
set @StartDate = '2016-12-01';
set @EndDate = '2016-12-14';

select 
    COUNT((dueDate < finishDate) or finishDate = nul) as exceedTasks,   
    COUNT(taskID) as totalTasks 
from 
    Task
where 
    empId = @UserId 
    and startDate >= @StartDate 
    and dueDate <= @EndDate

I want get the count of tasks which are exceed dueDate.But this query has syntax error.

Upvotes: 1

Views: 386

Answers (1)

Rohit Gupta
Rohit Gupta

Reputation: 455

May be this because you have written nul instead of null. You can try this

declare @UserId varchar(30);
declare @StartDate datetime;
declare @EndDate datetime;

set @UserId = 'E2';
 set @StartDate = '2016-12-01';
   set @EndDate = '2016-12-14';

  select 
 COUNT((dueDate < finishDate) or finishDate = null) as exceedTasks,   
     COUNT(taskID) as totalTasks 
   from 
    Task
      where 
empId = @UserId 
and startDate >= @StartDate 
and dueDate <= @EndDate

Try This

 declare @UserId varchar(30);
declare @StartDate datetime;
 declare @EndDate datetime;

set @UserId = 'E2';
set @StartDate = '2016-12-01';
 set @EndDate = '2016-12-14';

 select 
 COUNT(CASE WHEN dueDate < finishDate THEN dueDate WHEN finishDate = null THEN dueDate END ) as exceedTasks,   
 COUNT(taskID) as totalTasks 
   from 
   Task
  where 
empId = @UserId 
and startDate >= @StartDate 
and dueDate <= @EndDate

Upvotes: 1

Related Questions