How to get next date data for a Begin and End date

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 2005 Forums

Transact-SQL (2005)

How to get next date data for a Begin and End date

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-11-29 : 11:40:33

Hi,
I am using SQL 2005.

I have a Period Table which has data stored for the below Begin and End Dates in datetime format.

I have @FromDate and @EndDate as datetime parameters.

If @FromDate = 12/01/2010 and @EndDate = 12/31/2010 then I want to get data for teh next period which is 2011/01/01 to 2011/01/01. The next priod indicates increment date parmaters by one month.

I have three cases: Only Case 1 works so far

--CASE 1 Need to get data for 02/01/2010 to 02/28/2010
declare @FromDate datetime
declare @EndDate datetime
set @FromDate = '01/01/2010'
set @EndDate = '01/31/2010'
select * from Period
where PshipID = 9
and month(BeginDate) = month(@FromDate) + 1
and year(BeginDate) = year(@FromDate)
and month(EndDate) = month(@EndDate) + 1

I cannot eliminate the rows for Feb and March for case 3. When the date is 12/01/2010, I am unable to figure out how to move to next calendar month.

--CASE 3 Need to get data for 01/01/2011 to 01/01/2011
declare @FromDate datetime
declare @EndDate datetime
set @FromDate = '12/01/2010'
set @EndDate = '12/31/2010'
select * from Period
where PshipID = 9
and year(BeginDate) > year(@FromDate)

--CASE 2 Need to get data for 02/01/2010 to 01/01/2011
declare @FromDate datetime
declare @EndDate datetime
set @FromDate = '01/01/2010'
set @EndDate = '12/31/2010'
select * from Period
where PshipID = 9
and month(BeginDate) = month(@FromDate) + 1
and year(BeginDate) = year(@FromDate)

PshipID Period BeginDate EndDate
9 40 2010-01-01 00:00:00.000 2010-01-31 00:00:00.000
9 41 2010-02-01 00:00:00.000 2010-02-28 00:00:00.000
9 42 2010-03-01 00:00:00.000 2010-03-31 00:00:00.000
9 43 2010-04-01 00:00:00.000 2010-04-30 00:00:00.000
9 44 2010-05-01 00:00:00.000 2010-05-31 00:00:00.000
9 45 2010-06-01 00:00:00.000 2010-06-30 00:00:00.000
9 46 2010-07-01 00:00:00.000 2010-07-31 00:00:00.000
9 47 2010-08-01 00:00:00.000 2010-08-31 00:00:00.000
9 48 2010-09-01 00:00:00.000 2010-09-30 00:00:00.000
9 49 2010-10-01 00:00:00.000 2010-10-31 00:00:00.000
9 50 2010-11-01 00:00:00.000 2010-11-30 00:00:00.000
9 51 2010-12-01 00:00:00.000 2010-12-31 00:00:00.000
9 52 2011-01-01 00:00:00.000 2011-01-01 00:00:00.000
9 53 2011-01-02 00:00:00.000 2011-02-28 00:00:00.000
9 54 2011-03-01 00:00:00.000 2011-03-31 00:00:00.000

bobmcclellan
Starting Member

46 Posts

Posted - 2010-11-29 : 12:09:40

google for dates table sql.
An easy way to accomplish what you are looking for is to use
a dates table. then just use Min(date), Max(date) where month = month(FromDate).
Target month can be current month + 1
hth,
..bob

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-11-29 : 13:13:10

Thanks bob.

I am asked to use the Period table which has the Begin and End dates fro each unique Period. If I want to use a case statement to check for @FromDate and @EndDate parameter values whether they are equal to the BeginDate and EndDate from the Period table and also check whether Period exists for the next month, then I want to set the @FromDate and @EndDate values to move up one month and the function that uses the date parameters should use teh new @FromDate and @ToDate.

In the below example, @FromDate should be set to 02/01/2010 if the case is true and @EndDate should be set to 02/28/2010 if case is true. How can I use the below syntax and achieve this using CASE.
example

--CASE 1
declare @FromDate datetime
declare @EndDate datetime
declare @CustBool BIT
set @CustBool = 1
set @FromDate = '01/01/2010'
set @EndDate = '01/31/2010'

SELECT
TS.PshipID

CASE WHEN @CustBool1 = 1 AND month(p.BeginDate) = month(@FromDate) AND year(p.BeginDate) = year(@FromDate) AND month(p.EndDate) = month(@EndDate) THEN TS.GrossAmount ELSE 0
END END)WDGrossAmountNext

FROM fAxpDVTransactionSummary(@SessionID, @ReportID, @FromDate, @ToDate, @Status, @Type,
@Warnings, @ShowZeroCashFlow, @ShowClosingMethodDetail, @SidePocketCapitalOption) TS

LEFT JOIN PshipInfo psi ON TS.PshipID = psi.PshipID
LEFT JOIN Period p ON TS.PshipID = p.PshipID

WHERE Status not in ('Pending', 'Rejected')
GROUP BY TS.PshipID, TS.ClassID, TS.SeriesID, TS.SubEntityID,TS.EntityID

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-11-29 : 13:44:17

For the below using CASE, I am getting the next date value of 02/01/2010 for the right Period, but I am also getting the other dates for teh actual value of @FromDate. I am also not sure how to set the date value to @FromDate within the case.

--CASE 1
declare @FromDate datetime
declare @EndDate datetime
declare @CustBool BIT
set @CustBool = 1
set @FromDate = '01/01/2010'
set @EndDate = '01/31/2010'
--select *, p.BeginDate AS FromDate,p.EndDate AS EndDate
select
case when @CustBool = 1 and month(p.BeginDate) = month(@FromDate) + 1 and month(p.EndDate) = month(@EndDate) + 1
and year(p.BeginDate) = year(@FromDate) and Period <= max(p.Period)then p.BeginDate ELSE @FromDate END

from Period p
where PshipID = 9
group by p.BeginDate,p.EndDate, p.Period

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-11-29 : 13:57:02

Using a WHERE clause, I am able to get the single result set with the FromDate and EndDate which I need for the next period that is increment by a month but I am unable to get a single row using case..

--CASE 1
declare @FromDate datetime
declare @EndDate datetime
declare @CustBool BIT
set @CustBool = 1
set @FromDate = '01/01/2010'
set @EndDate = '01/31/2010'
select *, p.BeginDate AS FromDate,p.EndDate AS EndDate
from Period p
where PshipID = 9
and month(BeginDate) = month(@FromDate) + 1
and year(BeginDate) = year(@FromDate)
and month(EndDate) = month(@EndDate) + 1

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-11-29 : 14:01:09

I used distinct and this gave me two result sets. I need just one for the FromDate which should be 2010-02-01 00:00:00.000

2010-01-01 00:00:00.000
2010-02-01 00:00:00.000

--CASE 1
declare @FromDate datetime
declare @EndDate datetime
declare @CustBool BIT
set @CustBool = 1
set @FromDate = '01/01/2010'
set @EndDate = '01/31/2010'
--select *, p.BeginDate AS FromDate,p.EndDate AS EndDate
select distinct
case when month(p.BeginDate) = month(@FromDate) + 1 and month(p.EndDate) = month(@EndDate) + 1
and year(p.BeginDate) = year(@FromDate) and Period <= max(p.Period)then p.BeginDate ELSE @FromDate END

from Period p
where PshipID = 9
group by p.BeginDate,p.EndDate, p.Period

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-11-29 : 14:02:54

Please post what your expected results are for each case. For case 3, 01/01/2011 to 01/01/2011 should that return 0 records or just records = '01/01/2011'? I don't think you need this much complexity.

select *, dateadd(month,1,@FromDate),dateadd(month,1,@EndDate)
from @period

where PshipID = 9 and
BeginDate >= dateadd(month,1,@FromDate) and beginDate <= dateadd(month,1,@EndDate)

Jim

Everyday I learn something that somebody else already knew

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-11-29 : 14:11:01

Jim

For CASE 1 when @FromDate = '01/01/2010' and @EndDate = '01/31/2010', if @CusBool1 = 1, I want the @FromDate to be set to 02/01/2010 and @EndDate to be set to 02/28/2010 and the function that I join to will use these parameter values for the FromDate and EndDate.

For CASE 2 when @FromDate = '01/01/2010' and @EndDate = '12/31/2010' I want the @FromDate to be set to 02/01/2010 and @EndDate to be set to 01/01/2011 and the function that I join to will use these parameter values for the FromDate and EndDate.

Your below query works fine with the WHERE clause. If I need to use this with a CASE, I am unable to convert to CASE and get the same single result set. I also tried your query and I get two rows of data when I just want the next month 2010-02-01 00:00:00.000

2010-01-01 00:00:00.000
2010-02-01 00:00:00.000

--CASE 1
declare @FromDate datetime
declare @EndDate datetime
declare @CustBool BIT
set @CustBool = 1
set @FromDate = '01/01/2010'
set @EndDate = '01/31/2010'

select *, dateadd(month,1,@FromDate),dateadd(month,1,@EndDate)
from Period

where PshipID = 9 and
BeginDate >= dateadd(month,1,@FromDate) and BeginDate <= dateadd(month,1,@EndDate)

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-11-29 : 14:20:08

I used a temp table variable. The results is exactly how I want it. Now I would need to additionally join to this table in the case statement when I do not want to use the @FronDate and @EndDate parameters passed but instead I want to use the dates for the next period. I will test further but I believe this is close. Thanks.

FromDate EndDate
2010-02-01 00:00:00.000 2010-02-28 00:00:00.000

--CASE 1
declare @FromDate datetime
declare @EndDate datetime
declare @CustBool BIT
set @CustBool = 1
set @FromDate = '01/01/2010'
set @EndDate = '01/31/2010'

DECLARE @DateParameter TABLE (FromDate datetime, EndDate datetime)

INSERT INTO @DateParameter select dateadd(month,1,@FromDate),dateadd(month,1,@EndDate)
from Period

where PshipID = 9 and
BeginDate >= dateadd(month,1,@FromDate) and BeginDate <= dateadd(month,1,@EndDate)
select * from @DateParameter

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-11-29 : 14:26:59

[code]
declare @FromDate datetime
declare @EndDate datetime
declare @cusbool tinyint
set @FromDate = '20100101'
set @EndDate = '20100131'
set @cusbool = 1

IF @cusBool = 1 and @fromdate = '20100101' and @endDate = '20100131'
BEGIN
SET @fromDate = dateadd(month,1,@fromDate)
SET @endDate = dateadd(month,1,@endDate)
END
IF @cusBool = 1 and @fromdate = '20100101' and @endDate = '20101231'
BEGIN
SET @fromDate = dateadd(month,1,@fromDate)
SET @endDate = dateadd(year,1,@fromDate)
END
[/code]

I'm still not clear on case 3 (or case 1 and 2 for that matter) does @cusbool come in to play for case 3? I assumed @cusbool = 1 for case 2

Jim

Jim

Everyday I learn something that somebody else already knew

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-11-29 : 14:32:00

Jim,

@CustBool = 1 comes for all three cases, different date ranges yes. I am having an issue where I am getting many rows. Using a distinct helps but when I join the table variable to the function in my stored procedure, I do not want to use DISTINCT. Is deleteing all rows and leaving just one row an efficient approach. Or is using Distinct the only solution?

--CASE 1
declare @FromDate datetime
declare @EndDate datetime
declare @CustBool BIT
set @CustBool = 1
set @FromDate = '01/01/2010'
set @EndDate = '12/31/2010'

DECLARE @DateParameter TABLE (FromDate datetime, EndDate datetime)

INSERT INTO @DateParameter select dateadd(month,1,@FromDate),dateadd(month,1,@EndDate)
from Period

where PshipID = 9 and
BeginDate >= dateadd(month,1,@FromDate) and BeginDate <= dateadd(month,1,@EndDate)
select FromDate, EndDate from @DateParameter

FromDate EndDate
2010-02-01 00:00:00.000 2011-01-31 00:00:00.000
2010-02-01 00:00:00.000 2011-01-31 00:00:00.000
2010-02-01 00:00:00.000 2011-01-31 00:00:00.000
2010-02-01 00:00:00.000 2011-01-31 00:00:00.000
2010-02-01 00:00:00.000 2011-01-31 00:00:00.000
2010-02-01 00:00:00.000 2011-01-31 00:00:00.000
2010-02-01 00:00:00.000 2011-01-31 00:00:00.000
2010-02-01 00:00:00.000 2011-01-31 00:00:00.000
2010-02-01 00:00:00.000 2011-01-31 00:00:00.000
2010-02-01 00:00:00.000 2011-01-31 00:00:00.000
2010-02-01 00:00:00.000 2011-01-31 00:00:00.000
2010-02-01 00:00:00.000 2011-01-31 00:00:00.000
2010-02-01 00:00:00.000 2011-01-31 00:00:00.000

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-11-29 : 14:40:41

I think I will use the distinct when I insert into the table variable. I will then join the table variable to the function and grab the FromDate and ToDate when the case statement is satisfied. Thanks.

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-11-29 : 14:53:57

Jim,

I have to get the Dates from a table which we call Period. The structure of the Period table is
as below.

So I will need to tweak the query again. So for example, if @CustBool1 = 1 then I need to set teh FromDate to the BeginDate in the Period table for Period 41. Similarly, I need to set the EndDate to the EndDate from the Period table where the Period is 41. Period values are unique for each PshipID.

PshipID Period BeginDate EndDate
9 40 1/1/2010 1/31/2010
9 41 2/1/2010 2/28/2010
9 42 3/1/2010 3/31/2010
9 43 4/1/2010 4/30/2010

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-11-29 : 15:15:26

I tried the below but it does not give me a single row as before: I might be missing something in the where clause...

declare @FromDate datetime
declare @EndDate datetime
declare @CustBool BIT
set @CustBool = 1
set @FromDate = '01/01/2010'
set @EndDate = '12/31/2010'

select distinct p.BeginDate,p.EndDate, p.Period
from Period p
where PshipID = 9 and
p.BeginDate >= dateadd(month,1,@FromDate) and p.BeginDate <= dateadd(month,1,@EndDate)

BeginDate EndDate Period
2010-02-01 00:00:00.000 2010-02-28 00:00:00.000 41
2010-03-01 00:00:00.000 2010-03-31 00:00:00.000 42
2010-04-01 00:00:00.000 2010-04-30 00:00:00.000 43
2010-05-01 00:00:00.000 2010-05-31 00:00:00.000 44
2010-06-01 00:00:00.000 2010-06-30 00:00:00.000 45
2010-07-01 00:00:00.000 2010-07-31 00:00:00.000 46
2010-08-01 00:00:00.000 2010-08-31 00:00:00.000 47
2010-09-01 00:00:00.000 2010-09-30 00:00:00.000 48
2010-10-01 00:00:00.000 2010-10-31 00:00:00.000 49
2010-11-01 00:00:00.000 2010-11-30 00:00:00.000 50
2010-12-01 00:00:00.000 2010-12-31 00:00:00.000 51
2011-01-01 00:00:00.000 2011-01-01 00:00:00.000 52
2011-01-02 00:00:00.000 2011-02-28 00:00:00.000 53

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-11-29 : 15:24:57

Using the below query, I get one row but the EndDate is wrong. I need 2011-01-01 00:00:00.000 as teh EndDate

BeginDate EndDate Period
2010-02-01 00:00:00.000 2010-02-28 00:00:00.000 41

--CASE 1
declare @FromDate datetime
declare @EndDate datetime
declare @CustBool BIT
set @CustBool = 1
set @FromDate = '01/01/2010'
set @EndDate = '12/31/2010'

select distinct p.BeginDate,p.EndDate, p.Period
from Period p
where PshipID = 9 and
p.BeginDate = dateadd(month,1,@FromDate) and p.EndDate <= dateadd(month,1,@EndDate)

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-11-29 : 15:29:55

The script when selecting the dates from the Period table works fine for dates 01/01/2010 to 01/31/2010 and 12/01/2010 to 12/31/2010.

However why does it not work for dates 01/01/2010 to 12/31/2010 , I am not sure.

--CASE 1
declare @FromDate datetime
declare @EndDate datetime
declare @CustBool BIT
set @CustBool = 1
set @FromDate = '12/01/2010'
set @EndDate = '12/31/2010'

select distinct p.BeginDate,p.EndDate, p.Period
from Period p
where PshipID = 9 and
p.BeginDate >= dateadd(month,1,@FromDate) and p.EndDate <= dateadd(month,1,@EndDate)

BeginDate EndDate Period
2011-01-01 00:00:00.000 2011-01-01 00:00:00.000 52

--CASE 1
declare @FromDate datetime
declare @EndDate datetime
declare @CustBool BIT
set @CustBool = 1
set @FromDate = '01/01/2010'
set @EndDate = '01/31/2010'

select distinct p.BeginDate,p.EndDate, p.Period
from Period p
where PshipID = 9 and
p.BeginDate >= dateadd(month,1,@FromDate) and p.EndDate <= dateadd(month,1,@EndDate)

BeginDate EndDate Period
2010-02-01 00:00:00.000 2010-02-28 00:00:00.000 41

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-11-29 : 16:05:47

Below is the entire result for Period and note that there is no EndDate 01/31/2011 existing in the table

select p.BeginDate,p.EndDate, p.Period
from Period p
where PshipID = 9

BeginDate EndDate Period
2006-10-01 00:00:00.000 2006-10-31 00:00:00.000 1
2006-11-01 00:00:00.000 2006-11-30 00:00:00.000 2
2006-12-01 00:00:00.000 2006-12-31 00:00:00.000 3
2007-01-01 00:00:00.000 2007-01-31 00:00:00.000 4
2007-02-01 00:00:00.000 2007-02-28 00:00:00.000 5
2007-03-01 00:00:00.000 2007-03-31 00:00:00.000 6
2007-04-01 00:00:00.000 2007-04-30 00:00:00.000 7
2007-05-01 00:00:00.000 2007-05-31 00:00:00.000 8
2007-06-01 00:00:00.000 2007-06-30 00:00:00.000 9
2007-07-01 00:00:00.000 2007-07-31 00:00:00.000 10
2007-08-01 00:00:00.000 2007-08-31 00:00:00.000 11
2007-09-01 00:00:00.000 2007-09-30 00:00:00.000 12
2007-10-01 00:00:00.000 2007-10-31 00:00:00.000 13
2007-11-01 00:00:00.000 2007-11-30 00:00:00.000 14
2007-12-01 00:00:00.000 2007-12-31 00:00:00.000 15
2008-01-01 00:00:00.000 2008-01-31 00:00:00.000 16
2008-02-01 00:00:00.000 2008-02-29 00:00:00.000 17
2008-03-01 00:00:00.000 2008-03-31 00:00:00.000 18
2008-04-01 00:00:00.000 2008-04-30 00:00:00.000 19
2008-05-01 00:00:00.000 2008-05-31 00:00:00.000 20
2008-06-01 00:00:00.000 2008-06-30 00:00:00.000 21
2008-07-01 00:00:00.000 2008-07-31 00:00:00.000 22
2008-08-01 00:00:00.000 2008-08-31 00:00:00.000 23
2008-09-01 00:00:00.000 2008-09-30 00:00:00.000 24
2008-10-01 00:00:00.000 2008-10-31 00:00:00.000 25
2008-11-01 00:00:00.000 2008-11-30 00:00:00.000 26
2008-12-01 00:00:00.000 2008-12-31 00:00:00.000 27
2009-01-01 00:00:00.000 2009-01-31 00:00:00.000 28
2009-02-01 00:00:00.000 2009-02-28 00:00:00.000 29
2009-03-01 00:00:00.000 2009-03-31 00:00:00.000 30
2009-04-01 00:00:00.000 2009-04-30 00:00:00.000 31
2009-05-01 00:00:00.000 2009-05-31 00:00:00.000 32
2009-06-01 00:00:00.000 2009-06-30 00:00:00.000 33
2009-07-01 00:00:00.000 2009-07-31 00:00:00.000 34
2009-08-01 00:00:00.000 2009-08-31 00:00:00.000 35
2009-09-01 00:00:00.000 2009-09-30 00:00:00.000 36
2009-10-01 00:00:00.000 2009-10-31 00:00:00.000 37
2009-11-01 00:00:00.000 2009-11-30 00:00:00.000 38
2009-12-01 00:00:00.000 2009-12-31 00:00:00.000 39
2010-01-01 00:00:00.000 2010-01-31 00:00:00.000 40
2010-02-01 00:00:00.000 2010-02-28 00:00:00.000 41
2010-03-01 00:00:00.000 2010-03-31 00:00:00.000 42
2010-04-01 00:00:00.000 2010-04-30 00:00:00.000 43
2010-05-01 00:00:00.000 2010-05-31 00:00:00.000 44
2010-06-01 00:00:00.000 2010-06-30 00:00:00.000 45
2010-07-01 00:00:00.000 2010-07-31 00:00:00.000 46
2010-08-01 00:00:00.000 2010-08-31 00:00:00.000 47
2010-09-01 00:00:00.000 2010-09-30 00:00:00.000 48
2010-10-01 00:00:00.000 2010-10-31 00:00:00.000 49
2010-11-01 00:00:00.000 2010-11-30 00:00:00.000 50
2010-12-01 00:00:00.000 2010-12-31 00:00:00.000 51
2011-01-01 00:00:00.000 2011-01-01 00:00:00.000 52
2011-01-02 00:00:00.000 2011-02-28 00:00:00.000 53
2011-03-01 00:00:00.000 2011-03-31 00:00:00.000 54

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-11-29 : 16:21:42

