Viewing 15 posts - 2,221 through 2,235 (of 7,619 total)
If you CAST the value to a different data type, such as decimal, SQL will automatically round the value for that type, so you will have to do a ROUND...
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 23, 2020 at 7:23 am
Should I add the Semester_ID Reference here?
I guess. What you really need to do is step back and do a true logical data model first including normalization. Your current table structures...
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 23, 2020 at 3:43 am
Also, btw, the data model you have is not correct. You should have a separate "master" Course table, with nothing about semester in it, that contains core info about...
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 23, 2020 at 3:40 am
It's likely your PK on Inventory should be ( WarehouseID, ProductID ), at least based on that query (of course if you have a significant number of queries that use...
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 23, 2020 at 3:35 am
I'd need directly usable sample data for the holiday table -- CREATE TABLE and INSERT statement(s) -- to correct this for you.
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 23, 2020 at 2:51 am
The problem is that you multiplied the minutes difference by 60; just get rid of the "60*".
SELECT CONVERT(CHAR(5), DATEADD(MINUTE, (DATEDIFF(MINUTE,CAST(@hFrom +':'+@mFrom AS TIME), CAST(@hTo +':'+ @mTo...
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 22, 2020 at 1:20 pm
> Missing the point on this last one - you have multiple joins, not just 1 join. <<
It depends on how you look at it. The view itself has only...
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 22, 2020 at 10:06 am
DECLARE @job_name nvarchar(128)
SET @job_name = 'JobA'
SELECT
job_name AS name,
COALESCE(CAST(j.enabled AS varchar(1)), 'Job not present') AS enabled,
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 22, 2020 at 9:59 am
For SQL Express, you'd need to use the task scheduler in Windows directly, instead of using SQL Server jobs. It's more of a pain, but it should work.
You should explicitly...
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 22, 2020 at 1:36 am
That's why I put the note: my EXISTS example does NOT reference an outer table. A rare situation perhaps, but not impossible, more likely:
WHERE EXISTS(SELECT 1 FROM sys.tables WHERE 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".
October 21, 2020 at 2:37 pm
Wouldn't that count every reference as a "join", including a select from a single table? And any other single select from a table.
SELECT ...
FROM dbo.table1
SELECT ...
FROM dbo.table1
WHERE EXISTS(SELECT 1 FROM...
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 21, 2020 at 12:55 pm
I think should be pretty accurate, at least without going to a lot more trouble to write. Yes, it uses recursion, because we need to find every JOIN. If somehow...
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 20, 2020 at 9:24 pm
When using NOT IN, you should make sure a NULL value does not appear in the results. For example, like below. Try that first and see if it cleans up...
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 19, 2020 at 5:25 pm
SELECT T1.DocumentNo, T2.LongSentence
FROM Table2 T2
INNER JOIN Table1 T1 ON T2.LongSentence LIKE '%' + T1.DocumentNo + '%'
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 19, 2020 at 5:17 pm
Yes, yes and yes, as long as the proc not being EXEC'd by each other.
That is, this should work fine:
--ok
EXEC sp1
EXEC sp2
EXEC sp3
because SQL will clear temp tables created within...
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 18, 2020 at 6:35 am
Viewing 15 posts - 2,221 through 2,235 (of 7,619 total)