ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,201 through 4,215 (of 10,143 total)

RE: Query Help - Sum by Month

That's well sneaky Stefan! This mod should make it quicker, too:
;with cte1 as (
-- get all interesting months
select
FirstDay = dateadd(month, N, '20100101'),
qq = dateadd(month, 1, dateadd(month, N, '20100101'))
from...
^{“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 8, 2013 at 9:32 am

#1639843
RE: Query Help - Sum by Month

Stefan_G (8/7/2013)
A little challenge:
Assume there is a clustered index on the date column.
Can you create a query that exploits the clustered index to achieve even better performance?
Borrowing from Luis' test...
^{“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 8, 2013 at 7:46 am

#1639778
RE: cook book

GilaMonster (8/8/2013)
ChrisM@Work (8/8/2013)
Sounds like a Sisyphean task, similar to those given to raw recruits of some services - "Get me a Portuguese handpump and bring a bucket of steam with...
^{“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 8, 2013 at 7:10 am

#1639747
RE: cook book

Sounds like a Sisyphean task, similar to those given to raw recruits of some services - "Get me a Portuguese handpump and bring a bucket of steam with you".
^{“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 8, 2013 at 6:57 am

#1639741
RE: Joins and Counts

Hi James
The table alias 'c' is now used in the APPLY subquery and is not visible to the outer SELECT list. If it were in the SELECT list of the...
^{“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 8, 2013 at 6:44 am

#1639728
RE: How to update Flag based on Maximum Amount

This is an exact match for your stated requirements and results. However, if I were a betting person, I'd put money on it being wrong - because you've missed out...
^{“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 8, 2013 at 6:40 am

#1639723
RE: Are the posted questions getting worse?

HowardW (8/8/2013)
ChrisM@Work (8/8/2013)
Howard - the name of the guy made me chuckle, given the pidgin English 😀
Check out the strap-line under the logo on the website as well. Comedy...
^{“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 8, 2013 at 6:11 am

#1639702
RE: Are the posted questions getting worse?

Howard - the name of the guy made me chuckle, given the pidgin English 😀
Has anyone else had issues with posting image links? I've used Postimg before and it's...
^{“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 8, 2013 at 5:30 am

#1639686
RE: Joins and Counts

This is a real stab in the dark;
SELECT
MT.[Organisation ]
,MT.[Assignment Number ]
,MT.[First Name ]
,MT.[Last Name ]
,MT.[Position Title ]
,MT.[Site Name ]
,MT.[Date of Expense ]
,MT.[Reason ]
,MT.[Expense Item ]
,MT.[From ]
,MT.[FromPostcode ]
,MT.[To ]
,MT.[ToPostcode ]
,MT.[Step...
^{“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 8, 2013 at 5:27 am

#1639682
RE: tsql query for counting a 'grouped by' and a where clause

You will need to include both tables in your query:
FROM employers
LEFT JOIN superusers
^{“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 8, 2013 at 5:20 am

#1639679
RE: Query Help - Sum by Month

Luis Cazares (8/7/2013)
wolfkillj (8/7/2013)
Sean Grebey (8/6/2013)
Thanks for the replies. Out of curiosity, this is the approach I took. Is there an issue with it?
select ROUND(sum(ContractValue),2) ContractValue, MONTH(ContractDate), YEAR(ContractDate)
From Reporting.dbo.PrgSum
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

August 7, 2013 at 9:36 am

#1639452
RE: Splitting Comma Separated Values into Rows

Sean Lange (8/7/2013)
Please don't cross post. It just fragments the replies.
Here is the other one. http://www.sqlservercentral.com/Forums/Topic1481654-392-1.aspx
1970's toilet graffiti: on the left-hand side of the cubicle is written "see other side...
^{“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 7, 2013 at 7:45 am

#1639387
RE: Table data in tabular form

freecoder (8/7/2013)
Thanks 🙂
It works?
^{“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 7, 2013 at 7:29 am

#1639368
RE: Need help with TSQL

rajg (8/7/2013)
I am really not sure but this is that table structure with data that is given to me. Would really be a great help if someone can help....
^{“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 7, 2013 at 7:28 am

#1639366
RE: Trying to select the last 3 months of data

;WITH base AS (
SELECT Max_DM = MAX(post_dm) FROM ztb_forecastable_metrics_hist
)
SELECT
post_dm -- etc
FROM ztb_forecastable_metrics_hist
WHERE post_dm BETWEEN DATEADD(mm,-3,base.Max_DM) AND base.Max_DM
GROUP BY Post_DM
^{“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 7, 2013 at 7:26 am

#1639363

Viewing 15 posts - 4,201 through 4,215 (of 10,143 total)