ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,226 through 3,240 (of 10,143 total)

RE: Running total in sql and additional issue

pwalter83 (6/19/2014)
Sachin Nandanwar (6/19/2014)
Something like this
SELECT SUM(YourSum) FROM
(
SELECT SUM(CASE WHEN SOA_TYPE = 'Agent' THEN USD_AMOUNT ELSE 0 END) +
SUM(CASE WHEN SOA_TYPE = 'Freight' THEN USD_AMOUNT ELSE 0 END) +...
^{“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

June 23, 2014 at 1:47 am

#1723908
RE: Help needed for a complex query.

Are we good so far?
DECLARE @fromDate DATE = 'Apr 01 2008'
DECLARE @tODate DATE = 'Apr 30, 2014'
DECLARE @NumberOfRecords INT = 6
DECLARE @period VARCHAR(10) = 'monthly' -- (This can...
^{“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

June 20, 2014 at 8:08 am

#1723613
RE: Help needed for a complex query.

KnowledgeSeeker (6/20/2014)
Exactly.
Good. You have some guesses - which is nice.
^{“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

June 20, 2014 at 7:32 am

#1723587
RE: update error

Or:
UPDATE t1 SET
P = T.NewP
FROM table_1 t1
CROSS APPLY (
SELECT NewP = ISNULL(MAX(p),0)+1
FROM table_1 t2
WHERE t1.link = t2.link
) t
What can you tell us about the distribution of values...
^{“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

June 20, 2014 at 7:30 am

#1723583
RE: Running total error

pwalter83 (6/20/2014)
Sachin Nandanwar (6/20/2014)
But you seem to having one already in your query
(SELECT SUM(CASE WHEN SOA_TYPE IN ('Agent','Freight','Cost') or AC = '16208'THEN USD_AMOUNT ELSE 0 END) YourSum
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

June 20, 2014 at 7:13 am

#1723574
RE: Help needed for a complex query.

KnowledgeSeeker (6/20/2014)
Sorry if the requirement is not clearly mentioned(English is not my first language). In the example I gave the dates the user passed are June 05, 2010 and June...
^{“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

June 20, 2014 at 6:12 am

#1723558
RE: Help needed for a complex query.

KnowledgeSeeker (6/20/2014)
No. The requirement is quite clear. If the user wants to find last 5 week's data from the table and he has the upper range for date say, June...
^{“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

June 20, 2014 at 6:01 am

#1723552
RE: Help needed for a complex query.

KnowledgeSeeker (6/20/2014)
I have a table with large data. The structure along with the sample data of the table is as follows:
CREATE TABLE #SampleData
(
BaseYear INT,
TransYear INT,
ClientName VARCHAR(50),
DepID INT,
Revenue, INT,
TransactionDate DATETIME
)
INSERT...
^{“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

June 20, 2014 at 5:42 am

#1723543
RE: Difference Between cross apply and cross join

daniarchitect (6/19/2014)
bitbucket-25253....wasted my time what a response
If Bitbucket's explanation doesn't exactly meet your requirements, then simply ask what you need to know. Rudeness is unnecessary and unprofessional.
^{“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

June 20, 2014 at 1:40 am

#1723483
RE: Multiple ON Clauses In One Join?

Eugene Elutin (6/19/2014)
...and the example was later extended for use as a TSQL recruiting test.
have you ever tried asking on interview what kind of results the following statement will produce:
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

June 19, 2014 at 9:50 am

#1723329
RE: Multiple ON Clauses In One Join?

autoexcrement (6/19/2014)
What a fantastic explanation! Thank you so much for taking the time!
You're welcome - but the time was taken a while ago to demonstrate a point to a colleague...
^{“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

June 19, 2014 at 8:36 am

#1723284
RE: Multiple ON Clauses In One Join?

Eugene Elutin (6/19/2014)
...
-- Query 4
SELECT c.*, o.*, ol.*
FROM #Customers c
LEFT JOIN #Orders o
INNER JOIN #Orderlines ol
ON ol.OrderID = o.OrderID
...
^{“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

June 19, 2014 at 8:09 am

#1723269
RE: Multiple ON Clauses In One Join?

This might help:
“You have a query which joins two tables, say Customers and Orders. It’s an outer join because you want all customers whether or not they’ve ever placed an...
^{“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

June 19, 2014 at 1:47 am

#1723090
RE: display all data from row 1 but include sum total of all rows amount column in data set

This might get you started:
SELECT --DISTINCT
t.transactionID, b.TransDate, b.TransType, b.TransDesc1, b.TransDesc2, b.TransDesc3, b.TransDesc4, b.TransDesc5,
(b.Debit + b.Credit) as Amount,
ISNULL(r.OrthoID, 0) as OrthoID,
ISNULL(t.PatientID, 0) as PatientID,
t.ledgerAmount,
t.LedgerType,
(b.Debit + b.Credit) -...
^{“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

June 18, 2014 at 9:37 am

#1722853
RE: display all data from row 1 but include sum total of all rows amount column in data set

mattech06 (6/18/2014)
that works for the sample but when I apply it to the working sql I get a problem with the group by
;WITH Main as
(
SELECT
DISTINCT t.transactionID, b.TransDate, b.TransType,...
^{“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

June 18, 2014 at 9:19 am

#1722839

Viewing 15 posts - 3,226 through 3,240 (of 10,143 total)