Viewing 15 posts - 7,516 through 7,530 (of 7,613 total)
This does not work if two or more people have the same total.
To paraphrase Clinton, it depends on the definition of "work". If two people have the same total,...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 21, 2011 at 10:33 am
Don't see how you can do this w/o an ORDER BY, but it's trivial with one:
SELECT TOP 1 Name, SUM(Consumption) AS Total_Consumption
FROM tablename
GROUP BY Name
ORDER BY SUM(Consumption) DESC
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 21, 2011 at 9:40 am
Interesting code.
But can't use just do either of these:
DECLARE @result TABLE (value decimal(24, 4))
INSERT INTO @result
EXEC('SELECT 3.0 / 4 + 3.2')
SELECT * FROM @result
Or:
DECLARE @value decimal(24, 4)
EXEC sp_executesql N'SELECT @value...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 21, 2011 at 9:37 am
Try something like:
REPLACE(REPLACE(column, CHAR(13), ''), CHAR(10), '')
Of course, CHAR(13) = cr, CHAR(10) = lf
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 21, 2010 at 12:12 pm
Ah, you mean exactly as I stated in the initial comment to your q? 🙂
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 9, 2010 at 1:03 pm
I don't think you have to do all that. Just change the first SELECT ... INTO statement:
...
PTS_QA_CE = CAST(Null AS decimal(10, 2)),
...
Btw, don't use decimal(10, 2) unless you really...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 7, 2010 at 4:56 pm
Instead of a "CROSS JOIN" on the tally table, use an INNER JOIN, specifying the WHERE conditions relating to tally values as ON conditions instead.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 7, 2010 at 1:46 pm
I don't know... you have 4 instances of CHARINDEX vs just 2
Quite true. A valid point.
3 of them are exactly the same function. Unfortunately, I...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 7, 2010 at 12:44 pm
Technically, I think the first calc can be reduced ever so slightly 🙂 :
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1) AS LastDayOfLastMonth
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 6, 2010 at 4:32 pm
I think you can do that more easily, like so:
SELECT SUBSTRING(col1, (CHARINDEX('Website is', col1) + 11),
CHARINDEX(' because', col1, (CHARINDEX('Website is', col1) + 11)) -
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 6, 2010 at 3:47 pm
You're pulling data from the comma forward, so you need a leading comma as well as a trailing one.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 6, 2010 at 3:33 pm
And Lynn's method declares the leap-year person to be 18 on the 28th.
Yes, but I think someone made that point that legally in some jurisdictions, a person with...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 16, 2009 at 2:23 pm
It's not even guaranteed to be unique, which would be a bigger problem .
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 24, 2009 at 4:48 pm
Please tell me what is the most efficient way I can use to write the query?
Use EXIST with OR between to check the other tables; check the most likely table...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 24, 2009 at 4:17 pm
Hmm, but if a user bulk inserts several rows at once, many of them could get the same datetime value (at least if we're talking about std datetime accuracy to...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 24, 2009 at 4:05 pm
Viewing 15 posts - 7,516 through 7,530 (of 7,613 total)