difficult query - SQL Server Forums

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

Transact-SQL (2000)

difficult query

Author

Topic

chasp
Starting Member

5 Posts

Posted - 2007-09-04 : 03:03:57

hello,

i have a problem with a history table:

4 columns: ID, old_value, new_value, date_of_modification

the ID is not unique. when more then one modification is made, then there are even more rows with the same ID.

i want to know which value is valid on a certain date. is there a pissibility to do this with a query?

best regards
chasp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 03:08:29

SELECT ht.ID, ht.new_value, ht.date_of_modification
FROM HistoryTable AS ht
INNER JOIN (
SELECT ID, MAX(Date_Of_Modification) AS maxDate FROM HistoryTable GROUP BY ID
) AS d ON d.ID = ht.ID AND d.maxDate = ht.date_of_modification
ORDER BY ht.ID

E 12°55'05.25"
N 56°04'39.16"

chasp
Starting Member

5 Posts

Posted - 2007-09-04 : 03:14:16

doesn't work so..

example:

ID, old_value, new_value, date_of_modification
1,a,b,2007/01/01
1,b,c,2007/02/01
1,c,d,2007/03/01

which value is valid on 2007/01/15 ? --> b

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 03:20:31

I gave you a query that you should investigate and learn from.
This is not only a place where you get quick and accurate answers, you also get the opportunity to learn by laborating with the suggestions given to you.

If you had done so, you should have noticed that you should write a WHERE in the derived table, like this

SELECT		ht.ID,
		ht.new_value,
		ht.date_of_modification
FROM		HistoryTable AS ht
INNER JOIN	(
			SELECT		ID,
					MAX(Date_Of_Modification) AS maxDate
			FROM		HistoryTable
			WHERE		Date_Of_Modification < DATEADD(DAY, 1, '20070115')
			GROUP BY	ID
		) AS d ON d.ID = ht.ID AND d.maxDate = ht.date_of_modification
ORDER BY	ht.ID

E 12°55'05.25"
N 56°04'39.16"

chasp
Starting Member

5 Posts

Posted - 2007-09-04 : 03:38:59

sorry, but i am a complete beginner!

when you replace the date '20070115' with '2006/12/01' then you get back NULL. but i need a as the return value...

can you please help me once again? i do not understand

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 03:41:22

Aha, you want the NEAREST date before (in first case) or after (in second case)

For the date of "December 1, 2006" you want the record with
1,a,b,2007/01/01
back?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 03:42:17

quote:
Originally posted by chasp

when you replace the date '20070115' with '2006/12/01' then you get back NULL.

Well, there are no records in the history table before '2006/12/01'.

E 12°55'05.25"
N 56°04'39.16"

chasp
Starting Member

5 Posts

Posted - 2007-09-04 : 03:46:32

yes, i want for the date of "December 1, 2006" the old_value back from the latest date. in this case it would be the "a"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 04:01:22

[code]-- Prepare sample data
declare @sample table (id int, old_value varchar(2), new_value varchar(2), date_of_modification datetime)

insert @sample
select 1, 'a', 'b', '20070101' union all
select 1, 'b', 'c', '20070201' union all
select 1, 'c', 'd', '20070301' union all
select 2, 'p', 'q', '20070110' union all
select 2, 'q', 'r', '20070211' union all
select 2, 'r', 's', '20070312'

-- Get some data
DECLARE @theDate DATETIME

SET @theDate = '20061201'

SELECT @theDate AS WantedDate,
s.ID,
CASE
WHEN d.theDate < @theDate THEN s.old_value
ELSE s.new_value
END AS theValue,
s.Date_Of_Modification
FROM @Sample AS s
INNER JOIN (
SELECT d.ID,
MIN(d.theDate) AS theDate
FROM (
SELECT ID,
MAX(Date_Of_Modification) AS theDate
FROM @Sample
WHERE Date_Of_Modification < DATEADD(DAY, 1, @theDate)
GROUP BY ID

UNION ALL

SELECT ID,
MIN(Date_Of_Modification)
FROM @Sample
WHERE Date_Of_Modification >= @theDate
GROUP BY ID
) AS d
GROUP BY d.ID
) AS d ON d.ID = s.ID AND s.Date_Of_Modification = d.theDate

SET @theDate = '20070115'

SELECT @theDate AS WantedDate,
s.ID,
CASE
WHEN d.theDate < @theDate THEN s.old_value
ELSE s.new_value
END AS theValue,
s.Date_Of_Modification
FROM @Sample AS s
INNER JOIN (
SELECT d.ID,
MIN(d.theDate) AS theDate
FROM (
SELECT ID,
MAX(Date_Of_Modification) AS theDate
FROM @Sample
WHERE Date_Of_Modification < DATEADD(DAY, 1, @theDate)
GROUP BY ID

UNION ALL

SELECT ID,
MIN(Date_Of_Modification)
FROM @Sample
WHERE Date_Of_Modification >= @theDate
GROUP BY ID
) AS d
GROUP BY d.ID
) AS d ON d.ID = s.ID AND s.Date_Of_Modification = d.theDate[/code]

E 12°55'05.25"
N 56°04'39.16"

chasp
Starting Member

5 Posts

Posted - 2007-09-04 : 04:09:36

many thanks! you are my hero :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources