Viewing 15 posts - 3,781 through 3,795 (of 4,085 total)
FOR XML PATH was introduced in SQL 2005 and varchar(max) was also introduced in SQL 2005. The OP already told you that he couldn't use FOR XML PATH.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 28, 2011 at 1:32 pm
Sailor (1/28/2011)
select name, table2.account, table3.city
from table1
left join table2 on table1.userid = table2.userid
left join (select top 1 account from table3)
as tab3 on...
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 28, 2011 at 1:20 pm
I forgot to include the ON statement.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 27, 2011 at 2:21 pm
GSquared (1/27/2011)
;WITH MaxDates (OrderID, MaxDate) AS(SELECT OrderID, MAX(OrderDate)
FROM MyTable
GROUP BY OrderID)
SELECT T1.*
FROM MyTable AS T1
LEFT JOIN CTE
ON T1.OrderID = CTE.OrderID
AND T1.OrderDate = CTE.MaxDate
WHERE CTE.OrderID IS NULL;
This approach is inefficient, because...
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 27, 2011 at 1:52 pm
I'm not sure why you're joining to your table3 twice, but that is what is causing the problem. The first join is okay, because of the distinct clause, but...
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 27, 2011 at 1:25 pm
You know that there is a separate forum for SQL 2000/7.0.
You should read Jeff's article about the "quirky" update. http://www.sqlservercentral.com/articles/68467/. There are a lot of caveats for "quirky"...
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 27, 2011 at 1:00 pm
PIVOT won't work, because you need to pivot two columns (Col2 and Col3) and PIVOT will only work with one column. However, you are doing an aggregate, even though...
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 21, 2011 at 3:12 pm
dlam 18073 (1/20/2011)
but is there any different between puting the where clause in the sub-query and out side the sub-query
Absolutely. The WHERE clause in the main query would be...
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 21, 2011 at 7:46 am
You should use DateDiff() instead. Converting datetime data back and forth between char data is inefficient.
WHERE DateDiff(Month, DateTimeIn, GetDate()) < 12
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 19, 2011 at 10:00 am
Deeptiprasad (1/19/2011)
Ideally the query should be like thisWHERE ID =
CASE
WHEN @ID >0 THEN @CaseID
WHEN @ID = 0 THEN (RMARequestedDate > (GetDate()- @d))
END
"(RMARequestedDate > (GetDate()- @d))" is a Boolean...
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 19, 2011 at 9:46 am
Your query is using hidden RBAR which is why it is inefficient. You're better off using the Row_Number() function with the Partition By option to find the last call...
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 19, 2011 at 9:39 am
Carl Federl (1/18/2011)
1. subtract one-half of a day and subtract 2 milliseconds, which is 43200002 milliseconds.
2....
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 18, 2011 at 9:20 am
Rydunzel (1/17/2011)
This isn't an issue with the ISNULL funtion, it is just how the integer zero is treated.
No, it's how the STRING '' is treated and it follows logically from...
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 17, 2011 at 4:34 pm
As I said, the code was untested, because your data was not in an easily consumable format. (It still isn't.) I suggest reading Jeff's article about how to...
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 17, 2011 at 2:44 pm
IsNull() is more succinct than the CASE statement.
IsNull(a.PUser, b.PrimaryUser) AS PUser
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 17, 2011 at 11:20 am
Viewing 15 posts - 3,781 through 3,795 (of 4,085 total)