Viewing 15 posts - 346 through 360 (of 7,619 total)
Maybe this, if I understand the requirements correctly:
SELECT *
FROM POSDLYCHMQ P
LEFT OUTER JOIN (SELECT P0ISBN, p0CHN, P0WEDT,
COUNT(DISTINCT CASE WHEN cast(cast(sq.P0ADAT as varchar(30)) as date)
BETWEEN DATEADD(DAY,-7,DATEDIFF(DAY,0,cast(cast(sq.P0WEDT...
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 15, 2023 at 5:58 pm
As Jeffrey stated.
Most importantly, make sure you have the best clustering index on all the main tables so that you limit the scan/search activity on the tables. (Hint: most often...
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 15, 2023 at 5:42 pm
You don't need to actually count VLFs yourself, you can use sys.dm_db_log_stats instead (via column total_vlf_count).
You could fairly easily capture previous count(s) and compare them to current count using a...
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, 2023 at 3:01 pm
The problem in your original statement is that your calc -- CHARINDEX('^',RIGHT(UNDERLYING_SYMBOL, LEN(UNDERLYING_SYMBOL) - CHARINDEX('^', UNDERLYING_SYMBOL))) -- involves only numeric values, so the result is numeric. Since it's being assigned...
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, 2023 at 9:12 pm
update dbo.MainTable
set UNDERLYING_SYMBOL = STUFF(UNDERLYING_SYMBOL, 1, 1, '')
where underlying_symbol LIKE '^%'
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, 2023 at 9:10 pm
It's possible it would be far better to cluster the dbo.User_Events table by AuditTimestamp rather than UserID. That could be a significant change, however, so if you would first run...
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, 2023 at 2:27 pm
And to make the code not reliant on a particular DATEFIRST setting, don't use DATEPART, instead do this:
...
AND DATEDIFF(DAY, 0, a.date) % 7 <> 4 -- Ensure...
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, 2023 at 5:36 pm
I need just the passenger events (0 or o,1). But I think I may have gotten the answer from a colleague:
SELECT costCenterCode, scheduleDate, tripid, routeId, routename, activityId, eventOrder, CASE...
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, 2023 at 3:57 pm
It's just another expression per value, not statement. You could pre-code 10 or even 20 values without a lot of trouble:
SELECT ResourceType, CustomerID, DOB,
...
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, 2023 at 3:34 pm
I'm not sure I fully understand the requirements.
Do you need the first and last [activityId] = 0 / IN (0, 1) in the partition regardless, or does some other row...
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, 2023 at 3:17 pm
WITH cteRowNum(GroupName,AgentName,AgentEmail,
TicketsCompleted, RowNums) AS (
SELECT GroupName,AgentName,AgentEmail,TicketsCompleted,
DENSE_RANK() OVER(PARTITION BY GroupName ORDER BY TicketsCompleted DESC) AS RowNums
FROM Table
WHERE AgentName IS NOT NULL
)
SELECT cteRowNum.GroupName,cterowNum.AgentName,cteRowNum.AgentEmail, cteRowNum.TicketsCompleted
FROM cteRowNum
WHERE cteRowNum.RowNums = 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".
October 9, 2023 at 2:55 pm
I assume you could have a Function2 and then data for it. If so, there needs to be something to order the rows: an identity column, a datetime, etc.. Do...
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, 2023 at 9:24 pm
Perhaps you could create a view with the columns in your (client's) preferred order? It it was a full view, it should be updatable and therefore the view name could...
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 4, 2023 at 4:19 pm
SELECT
SUM(CASE WHEN HowManyDays <= 30 THEN thisAmt ELSE 0 END) AS aging_0_30,
SUM(CASE WHEN HowManyDays >= 31 AND HowManyDays <=...
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 3, 2023 at 8:27 pm
(1) Use differentials rather than full backups for most backups. For example, daily diffs and only, say, weekly full backups.
(2) If you're not already using backup compression, and particularly if...
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 3, 2023 at 2:22 pm
Viewing 15 posts - 346 through 360 (of 7,619 total)