Jeff Moden

Forum Replies Created

Viewing 15 posts - 2,956 through 2,970 (of 59,086 total)

Reply To: calculation for year month and quarter with date dimension table

Personally and generally, I think Date Dimension tables are usually overrated for such things. Using things like GROUP BY with ROLLUP, CUBE, or GROUPING SETs is much more effective. 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)

August 18, 2022 at 6:08 pm

#4078057
Reply To: Query Table without index

You also say you always want to show the largest positive number first. To be honest, that doesn't make any sense because it seems to provide no value in what...
--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)

August 18, 2022 at 1:57 pm

#4077927
Reply To: SQL Query

Consider people that have no idea about your data. We don't know things like how a "unique trip" is identified, what defines a "corresponding couple" of stations, etc. You'll also...
--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)

August 17, 2022 at 8:34 pm

#4077656
Reply To: Query Table without index
brian.cook wrote:
Here is an example where it goes from a NULL value to a Positive number then a Negative number in a three hour period.
```
WD 1116.0 (HILINE)1413HI...
```
--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)
August 17, 2022 at 8:26 pm

#4077650
Reply To: Query Table without index

brian.cook wrote:
Hi Jeff,
First thank you for responding,
The first column is the name of the locations. some are represented as a decimal representing the mile post of a location, however it...
--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)

August 17, 2022 at 8:22 pm

#4077648
Reply To: Help me understand SSIS Environments

Heh... when I read through this, my thought was "This is one of the primary reasons I don't use SSIS". 😀
--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)

August 17, 2022 at 5:39 pm

#4077583
Reply To: Combine or join 3 tables, 1 for each databases

The next step of Piet's recommendation can be found in the documentation for SQL Server. Look for "Partitioned VIEWS".
--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)

August 17, 2022 at 5:34 pm

#4077582
Reply To: Query Table without index

Your test data appears to contain two different line types, the rows for "E Hazen" being different than the rest. Save us some time and tell us which set 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)

August 17, 2022 at 1:45 pm

#4077398
Reply To: Need to rollup some rows to columns

You're welcome and thank you for the feedback.
The articles are based on "pivoting" numeric values but you can do like Kaj suggested and use Min or Max to pivot text. ...
--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)

August 17, 2022 at 1:48 am

#4077215
Reply To: Using LAG to return prior non null value
You added "columns" to the CROSS APPLY and changed the width of some but you didn't change the substring values that look like this...
```
,11,10)
,21,10)
,11,10)
,21,10)
```
--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)
August 17, 2022 at 12:26 am

#4077200
Reply To: Consistent Time

ZZartin wrote:
The difference between a datetime vs a datetime2 for this seems not very relevant for just capturing entry date the precision difference between the two is within the margin...
--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)

August 16, 2022 at 10:56 pm

#4077184
Reply To: Using LAG to return prior non null value

Chrissy321 wrote:
Thanks Jeff this looks fantastic! I truly appreciate the time you took to put this together.
I updated the CTEs to the best of my understanding but I can not...
--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)

August 16, 2022 at 9:23 pm

#4077160
Reply To: Query Tuning, Why Bother?

Virshu wrote:
Huh? why would that be?
I have no clue about why that would be. It was an observation especially but not limited to what should have been VARCHAR data...
--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)

August 16, 2022 at 5:12 pm

#4077068
Reply To: backup and storage questions

Bruin wrote:
What sort of overhead does it have on the server when running, and any idea of duration on a 2.1 billion ow table?
There have been a lot of ideas...
--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)

August 16, 2022 at 5:08 pm

#4077067
Reply To: Consistent Time

First, thanks Jeffrey and Steve for the feedback.
Also, I'll state that I never make final performance decisions based on what's in an execution plan and so I'm going to have...
--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)

August 16, 2022 at 4:47 pm

#4077045

Viewing 15 posts - 2,956 through 2,970 (of 59,086 total)