Find StartDate in datetime-spel

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

General SQL Server Forums

New to SQL Server Programming

Find StartDate in datetime-spel

Author

Topic

Clas
Starting Member

33 Posts

Posted - 2008-10-01 : 10:59:36

I work most with typed dataset and C# .
This solution is very slow with dataset/loop.
Is there a better solution with sql/procedure, SQL-server 2008 ?

Table with Events:
CREATE TABLE #EventsSource
(
eventID int,
IndividualID int,
EventDate date,
)
INSERT #EventsSource

VALUES
(10,100,'1922-01-01'),
(11,100,'1924-05-05'),
(12,100,'1925-05-06'),
(13,100,'1927-04-15'),
(14,200,'1926-08-09'),
(15,200,'1928-10-10'),
(16,200,'1928-10-11'),
(17,200,'1930-01-01'),
(18,300,'1926-01-01'),
(19,300,'1936-02-02')

SELECT * FROM #EventsSource

I have an query to find and insert Stopdate in #Spell. This query is OK.
Insert all StopDate:
CREATE TABLE #Spell
(
spellID int,
IndividualID int,
StartEventID int,
StartDate date,
StopEventID int,
StopDate date
)

INSERT #Spell
(
spellID,
IndividualID,
StopEventID,
StopDate
)
VALUES
(1,100,11,'1924-05-05'),
(2,100,13,'1927-04-15'),
(4,200,15,'1928-10-10'),
(5,200,17,'1930-01-01'),
(6,300,19,'1936-02-02')

SELECT * FROM #Spell

RESULT:
spellID ----- IndividualID ----- StartEventID ----- StartDate -----StopEventID ----- StopDate
1 -------------- 100 ------------------------------------------------ 11 ----------- 1924-05-05
2 -------------- 100 ------------------------------------------------ 13 ----------- 1927-04-15
4 -------------- 200 ------------------------------------------------ 15 ----------- 1928-10-10
5 -------------- 200 ------------------------------------------------ 17 ----------- 1930-01-01
6 -------------- 300 ------------------------------------------------ 19 ----------- 1936-02-02

Next Step is to find and update #Spell (StartEventID and StartDate) from EventsSource

ORDER BY IndividualID AND EventDate IN #EventsSource :
The Individuals Previous EventDate in EventsSource ( < Stopdate in Spell) = StartEvent

SQL ?????????????????????
SELECT and UPDATE #Spell

RESULT:

spellID ----- IndividualID ----- StartEventID ----- StartDate -----StopEventID ----- StopDate
1 -------------- 100 ---------------- 10 ----------- 1922-01-01 ------ 11 ----------- 1924-05-05
2 -------------- 100 ---------------- 12 ----------- 1925-05-06 ------ 13 ----------- 1927-04-15
4 -------------- 200 ---------------- 14 ----------- 1926-08-09 ------ 15 ----------- 1928-10-10
5 -------------- 200 ---------------- 16 ----------- 1928-10-11 ------ 17 ----------- 1930-01-01
6 -------------- 300 ---------------- 18 ----------- 1926-01-01 ------ 19 ----------- 1936-02-02

DROP table #EventsSource
DROP table #Spell

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 11:03:32

[code]UPDATE s
SET s.StartEventID=es.EventID,
s.StartDate=es.EventDate
FROM #Spell s
INNER JOIN #EventSource es
ON es.IndividualID=s.IndividualID
AND es.EventID=s.StopEventID -1[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 11:07:11

and just in case EventIDs are not contiguos

UPDATE s
SET s.StartEventID=es.EventID,
s.StartDate=es.EventDate
FROM #Spell s
CROSS APPLY(SELECT TOP 1 EventID,EventDate
FROM #EventSource 
WHERE IndividualID=s.IndividualID
AND es.EventID<s.StopEventID
ORDER BY EventDate DESC)es

Clas
Starting Member

33 Posts

Posted - 2008-10-03 : 06:46:22

Thanks !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-03 : 07:09:20

quote:
Originally posted by Clas

Thanks !

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources