April 4, 2002 at 1:56 pm
I've been racking my brains for several days over this problem so I hope someone might be able to give me a nudge in the right direction.
I have a simple sql 2000 table as below
ID - StartDate - EndDate (table_1)
1 - 01/02/03 - 05/02/03
2 - 17/04/04 - 24/05/04
...
I need to look at ID=1, insert all the dates from 01/02/03 --> 05/02/03 into another table, and then repeat the process for the other records (id=2, id=3 etc), giving the following results:
ID - DateIntervals (table_2)
1 - 01/02/03
1 - 02/02/03
1 - 03/02/03
1 - 04/02/03
1 - 05/02/03
2 - 17/04/04
2 - 18/04/04
2 - 19/04/04
2 - 20/04/04 ...etc
I've got a basic insert working using DATEADD for each consecutive date, but the problem is being able to run the insert after each indivdual record in the first table - I'm stuck on how to look at an individual record i.e. id=1, perform an insert based on the value of id, and then move onto the next record. Can I do this without cursors?
Any help greatly appreciated !
April 4, 2002 at 2:21 pm
You can by storing the id value in a @var and then incrementing by selecting the min(id) where id > @id.
Steve Jones
April 5, 2002 at 4:38 am
The following script does give you an exmple of how you can solve this problem without using a cursor - although I think that a cursor-based solution is probably faster...
This solution does use a temporary helper table with all dates between your earliest startdate and the latest enddate.
sven
-- SCRIPT
SET NOCOUNT ON
if object_id('dates1') is not null drop table dates1
go
CREATE TABLE dates1 (id int PRIMARY KEY, begindate datetime, enddate datetime)
go
INSERT INTO dates1 VALUES (1, '20020101', '20020103')
INSERT INTO dates1 VALUES (2, '20020106', '20020108')
INSERT INTO dates1 VALUES (3, '20020109', '20020111')
INSERT INTO dates1 VALUES (4, '20020209', '20020221')
GO
DECLARE @start datetime
DECLARE @ende datetime
-- Set up a helper table with all dates between first begindate and last enddate
CREATE TABLE #alldates (datum datetime PRIMARY KEY)
SELECT @start = MIN(begindate) FROM dates1
SELECT @ende = MAX(enddate) FROM dates1
WHILE @start < @ende
BEGIN
INSERT INTO #alldates VALUES (@start)
SET @start = @start + 1
END
-- Join the two tables to get all records
-- INSERT INTO table2
SELECT id, datum FROM dates1 INNER JOIN #alldates
ON #alldates.datum BETWEEN dates1.begindate and dates1.enddate
--WHERE id IS NOT NULL
DROP TABLE #alldates
April 15, 2002 at 2:24 am
I like Sven's solution, I'ld only add a word of caution to save you some possible grey hairs. I have to do this sort of thing all the time, and I'ld definitely recomend using a Cursor within a stored procedure as the job is INFINITELY easier to debug and support. It's so much easier to see and work out what's going on.
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
April 15, 2002 at 6:01 am
This is a variation of what Sven wrote that seemed more like what you were looking for. However I do not agree with the cursor statement as the while has the same effect as the cursor (it is easy to read and see exactly what is going on in the while loops) without the memory overhead, especially if this runs several times. Hope this helps.
SET NOCOUNT ON
--Replace with you table this was for example.
IF OBJECT_ID('dates1') IS NOT NULL DROP TABLE dates1
go
CREATE TABLE dates1 (id int PRIMARY KEY, begindate datetime, enddate datetime)
go
INSERT INTO dates1 VALUES (1, '20020101', '20020103')
INSERT INTO dates1 VALUES (2, '20020106', '20020108')
INSERT INTO dates1 VALUES (3, '20020109', '20020111')
INSERT INTO dates1 VALUES (4, '20020209', '20020221')
GO
--Replace this object with you date table
IF OBJECT_ID('alldates') IS NOT NULL DROP TABLE alldates
GO
CREATE TABLE [alldates] (
[id] [int] NOT NULL ,
[datenum] [datetime] NOT NULL ,
CONSTRAINT [PK__alldates__5E8A0973] PRIMARY KEY CLUSTERED
(
[id],
[datenum]
) ON [PRIMARY]
) ON [PRIMARY]
GO
DECLARE @start datetime
DECLARE @end datetime
DECLARE @looppos int
DECLARE @loopmax int
DECLARE @id int
--Create temp work table, this controls our loop flow.
CREATE TABLE #tempIDTbl (
[unid] [int] IDENTITY (1,1) NOT NULL,
[id] [int]
)
--Pull key values in based on do not already exist
INSERT INTO #tempIDTbl ([id]) SELECT DISTINCT dates1.[id] FROM dates1 LEFT JOIN alldates ON alldates.[id] = dates1.[id] WHERE alldates.[id] IS NULL
--Set our looping control variables
SET @loopmax = (SELECT max([unid]) FROM #tempIDTbl)
SET @looppos = 0
--Loop until condition met
WHILE @looppos < @loopmax
BEGIN
SET @looppos = @looppos + 1
--Get values key to this scenario.
SELECT @id = [id], @start = begindate, @end = enddate FROM dates1 WHERE [id] = (SELECT [id] FROM #tempIDTbl WHERE unid = @looppos)
--Inner loop to insert dates
WHILE @start <= @end
BEGIN
--Insert the key items
INSERT INTO alldates ([id], datenum) SELECT @id, @start
SET @start = DATEADD(d,1,@start)
END
END
--Drop temp table
DROP TABLE #tempIDTbl
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply