1,827 reputation
721
bio website
location Ukraine
age
visits member for 3 years, 1 month
seen 5 hours ago

Feb
20
comment Issues with Optimizing TSQL Script
Logically, the join condition in your last query is not equivalent to what you are checking in the multi-statement script. The former covers a case not covered by the latter: MD.ManagerID = CR.CustomIdentifier AND CR.CounterPartyID = '' AND MD.SecurityAccount = CR.AccountCode. But if that combination of values can never appear in your data, then your attempt seems fine, only you need to replace null as SecurityCode with cr.GenevaCode as SecurityCode, of course, as well as include cr.GenevaCode into the GROUP BY, I guess.
Feb
20
comment I would like to get all values greater than 80, rank, then group in order to get a count of threshold breaches
A SEDE link (SQL Fiddle's been sluggish lately).
Feb
20
comment I would like to get all values greater than 80, rank, then group in order to get a count of threshold breaches
Well, my take on this isn't much better. Same four sorts in the plan (one is for the final ORDER BY, so it must be a given, unless there's a covering index), it just uses one CASE fewer.
Feb
20
comment I would like to get all values greater than 80, rank, then group in order to get a count of threshold breaches
All right, will give it a try. In the meantime, your max int check actually causes a minor bug. If the first value in a partition is 80 or greater, its block number will be last rather than 1. (Just change the first 66 to e.g. 86 and you'll see.)
Feb
20
comment I would like to get all values greater than 80, rank, then group in order to get a count of threshold breaches
@spaghettidba: I agree that your solution would probably be more efficient, but that max int thing does seem redundant to me too. You've got Value >= 80 all over the place – what is the chance a row with Value >= 80 can get a bb of 0? Seems like safeguarding against something you haven't thought through properly.
Feb
20
comment How to create a SQL trigger to execute a stored procedure when a table is updated
THROW is another thing that wouldn't work in every version of SQL Server.
Feb
20
comment I would like to get all values greater than 80, rank, then group in order to get a count of threshold breaches
Why is it DENSE_RANK and not ROW_NUMBER? Is it because there can be multiple readings per (Source, PollTime) and you want to treat them as one reading?
Feb
19
comment SQL Server's aggregate functions ignore nulls. How do I correct this?
Your saying "how I can get it to work correctly" seems to imply that the documented behaviour is not correct. So, perhaps someone disagreed with that premise, considering such a question not useful.
Feb
19
comment SQL Server's aggregate functions ignore nulls. How do I correct this?
Seems a little overkill. You could just compare COUNT(col) and COUNT(*).
Feb
19
comment SQL Server's aggregate functions ignore nulls. How do I correct this?
In other words, it already works correctly. Problem solved.
Feb
4
comment How to split a large table in Teradata
There should be no comma before ORDER BY. But that's just a syntax issue, actually you shouldn't have a PARTITION BY clause there at all, because the restriction is simply "no more 30 million rows" rather than "no more 30 million rows per CUSTOMERID".
Jan
24
comment Can I manage NULL = 'SomeDefault' in the database so applications and SQL don't have to remember this optimization?
Maybe you could achieve some result with the help of functions NULLIF and either IFNULL or COALESCE, but the way I imagine you would likely use them you would certainly kill the performance. Consider rewriting your question by adding the underlying problem to it (presumably the one you've mentioned in your comment, except you may want to elaborate on it a little, as it doesn't seem very clear at the moment).
Jan
15
revised Is it possible to emulate the WITH command in MySQL 5.1.73?
clarified
Jan
15
revised Is it possible to emulate the WITH command in MySQL 5.1.73?
added 427 characters in body
Jan
15
answered Is it possible to emulate the WITH command in MySQL 5.1.73?
Jan
14
comment How to properly aggregate?
Doesn't seem entirely clear what the result is supposed to be. Could you add a data sample and the expected result for it to illustrate the problem?
Jan
2
comment What is the most efficient method of performing the FIZZBUZZ test in SQL Server?
We are still not on the same page, it seems. Just as you could rewrite stupidalias2 as a CTE, you could also do the same with stupidalias1 or, for that matter, with any of n0, n3, n9 etc. It would be just a syntax change and no challenge – that was the idea I (and probably Aaron too) tried to get across. In short, it wouldn't matter much really, it's just that your statement (Doesn't use any CTEs) does seem a bit on the negative side.
Jan
2
comment What is the most efficient method of performing the FIZZBUZZ test in SQL Server?
Not sure but it seems like by CTE you mean specifically a recursive CTE, even though the thing doesn't have to be recursive to be called "a CTE". And a non-recursive CTE (which I'm sure is what @Aaron was talking about) is almost the same thing as your "normal" subselect (it would be the same in this case anyway), you just use a slightly different syntax to declare it: instead of ... left outer join (select 3 as fizzstep, 'Fizz' as fizz) as stupidalias2 on ... you go with stupidalias2 as (select 3 as fizzstep, 'Fizz' as fizz) ... left outer join stupidalias2 on .... That is no challenge.
Dec
29
revised Sum of two columns from pivot
currected variable names
Dec
15
revised Why can't I use a CASE statement to see if a column exists and not SELECT from it?
corrected grammar for better clarity