Viewing 15 posts - 2,971 through 2,985 (of 4,087 total)
The WHERE clause is processed before the SELECT statement, therefore any aliases assigned in SELECT statements are not available for their corresponding WHERE clauses. I have gotten around this...
August 4, 2015 at 8:05 am
Lynn Pettis (8/3/2015)
Just another way to do it:
declare @Date date = '20150803',
@Time time = '15:00:00';
select dateadd(second,datediff(second,0,@Time),cast(@Date as datetime))
go
I considered this approach, but...
August 4, 2015 at 7:21 am
djj (8/3/2015)
I have had luck with
DECLARE @Date DATE = '1/1/2015', @Time TIME = '15:20'SELECT cast(@Date as datetime) + cast(@Time as datetime)
I expect that to stop working at some future...
August 3, 2015 at 12:24 pm
Here is a solution that will handle any length of extension. I added a *.ssmsproj entry to test a longer extension.
SELECT *, LEFT(RIGHT(td.breach_content, r.filename_index-1), r.filename_index - r.extension_index - 1), RIGHT(td.breach_content,...
August 3, 2015 at 11:20 am
Converting dates/times to character and back is horribly inefficient, but if your table is small enough, it may still be fast enough to not significantly affect performance. Another option...
August 3, 2015 at 10:54 am
I think this is what you are looking for.
SELECT
p.country_code,
p.local_client_code,
wwc.local_client_name,
sum(case when pr.fiscal_year = 2015 then pr.local_consulting_fees*er.rate + pr.local_product_fees * er.rate + pr.local_admin_fees * er.rate + pr.local_misc_fees * er.rate else 0...
July 30, 2015 at 2:50 pm
Another reason is that you might be storing information from an OUTPUT clause to use later, especially if you want to use information from both the INSERTED and DELETED temporary...
July 29, 2015 at 3:44 pm
First, the correct syntax for CAST is CAST(<expression> AS <datatype>).
Second, saying it didn't work doesn't give us much information to go on. Exactly how didn't it work? Did...
July 24, 2015 at 12:54 pm
DavidDroog (7/22/2015)
Are you also saying that a where clause is more efficient than a a join on clause?
As far as SQL is concerned, ON clauses and WHERE clauses are equivalent...
July 22, 2015 at 9:27 pm
Did you check out my post? My results are exactly like yours except that I also have a row for ParentID = 0, and it's not clear why you...
July 22, 2015 at 12:48 pm
It sounds like you want to use the DENSE_RANK() function.
;
WITH table_ranked AS (
SELECT iINDEX, PARENTID, DOCUMENTID, QTY, REVNR, XREFID
,DENSE_RANK() OVER(PARTITION BY PARENTID ORDER BY REVNR DESC) AS dr
FROM #TableA0
)
SELECT tr.iINDEX,...
July 22, 2015 at 12:26 pm
I would add that you are mixing join conditions with criteria. When joining tables, you almost always want to use only (implicit) foreign keys, because the joins should reflect...
July 22, 2015 at 9:44 am
As far as I can tell, this yields exactly the same plan, but I find it simpler to follow.
SELECT CompanyName, Year, Month, Cost
,AVG(Cost) OVER(PARTITION BY CompanyName) AS AverageCost
,SUM(Cost) OVER(PARTITION BY...
July 21, 2015 at 11:46 am
Jeff Moden (7/18/2015)
Why does everyone think that a post is only for the OP? Five years old or not, additional information never hurts.
The vast majority of the time, people...
July 20, 2015 at 10:21 am
The short answer is NO!
The long answer is that the easiest way to get the appropriate structure in your XML documents using T-SQL is by using nested queries. Using this...
July 17, 2015 at 1:28 pm
Viewing 15 posts - 2,971 through 2,985 (of 4,087 total)