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

Transact-SQL (2005)

Query

Author

Topic

saiJ
Starting Member

5 Posts

Posted - 2007-08-22 : 13:37:04

Hi,

We query on views instead of using tables.I have a view in which date field is there
from view1 i need to get these fields
date,folder
there is a view 2 in which there is date and folder.
I need to find out what is nearest date in view1 to date in view2?

View1
Date Folder
01-02-2007 Fold1
02-02-2007 Fold2
02-03-2007 Fold3

View2
Date Folder
02-23-2007 Fold4

so i need to query in such a way my final result should show the nearest date is 02-02-2007 Fold2.

Any help on this would be appreciated.

Thanks
Sai

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-22 : 13:41:24

How is '02-02-2007' closer to '02-23-2007' when you also have '02-03-2007'. Can you explain your expected result as I cannot see any pattern.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 13:49:33

SELECT TOP 1 v1.Date, v1.Folder
FROM View1 AS v1
CROSS JOIN View2 AS v2
ORDER BY ABS(DATEDIFF(DAY, v1.Date, v2.Date))

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

saiJ
Starting Member

5 Posts

Posted - 2007-08-22 : 13:50:08

sorry for the confusion

View1
Date Folder
Jan 2-2007 Fold1
Feb 2-2007 Fold2
Mar 2-2007 Fold3

View2
Date Folder
Feb 23 2007 Fold4

so the nearest date should be feb 2 2007.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 13:56:29

No, the NEAREST date is Mar 2-2007 becuase it is only 7 days away.
Feb 2-2007 is 21 days away.

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

saiJ
Starting Member

5 Posts

Posted - 2007-08-22 : 13:57:43

Hi Peso,

Your query is giving me different result as i should query on one subjectid and userid.when i add where clause to the query you gave it is just getting 1st row which is not nearest.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 13:59:47

Then why didn't you give us this piece of very important information from the beginning then?

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

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-22 : 14:01:42

quote:
Originally posted by saiJ

View1
Date Folder
01-02-2007 Fold1
02-02-2007 Fold2
02-03-2007 Fold3

View2
Date Folder
02-23-2007 Fold4

In your data your dateformats are misleading. Your dates from View1 seem to be of DMY format and date from View2 is MDY. which is what confused me.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 14:06:22

[code]-- Prepare sample data
DECLARE @View1 TABLE (Date SMALLDATETIME, Folder SYSNAME)

INSERT @View1
SELECT 'Jan 2, 2007', 'Fold1' UNION ALL
SELECT 'Feb 2, 2007', 'Fold2' UNION ALL
SELECT 'Mar 2, 2007', 'Fold3'

DECLARE @View2 TABLE (Date SMALLDATETIME, Folder SYSNAME)

INSERT @View2
SELECT 'Feb 23, 2007', 'Fold4'

-- Show nearest/closest date
SELECT TOP 1 v1.Date,
v1.Folder
FROM @View1 AS v1
CROSS JOIN @View2 AS v2
ORDER BY ABS(DATEDIFF(DAY, v1.Date, v2.Date))

-- Prepare nearest/closest previous date
SELECT TOP 1 v1.Date,
v1.Folder
FROM @View1 AS v1
CROSS JOIN @View2 AS v2
WHERE v1.Date < v2.Date
ORDER BY v1.Date DESC[/code]

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

saiJ
Starting Member

5 Posts

Posted - 2007-08-22 : 14:17:52

Here Vdate are visitdates.
Means patient visited 3 times in a year and in b/w 2nd and 3rd visit doctor fills out this form which is in 2nd view.
In 2nd view there is weight field i need to get from recent visit done by patient which is 2nd visit.

So i need query in such a way depending upon the date in view2 i should find what is nearest date.

I think i'm clear.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 14:20:02

This is also new information...
Did my previous post help you or not?

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

saiJ
Starting Member

5 Posts

Posted - 2007-08-22 : 14:25:34

Hi Peso,

Sorry for any confusion i caused i'm new to SQL.
You are inserting constants into temp table but i can't do that as i have to query from view1 what are dates and folders for certain subject.

DECLARE @Dates table (Vdate datetime,
Folder varchar(50),
subjecID int)

INSERT INTO @Dates
SELECT d.VDATE,
d.Folder,
d.subjectId
FROM dbo.V_LATE_PHASE_REGISTRY_VDATE d
where d.subjectid = 257 and d.userid = 28

Now once i get these dates i need to find what is the nearest visitdate happened based on date in View2.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources