March 20, 2015 at 12:52 pm
I have a query which provides the below result set:
1165 6
1,173.0013
9740 6
9820 13
2271 6
2287 13
10,952.006
11,029.0013
4,074.006
4,103.0013
I want to achieve something like below. It should subtract the '13' row to '6' row and provide another column with the result. the '6' and '13' category code share the same Key.
1165 6 -8.00
1,173.0013-8.00
9740 6 -80
9820 13 -80
2271 6 -16
2287 13 -16
10,952.006-77.00
11,029.0013-77.00
4,074.006-29.00
4,103.0013-29.00
March 20, 2015 at 1:07 pm
I don't think anyone will be able to answer this without knowing more about the source data and the query hat returns the results shown.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
March 24, 2015 at 9:04 am
SELECTm.ValueField,
m.FilterColumn,-- Either "6" or "13"
r.Result
FROMMyTable m
JOIN(
SELECTt6.CategoryCode,
t13.ValueField - t6.ValueField AS Result
FROM(SELECT CategoryCode, ValueField FROM MyTable WHERE FilterColumn = 6) t6
JOIN(SELECT CategoryCode, ValueField FROM MyTable WHERE FilterColumn = 6) t13
ONt13.CategoryCode = t6.CategoryCode
) r
ONr.CategoryCode = m.CategoryCode
March 24, 2015 at 1:41 pm
Thank you for the reply...Appreciate your help...Actually I found the solution....What I did was just created two temp tables with different category codes and field values in my stored proc and then deducted the fields which were needed.
Thanks anyways,
March 24, 2015 at 6:09 pm
fahey.jonathan (3/24/2015)
SELECTm.ValueField,m.FilterColumn,-- Either "6" or "13"
r.Result
FROMMyTable m
JOIN(
SELECTt6.CategoryCode,
t13.ValueField - t6.ValueField AS Result
FROM(SELECT CategoryCode, ValueField FROM MyTable WHERE FilterColumn = 6) t6
JOIN(SELECT CategoryCode, ValueField FROM MyTable WHERE FilterColumn = 6) t13
ONt13.CategoryCode = t6.CategoryCode
) r
ONr.CategoryCode = m.CategoryCode
Can you explain how this guarantees that the first row with category code = 6 gets paired up with the first row that has category code = 13?
I was under the impression that SQL queries had no inherent order to the returned results unless you specify ORDER BY.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 24, 2015 at 6:16 pm
To demonstrate my statement above:
WITH SampleData (Value, CatCode) AS
(
SELECT 1165, 6
UNION ALL SELECT 1173.00, 13
UNION ALL SELECT 9740, 6
UNION ALL SELECT 9820, 13
UNION ALL SELECT 2271, 6
UNION ALL SELECT 2287, 13
UNION ALL SELECT 10952.00, 6
UNION ALL SELECT 11029.00, 13
UNION ALL SELECT 4074.00, 6
UNION ALL SELECT 4103.00, 13
)
SELECT *
,CASE CatCode
WHEN 6 THEN Value-LEAD(Value,1) OVER (ORDER BY (SELECT NULL))
WHEN 13 THEN LAG(Value,1) OVER (ORDER BY (SELECT NULL))-Value
END
FROM SampleData;
See the "ORDER BY (SELECT NULL))?" This only works because SQL has chosen to return the rows in the order they appear in the SampleData, but that is not guaranteed.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply