Jeff Moden

Forum Replies Created

Viewing 15 posts - 4,021 through 4,035 (of 59,098 total)

Reply To: group records by week

Bruin wrote:
Thanks the above query worked great. If I wanted to do a daily extract of records saying start at 00:00 to 11:59 how would that query look?
Thx.
What does 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)

January 29, 2022 at 1:54 am

#3978552
Reply To: Question about malformed ZIP codes

Of course, an even better way would be to buy an up-to-date zip code database and do a lookup because not every number from 00000 to 99998 is a valid...
--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 29, 2022 at 1:38 am

#3978548
Reply To: Question about malformed ZIP codes
Another possibility is a bit shorter, uses only LIKE instead of LIKE and the LEN() function, and does the same thing is...
```
IIF(D.Zip LIKE '[0-9][0-9][0-9][0-9][0-9]', d.Zip, '99999') 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 29, 2022 at 12:31 am

#3978536
Reply To: Unable to append strings to record from SELECT Statement

As a bit of a sidebar, my personal preference for assigning alias names to columns is not to use "expression AS aliascolumnname". I use aliascolumname = expression.
Some people don't like...
--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 29, 2022 at 12:20 am

#3978535
Reply To: Unable to append strings to record from SELECT Statement

Imrans23 wrote:
Hey Ratbak,
Thanks again, I tried SELECT CAST('A1-'+CustomerID as text) as CustomerID FROM Customers
Get the message. Conversion failed when converting the varchar value 'C25-' to data type int. Any help...
--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 29, 2022 at 12:09 am

#3978534
Reply To: sql query help

Use the test data you were kind enough to post (nicely done there), here's an answer that uses a CROSSTAB. If you actually DO want NULL to appear, just change...
--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 28, 2022 at 2:40 am

#3978129
Reply To: Unable to append strings to record from SELECT Statement

Perhaps the following but of study will convince you... 😀
https://docs.microsoft.com/en-US/sql/t-sql/queries/select-transact-sql?view=sql-server-ver15
https://www.w3schools.com/sql/sql_select.asp
https://www.techonthenet.com/sql_server/select.php
https://sql-server-tutorial-blog.blogspot.com/2022/01/sql-server-select-statement.html
--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 28, 2022 at 2:02 am

#3978121
Reply To: Data not returned in Date Range WHERE

ScottPletcher wrote:
Jeff Moden wrote:
ScottPletcher wrote:
Don't use functions in a WHERE clause, that's a very bad habit. Yes, SQL Server now generally covers for you with dates/datetimes, but not always, there are...
--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 28, 2022 at 1:54 am

#3978118
Reply To: How to reclaim space from a table

WhiteLotus wrote:
every night we perform Index maintenance on that database
As a bit of a sidebar, I can almost guarantee that you're doing you index maintenance wrong. Spend more time doing...
--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 27, 2022 at 8:50 pm

#3978056
Reply To: How to reclaim space from a table

ScottPletcher wrote:
I'd clean up the prod problem first. You can use a backup and research to research the other stuff, if you think it's necessary.
Only if you have a place...
--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 27, 2022 at 8:41 pm

#3978055
Reply To: Create View in stored procedure

Ummmmm... I don't see anywhere where the OP did an EXEC [art].[pCreateArtList_View]... 😉
You DO have to actually execute the stored procedure for IT to create the view.
--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 27, 2022 at 8:33 pm

#3978053
Reply To: Data not returned in Date Range WHERE

ScottPletcher wrote:
Don't use functions in a WHERE clause, that's a very bad habit. Yes, SQL Server now generally covers for you with dates/datetimes, but not always, there are some restrictions. ...
--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 27, 2022 at 8:25 pm

#3978052
Reply To: QQ regarding DB_ID()

ScottPletcher wrote:
Don't rely on the DB_ID() to reference a db. Assume it could change at any time.
If you'll notice, in its system tables, MS stores the db name rather than...
--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 27, 2022 at 8:21 pm

#3978050
Reply To: QQ regarding DB_ID()

David Burrows wrote:
Not sure what you are alluding too but I tried all three options without creating a new database and in each case the id was reused. Only the following...
--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 27, 2022 at 8:18 pm

#3978049
Reply To: Restore autmomation

ramyours2003 wrote:
We are using the sql agent job to take the local drive backup of all the databases on the source and having sufficient permissions on the destination server .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)

January 27, 2022 at 2:55 pm

#3977932

Viewing 15 posts - 4,021 through 4,035 (of 59,098 total)