T sql query help checking for dates between table

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)

T sql query help checking for dates between table

Author

Topic

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2010-04-16 : 16:02:12

Hi,
I have two tables Location and Administration as listed below. I would like to have a new column in the Administration table with LocationID from the Location but the logic should be get the LocationID for corresponding row in Administrations with AdminDateTime <= EffectiveDateTime of the Location table.

Administration Table

VisitID RxID AdminDateTime
F10001158092 2970229 4/16/10 7:44
F10001158092 2970230 4/16/10 8:28
F10001158092 2970233 4/16/10 8:29
F10001158092 2970234 4/16/10 8:28
F10001158092 2970290 4/16/10 9:29
F10001158092 2970291 4/16/10 10:05
F10001158092 2970295 4/16/10 8:56
F10001158092 2970299 4/16/10 8:56
F10001158092 2970304 4/16/10 10:52
F10001158092 2970357 4/16/10 9:33

Location Table
VisitID EffDateTime UserID LocationID
F10001158092 4/16/10 7:30 ILDIAZ NULL
F10001158092 4/16/10 7:30 RBENGCION NULL
F10001158092 4/16/10 7:30 JLOUIS NULL
F10001158092 4/16/10 6:14 RBENGCION WL&D
F10001158092 4/16/10 10:00 VHARVEY WOB

the Output should look like

VisitID RxID AdminDateTime LocationID
F10001158092 2970229 4/16/10 7:44 WL&D
F10001158092 2970230 4/16/10 8:28 WL&D
F10001158092 2970234 4/16/10 8:28 WL&D
F10001158092 2970233 4/16/10 8:29 WL&D
F10001158092 2970295 4/16/10 8:56 WL&D
F10001158092 2970299 4/16/10 8:56 WL&D
F10001158092 2970290 4/16/10 9:29 WL&D
F10001158092 2970357 4/16/10 9:33 WL&D
F10001158092 2970291 4/16/10 10:05 WOB
F10001158092 2970304 4/16/10 10:52 WOB

I spent a lot of time but could not figure out, any help is greatly appreciated.

Thanks,

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-04-16 : 16:48:08

Are you sure your descrioption of the logic is correct? I do not see how the AdminDateTime of "4/16/10 7:44" is <= the EffectiveDateTime of "4/16/10 6:14"

How do you determine that "WL&D" should match the rows in the Administration table in your expected output? Why not the NULL LocationIDs?

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2010-04-16 : 16:52:56

sorry Lamprey; Forgot to mention that several of the LocationID will be NULL in that case the previous populated value should be considered and also AdminDateTime >= EffectiveDateTime.

sorry for the confusion.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-04-16 : 17:05:22

[code]SELECT
* -- Replace with column names
FROM
Administration AS A
CROSS APPLY
(
SELECT TOP 1
LocationID
FROM
Location AS L
WHERE
A.VisitID = L.VisitID
AND A.AdminDateTime >= L.EffDateTime
AND L.LocationID IS NOT NULL
ORDER BY
L.EffDateTime DESC
) AS T
ORDER BY
A.AdminDateTime[/code]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-04-16 : 17:17:08

For fun here is another way using the DENSE_RANK() function:

SELECT
	VisitID,
	RxID,
	AdminDateTime,
	LocationID
FROM
	(
		SELECT 
			A.VisitID,
			A.RxID,
			A.AdminDateTime,
			L.LocationID,
			DENSE_RANK() OVER (PARTITION BY A.VisitID, A.AdminDateTime ORDER BY L.EffDateTime DESC) AS RowNum
		FROM 
			Administration AS A
		LEFT OUTER JOIN
			Location AS L
			ON A.VisitID = L.VisitID
		WHERE
			A.AdminDateTime >= L.EffDateTime
			AND L.LocationID IS NOT NULL
	) AS T
WHERE
	RowNum = 1
ORDER BY 
	AdminDateTime

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2010-04-19 : 09:36:31

Sorry Lamprey; I posted this in the wrong forum as this old server i am working on is SQL 2000 rather than 2005. I forgot about this when i posted as i usually dont deal with this server often.

can u please suggest how it can be done in 2000 as the cross apply function is not working in 2000.

Thanks for all ur help.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-04-19 : 10:51:52

The quick and diry way is to change the cross apply to a correlated-sub query (not the fastest, but it'll work):

SELECT 
	* -- Replace with column names,
	(
		SELECT TOP 1 
			LocationID
		FROM 
			Location AS L
		WHERE
			A.VisitID = L.VisitID
			AND A.AdminDateTime >= L.EffDateTime
			AND L.LocationID IS NOT NULL
		ORDER BY 
			L.EffDateTime DESC
	) AS LocationID

FROM 
	Administration AS A
ORDER BY
	A.AdminDateTime

EDIT: Forgot to remove the table varible @'s

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-19 : 11:03:09

[code]SELECT a.*,
(SELECT TOP 1 FROM Location WHERE VisitID= a.VisitID AND AdminDateTime<=a.AdminDateTime AND LocationID
IS NOT NULL ORDER BY AdminDateTime DESC) AS LocationID
FROM Administration a
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2010-04-19 : 16:33:09

Thanks a lot everyone.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-20 : 00:57:47

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources