Viewing 15 posts - 1,216 through 1,230 (of 7,619 total)
You're welcome. I'm very glad it helped. And thanks for the follow-up and nice feedback!
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".
October 14, 2021 at 5:10 pm
A trigger is the perfect approach for this. Some developers just irrationally avoid triggers no matter what.
Be sure to specify option FIRE_TRIGGERS in your BULK INSERT command.
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".
October 13, 2021 at 6:45 pm
Give me a reason to use the new data type when I can't use date functions with it natively, or can't get DATEDIFF in particular to work with it...
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".
October 12, 2021 at 7:24 pm
I too don't think OUTER APPLY is the main problem. But I would strongly urge you to get rid of the function against the table column you are searching on,...
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".
October 11, 2021 at 1:54 pm
Not much to go on, but, in general, just put the appropriate conditions in the WHERE clause:
DECLARE @key_value ...
DECLARE @col_value ...
SET @key_value = ...
SET @col_value = ...
UPDATE...
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".
October 11, 2021 at 1:40 pm
Keep in mind, too, that the point of naming is to give accurate business names to objects, not to make things easier for developers. That is, just because a naming...
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".
October 11, 2021 at 1:20 pm
With stored procedures, I like a 2-3 letter acronym at the start to define the process (adm for admin, fin for finance, etc) followed by _ and then what...
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".
October 9, 2021 at 12:42 am
Give your databases names that reflect their business purpose. Don't use prefixes or suffixes in the name to indicate "database". MS got this half right: MS has dbs "master", "model",...
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".
October 9, 2021 at 12:38 am
Make sure the D_Date table is uniquely clustered on Date (it should already be, but verify, just in case). I'd stick with a scalar function for now, but get rid...
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".
October 7, 2021 at 2:54 pm
Actually, for an even number of rows, the median is the avg of the middle two (IIRC).
For example, for values:
1, 2, 3, 101, 200, 500 :: median is 52 (...
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".
October 6, 2021 at 8:15 pm
Agreed, we need to see the code.
And, do you have a table with non-workdays in it? We'll need to know the DDL for that table to properly determine business days.
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".
October 6, 2021 at 6:49 pm
That's not an accurate method to get a median value.
For example, if you have these values:
SELECT (MAX(value) - MIN(value)) / 2 + MIN(value)
FROM ( VALUES(1),(2),(3),(1000),(3000) ) AS data(value)
the median (middle) 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".
October 6, 2021 at 6:46 pm
;WITH cte_add_row_nums AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY Id ORDER BY Branch) AS row_num
FROM #temp
)
SELECT id, branch, city
FROM cte_add_row_nums
WHERE...
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".
October 6, 2021 at 12:57 am
You might be able to use a login trigger to deny the connection for those specific users if they were not coming from a linked server. You'd have to review...
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".
October 5, 2021 at 3:30 pm
NTILE specifies the number of groups, not the size of each group. So in your example it should be 100 not 800.
For the sample data shown, 10 groups makes 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".
October 5, 2021 at 1:55 pm
Viewing 15 posts - 1,216 through 1,230 (of 7,619 total)