The date ranges could be given as

01/01/2010 to 06/30/2010 and then FromDate and EndDate in this case when @CustBool1 = 1 shoul dbe

02/01/2010 and 07/31/2010.

I am not able to get the EndDate correct.

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-11-29 : 17:47:18

I still had no luck getting the correct data. Any help is greatly appreciated

bobmcclellan
Starting Member

46 Posts

Posted - 2010-11-29 : 21:29:40

Sqlnovice123,
Please post Create Table DDL along with some Insert SQL for sample data.
Also, Please include the desired result in the same row.
Maybe columns DesiredBeginDate and desiredEndDate.
Are you simply trying to count the days in the current date range and then
Start the next date range as NewFromDate = EndDate + 1,
and make NewEndDate something like... = dateadd(d,datediff(FromDate, EndDate),EndDate + 1) ?
Sorry for the additional questions. I am having a hard time following what your desired result should
look like. The fact that some end dates start at the beginning of the month and some at the end is
prompting my question.
thanks.
..bob

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-29 : 21:58:59

I am not sure where filter pShipID = 9 comes from, but the where clause works fine if the pShipID is correct. The sample data doesn't correspond to the right query. What does @CustBool represent?

You have to remember that 2/28/2010 + 1 month = 3/28/2010 in the dateadd results too...so your date formula may need some work.


Drop Table Period
go
Create table Period(BeginDate datetime not null, Enddate datetime not null,Period int not null)

GO
Insert into Period
SELECT	'20061001','20061031',1		UNION ALL
SELECT	'20061101','20061130',2		UNION ALL
SELECT	'20061201','20061231',3		UNION ALL
SELECT	'20070101','20070131',4		UNION ALL
SELECT	'20070201','20070228',5		UNION ALL
SELECT	'20070301','20070331',6		UNION ALL
SELECT	'20070401','20070430',7		UNION ALL
SELECT	'20070501','20070531',8		UNION ALL
SELECT	'20070601','20070630',9		UNION ALL
SELECT	'20070701','20070731',10		UNION ALL
SELECT	'20070801','20070831',11		UNION ALL
SELECT	'20070901','20070930',12		UNION ALL
SELECT	'20071001','20071031',13		UNION ALL
SELECT	'20071101','20071130',14		UNION ALL
SELECT	'20071201','20071231',15		UNION ALL
SELECT	'20080101','20080131',16		UNION ALL
SELECT	'20080201','20080229',17		UNION ALL
SELECT	'20080301','20080331',18		UNION ALL
SELECT	'20080401','20080430',19		UNION ALL
SELECT	'20080501','20080531',20		UNION ALL
SELECT	'20080601','20080630',21		UNION ALL
SELECT	'20080701','20080731',22		UNION ALL
SELECT	'20080801','20080831',23		UNION ALL
SELECT	'20080901','20080930',24		UNION ALL
SELECT	'20081001','20081031',25		UNION ALL
SELECT	'20081101','20081130',26		UNION ALL
SELECT	'20081201','20081231',27		UNION ALL
SELECT	'20090101','20090131',28		UNION ALL
SELECT	'20090201','20090228',29		UNION ALL
SELECT	'20090301','20090331',30		UNION ALL
SELECT	'20090401','20090430',31		UNION ALL
SELECT	'20090501','20090531',32		UNION ALL
SELECT	'20090601','20090630',33		UNION ALL
SELECT	'20090701','20090731',34		UNION ALL
SELECT	'20090801','20090831',35		UNION ALL
SELECT	'20090901','20090930',36		UNION ALL
SELECT	'20091001','20091031',37		UNION ALL
SELECT	'20091101','20091130',38		UNION ALL
SELECT	'20091201','20091231',39		UNION ALL
SELECT	'20100101','20100131',40		UNION ALL
SELECT	'20100201','20100228',41		UNION ALL
SELECT	'20100301','20100331',42		UNION ALL
SELECT	'20100401','20100430',43		UNION ALL
SELECT	'20100501','20100531',44		UNION ALL
SELECT	'20100601','20100630',45		UNION ALL
SELECT	'20100701','20100731',46		UNION ALL
SELECT	'20100801','20100831',47		UNION ALL
SELECT	'20100901','20100930',48		UNION ALL
SELECT	'20101001','20101031',49		UNION ALL
SELECT	'20101101','20101130',50		UNION ALL
SELECT	'20101201','20101231',51		UNION ALL
SELECT	'20110101','20110101',52		UNION ALL
SELECT	'20110102','20110228',53		UNION ALL
SELECT	'20110301','20110331',54	 

--	


declare @FromDate datetime
declare @EndDate datetime
declare @CustBool BIT
set @CustBool = 1
set @FromDate = '12/01/2010'
set @EndDate = '12/31/2010'

select distinct p.BeginDate,p.EndDate, p.Period
from Period p
where p.BeginDate >= dateadd(month,1,@FromDate) and p.EndDate <= dateadd(month,1,@EndDate)

--returns 
/*BeginDate	EndDate	Period
2011-01-01 00:00:00.000	2011-01-01 00:00:00.000	52
*/

However, if you reset the From/End dates to 1 month forward as below, you can get the desired results


declare @FromDate datetime
declare @EndDate datetime
declare @CustBool BIT
set @CustBool = 1
set @FromDate = '02/01/2010'
set @EndDate = '06/30/2010'

/*advance the dates 1 month, resetting to correct BOM/EOM dates */
Set @FromDate = dateadd(m,1,@fromdate)-day(@fromdate)--go 1 month forward, subtract day to go to 1st of month 1 month future
Set @enddate = dateadd(m,2,@enddate)-day(@enddate)---go 2 months forward, subtract number of days to get EOM date of 1 month future


select distinct p.BeginDate,p.EndDate, p.Period
from Period p
where p.BeginDate >= @FromDate and p.EndDate <=  @EndDate

/*Returns below 
BeginDate	EndDate	Period
2010-03-01 00:00:00.000	2010-03-31 00:00:00.000	42
2010-04-01 00:00:00.000	2010-04-30 00:00:00.000	43
2010-05-01 00:00:00.000	2010-05-31 00:00:00.000	44
2010-06-01 00:00:00.000	2010-06-30 00:00:00.000	45
2010-07-01 00:00:00.000	2010-07-31 00:00:00.000	46
*/

Still not sure about @custbool,. but maybe this will help anyway.

Poor planning on your part does not constitute an emergency on my part.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources