Jeff Moden

Forum Replies Created

Viewing 15 posts - 7,321 through 7,335 (of 59,102 total)

Reply To: Case when with a Pivot

gjoelson 29755 wrote:
To answer the question "can a Cust_num have more that one Co_num or not?" yes, in our case customers can have multiple Co_num , so need to figure a...
--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)

April 12, 2020 at 12:08 am

#3742252
Reply To: Stored Procedure

Shifting gears a bit, it sounds to me like the SSNs in these 45 tables are in clear text. If that's true, please inform management that's freakin' insanely stupid and...
--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)

April 11, 2020 at 8:16 pm

#3742236
Reply To: Create delete update tables in one database to another database.

sampathmeka wrote:
Hi Williams,
Thanks for making your time for reply!!
why do you need a copy of the data in the secondary database?
My reply:-Actually my task was to update the data across...
--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)

April 11, 2020 at 8:09 pm

#3742235
Reply To: Case when with a Pivot

Bloody amazing machines nowadays. The laptop I'm currently using has the following specs...
Processor Intel(R) Core(TM) i7-8750H CPU @ 2.20GHz, 2208 Mhz, 6 Core(s), 12 Logical Processor(s) (w/automatic Overclock up 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)

April 11, 2020 at 7:47 pm

#3742232
Reply To: Cannot delete filename. There has been a sharing violation.

Post deleted. It's no longer needed.
--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)

April 11, 2020 at 6:19 pm

#3742230
Reply To: Do You Have a Mentor?

Great article. Just don't settle on a single mentor because no one person knows it all. Have many and include yourself in that. It's amazing what you can string together...
--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)

April 11, 2020 at 5:24 pm

#3742228
Reply To: Case when with a Pivot
Jeffrey Williams wrote:
In both solutions - I would move the calculation to a CROSS APPLY:
```
SELECT  Cust_num
        ,Co_num
   ...
```
--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)
April 11, 2020 at 5:03 pm

#3742223
Reply To: Case when with a Pivot

Steve Collins wrote:
Very very nice pre-aggregation! Now thaaaat's the way to do it.
It's not just me... we did it together. You reminded me not only of the IIF function but 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)

April 11, 2020 at 5:00 pm

#3742222
Reply To: Select return no records

jcelko212 32090 wrote:
>> A better idea is to use the correct datatype. ,<<
I agree with that statement. In fact read his posted this in a wonderful single principal, for which a...
--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)

April 11, 2020 at 4:52 pm

#3742220
Reply To: Case when with a Pivot
Steve Collins wrote:
```
select
  Cust_num, Co_num,
  sum(amount) Total,
  sum(iif(floor(datediff(d,due_date,getdate())/30)=0,amount,0)) [1-30 Days],
  sum(iif(floor(datediff(d,due_date,getdate())/30)=1,amount,0)) [31-60 Days],
  sum(iif(floor(datediff(d,due_date,getdate())/30)=2,amount,0)) [61-90 Days],
  sum(iif(floor(datediff(d,due_date,getdate())/30)>2,amount,0)) [>90 Days]
from EES_App.dbo.artran_all
group by Cust_num,...
```
--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)
April 11, 2020 at 3:45 pm

#3742213
Reply To: Whad does sql server dba need to know about Azure CLoud?

Post deleted...
--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)

April 10, 2020 at 10:09 pm

#3742129
Reply To: Case when with a Pivot

Since you attempt at code and what you posted as your desired final output have several difference between the two, I split the difference and can derived the following code. ...
--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)

April 10, 2020 at 8:57 pm

#3742117
Reply To: Select return no records

A better idea is to use the correct datatype. If the column is supposed to only handle digits, then changing the datatype of the column to be an INTEGER 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)

April 10, 2020 at 6:45 pm

#3742106
Reply To: Query time after specified date

jham55 wrote:
I need help with a query. If I have a list of people each with a service date and I want to sum their spend one year and two...
--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)

April 10, 2020 at 6:33 pm

#3742097
Reply To: Convert date value

Recreating such a thing in yet another "string format" would go against my grain. Why not convert it to a proper DATE or DATETIME datatype?

--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)

April 10, 2020 at 4:11 pm

#3742040

Viewing 15 posts - 7,321 through 7,335 (of 59,102 total)