ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,186 through 4,200 (of 10,143 total)

RE: Subtraction and Group by in the same table

Thanks Mr Sommarskog, that question came in after hometime.
It's good to see an "old master" at work here on ssc. I've been using your website - and recommending 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

August 12, 2013 at 1:00 am

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

SELECT
e.EmployerName,
super_userCnt = ISNULL(x.super_userCnt, 0)
FROM dbo.mytableEmployer e
LEFT OUTER JOIN (
SELECT
employerName,
super_userCnt = COUNT(*)
FROM employerSuper_Users
WHERE [Role] = 'Super_User'
GROUP BY employerName
HAVING COUNT(*) > 1
) x ON e.EmployerName...
^{“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, 2013 at 12:54 am

#1640449
RE: Ideas on how to handle 1000+ databases in one instance

A method I've used in the past is to restore elsewhere, remove the unwanted data from the restore, refresh indexes then use the restore to update live. With the relatively...
^{“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 9, 2013 at 7:59 am

#1640160
RE: Ideas on how to handle 1000+ databases in one instance

berniesprouster (8/9/2013)
Thanks for the suggestion. With that though, would I not have an issue if I needed to restore a particular customer's data from a point in time?
Is this 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 9, 2013 at 7:44 am

#1640154
RE: What is your favorite "I didn't know that" moment in T-SQL?

Sean Lange (8/8/2013)
This whole English thing is the reason we American's don't know any other languages. All the others have actual rules that you follow all of the time. In...
^{“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 9, 2013 at 7:28 am

#1640143
RE: Ideas on how to handle 1000+ databases in one instance

berniesprouster (8/9/2013)
Point taken about restores if all the data is stored in one set of tables.
Regarding partitioning, I thought you were thinking of partition functions/schemes if all the data was...
^{“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 9, 2013 at 6:52 am

#1640125
RE: Are the posted questions getting worse?

dwain.c (8/9/2013)
ChrisM@Work (8/9/2013)
dwain.c (8/8/2013)
ChrisM@Work (8/8/2013)
Howard - the name of the guy made me chuckle, given the pidgin English 😀
I've been hearing a lot of Tok Pisin for the last...
^{“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 9, 2013 at 6:23 am

#1640117
RE: TempDB Full...!!

Ed Wagner (8/6/2013)
I looked at the stored procedure, but it doesn't look like it hits anything that hard. It looks to me like an upgrade or version management procedure...
^{“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 9, 2013 at 5:14 am

#1640102
RE: Reason for dropping TEXT, IMAGE data types

T.Ashish (8/9/2013)
With large-value data types You can define variables that can store large amounts of data, up to 2^31-1 bytes of character, binary, and Unicode data. That was not possible...
^{“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 9, 2013 at 4:23 am

#1640091
RE: Subtraction and Group by in the same table

venkyzrocks (8/7/2013)
hi erland,
I'm sorry if the requirement was not clear.
I'll rephrase it :
The data is divided by Scenario : Jun (0+12) forecast and Actual.
And every Scenario will have 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 9, 2013 at 4:17 am

#1640090
RE: Query returning one extra record

pdanes (8/9/2013)
ChrisM@Work (8/9/2013)
You're welcome Mr Danes. Assuming you're a taxonomist first and a programmer second, this is a fairly standard method to identify cardinality issues in a query.
No, actually I'm...
^{“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 9, 2013 at 3:08 am

#1640084
RE: Query returning one extra record

You're welcome Mr Danes. Assuming you're a taxonomist first and a programmer second, this is a fairly standard method to identify cardinality issues in a query.
Care to share the code...
^{“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 9, 2013 at 2:11 am

#1640074
RE: Query help - summing between dates ranges and calculations...

Read the tables only once. Shamelessly nicked from both Scott's and Stefan's code:
WITH Preagg AS (
SELECT
x.Period,
c.CustomerID,
c.CustomerName,
ii.PartNumber,
SumPrice= SUM(ii.qty*ii.price),
SumQty= SUM(ii.qty),
SumValue= SUM(ii.qty*ii.price) / SUM(ii.qty)
FROM Customers c
INNER JOIN Invoices i...
^{“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 9, 2013 at 2:05 am

#1640071
RE: Query returning one extra record

One of the items (table sources) in your FROM list has two rows per single row in table Podrobnosti. Since there's no aggregation and all of the other tables are...
^{“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 9, 2013 at 1:44 am

#1640065
RE: Are the posted questions getting worse?

dwain.c (8/8/2013)
ChrisM@Work (8/8/2013)
Howard - the name of the guy made me chuckle, given the pidgin English 😀
I've been hearing a lot of Tok Pisin for the last week. ...
^{“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 9, 2013 at 1:17 am

#1640056

Viewing 15 posts - 4,186 through 4,200 (of 10,143 total)