Viewing 15 posts - 4,186 through 4,200 (of 7,615 total)
Add an extra join condition so you only get a single occurrence:
...
INNER JOIN (SELECT ConsumerID, ConsumerAccountID
FROM Consumer
WHERE ConsumerType = 'S') s ON p.ConsumerAccountID = s.ConsumerAccountID
AND...
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".
August 26, 2016 at 2:18 pm
Did you look at the code I posted?
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".
August 25, 2016 at 10:55 am
Rather than literal strings of spaces, I recommend using SPACE(), just for readability:
/*CREATE FUNCTION ...*/
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(@Text)),
SPACE(33), ' '),
SPACE(17), ' '),
SPACE(9),...
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".
August 25, 2016 at 9:50 am
INSERT INTO dbo.TableA
( Sequence, Title, Signature, IsDefault, UserName )
SELECT A_Last.Sequence + B.row_num, A.Title, A.Signature, 'Y' AS IsDefault, B.UserName
FROM (
SELECT B2.*, ROW_NUMBER() OVER(ORDER BY B2.UserName) AS row_num
...
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".
August 25, 2016 at 9:44 am
--
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".
August 24, 2016 at 3:29 pm
GilaMonster (8/24/2016)
Your...
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".
August 24, 2016 at 2:14 pm
Afaik, there is no way to get that number back from SQL. In theory, SQL could have had to cascade thru a dozen tables (or more).
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".
August 19, 2016 at 1:24 pm
I give up. You keep randomly changing things. And this message:
"The variable name '%.*ls' has already been declared. Variable names must be unique within a query batch or...
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".
August 19, 2016 at 9:34 am
SET @SQLstmt =
N'DECLARE @RCount int ' +
' SELECT [LCPRBSELF]' +
' FROM [' + @rowDSServer+ ']' + '.'
+ '['...
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".
August 19, 2016 at 8:16 am
DOH, sorry, typo on my part. Should be:
values('DP'),('SP')
[rather than values('DP','SP')]
Edit: added bold to the parens I added to highlight them better.
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".
August 19, 2016 at 8:09 am
Floating point values are only approximate values rather than exact values. If you need to make an equals comparison, you need to use decimal values.
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".
August 18, 2016 at 4:13 pm
Your variable in the code is named @Rcount. You need to use that name in the @params clause:
EXEC sys.sp_executesql @stmt = @SQLstmt
, @params = N'@Rcount INT OUTPUT'
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".
August 18, 2016 at 11:33 am
select MBR_LAST_NAME, MBR_FIRST_NAME, BIRTH_DT, SERVICE_DT,
SERVICE_TYPE_CD, case when SERVICE_TYPE_CD = 'DP' then DP ELSE SP END AS BLOOD_PRESSURE
from (
select MBR_LAST_NAME, MBR_FIRST_NAME, BIRTH_DT, SERVICE_DT,...
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".
August 18, 2016 at 8:52 am
select MBR_LAST_NAME, MBR_FIRST_NAME, BIRTH_DT, SERVICE_DT,
MAX(case when SERVICE_TYPE_CD = 'DP' then <value> end) AS DP,
MAX(case when SERVICE_TYPE_CD = 'SP' then <value> end)...
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".
August 17, 2016 at 4:16 pm
Those are excellent points. Thus, the other rule you should follow when creating indexes is to always explicitly specify CLUSTERED or NONCLUSTERED. Again, clustering is far too critical...
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".
August 17, 2016 at 2:55 pm
Viewing 15 posts - 4,186 through 4,200 (of 7,615 total)