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

SQL Server Development (2000)

query

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2006-07-26 : 13:00:38

Can you please help me under select in where condition at this line:
And isdate(A.DueDate) = 1

I want to get the closest duedate record to getdate()

for example: if there are three record:

1st record has duedate: 072906
2nd record has duedate: 072706
3rd record has duedate: 081406

The 2nd records duedate is closest to getdate(), if there are two or multiple records with the same closest date i would like to show all those records in the above scenario the closest date is 072706, if i have multiple records with the same dudate then i would like to show all records. first i would like to establish what is the closest date record available in the table comparing with getdate() and then pulling all those dated records in the select query:


SELECT S.SUNumber, S.SUTitle, A.Description, S.PDSupervisor, S.Suid, 
FROM TAB_ccsNetSU AS S INNER JOIN TAB_ccsNetActions A ON S.SUID = A.ModuleRecordID 
WHERE A.ModuleName = 'SU'
AND isdate(A.PDToContractorDate) = 0

And isdate(A.DueDate) = 1

GROUP BY S.SUNumber, S.SUTitle, S.PDSupervisor, S.SUID, A.description

Thank you very much for the information.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-07-26 : 13:01:20

Repost of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69639

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-07-27 : 02:48:37

SELECT DATEADD(Day, -1, GetDate() AS Date
FROM <Ur TableName>

-1 :- is used for calculating & checking close date, i.e as interval

BMahesh

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-07-27 : 03:12:05

Ok, something along these lines will give you the records you need:

SELECT * 
FROM table1 AS a INNER JOIN
   (SELECT TOP 1 Duedate, ABS(DATEDIFF(day, duedate, getdate())) AS Diff
     FROM table1 
     ORDER BY Diff) AS b
  ON a.Duedate = b.Duedate

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Q
Yak Posting Veteran

76 Posts

Posted - 2006-07-27 : 05:04:18

Don't think this code will show the correct the correct result.
Example:
getDate() is 27-07-2006
one record has date 26-07-2006
an other record has date 28-06-2006 (if possible???)

Both records have an absolute difference of 1 day, but only one will be shown I think...
Maybe something like

...
where dueDate in
(select dueDate, MIN(ABS(DATEDIFF(day, duedate, getdate()))
from table1)

Haven't tested it, so maybe I'm all wrong...

Q
Yak Posting Veteran

76 Posts

Posted - 2006-07-27 : 05:19:47

select distinct dat.datum, ABS(DATEDIFF(day, dat.datum, getdate()))
from dat
where ABS(DATEDIFF(day, dat.datum, getdate()))=
(
select min(ABS(DATEDIFF(day, dat.datum, getdate())))
from dat
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources