Viewing 15 posts - 2,776 through 2,790 (of 7,613 total)
It's a waste of space to store dashes: simply char(9) will do. Assuming this is some type of temporary table, otherwise you need to encrypt the data which means you'd...
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".
July 1, 2019 at 7:48 pm
You can do this easily with string functions, but it’s still a stinking kludge caused by bad design. Most of the work in SQL is done in 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".
June 28, 2019 at 9:31 pm
;WITH SampleData AS (
SELECT * FROM (VALUES (''),('3#5'),('8063#0018375'),('8063#018375'),
('063#018375'),('063invalid018375')) Data(AccountNumber)
)
SELECT AccountNumber, RIGHT('000000' + SUBSTRING(AccountNumber,
...
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".
June 28, 2019 at 7:20 pm
IF DATEDIFF(DAY, 0, GETDATE()) % 7 <= 4 /*0=Mon;4=Fri;5=Sat*/
AND NOT EXISTS(SELECT 1 FROM dbo.your_holiday_table yht WHERE yht.date = CAST(GETDATE() AS date))
BEGIN
INSERT INTO...
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".
June 28, 2019 at 3:03 pm
On top of that throw in that, the DDL and the DML have different rules. In DML, the {false, unknown} values are treated the same and reject a search condition....
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".
June 27, 2019 at 6:45 pm
Cluster the Attrib table on ( ClinicID, ItemID ) and any of the methods will likely run fast enough, although I like the CROSS APPLY approach myself.
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".
June 25, 2019 at 9:29 pm
Nothing's wrong with it, per se. I thought checking LEN was a bit more efficient, but either will do. Edit: SQL may actually convert { = '' } to checking...
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".
June 25, 2019 at 9:22 pm
1 Don't concatenate the strings, that's more overhead, just check the len of each:
LEN([address 1]) + LEN([address 2]) + LEN([address 3]) = 0 /*easier, to me*/
OR
(LEN([address 1]) = 0 AND...
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".
June 25, 2019 at 8:28 pm
We have to make many guesses here, since you didn't specify any details on these tables.
If you only need to lookup consolidation_ind if it is = 1, then create 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".
June 24, 2019 at 2:46 pm
As regards the original "1. Key column ...", it should at least be demoted to a secondary key (to make the clustering key unique).
This table almost certainly should be clustered...
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".
June 19, 2019 at 9:09 pm
Try this:
BEGIN TRY
BEGIN TRANSACTION [Tran1]
select top (1) @caid=ca.id from Cases ca WITH (UPDLOCK)
where ca.applicationstatusentityID in (1,2,12,15)
Insert into CaseAssigned table the caseId selected above
Delete from the Cases table once a 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".
June 19, 2019 at 7:14 pm
I ignored the actual calc before, but Drew is quite right, of course, that needs corrected too:
SELECT TOP (100) PERCENT
DATEADD(SECOND, DATEDIFF(SECOND, base_date,...
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".
June 19, 2019 at 3:45 pm
I'd use RIGHT rather than PATINDEX, just because I think's it mildly clearer:
WHERE RIGHT(name, 2) LIKE '[0-9][0-9]'
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".
June 19, 2019 at 3:38 pm
You can also use a computed column for as_of_month, there's no need to physically store it again.
as_of_date date NOT NULL,
as_of_month AS CONVERT(varchar(6), as_of_date, 112),
That column is fully usable by all...
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".
June 18, 2019 at 8:57 pm
I'm guessing the ParentTaskCd is used to link the tasks together. Naturally adjust the code as needed to get the specific results you need, but this is a general way...
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".
June 18, 2019 at 8:54 pm
Viewing 15 posts - 2,776 through 2,790 (of 7,613 total)