Viewing 15 posts - 1,276 through 1,290 (of 7,619 total)
Fwiw in 2021. In the FROM clause use dbo.fnTally and CROSS APPLY (both twice) to hierarchically generate the first 7 days of each month. Then in the 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".
September 21, 2021 at 10:47 pm
I believe the calc should be:
CAST(((f.TOTFCST - f.TOTHIST) * 100.00) / f.TOTHIST AS decimal(5, 2))
I have no idea at all why you are subtracting 1 from the quotient in 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".
September 21, 2021 at 10:43 pm
Fwiw in 2021. In the FROM clause use dbo.fnTally and CROSS APPLY (both twice) to hierarchically generate the first 7 days of each month. Then in the WHERE clause...
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".
September 21, 2021 at 6:48 pm
If you add the clustering before you add the data, make sure that you use WITH(TABLOCK) on the INSERT INTO statement to help take advantage of "Minimal Logging", which...
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".
September 21, 2021 at 6:47 pm
You need to cluster tables #SmallerRange and #LargerRange on the the JOIN columns: ( VID, IorO, OtherLocationID ). If those 3 column values are unique in one/both of the tables,...
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".
September 21, 2021 at 6:21 pm
SELECT OBJECT_NAME(object_id) AS object_name, OBJECTPROPERTYEX(object_id, 'BaseType') AS object_type
FROM sys.sql_modules
WHERE definition LIKE N'%<string_you''re_looking_for>%'
ORDER BY 2, 1
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".
September 20, 2021 at 12:41 am
SELECT
COALESCE(P.DatabaseName, D.DatabaseName, T.DatabaseName, S.DatabaseName) AS DatabaseName,
CASE WHEN P.DatabaseName IS NULL THEN '' ELSE 'Yes' END AS Prod,
...
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".
September 17, 2021 at 4:23 pm
Best is to move the SUM() into a derived table, like so:
SELECT F.LOC AS 'Chain', F.DMDUNIT AS 'Item', D.DESCR AS 'Item Attributes. Description', D.U_PRODUCT_CATEGORY AS 'Item Attributes....
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".
September 17, 2021 at 1:11 am
You're welcome!
Btw, you got 0 in the original calc because 2/100 is 0.02. But, since the values were integer, SQL makes the result integer and thus 0 only (the .02...
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".
September 16, 2021 at 10:20 pm
...
CAST(p.QTY * 100.00 / s.OH AS decimal(5, 2)) AS 'Instock %'
...
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".
September 16, 2021 at 10:03 pm
WITH cde_a AS
(SELECT 'Berries' AS fruit_type,
'strawberries' AS fruit,
1 AS red
UNION ALL
SELECT...
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".
September 15, 2021 at 6:46 pm
#1 is (way) too much overhead.
#2 is better. You want static code that is generated dynamically.
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".
September 15, 2021 at 2:41 pm
In a DB I executed
alter database xxx set read_committed_snapshot ON
If I execute
set transaction isolation level read committed
Is the session with isolation level read committed or read committed snapshot?
Read Committed...
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".
September 15, 2021 at 2:39 pm
AND, looking a bit closer, my code doesn't work correctly using the following data but the mod to pietlinden's code does, so use his code with the mod.
Here's the...
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".
September 15, 2021 at 3:36 am
Of course lots of changes could be made to a db that would not be reflected in sys.dm_db_index_usage_stats. For example, file(s) added; user(s) added/removed; permissions added/removed; etc..
The most likely causes:...
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".
September 10, 2021 at 7:08 pm
Viewing 15 posts - 1,276 through 1,290 (of 7,619 total)