ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,196 through 6,210 (of 10,143 total)

RE: Currency Conversion - Many to Many

Sean Lange (6/25/2012)
Is there a question here?
Only yours, Sean 😉
^{“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 25, 2012 at 7:53 am

#1505062
RE: if any body compress my code then please do that

No problem:
select distinct likp.VBELN as delivery,vbak.VBELN as sales_document,vbrp.VBELN as bill_no,
vbrk.FKDAT as invoice_date,AUDAT as sales_order_date,likp.ZZCND as delivery_date,likp.PODAT as POD_date,
submission_date as sub_date,vbak.ZZP01 as payment_rule,vbrk.ZTERM as payment_term,
(case
when vbak.ZZP01 = 'A' then DATEADD(DD,x.DateDelta,likp.ZZCND)
when vbak.ZZP01...
^{“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 25, 2012 at 7:40 am

#1505057
RE: Availability calculation - a nice T-SQL problem

Don't forget the rCTE version:
;WITH OrderedData AS (SELECT *, rn = ROW_NUMBER() OVER (ORDER BY HotelId, RoomTypeId, DateKey DESC) FROM @booking),
Calculator AS (
SELECT rn, Id, HotelId, RoomTypeId, DateKey, FreeCount,
Availability =...
^{“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 25, 2012 at 7:24 am

#1505045
RE: how to calculate incentive comission for a manager ?

Change the final select to this:
SELECT
LevelNum,
MemberID,
sortorder,
ProductID, SaleClosedPrice,
CAST(REPLICATE(' | ', LevelNum - 1) + MemberName AS VARCHAR(100)) AS MemberName,
[Commission%],
[Incentive%]
FROM Hierarchy
CROSS APPLY (SELECT [Commission%] = CASE WHEN LevelNum = 1...
^{“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 25, 2012 at 6:24 am

#1505004
RE: how to calculate incentive comission for a manager ?

SaleClosePrice and Commission% are both returned by me last query. I can't understand where you are having a problem - surely all you have to do is multiply the two?
DECLARE@MemberID...
^{“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 25, 2012 at 5:51 am

#1504990
RE: how to calculate incentive comission for a manager ?

sivag (6/25/2012)
the commission will be place depen upon closed price
from the closed price of the sale only 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

June 25, 2012 at 5:09 am

#1504978
RE: Query Taking ages to execute

vinu512 (6/25/2012)
Hi guys,
Thanks for all the replies. I solved the problem. Well, technically... I didn't solve it, I took a Work around.
My Procedure had the following Query:
SELECT Distinct a.BookCode, b.MaterialType,...
^{“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 25, 2012 at 5:04 am

#1504975
RE: how to calculate incentive comission for a manager ?

sivag (6/25/2012)
in final u just mentioned
CAST(REPLICATE(' | ', LevelNum - 1) + MemberName AS VARCHAR(100)) AS MemberName,
[Commission] = CASE WHEN LevelNum = 1 THEN 6 ELSE 2 END
i need...
^{“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 25, 2012 at 4:42 am

#1504963
RE: how to calculate incentive comission for a manager ?

Try this. I've left in several columns to show how the query works, and omitted the final aggregate of the results.
DECLARE@MemberID INTEGER = 1
;WITH
MemberAndParent AS (
SELECT m.MemberID, m.Name AS...
^{“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 25, 2012 at 4:16 am

#1504956
RE: how to calculate incentive comission for a manager ?

Please make sure your DDL/DML statements actually do work before posting them here.
A few questions:
How much commission does Siva get for selling ProductID = 1?
How much commission does Siva get...
^{“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 25, 2012 at 3:48 am

#1504953
RE: Query Taking ages to execute

vinu512 (6/25/2012)
O.K. no replies yet...that means I did something wrong.
I've simplified the requirement further.
The following query runs in 4 seconds on the above mention table having 40,000+rows bringing back 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

June 25, 2012 at 2:42 am

#1504925
RE: Star and merge joins

Ted_Kert (6/24/2012)
How do I know if a join (e.g., INNER, OUTER, etc.) is a star or merge join?
What's your context? If you're attempting to identify Star-join optimisation, then here's 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

June 25, 2012 at 2:10 am

#1504911
RE: Aggregate Query question

dwain.c (6/22/2012)
...The only thing I don't like about your version, and I'm really splitting hairs here, is the hardcoding of the 4 on the LEFT. If you use this...
^{“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 22, 2012 at 7:20 am

#1504478
RE: SQL JOIN related issue/Cartesean

Nthuloane.Marotholi (6/22/2012)
I am not sure if this is something close to what you were looking for. But I got three rows as you wanted.. Here check this out
select distinct C.*...
^{“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 22, 2012 at 3:42 am

#1504404
RE: Aggregate Query question

dwain.c (6/22/2012)
After multiple runs, I am starting to see an improvement using your modification over the original! Great work Chris!
When I posted the article, Jeff took a shot at...
^{“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 22, 2012 at 3:28 am

#1504402

Viewing 15 posts - 6,196 through 6,210 (of 10,143 total)