CODEWITHSUNDEEP
sqlsql-serversql-server-2008
jay rathod
jay rathod

Reputation: 165

fetching null value with specify condition with left join

i have the following data in table leave:-

EMPNO   NAME    DATEFROM                   DATETO
111     xxx   2014-08-03  00:00:00.000    2014-09-05  00:00:00.000
222     yyy   2014-10-03  00:00:00.000    2014-10-05  00:00:00.000

but when i write below query :

select empno,name,sum(datediff(day,DATEFROM,DATETO)+1) as holiday 
from leave 
where DATEFROM>='2014-08-01' 
   and DATETO<='2014-08-31' 
   and empno=111 
group by name ,empno

then I didnt get any value..but with this query i want below output:

 EMPNO  NAME    holiday
  111   xxx      29

august has 31 days..

then how can i get?

it will only count the day of august(08) month

Upvotes: 1

Views: 55

Answers (1)

Dave C
Dave C

Reputation: 7392

In your query you are specifying DateTo MUST be <= 2014-08-31... but the DateTo value on your record is 2014-09-05. So that is excluding it.

I'm assuming you want all records that start in August, so you want DateFrom in both conditions.

Per your updates/comments:

declare @data table (empno int, name varchar(5), datefrom datetime, dateto datetime)
insert into @data
select 111,'xxx','2014-08-03 00:00:00.000','2014-09-05  00:00:00.000'

declare @maxdate datetime, @mindate datetime
set @mindate = '2014-08-01'
set @maxdate = '2014-08-31'

select empno,name,sum(datediff(day,DATEFROM,case when dateto > @maxdate then @maxdate else dateto end)+1) as holiday 
from @data 
where DATEFROM >=@mindate and DATEFROM <=@maxdate and empno=111 group by name ,empno

Per your last question regarding weekends. What you need to do is cross join a calendar table to the event, and omit the days you don't want. From there, instead of a datediff() you can simply count the remaining days.

declare @data table (empno int, name varchar(5), datefrom datetime, dateto datetime)
insert into @data
select 111,'xxx','2014-08-03 00:00:00.000','2014-09-05  00:00:00.000'

declare @maxdate datetime, @mindate datetime
set @mindate = '2014-08-01'
set @maxdate = '2014-08-31'

;with cal as
(
    select @mindate as dDate, datename(dw, @mindate) as DayWeek
    union all
    select dDate+1, datename(dw, dDate+1) as DayWeek
    from cal
    where dDate+1 <= @maxdate
)

select empno, name, count(*) as holiday
from @data d
cross join cal c
where c.ddate between d.datefrom and d.dateto
and c.dayweek not in ('saturday','sunday')
and DATEFROM >=@mindate and DATEFROM <=@maxdate and empno=111 
group by name ,empno

Upvotes: 1

Related Questions