Days falling in a certain period

I have 4 dates:
declare @.PeriodDateBegin smalldatetime
declare @.PeriodDateEnd smalldatetime
declare @.BeginDate smalldatetime
declare @.EndDate smalldatetime
Does anyone know a function that can give me the number of days that
fall in a period?
Example:
set @.PeriodDateBegin = '2005-01-01'
set @.PeriodDateEnd = '2005-01-31'
set @.BeginDate = '2005-01-16'
set @.EndDate = '2005-06-01'
The result should be 16.
The function should work quickly so I prefer not using a calendar
table, if this is possible.
Kind regards,
Stijn Verrept.Hi
Can you be more specific? Why 16?
How does four dates relate each other?
"Stijn Verrept" <stijn@.no_s.p.a.n.entrysoft.com> wrote in message
news:%23Y%23wqO9UFHA.2520@.TK2MSFTNGP09.phx.gbl...
> I have 4 dates:
> declare @.PeriodDateBegin smalldatetime
> declare @.PeriodDateEnd smalldatetime
> declare @.BeginDate smalldatetime
> declare @.EndDate smalldatetime
> Does anyone know a function that can give me the number of days that
> fall in a period?
> Example:
> set @.PeriodDateBegin = '2005-01-01'
> set @.PeriodDateEnd = '2005-01-31'
> set @.BeginDate = '2005-01-16'
> set @.EndDate = '2005-06-01'
> The result should be 16.
> The function should work quickly so I prefer not using a calendar
> table, if this is possible.
>
> --
> Kind regards,
> Stijn Verrept.|||Uri Dimant wrote:

> Hi
> Can you be more specific? Why 16?
> How does four dates relate each other?
Of course: you have two timespans: the one from @.PeriodDateBegin till
@.PeriodDateEnd and the one from @.BeginDate till @.EndDate. 16 is the
number of days that overlap.
I hope it is clear now.
Kind regards,
Stijn Verrept.|||Hi
A calendar table is often the fastest solution when working with
dates/periods, therefore you may not get the quickest solution without
trying that option.
John
"Stijn Verrept" <stijn@.no_s.p.a.n.entrysoft.com> wrote in message
news:%23Y%23wqO9UFHA.2520@.TK2MSFTNGP09.phx.gbl...
>I have 4 dates:
> declare @.PeriodDateBegin smalldatetime
> declare @.PeriodDateEnd smalldatetime
> declare @.BeginDate smalldatetime
> declare @.EndDate smalldatetime
> Does anyone know a function that can give me the number of days that
> fall in a period?
> Example:
> set @.PeriodDateBegin = '2005-01-01'
> set @.PeriodDateEnd = '2005-01-31'
> set @.BeginDate = '2005-01-16'
> set @.EndDate = '2005-06-01'
> The result should be 16.
> The function should work quickly so I prefer not using a calendar
> table, if this is possible.
>
> --
> Kind regards,
> Stijn Verrept.|||declare @.PeriodDateEnd smalldatetime
declare @.BeginDate smalldatetime
declare @.EndDate smalldatetime
set @.PeriodDateBegin = '2005-01-01'
set @.PeriodDateEnd = '2005-01-31'
set @.BeginDate = '2005-01-16'
set @.EndDate = '2005-06-01'
Select DateDiff(day,
Case When @.PeriodDateBegin > @.BeginDate
Then @.PeriodDateBegin Else @.BeginDate End,
Case When @.PeriodDateEnd < @.EndDate
Then @.PeriodDateEnd Else @.EndDate End)
+ 1 -- to count days inclusively and generate 16...
"Stijn Verrept" wrote:

> Uri Dimant wrote:
>
> Of course: you have two timespans: the one from @.PeriodDateBegin till
> @.PeriodDateEnd and the one from @.BeginDate till @.EndDate. 16 is the
> number of days that overlap.
> I hope it is clear now.
>
> --
> Kind regards,
> Stijn Verrept.
>|||The + 1 is because the datediff() function actually counts date "boundaries"
between the two specified datetimes
"CBretana" wrote:
> declare @.PeriodDateEnd smalldatetime
> declare @.BeginDate smalldatetime
> declare @.EndDate smalldatetime
> set @.PeriodDateBegin = '2005-01-01'
> set @.PeriodDateEnd = '2005-01-31'
> set @.BeginDate = '2005-01-16'
> set @.EndDate = '2005-06-01'
> Select DateDiff(day,
> Case When @.PeriodDateBegin > @.BeginDate
> Then @.PeriodDateBegin Else @.BeginDate End,
> Case When @.PeriodDateEnd < @.EndDate
> Then @.PeriodDateEnd Else @.EndDate End)
> + 1 -- to count days inclusively and generate 16...
> "Stijn Verrept" wrote:
>|||CBretana wrote:

> The + 1 is because the datediff() function actually counts date
> "boundaries" between the two specified datetimes
Great thanks a lot!!! I believe this will indeed go faster than
working with calendar table.
Kind regards,
Stijn Verrept.