Jeff Moden

Forum Replies Created

Viewing 15 posts - 6,751 through 6,765 (of 59,102 total)

Reply To: A faster way to parse QueryPlan XML???

Jason A. Long wrote:
Jeff Moden wrote:
Wouldn't it be easier to take a snapshot of sys.dm_db_index_usage_stats from one day till the next and do a comparison? The added benefit here is that it includes...
--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)

July 22, 2020 at 5:53 am

#3772343
Reply To: Calculate aging between two date fields excluding weekends and holidays in SQL
ScottPletcher wrote:
Jeff Moden wrote:
Anand929 wrote:
Hi,
Please refer below code snippet -
```
;WITH DateTable AS (
SELECT * FROM (VALUES
('2020-07-01',0,0),
('2020-07-02',0,0),
('2020-07-03',0,1),
('2020-07-04',1,0),
('2020-07-05',1,0),
('2020-07-06',0,0),
('2020-07-07',0,0),
('2020-07-08',0,0)
) AS t(DateValue,isWeekEnd,isHoliday)
),
TicketTable AS (
SELECT * FROM (VALUES
('Ticket1','2020-07-01','2020-07-06'),
('Ticket2','2020-07-07','2020-07-08'),
('Ticket3','2020-07-07',NULL)
) AS t(Ticket,CreateDate,ResolvedDate)
)
SELECT t.Ticket,t.CreateDate,t.ResolvedDate,
DATEDIFF(DAY,t.CreateDate,t.ResolvedDate) - SUM(d.isWeekEnd)-SUM(d.isHoliday)...
```
--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)
July 22, 2020 at 5:49 am

#3772332
Reply To: Select Statement Where Column Does Not Exist

dallastexas wrote:
Yes, I was also thinking from a maintenance perspective dynamic would not be the best solution. Today its 3 missing, it might be 5 in the future so adding...
--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)

July 22, 2020 at 5:15 am

#3772289
Reply To: Select Statement Where Column Does Not Exist

Jeff Moden wrote:
You said that the files that are missing columns are the same except for the missing columns. With that in mind, it's a whole lot easier if you make...
--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)

July 22, 2020 at 5:12 am

#3772285
Reply To: Select Statement Where Column Does Not Exist

You said that the files that are missing columns are the same except for the missing columns. With that in mind, it's a whole lot easier if you make multiple...
--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)

July 22, 2020 at 5:11 am

#3772283
Reply To: Trying to Make combinations within a bracket in SSMS column
Jonathan AC Roberts wrote:
Here's my attempt:
```
;with TestData as 
(
    SELECT *
      FROM (VALUES ('A[kfg]'),
       ...
```
--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)
July 22, 2020 at 1:46 am

#3772027
Reply To: Trying to Make combinations within a bracket in SSMS column

dndaughtery wrote:
Not sure if i read it correctly but look into STRING_AGG or STRING_SPLIT
Got 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)

July 22, 2020 at 12:28 am

#3771967
Reply To: SQL Query Help - CHARINDEX

Jason A. Long wrote:
In addition to Jeff's good comments, another (and in my own opinion better) solution would be to normalize the table and split that column into a completely different table,...
--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)

July 22, 2020 at 12:26 am

#3771965
Reply To: Calculate aging between two date fields excluding weekends and holidays in SQL

BI_Analyticz wrote:
Hi Friends,
I have a table which has DateTime, IsWeekend, IsHoliday fields in my DB.
I have another table which has the list of tickets logged with columns like
Select TicketID, Status,CreatedDate,ResolvedDate,...
--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)

July 22, 2020 at 12:17 am

#3771961
Reply To: Calculate aging between two date fields excluding weekends and holidays in SQL
Anand929 wrote:
Hi,
Please refer below code snippet -
```
;WITH DateTable AS (
SELECT * FROM (VALUES
('2020-07-01',0,0),
('2020-07-02',0,0),
('2020-07-03',0,1),
('2020-07-04',1,0),
('2020-07-05',1,0),
('2020-07-06',0,0),
('2020-07-07',0,0),
('2020-07-08',0,0)
) AS t(DateValue,isWeekEnd,isHoliday)
),
TicketTable AS (
SELECT * FROM (VALUES
('Ticket1','2020-07-01','2020-07-06'),
('Ticket2','2020-07-07','2020-07-08'),
('Ticket3','2020-07-07',NULL)
) AS t(Ticket,CreateDate,ResolvedDate)
)
SELECT t.Ticket,t.CreateDate,t.ResolvedDate,
DATEDIFF(DAY,t.CreateDate,t.ResolvedDate) - SUM(d.isWeekEnd)-SUM(d.isHoliday) AS TicketAge...
```
--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)
July 22, 2020 at 12:15 am

#3771960
Reply To: SSIS 4 Minute Job Takes Over 19 *Hours* on New Server

Have you actually check what code is running on the server during this 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)

July 21, 2020 at 11:43 pm

#3771932
Reply To: Update

6pravin9@gmail.com wrote:
I have a Use case like.
We migrated around 1000 millions of rows into around 5-6 tables.
client requirement frequently change to update some columns. Is there any best way 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)

July 21, 2020 at 11:20 pm

#3771926
Reply To: DateDiff for only @p_start_date and @p_enddate

cbrammer1219 wrote:
So I am trying to get the datediff for parameters selected, even though the 2 dates could span 60 days, for an example I have 2 dates available(2018-01-05 11:08:00)...
--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)

July 21, 2020 at 11:17 pm

#3771925
Reply To: How to performance tune ssis pkg when dev environment isnt as good as prod

Congratulations on the improvement you made but I don't believe your job is complete yet. My biggest concern would be to find out exactly why a single package is consuming...
--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)

July 21, 2020 at 11:12 pm

#3771923
Reply To: Best way to replicate 1 table from Production DB to Dev Db

drbob wrote:
There is a job that runs every 2 hours, it grabs the last 10k records, truncates and loads those records back with fresh new IDs.
To what end? What is...
--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)

July 21, 2020 at 11:08 pm

#3771922

Viewing 15 posts - 6,751 through 6,765 (of 59,102 total)