Viewing 15 posts - 2,491 through 2,505 (of 7,613 total)
Select p.PREmp, p.PRDept, 0 As [Admin]
From Payroll p
UNION ALL
Select h.PREmp, p.PRDept, 1 As [Admin]
From HR h
cross join (
select distinct PRDept
...
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 17, 2020 at 10:57 pm
How's about this instead?!:
SELECT
us.Name
,SUM(wl.HoursWorked) / COUNT(DISTINCT us.TeamID) AS HoursWorked
FROM Users us
INNER JOIN WorkLog wl ON wl.UserKey = us.UserKey
GROUP BY...
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 17, 2020 at 8:08 pm
WHEN 'SCC' THEN CONCAT(' (', CAST(ROUND([Column], 0, 1) AS int), ' ', nestedTempTableAlerts.[Threshold_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".
February 17, 2020 at 7:22 pm
SELECT value AS original_value,
CAST(ROUND(value, 0, 1) AS int) AS value0,
CAST(ROUND(value, 2, 1) AS decimal(9, 2)) AS value2,
CAST(ROUND(value, 1, 1) AS decimal(9, 1)) AS value1
FROM ( VALUES(100.199) ) AS test_data(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".
February 17, 2020 at 5:58 pm
That code is for one row, so whatever values you are INSERTing for the current row contains the key(s).
For multiple rows / a batch of rows, yes, you would initially...
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 13, 2020 at 6:52 pm
You'll want to add ERROR_MESSAGE() at least to your CATCH code. That will give you the specific error msg SQL issued. You should probably go ahead and add ERROR_LINE(), although...
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 13, 2020 at 6:07 pm
See if this works for you:
;WITH cte_raw_counts AS (
SELECT COUNT(*) AS total_company_count,
SUM(CASE WHEN rr.[company_name] =...
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 12, 2020 at 8:17 pm
I think it would be more accurate and far less work to rely on the existing constraints.
You should be able to use a BEGIN TRANSACTION and a TRY/CATCH block 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 12, 2020 at 3:42 pm
I'd do an explicit sp_recompile on all the tables affected by the renames, just to be safe. Renaming is great, but it's a short-cut method that's not fully "recognized" by...
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 10, 2020 at 10:29 pm
Out of interest - by 'later', are you saying that the CAST/ TRY_CAST is being executed in the SELECT before the WHERE?
That shouldn't be possible. Per Logical...
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 10, 2020 at 6:09 pm
I think creating a table and using EXCEPT / INTERSECT would perform better than a long string of ORs. Either would also take care of NULL values for you. So,...
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 10, 2020 at 6:07 pm
WHERE PS.popup_gid = 5 OR
(PS.popup_gid = 2 AND (T.record_Key_4 = '1' OR T.Record_Key_5 NOT IN ('I', 'X')))
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 7, 2020 at 4:12 pm
Just CAST() the column as a date, that gives you the most flexibility with the format of the column:
CONVERT(varchar(5), CAST(alertQueue.[Pickup_Date] AS date), 101) AS PickupDate
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 6, 2020 at 6:49 pm
Be sure to run the Data Migration Assistant on every user db. Don't assume one db is "just like" another one.
If the db is open and usable after it migrates,...
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 5, 2020 at 3:55 pm
Rats, I'd hoped it would work too.
If it's keyed lookups, the size of the underlying table won't matter, SQL would still do the lookup, I understand that.
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 4, 2020 at 5:46 pm
Viewing 15 posts - 2,491 through 2,505 (of 7,613 total)