Counting Between Dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Counting Between Dates

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2010-09-20 : 04:25:14

SQL 2008

CREATE TABLE [tbCBMBankHoliday](
[HolidayDate] [smalldatetime] NOT NULL
) ON [PRIMARY]

GO

INSERT INTO tbCBMBankHoliday
([HolidayDate])
VALUES
(CONVERT(smalldatetime, '02/04/2010', 103))

INSERT INTO tbCBMBankHoliday
([HolidayDate])
VALUES
(CONVERT(smalldatetime, '05/04/2010', 103))

Declare @Filedate smalldatetime

I need to know if @Filedate more than five days previous to getdate()
Saturday and Sundays are not included in the count, nor is any date in
tbCBMBankHoliday table
i.e
If Filedate = 01/04/2010 and GETDATE = '09/04/2010' then i need a return of
false
If Filedate = 09/04/2010 and GETDATE = '16/04/2010' then i need a return of
true

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-20 : 05:49:12

try this:


create table TBL_Vacation
(Name varchar(10)
,DateFrom smalldatetime
,DateTo smalldatetime
)

insert into TBL_Vacation
            select 'Vacation1',getdate()-16, getdate()+2
union all select 'Vacation2',getdate()-25, getdate()-13
union all select 'Vacation3',getdate()-48, getdate()-36
union all select 'Vacation4',getdate()-5, getdate()+12
union all select 'Vacation5',getdate()-53, getdate()+12


set datefirst 1;

with f_cte (Name, month_,day_,start_day,end_,diff,vacation)
as
(

      select
            Name
            ,month_ = month(DateFrom) 
            ,day_ = day(DateFrom)
            ,start_day = DateFrom
            ,end_ = DateTo
            ,diff = datediff(day, DateFrom, DateTo)
            ,vacation= case when datepart(dw,DateFrom) in (1,2,3,4,5) then 'work' else 'weekend' end
      from TBL_Vacation

union all
      select 
            Name
            ,mesec = month(start_day)
            ,dan = day(start_day)
            ,start_day = start_day + 1
            ,end_
            ,diff = diff - 1
            ,vacation =case when datepart(dw,start_day) in (1,2,3,4,5) then 'work' else 'weekend' end
      from
            f_cte
      where
            end_ >= start_day
      
)
select 
Name
,month(start_day) as MonthOf_vacation
,count(vacation) as DayOf_vacation

from f_cte
where diff >= 0
and vacation = 'work'
group by month(start_day) 
,Name
order by Name

parody
Posting Yak Master

111 Posts

Posted - 2010-09-20 : 06:05:38

was just working on something similar

declare @filedate date
declare @today date
set dateformat dmy
set @filedate = '01/04/2010'
set @today = '09/04/2010'

;with cte as
	(select 
		@filedate as filedate
		, case when datename(dw,@filedate) in ('Saturday','Sunday') or @filedate in (select [HolidayDate] from tbCBMBankHoliday) then 0 else 1 end as Cnt
	union all
	select 
		dateadd(dd,+1,filedate)
		,case when datename(dw,dateadd(dd,+1,filedate)) in ('Saturday','Sunday') or dateadd(dd,+1,filedate) in (select [HolidayDate] from tbCBMBankHoliday) then 0 else 1 end as Cnt
	from cte
	where filedate <= @today)	
, cte2 as 
	(select
		case when sum(cnt) > 5 then 'true' else 'false' end as result
	from cte)
	
select result from cte2
OPTION (MAXRECURSION 32767)

parody
Posting Yak Master

111 Posts

Posted - 2010-09-20 : 06:07:21

careful using datepart for the w/e ratehr than datename. part will change depending on datefirst setting.

parody
Posting Yak Master

111 Posts

Posted - 2010-09-20 : 06:12:07

actually this should be better performance if your dates table is large


declare @filedate date
declare @today date
set dateformat dmy
set @filedate = '09/04/2010'
set @today = '16/04/2010'

;with cte as
	(select 
		@filedate as filedate
	union all
	select 
		dateadd(dd,+1,filedate)
	from cte
	where filedate <= @today)	
,cte2 as
	(select 
			filedate
			,case when datename(dw,filedate) in ('saturday','sunday') or [HolidayDate] is not null then 0 else 1 end as cnt
		from cte a
			left join tbCBMBankHoliday b
				on a.filedate = b.[HolidayDate])
, cte3 as 
	(select
		case when sum(cnt) > 5 then 'true' else 'false' end as result
	from cte2)
	
select result from cte3
OPTION (MAXRECURSION 32767)

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-20 : 06:26:40

Create the following function and use like this:

DECLARE @Filedate smalldatetime
SET @Filedate ='2010-09-13'

SELECT dbo.fnDayDiffGreaterThanFive(@Filedate) as Result
--------- function def:
CREATE FUNCTION [dbo].[fnDayDiffGreaterThanFive]
(
@param_File_date smalldatetime
)
RETURNS bit
AS
BEGIN

DECLARE @Count INT
DECLARE @isGreater bit
DECLARE @file_date smalldatetime

SET @Count =0
SET @isGreater = 0
SET @file_date = @param_File_date

WHILE (@file_date < GETDATE()-1)
BEGIN
DECLARE @dayname VARCHAR(50)
SET @file_date= DATEADD(dd,1,@file_date)
SET @dayname = DATENAME(dw, @file_date)
--PRINT CAST(@file_date AS VARCHAR(50)) + ' ' + @dayname
IF NOT EXISTS(SELECT 1 FROM dbo.tbCBMBankHoliday WHERE HolidayDate = @file_date)
BEGIN

IF((@dayname <> 'Sunday') AND (@dayname <> 'Saturday'))
BEGIN
SET @Count = @Count + 1
--PRINT 'LEGAL'
END
END

END

IF (@Count > 5)
SET @isGreater = 1

RETURN @isGreater
END

Pete_N
Posting Yak Master

181 Posts

Posted - 2010-09-20 : 06:29:00

quote:

Originally posted by slimt_slimt

try this:


create table TBL_Vacation
(Name varchar(10)
,DateFrom smalldatetime
,DateTo smalldatetime
)

insert into TBL_Vacation
            select 'Vacation1',getdate()-16, getdate()+2
union all select 'Vacation2',getdate()-25, getdate()-13
union all select 'Vacation3',getdate()-48, getdate()-36
union all select 'Vacation4',getdate()-5, getdate()+12
union all select 'Vacation5',getdate()-53, getdate()+12


set datefirst 1;

with f_cte (Name, month_,day_,start_day,end_,diff,vacation)
as
(

      select
            Name
            ,month_ = month(DateFrom) 
            ,day_ = day(DateFrom)
            ,start_day = DateFrom
            ,end_ = DateTo
            ,diff = datediff(day, DateFrom, DateTo)
            ,vacation= case when datepart(dw,DateFrom) in (1,2,3,4,5) then 'work' else 'weekend' end
      from TBL_Vacation

union all
      select 
            Name
            ,mesec = month(start_day)
            ,dan = day(start_day)
            ,start_day = start_day + 1
            ,end_
            ,diff = diff - 1
            ,vacation =case when datepart(dw,start_day) in (1,2,3,4,5) then 'work' else 'weekend' end
      from
            f_cte
      where
            end_ >= start_day
      
)
select 
Name
,month(start_day) as MonthOf_vacation
,count(vacation) as DayOf_vacation

from f_cte
where diff >= 0
and vacation = 'work'
group by month(start_day) 
,Name
order by Name

Cheers for that, I can tweek this to my requirements

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-20 : 07:58:16

great. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources