sql server programming: Hi

I think you are missing brackets around the subquery.
When posting please specify ddl and example data
http://www.aspfaq.com/etiquette.asp?id=5006 such as:

CREATE TABLE #MyDates1 ( day datetime)
CREATE TABLE #MyDates2 ( day datetime)

DECLARE @days INT
SET @days = 5
WHILE @Days > 0
BEGIN
INSERT INTO #MyDates1 ( day) values (dateadd(dd,@days,getdate() ))
SET @days = @days - 1
END

INSERT INTO #MyDates2 ( day) values (dateadd(dd,4,getdate()) )

SELECT day, (SELECT MAX(Day) FROM #MyDates2) as Controldate, DATEDIFF(dd,
day,(SELECT MAX(Day) FROM #MyDates2)) FROM #MyDates1

John

[quoted text, click to view]

It will help solve future problems for you if you tell us the actual error
message. You need parentheses around your subquery and your ON expression
isn't valid:

SELECT DISTINCT tblA.id,
DATEDIFF(DAY,
(SELECT MAX(tblB.date)
FROM tblB
WHERE tblA.id = tblB.id), CURRENT_TIMESTAMP)
FROM tblA
JOIN tblB
ON tblA.id = tblB.id ;

Also, try this query, which should be a more efficient version of the above:

SELECT tblA.id,
DATEDIFF(DAY, MAX(tblB.date), CURRENT_TIMESTAMP)
FROM tblA
JOIN tblB
ON tblA.id = tblB.id
GROUP BY tblA.id ;

--
David Portas
SQL Server MVP
--

hi I am trying to convers an acccess sql given below to a view in sql server
2000

SELECT DISTINCT tblA.ID,
Date()-(SELECT max(Date) FROM tblB WHERE tblA.ID = tblB.ID ) AS Expr1
FROM tblA INNER JOIN tblB ON ID;


I TRIED

SELECT DISTINCT tblA.ID,
DATEDIFF(day, SELECT max(Date) FROM B , GETDATE())
FROM tblA A INNER JOIN tblB ON ID;

But it gives me an error, any ideas to go around this??


cheers
Suman