1. Registered User
Join Date
Jul 2003
Posts
123

I have a table that contains agrrements and contracts with dates. Now I need to calculate some things and I'd like the rows to only have one month per row.

I have rows like:

Agreement, Start, End
ID001, 2004-01-01, 2004-04-30

If I could get these single rows that contains 4 months into a temptable like this:

Agreement, Start, End
ID001, 2004-01-01, 2004-01-31
ID001, 2004-02-01, 2004-02-29
ID001, 2004-03-01, 2004-03-31
ID001, 2004-04-01, 2004-04-30

It would simplify my calculations very much. How to do this with a query or sp?

2. Registered User
Join Date
Apr 2004
Location
Kansas City, MO
Posts
734
SELECT Agreement, DATEPART(MM,Start) AS Month
FROM Agreement
GROUP BY DATEPART(MM,Start). The hard part here will be if you have a weird month. Otherwise, you can just use the above statement.

3. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
derrick, i think what was wanted was to generate 4 rows of output for the single row that has a date range of january - april

Selecting all months even if they're missing
(site registration may be required, but it's free)

join the integers table to your rows with
Code:
`... on i between month(start) and month(end)`

4. Registered User
Join Date
Jul 2003
Posts
123
Ok, I've looked into the article, but I think I'm too tired right now to get it.

It appears that I have to create a table which contains integers, but how many? Does that depend on how many months there can be between Start and End?

A table which only contains 0,1,2,3,4,5,6,7,8,9,10,11,12 ?? For one year..

Seems like a nice solution though, now if I could only understand the logic behind it. :-)

I'll look into this again tomorrow, now it's time to go home...

5. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
this was a bit trickier than it looked

the integers table approach still works, but generating feb 29 required a good rethink

okay, so here it is

however many months your agreements can span from start to finish is how many integers you need

for example, for an agreement from 2002-12-01 to 2005-01-31, you want to generate 26 rows, so you need 26 integers

Code:
```create table Agreements
( Agreement char(1)
, Startdate datetime
, Enddate datetime
)
insert into Agreements values ('a','2002-12-01','2005-01-31')
insert into Agreements values ('b','2004-02-01','2005-01-31')

create table integers (i integer)
insert into integers
select 0 union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10 union all
select 11 union all
select 12 union all
select 13 union all
select 14 union all
select 15 union all
select 16 union all
select 17 union all
select 18 union all
select 19 union all
select 20 union all
select 21 union all
select 22 union all
select 23 union all
select 24 union all
select 25 union all
select 26 union all
select 27 union all
select 28```
you can load as many integers into this table as necessary to cover the largest span of months

(i usually use only 0 through 9 and cross join them together three times to get 0 through 999, but let's keep it simple, and use just one integers table)

Code:
```select Agreement
, convert(char(10),dateadd(m, i, Startdate),120)   as Startdate
)          ,120)   as Enddate
from integers
cross
join Agreements
where i <= datediff(month,Startdate,Enddate)
order by 1,2

a 2002-12-01 2002-12-31
a 2003-01-01 2003-01-31
a 2003-02-01 2003-02-28
a 2003-03-01 2003-03-31
a 2003-04-01 2003-04-30
a 2003-05-01 2003-05-31
a 2003-06-01 2003-06-30
a 2003-07-01 2003-07-31
a 2003-08-01 2003-08-31
a 2003-09-01 2003-09-30
a 2003-10-01 2003-10-31
a 2003-11-01 2003-11-30
a 2003-12-01 2003-12-31
a 2004-01-01 2004-01-31
a 2004-02-01 2004-02-29
a 2004-03-01 2004-03-31
a 2004-04-01 2004-04-30
a 2004-05-01 2004-05-31
a 2004-06-01 2004-06-30
a 2004-07-01 2004-07-31
a 2004-08-01 2004-08-31
a 2004-09-01 2004-09-30
a 2004-10-01 2004-10-31
a 2004-11-01 2004-11-30
a 2004-12-01 2004-12-31
a 2005-01-01 2005-01-31
b 2004-02-01 2004-02-29
b 2004-03-01 2004-03-31
b 2004-04-01 2004-04-30
b 2004-05-01 2004-05-31
b 2004-06-01 2004-06-30
b 2004-07-01 2004-07-31
b 2004-08-01 2004-08-31
b 2004-09-01 2004-09-30
b 2004-10-01 2004-10-31
b 2004-11-01 2004-11-30
b 2004-12-01 2004-12-31
b 2005-01-01 2005-01-31```

6. Registered User
Join Date
Jul 2003
Posts
123
Perfect!

Now I can drop two out of three temptables in my sp, AND a problem is solved. :-)

I bet this solution is usable in other situations as well, thanx again.

7. Registered User
Join Date
Jul 2003
Posts
123
A problem has occurred.. agreements can be started and ended at ANY date, not just at the beginning or the end of the month...

I tried splitting the dates day by day but that method generates far to many rows...

How do I handle this?

Example:

Start : 2004-01-15
End : 2004-03-31

With the code I have it generates:
2004-01-15 - 2004-02-14
2004-02-15 - 2004-03-14
2004-03-15 - 2004-04-14

I want it to generate:
2004-01-15 - 2004-01-31
2004-02-01 - 2004-02-29
2004-03-01 - 2004-03-31

How can I do this? Is it possible in one query or do I need several? This is done in a sp so multiple queries are no problem.
-------------------------

8. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
This is a pure "off the cuff" shot, but something like:
Code:
```CREATE PROCEDURE p200407070726
@pdBegin DATETIME
,  @pdEnd DATETIME
AS

CREATE TABLE #ranges (
rbegin DATETIME NOT NULL
,  rend DATETIME NOT NULL
)

SET @dBegin = @pdBegin
SET @dEnd = DateAdd(ms, -3, Convert(CHAR(8), DateAdd(month, 1, @dBegin), 121) + '01')

WHILE @dBegin < @pdEnd
BEGIN
INSERT INTO #ranges (rbegin, rend) @dWork, CASE WHEN @dEnd < @pdEnd THEN @dEnd ELSE @pdEnd END
SET @dBegin = DateAdd(month, 1, @dEnd)
SET @dEnd = DateAdd(ms, -3, Convert(CHAR(8), DateAdd(month, 1, @dBegin), 121) + '01')
END

SELECT Agreement
,  CASE WHEN r.rBegn < a.start THEN a.start ELSE r.begin END
,  CASE WHEN r.rEnd < a.end THEN r.rEnd ELSE a.end END
FROM Agreement AS a
JOIN #ranges AS r
ON (r.rBegin <= a.end
AND a.start <= r.rEnd)

RETURN```
-PatP

9. Registered User
Join Date
Jul 2003
Posts
123
Generates errors when I check syntax. I'm not so experienced with variables in sp, but I figured out that @dBegin, @dEnd needed to be declared.

But whats @dWork for?

And how will I use this sp if I have a table with 10000 rows with agreements?

Question questions... :-)

10. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Let me get to somewhere I can set up a test case, and run it on a screen larger than 160 by 160. Maybe when I can see/test what I'm doing, I'll get it right on the next try!

-PatP

11. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Ok, now that I can see the whole thing, I didn't do too bad for composing on the fly, scribbling on a somewhat bloated postage stamp! Let's try again, using:
Code:
```CREATE TABLE tAgreement (
Agreement		VARCHAR(20)		NOT NULL
,  start		DATETIME		NOT NULL
,  [end]		DATETIME		NOT NULL
)

INSERT INTO tAgreement VALUES ('ID001', '2004-04-01', '2004-04-30')
INSERT INTO tAgreement VALUES ('PP001', '2004-01-01', '2004-12-31')
GO

DROP PROCEDURE p200407070726
GO
CREATE PROCEDURE p200407070726
@pdBegin DATETIME
,  @pdEnd DATETIME
AS

CREATE TABLE #ranges (
rbegin DATETIME NOT NULL
,  rend DATETIME NOT NULL
)

DECLARE
@dBegin	DATETIME
,  @dEnd	DATETIME

SET @dBegin = @pdBegin
SET @dEnd = DateAdd(day, -1, Convert(CHAR(8), DateAdd(month, 1, @dBegin), 121) + '01')

WHILE @dBegin < @pdEnd
BEGIN
INSERT INTO #ranges (rbegin, rend)
SELECT @dBegin, CASE WHEN @dEnd < @pdEnd
THEN @dEnd ELSE @pdEnd END

SET @dBegin = Convert(CHAR(8), DateAdd(month, 1, @dBegin), 121) + '01'
SET @dEnd = DateAdd(day, -1, Convert(CHAR(8)
,        DateAdd(month, 1, @dBegin), 121) + '01')
END

SELECT Agreement
,  CASE WHEN r.rBegin < a.start THEN a.start ELSE r.rBegin END
,  CASE WHEN r.rEnd < a.[end] THEN r.rEnd ELSE a.[end] END
FROM tAgreement AS a
JOIN #ranges AS r
ON (r.rBegin <= a.[end]
AND a.start <= r.rEnd)

RETURN
GO

EXECUTE p200407070726 '2004-02-01', '2004-09-30'```
Note that this builds test data, the procedure, and executes it.

-PatP

12. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Originally Posted by Pat Phelan
Let me get to somewhere I can set up a test case, and run it on a screen larger than 160 by 160. Maybe when I can see/test what I'm doing, I'll get it right on the next try!

-PatP

Pat...are you drunk?

What's @dWork for?

Code:
```USE Northwind
GO

CREATE PROCEDURE p200407070726
@pdBegin DATETIME
,  @pdEnd DATETIME
AS

DECLARE    @dBegin DATETIME
,  @dEnd DATETIME

CREATE TABLE #ranges (
rbegin DATETIME NOT NULL
,  rend DATETIME NOT NULL
)

SET @dBegin = @pdBegin
SET @dEnd = DateAdd(ms, -3, Convert(CHAR(8), DateAdd(month, 1, @dBegin), 121) + '01')

WHILE @dBegin < @pdEnd
BEGIN
INSERT INTO #ranges (rbegin, rend) SELECT @dWork, CASE WHEN @dEnd < @pdEnd THEN @dEnd ELSE @pdEnd END
SELECT 	  @dBegin = DateAdd(month, 1, @dEnd)
, @dEnd = DateAdd(ms, -3, Convert(CHAR(8), DateAdd(month, 1, @dBegin), 121) + '01')
END

SELECT Agreement
,  CASE WHEN r.rBegn < a.start THEN a.start ELSE r.begin END
,  CASE WHEN r.rEnd < a.end THEN r.rEnd ELSE a.end END
FROM Agreement AS a
JOIN #ranges AS r
ON (r.rBegin <= a.end
AND a.start <= r.rEnd)

RETURN
GO

EXEC p200407070726 '1/1/2004', '6/30/2004'
GO

DROP PROC p200407070726
GO```
Got rid of the rest of the syntax errors, except for that....

13. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Originally Posted by Brett Kaiser
Pat...are you drunk?
No, not drunk at all... That was a side effect of writing code on a 160 by 160 screen, with a machine where I couldn't test at all. If you check my revised post, it works way more gooder yet even!

-PatP

14. Registered User
Join Date
Jul 2003
Posts
123
"And how will I use this sp if I have a table with 10000 rows with agreements?"

I don't understand how this sp works... first I tried it with an empty tAgreement table, didn't work. Then I tried it with the values that you had there (what are they for?) And got a faulty result.

I tried EXEC p200407070726 '2004-01-16', '2004-05-25'

and got the dates

2004-01-16 - 2004-01-31
2004-02-01 - 2004-02-29
2004-03-01 - 2004-03-31
2004-04-01 - 2004-04-30
2004-04-01 - 2004-04-30
2004-05-01 - 2004-05-25

TWO rows with 2004-04-01 - 2004-04-30...

And I still don't get it how I will be able to use this proc on my table.

I have one table with agreements (about 10000 rows) and want to generate another table where these rows are split month by month.

15. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579