Jeff Moden

Forum Replies Created

Viewing 15 posts - 6,226 through 6,240 (of 59,101 total)

Reply To: Count the Number of Weekend Days between Two Dates

Jeffrey Williams wrote:
I was thinking more about this - realized we can do this:
SELECT weekend_day_count=y/7-x/7+(y+1)/7-(x-1)/7
FROM (VALUES(DATEDIFF(DAY,-53690,'1899-01-01') + 1,DATEDIFF(DAY,-53690,'1900-01-14') + 1))x(x,y)
We can specify the anchor date as 1753-01-01 (-53690) and then add...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 23, 2020 at 12:34 pm

#3800414
Reply To: difference between two times
Just curious... where are the original "from" and "to" values coming from? I ask because, if we know that, we might be able to...
1. Strip a whole lot complexity from...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
October 22, 2020 at 9:07 pm

#3800235
Reply To: Killed SPID on xp_LogInfo not rolling back and growing TempDB

Just a shot in then dark (which I can help with), does anything in the job make a call to xp_CmdShell?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 22, 2020 at 8:27 pm

#3800221
Reply To: Backup taking too long - SQL Server 2016

PJ_SQL wrote:
We had the switch upgrade right before the backup started taking long time to finish.
I'm thinking that you've just identified your problem with the backups. 😉
Have the check the...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 21, 2020 at 9:48 pm

#3799883
Reply To: Backup taking too long - SQL Server 2016

PJ_SQL wrote:
No, the backup is not broken into several files, we can try to do that.
As you said, you didn't have this problem just a little bit ago. I'm with...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 21, 2020 at 7:35 pm

#3799849
Reply To: Poor peformance

Johan Bijnens wrote:
here is a nice discussion about it all "Mixed Extents: 02 - Indexing and Stats Maintenance"
I saw that. It's a shame that they didn't get into the Index...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 21, 2020 at 6:43 pm

#3799840
Reply To: Poor peformance

ramyours2003 wrote:
I ran the update stats on a table by creating the indexes, when i checked after executing the update stats the query performance is degraded , may i know...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 20, 2020 at 3:47 pm

#3799323
Reply To: Need help. Find matchng Substring

On the subject of "performance", trailing and mid-string lookups are terrible. If you have to do this more than once, consider another method especially if the data is relatively static. ...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 20, 2020 at 2:19 pm

#3799275
Reply To: Chopping Off Data

Jonathan Gennick wrote:
The article says that "When a CSV file longer than that is opened, the bottom rows get cut off and are no longer displayed."
Did Excel provide any warning that...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 19, 2020 at 7:15 pm

#3798987
Reply To: Attachment file types

Steve Jones - SSC Editor wrote:
I'll file a note to list allowed types. I think it's a short list, so this shouldn't be hard.
It also seems strange to me that a good number of...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 19, 2020 at 7:09 pm

#3798984
Reply To: Help With a Dynamic Pivot Returning All Nulls

Any chance you could include all of the code from the variable (as code, not a graphic, please) that gets executed so I can have a look?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 19, 2020 at 5:57 pm

#3798950
Reply To: processing a flat file without knowing the name

david.edwards 76768 wrote:
Jeff Moden wrote:
8<
I also have been known to use xp_CmdShell to work some serious magic for things like file prep (especially on CSVs), file handling (find it, use it, move...
Attachments:
You must be logged in to view attached files.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 19, 2020 at 5:38 pm

#3798945
Reply To: Count the Number of Weekend Days between Two Dates

Just to be sure, Jacob... I really like your solution (especially since I've tested the bejeezus out of it in the area of functionality) because of the integer math, lack...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 18, 2020 at 5:11 pm

#3798591
Reply To: using same name for cursors and temp tables on different stored procedures

To Frederico's good point, please see the following article for an introduction to "Tally Tables" (also known as "Numbers" tables). Keep in mind that it's only an introduction and do...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 18, 2020 at 4:58 pm

#3798590
Reply To: Count the Number of Weekend Days between Two Dates

SwePeso wrote:
I use Google...
https://weblogs.sqlteam.com/peterl/2009/07/12/how-to-calculate-the-number-of-weekdays-for-any-given/
That, however, doesn't count the number of weekend days as the OP requested. It wouldn't, however, provide much difficultly to the OP to realize that if you...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 17, 2020 at 7:35 pm

#3798406

Viewing 15 posts - 6,226 through 6,240 (of 59,101 total)

Jeff Moden

Forum Replies Created

Attachments: