ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,291 through 1,305 (of 10,143 total)

RE: Last day of month in a WHERE clause

This -
select top(185) EoMonth('1/1/2016',(1-n)) as Eom from tally
- using Bob's tally view covers roughly 2000 to 2016, the entire range of dates in my table of interest.
^{“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

August 12, 2016 at 9:55 am

#1895252
RE: Are the posted questions getting worse?

Lynn Pettis (8/12/2016)
Steve Jones - SSC Editor (8/12/2016)
Lynn Pettis (8/11/2016)
Having rules for answering questions makes sense but I have to ask the question, Why? Has as been noted there...
^{“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

August 12, 2016 at 9:40 am

#1895247
RE: Last day of month in a WHERE clause

The Dixie Flatline (8/12/2016)
Guys, I'm trying to figure out the disconnect here. I am running against a table of almost 41 million rows using the OPs table...
^{“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

August 12, 2016 at 9:34 am

#1895245
RE: Last day of month in a WHERE clause

The Dixie Flatline (8/12/2016)
After thinking about times being included, we don't need to waste cycles converting Check_Date from datetime to date. We just need to go a...
^{“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

August 12, 2016 at 7:33 am

#1895214
RE: Are the posted questions getting worse?

Steve Jones - SSC Editor (8/11/2016)
...
Part of the process noted that we have a few "how to post code, how to ask a question, how to format code" articles. We...
^{“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

August 12, 2016 at 3:48 am

#1895177
RE: Strange update behaviour

Thanks.
The UPDATE affects the PK. Is this PK referenced by FK constraints in other tables? If so, are they indexed?
Edit: or it would, if there were any rows affected...
^{“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

August 11, 2016 at 10:02 am

#1895043
RE: Mistakes Columbus made.

Jeff Moden (8/11/2016)
ben.brugman (8/11/2016)
Eric M Russell (8/11/2016)
"If you break a few eggs, then make an omelet."
Christopher Columbus,... challenges his critics to make an egg stand on its tip. 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

August 11, 2016 at 8:17 am

#1895003
RE: Count of same value in order

DROP TABLE #temp;
WITH cte as (
select 1 AS ID, 'x' as Item union all
select 2, 'x' union all
select 3, 'x' union all
select 4, 'x' union all
select 5, 'A' union all
select...
^{“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

August 11, 2016 at 7:08 am

#1894988
RE: DynamicDateRangeGenerator

mitch 14804 (8/11/2016)
@chrism-2@Work
Hi Chris,
Thanks for the feedback. The reasoning behind my long process is so I could put it in a view not a procedure. It now allows...
^{“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

August 11, 2016 at 6:56 am

#1894985
RE: Count of same value in order

There's currently no column to set the order of these rows. Without one, SQL Server will not guarantee that the rows will be processed (output) in any specific order. A...
^{“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

August 11, 2016 at 6:43 am

#1894978
RE: Last day of month in a WHERE clause

The results I get are much closer than that, John (except for Kevin's).
This might be the simplest option:
SELECT
Adj_CheckDate,
cnt = COUNT(*)
FROM trans t
CROSS APPLY (SELECT ldom = EOMONTH([date]),...
^{“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

August 11, 2016 at 6:08 am

#1894970
RE: Last day of month in a WHERE clause

TheSQLGuru (8/11/2016)
I quickly slapped this together. Can someone do some scalability testing for me to see how it stacks up with the other code? Sadly I have to grab a...
^{“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

August 11, 2016 at 5:35 am

#1894961
RE: Last day of month in a WHERE clause

twin.devil (8/11/2016)
Just a minor adjustment to Chris solution, It will not handle if there is date and time are coming in the check_date column.
Here is Chris updated script:
SELECT
t.[Check_date],
cnt =...
^{“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

August 11, 2016 at 4:48 am

#1894954
RE: Last day of month in a WHERE clause

SELECT
t.[Date],
cnt = COUNT(*)
FROM Trans t -- 36M rows
CROSS APPLY (SELECT ldom = DATEADD(MONTH,1+DATEDIFF(MONTH,0,t.[Date]),0)-1) x
WHERE DAY([Date]) > 27
AND t.[Date] = x.ldom
GROUP BY t.[Date]
-- (100 row(s) affected) /...
^{“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

August 11, 2016 at 3:51 am

#1894945
RE: Strange update behaviour

cmullen77 29296 (8/10/2016)
Hey Alan,
That was my point.. I had an issue running an update statement, so I started eliminating rows to figure out what was causing my problem. ...
^{“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

August 11, 2016 at 2:04 am

#1894936

Viewing 15 posts - 1,291 through 1,305 (of 10,143 total)