ChrisM@Work

Forum Replies Created

Viewing 15 posts - 61 through 75 (of 10,143 total)

Reply To: Outstanding balances for invoice table

Which method of running total is the report currently using?
Have you considered using SUM() OVER()?
Whichever method you're using, indexing will profoundly affect the query velocity.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 4, 2020 at 1:22 pm

#3722905
Reply To: Are the posted questions getting worse?

jasona.work wrote:
OK, so I'm curious and interested in expanding my repertoire when it comes to liquor...
Whiskey / whisky vs scotch, and, what's so bad about Jack D?
(Note, I've only recently...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 31, 2020 at 2:13 pm

#3722015
Reply To: Are the posted questions getting worse?

Ed Wagner wrote:
ChrisM@Work wrote:
Oh and Ed Wagner, if you're reading this - I tried your recommendation of bacon and it's absolutely wonderful, thank you so much matey.
Thank you so much for...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 31, 2020 at 10:59 am

#3721954
Reply To: Are the posted questions getting worse?

MVDBA (Mike Vessey) wrote:
ChrisM@Work wrote:
Did someone mention food?
please don't start this - as much as I love food, i'm sick of it at the minute - i'm forced to eat nearly 5000...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 30, 2020 at 4:43 pm

#3721683
Reply To: Are the posted questions getting worse?

Did someone mention food?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 30, 2020 at 4:07 pm

#3721674
Reply To: Partition by and count

Sure
GROUP BY City ORDER BY COUNT(*)
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 2, 2020 at 4:03 pm

#3711834
Reply To: Partition by and count
```
SELECT city, 
       COUNT(*) 
FROM gcdf_DB..PeopleContactInfo
GROUP BY City

SELECT city, 
       COUNT(DISTINCT PeopleID) 
FROM gcdf_DB..PeopleContactInfo
GROUP BY...
```
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 2, 2020 at 3:50 pm

#3711827
Reply To: I am looking to optimize this T-SQL Statement (it generates very High Reads..)

If some conditions are met...
https://sqlperformance.com/2018/02/sql-plan/setting-and-identifying-row-goals
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 14, 2019 at 3:56 pm

#3697712
Reply To: I am looking to optimize this T-SQL Statement (it generates very High Reads..)

Please post an actual (as opposed to estimated) execution plan as a .plan attachment. You can use your plan interpreter to mask real table names if necessary. Do this after...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 12, 2019 at 8:43 am

#3696947
Reply To: Need help to calculate in Single Query
```
SELECT 
get_customeVal_3 = ISNULL(SUM(CASE WHEN year_num = @year1 AND chart_code = '3' THEN close_amt ELSE 0 END),0)
- ISNULL(SUM(CASE WHEN year_num = @year1 AND chart_code = '36101' THEN...
```
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 7, 2019 at 4:26 pm

#3695979
Reply To: Get some row depending of Date of nexts rows

koti.raavi wrote:
Hello, below is the data before filtering out, if you see A-06/11/2019 9:15 between A-06/11/2019 9:10 and A-06/11/2019 9:25, that's why it is not showing in end result. It...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 7, 2019 at 12:54 pm

#3695886
Reply To: Get some row depending of Date of nexts rows
```
;WITH MyTable AS
(
SELECT * FROM (VALUES
('A', '06/11/2019 09:00', '06/11/2019 09:15'),
('A', '06/11/2019 09:05', '06/11/2019 09:20'),
('A', '06/11/2019 09:10', '06/11/2019 09:25'),
('A', '06/11/2019 09:15', '06/11/2019 09:30'),

('B', '06/11/2019 09:05', '06/11/2019 09:20'),
('B', '06/11/2019...
```
- This reply was modified 6 years, 7 months ago by ChrisM@Work.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 7, 2019 at 11:13 am

#3695862
Reply To: last day of the month minus 1 day

Jonathan AC Roberts wrote:
ChrisM@Work wrote:
SELECT
[LastDayOfThisMonth] = EOMONTH(GETDATE()),
[SecondLastDayOfThisMonth] = DATEADD(DAY,-1,EOMONTH(GETDATE()))
I think EOMONTH was introduced in SQL 2012 and this is the SQL 2008 board.

Good catch Jonathan, thanks.
https://blogs.msdn.microsoft.com/samlester/2013/09/23/eomonth-equivalent-in-sql-server-2008-r2-and-below/

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 31, 2019 at 2:55 pm

#3693567
Reply To: Report runs forever.`

-- Avoid DISTINCT, it almost always invokes a SORT.
-- Avoid reading tables more times than is necessary
SELECT -- DISTINCT
H.Customs_Entry_Num as [Entry Num]
,H.Entry_Summary_Date
,L.Part_Num
,L.HTS_Num
,l.HTS_Value
,L.Line_Item_Duty AS Duty
,z.HTS_NUMBER
,z. PTNR_ID
,z.ELIGIBILITY
,z.Created_Date
,z.COMPOSITE_PART
FROM ADHOC.ATS_ESH H
INNER JOIN adhoc.ATS_ESL L
ON...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 31, 2019 at 2:24 pm

#3693555
Reply To: last day of the month minus 1 day

SELECT
[LastDayOfThisMonth] = EOMONTH(GETDATE()),
[SecondLastDayOfThisMonth] = DATEADD(DAY,-1,EOMONTH(GETDATE()))
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 31, 2019 at 2:10 pm

#3693549

Viewing 15 posts - 61 through 75 (of 10,143 total)