ScottPletcher

Forum Replies Created

Viewing 15 posts - 2,266 through 2,280 (of 7,614 total)

Reply To: Self referencing formula has me stumped

Here's how to calc the new selling price for any new net margin. (I always did love algebra.)

Edit: I didn't see Des's until after I had posted mine.

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 1, 2020 at 4:07 pm

#3792300

Reply To: trigger on a Large 5 billion row table

Hmm, that task sounds like it should be a single INSERT statement, which means SQL should resolve GETDATE() only once. Therefore it seems as if your trigger is using a...

October 1, 2020 at 9:57 am

#3792181

Reply To: convert scalar function to table valued function

You don't need a calendar table for this. Also, note that the Friday check below works under any/all DATEFIRST settings. DATEFIRST is not something you want to change in your...

October 1, 2020 at 9:50 am

#3792178

Reply To: Calculating offset (adding days to a date but keeping same day name of week)

SELECT DATEADD(DAY, (@DaysDifference + 6) / 7 * 7, @OriginalFirstStartDate)

September 29, 2020 at 9:04 pm

#3791760

Reply To: issue with adding another column in a table

The UPDATE "doesn't work" because since reserve is null, it will stay null, even though you think you "added 1 to it". NULL + 1 = NULL.

If you prefer, you...

September 29, 2020 at 8:58 pm

#3791759

Reply To: Only return first matches per Id and calculate difference of dates in minutes

SELECT
    Id,
    MIN(CASE WHEN StatusName = 'Draft' THEN CreatedOn ELSE NULL END) AS Draft,
    MIN(CASE WHEN StatusName =...

September 29, 2020 at 4:12 am

#3791499

Reply To: Error for updating Extended Property

Would you provide the actual T-SQL statement you were attempting to execute?

September 28, 2020 at 3:13 pm

#3791348

Reply To: Error - The conversion of a varchar data type to a datetime data

The fix is:

Select STUFF((Select Distinct + ', '+ CONVERT(varchar(20), A1.date, 105)

...

Since A1.date is a datetime, which has a higher precedence than varchar, SQL is attempting to add ',' to the...

September 28, 2020 at 7:29 am

#3791230

Reply To: about the option (fast n) and acutal execution plan

Frankly the percentages from an estimated query plan are often so inaccurate they should just be ignored.

Look at the actual operations in the query plan, not the percentages.

September 28, 2020 at 7:23 am

#3791229

Reply To: SQL Agent error history

Restore old msdb backup(s) to a different db name. We typically add '_' and the date of the backup to the name. Something like this:

RESTORE DATABASE msdb_20190915 FROM DISK =...

September 25, 2020 at 3:55 pm

#3790985

Reply To: Function Code

As to the actual code, a more straightforward way is to CAST to date, which will perforce remove the time value, then CAST back to datetime (this probably isn't strictly...

September 25, 2020 at 3:48 pm

#3790984

Reply To: sql server 2016 performance tuning

Getting rid of NOLOCK will only hurt performance, never help it. That's not an endorsement of nor objection to NOLOCK in these specific statements, just a general statement of fact. ...

September 22, 2020 at 7:17 pm

#3790188

Reply To: slow query performance

Quote: "We couldn't do much about the database design as it is a vendor specific database."

Understood. Lol, the only thing potentially worse than a "programmer (non)'designed' table" is a "vendor...

September 22, 2020 at 3:38 pm

#3790138

Reply To: Insert Trigger

Try below. Sorry, I don't have time right now to explain the code.

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO

CREATE TRIGGER [dbo].[Dupl_Rec]
ON [dbo].[Table A]
AFTER INSERT 
AS
SET NOCOUNT ON;

INSERT INTO dbo.Audit...

September 21, 2020 at 5:21 pm

#3789857

Reply To: slow query performance

vsamantha35 wrote:

DesNorton wrote:

According to your table structure, there is no index on [MULE_BATCH_ID]. This means that SQL has to do a table scan to find the record.

You are doing a...

September 21, 2020 at 2:47 pm

#3789814

Viewing 15 posts - 2,266 through 2,280 (of 7,614 total)