ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,646 through 6,660 (of 10,143 total)

RE: declare table from a string syntax help

At my last place, the product transaction table was about 70 million rows. A requirement was to extract transactions for a single product - about 30k to 70k rows -...
^{“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

September 13, 2011 at 9:13 am

#1380738
RE: Are the posted questions getting worse?

Tom.Thomson (9/12/2011)
ChrisM@Work (9/12/2011)
WayneS (9/11/2011)
ALZDBA (9/11/2011)
:sig:
The one who's name thow shall not state felt the need to kill a forum newbe with ancient history and lousy advise.
http://www.sqlservercentral.com/Forums/FindPost1173017.aspx
The last sentence says 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

September 12, 2011 at 8:49 am

#1380082
RE: Are the posted questions getting worse?

WayneS (9/11/2011)
ALZDBA (9/11/2011)
:sig:
The one who's name thow shall not state felt the need to kill a forum newbe with ancient history and lousy advise.
http://www.sqlservercentral.com/Forums/FindPost1173017.aspx
The last sentence says it all about...
^{“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

September 12, 2011 at 6:33 am

#1379933
RE: Will Pivot Table work in this scenario

You can't put the roof on a house until the walls are up. Start with the foundations - build a query which returns the data you want to process into...
^{“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

September 9, 2011 at 8:02 am

#1379258
RE: Better Query instead of loop

uciltas-924976 (9/9/2011)
Hi Chris,
I just realized a minor problem on the code you sent.
To handle [AVG-DAYS] we said to use when it is current month data CTR_UPDATED will be getdate()
So...
^{“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

September 9, 2011 at 7:32 am

#1379220
RE: Mathematical Theory (controversy!)

GSquared (9/9/2011)
ChrisM@Work (9/9/2011)
GSquared (9/8/2011)
...breeding dogs for 10,000 years has yet to produce a T-Rex...
Isn't it fortunate for us that this wasn't the intention from the outset?
I don't know.
I think scritching...
^{“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

September 9, 2011 at 7:07 am

#1379196
RE: Multiple select/table join query

in_beta (9/9/2011)
Hi Chris,
Ok, I'll try attempt a rough table output, I'll omit DocDate unless you specify otherwise.
...
You're new here - this is better:
USE tempdb
GO
SET DATEFORMAT MDY
CREATE Table OCRD (CardCode VARCHAR(20),...
^{“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

September 9, 2011 at 7:04 am

#1379195
RE: Multiple select/table join query

Let's assume for a moment that the results aren't quite what you're expecting because of dupes in the output. Now, if you were to set up sample data scripts 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

September 9, 2011 at 6:12 am

#1379143
RE: Multiple select/table join query

Something like this?
SELECT
ocrd.CardCode,
ocrd.CardName,
ocrd.CntctPrsn,
ocrd.Phone1,
ocrd.Cellular,
YearTotal = (ISNULL(oinv.debit,0)-ISNULL(orin.credit,0))/1.1,
Jan2010Total = (ISNULL(moinv.dMTD,0)-ISNULL(morin.cMTD,0))/1.1
FROM OCRD ocrd
LEFT JOIN (
SELECT cardcode, debit = SUM(DocTotal)
FROM oinv
WHERE DocDate BETWEEN '2010-01-01 00:00:00.000' AND '2010-12-31...
^{“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

September 9, 2011 at 5:27 am

#1379124
RE: 2nd highest value in dataset

;WITH OrderedData AS (
SELECT
patient_id,
attendance_date,
rn = ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY attendance_date)
FROM PS_TestForOnline
)
SELECT *
FROM OrderedData v1 -- first visit
LEFT JOIN OrderedData v2...
^{“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

September 9, 2011 at 4:28 am

#1379106
RE: Time data type and negative TimeSpan values?

The difference between two times isn't a time, it's a quantity. You could use an integer datatype to store the number of minutes difference between the two times - easily...
^{“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

September 9, 2011 at 3:36 am

#1379089
RE: Mathematical Theory (controversy!)

GSquared (9/8/2011)
...breeding dogs for 10,000 years has yet to produce a T-Rex...
Isn't it fortunate for us that this wasn't the intention from the outset?
^{“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

September 9, 2011 at 3:21 am

#1379086
RE: Mathematical Theory (controversy!)

Naked Ape (9/8/2011)
...about 1% of lizards are capable of parthenogenesis...
Including, surprisingly, the biggest. One convenient theory for Komodo parthenogenesis is their habitat - there's a higher probability of an island-dwelling...
^{“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

September 9, 2011 at 3:17 am

#1379085
RE: declare table from a string syntax help

The Dixie Flatline (9/8/2011)
I have to ask: If all of the tables have similar data, why aren't you simply using one big table?
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

September 9, 2011 at 2:15 am

#1379065
RE: Multiple select/table join query

Post up what you have so far, should be straightforward 😉
^{“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

September 9, 2011 at 2:14 am

#1379063

Viewing 15 posts - 6,646 through 6,660 (of 10,143 total)