Jeff Moden

Forum Replies Created

Viewing 15 posts - 4,546 through 4,560 (of 59,069 total)

Reply To: How to find if the database is not being used

david.edwards 76768 wrote:
Jeff Moden wrote:
I figured it might be better than my BSOFH method which is to take the database offline and wait for the email. 😀
I thought that was accepted "Best...
--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)

September 28, 2021 at 12:45 am

#3933737
Reply To: How show only first friday of every month

Jonathan AC Roberts wrote:
Jeff Moden wrote:
There is a serious problem with the "equivalent" code you wrote though. It's not "equivalent" in the results for the last 3 tests. The table code comes up...
--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)

September 28, 2021 at 12:41 am

#3933736
Reply To: How show only first friday of every month

@ Jonathan,
I modified your code and comments a bit to provide an easier to read output on SQL Profiler. I also made each snippet write to a throwaway variable to...
--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)

September 27, 2021 at 4:54 pm

#3933605
Reply To: hierarchy related query

My belief is that you're doing this whole thing the wrong way. Have a look at how easy it can be at the following URL.
https://www.sqlservercentral.com/articles/hierarchies-on-steroids-1-convert-an-adjacency-list-to-nested-sets
Depending on your reporting requirements,...
--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)

September 27, 2021 at 3:25 pm

#3933556
Reply To: How to find if the database is not being used

I figured it might be better than my BSOFH method which is to take the database offline and wait for the email. 😀
--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)

September 27, 2021 at 3:01 pm

#3933543
Reply To: help with script to calculate gaps and overlaps from list of times

I've always considered the following article to be a must read on this subject.
https://www.itprotoday.com/sql-server/new-solution-packing-intervals-problem
--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)

September 27, 2021 at 11:47 am

#3933444
Reply To: How to find if the database is not being used

Alejandro Santana wrote:
The way we do it is that we don't check when was the last transaction, we put an extended event capturing anything that goes into the database and we...
--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)

September 27, 2021 at 11:35 am

#3933439
Reply To: Conversion failed when converting date and/or time from character string

carlton 84646 wrote:
Thank you Jeff. Spot on. Thanks
Scott was the first to respond. 😉
--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)

September 27, 2021 at 11:16 am

#3933430
Reply To: How to calculate Monthly aggregates from daily data

asita wrote:
Thank you very much Jeff. I noticed one thing if there is none changed in the latest month it is not showing any record for the month it would...
--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)

September 27, 2021 at 10:35 am

#3933422
Reply To: How to calculate Monthly aggregates from daily data

asita wrote:
Data output is for information what output columns expected etc. may not need to exactly match. but around.
Thank you much and apologize for the troublesome code
asiri
Just remember... 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)

September 27, 2021 at 2:05 am

#3933313
Reply To: How to calculate Monthly aggregates from daily data
That was just one of the problems with the data v.s. what you wanted for the output.
Anyway, here's the code you need.
```
WITH cte AS
(
 SELECT  FileMonth...
```
--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)
September 27, 2021 at 1:53 am

#3933310
Reply To: How to calculate Monthly aggregates from daily data

The latest version of the code works just fine now. I also took a look at your expected output. We don't need to create any code to know that your...
--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)

September 27, 2021 at 1:12 am

#3933303
Reply To: How to calculate Monthly aggregates from daily data

You really need to actually try to run the code you posted before you post it. Heh... you even apparently ignored what I told you what was wrong with 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)

September 27, 2021 at 12:01 am

#3933290
Reply To: GUIDs as clustered index

Ok. That sounds like the only reason you're adding the GUID is to try to avoid fragmentation. By itself, that's probably not a good enough reason. And, again, there's no...
--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)

September 26, 2021 at 11:54 pm

#3933287
Reply To: Conversion failed when converting date and/or time from character string

Not to mention "Y", "N", month names with no reference to a year and much more.
--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)

September 26, 2021 at 11:05 pm

#3933279

Viewing 15 posts - 4,546 through 4,560 (of 59,069 total)