Jeff Moden

Forum Replies Created

Viewing 15 posts - 21,811 through 21,825 (of 59,072 total)

RE: Recursive data but increasing date by one day

richardmgreen1 (1/19/2015)
To be honest, I didn't think my initial post made too much sense, I was hoping the spreadsheet would explain better (obviously not:blush:)
This is my original code:-
select distinct
abs1.[UnitNumber]
,abs1.AccountNumber
,abs1.AdmitDateTime
,abs1.DischargeDateTime
,datediff(d,abs1.admitdatetime,abs1.dischargedatetime) as...
--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)

January 19, 2015 at 12:00 pm

#1771197
RE: Using GetDate() between two dates

Briceston (1/19/2015)
I have a script that will be used in SSIS to produce a data extract on weekly basis.
What would be the correct GetDate date logic for the below...
--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)

January 19, 2015 at 11:54 am

#1771194
RE: Design vehicle and its parts database in SQL server

...and if it's not homework, hire a consultant for this.
--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)

January 19, 2015 at 11:53 am

#1771193
RE: How the datediff determinines the difference between two dates?

Be advised that the "wk" DATEPART can change depending on language settings and DATEFIRST settings. It's not considered to be a good thing to use.
--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)

January 19, 2015 at 11:50 am

#1771192
RE: Performance problem when joining to a view.

Daniel Forrester 123 (1/19/2015)
Hi Guys,
I found a work around which seems to trick the query optimizer into working properly by comparing the Age_Band View to other dimensions that don't seem...
--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)

January 19, 2015 at 8:49 am

#1771148
RE: Looking for an elegant way to tranpose my query result

To add to that, you're grouping by the wrong column. You need to group by the BOX TYPE column.
--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)

January 19, 2015 at 8:26 am

#1771138
RE: Looking for an elegant way to tranpose my query result

Two methods in the following article. For performance reasons and readability (even though the code can be a bit longer), I prefer the "CROSSTAB" method.
http://www.sqlservercentral.com/articles/T-SQL/63681/
--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)

January 19, 2015 at 8:22 am

#1771135
RE: Login Failed

The Windows Login still has to be present on the SQL Server or the AD Group that the login belongs to has to be present on the SQL Server.
--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)

January 19, 2015 at 8:18 am

#1771133
RE: Recursive data but increasing date by one day

@chris-2,
Agreed. You're going with what was given and I'm trying to read minds. 😛
--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)

January 19, 2015 at 8:15 am

#1771131
RE: Alter table performance

Estimates aren't going to work here. You have to know how long this is going to take and you'll likely need to schedule some downtime.
--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)

January 19, 2015 at 8:13 am

#1771129
RE: Alter table performance

Mauricio_ (1/19/2015)
Yes, test is the best, I agree. The only problem is that I underestimated the size of the table, it has 3 billion rows.
That's gonna leave a mark....
--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)

January 19, 2015 at 8:09 am

#1771126
RE: Alter table performance

Grant Fritchey (1/19/2015)
Test, test, test, test, test.
I agree that keeping it simple and using ALTER TABLE is the right answer most of the time. I also agree that 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)

January 19, 2015 at 7:52 am

#1771117
RE: Recursive data but increasing date by one day

Although Chris' code does what you want, I'm not sure it does what you actually need. Yes, it works correctly (haven't run it but looks correct). If I'm reading...
--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)

January 19, 2015 at 7:41 am

#1771115
RE: Performance problem when joining to a view.

How long does it take to get a return if you run the following, making the appropriate change in the FROM clause, of course?
SELECT TOP 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)

January 19, 2015 at 7:26 am

#1771110
RE: Rebuilding Indexes - Clustered v Non-Clustered

The clustered index is based on RowID which, ostensibly, is an ever-increasing value. That means that new data is added to the logical end of the table (the clustered...
--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)

January 19, 2015 at 1:18 am

#1771048

Viewing 15 posts - 21,811 through 21,825 (of 59,072 total)