August 20, 2015 at 7:53 am
Hi guys
Just want to see if anyone else has a different take on a query I'm trying to clean up.
Basically I'm running a number of selects, using unions to write out each select query as a distinct line in the output. Each line needs to be multiplied by -1 in order to create an offset balance (yes this is balance sheet related stuff) for each line. Each select will have a different piece of criteria.
Although I have it working, I'm thinking there's a much better or cleaner way to do it (I use the word better loosely)
Example:
SELECT 'Asset', 'House', TotalPrice * -1
FROM Accounts
WHERE AvgAmount > 0
UNION
SELECT 'Balance', 'Cover', TotalPrice
FROM Accounts
WHERE AvgAmount > 0
What gets messy here is having to write a similar set of queries where the amount is < 0 or = 0
I'm thinking something along the lines of building a table function contains all the descriptive text returning the relative values based on the AvgAmount I pass to it
If any of this is familiar to anyone who has had to deal with something similar please let me know
Regards
August 20, 2015 at 8:01 am
mitzyturbo (8/20/2015)
Hi guysJust want to see if anyone else has a different take on a query I'm trying to clean up.
Basically I'm running a number of selects, using unions to write out each select query as a distinct line in the output. Each line needs to be multiplied by -1 in order to create an offset balance (yes this is balance sheet related stuff) for each line. Each select will have a different piece of criteria.
Although I have it working, I'm thinking there's a much better or cleaner way to do it (I use the word better loosely)
Example:
SELECT 'Asset', 'House', TotalPrice * -1
FROM Accounts
WHERE AvgAmount > 0
UNION
SELECT 'Balance', 'Cover', TotalPrice
FROM Accounts
WHERE AvgAmount > 0
What gets messy here is having to write a similar set of queries where the amount is < 0 or = 0
I'm thinking something along the lines of building a table function contains all the descriptive text returning the relative values based on the AvgAmount I pass to it
If any of this is familiar to anyone who has had to deal with something similar please let me know
Regards
I have some ideas but it largely depends on the table structure. Please take a few minutes and read the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 20, 2015 at 11:32 am
Note that UNION will remove any duplicates. To avoid this problem and receive a possible performance improvement, use UNION ALL.
August 20, 2015 at 12:27 pm
This will generally perform much better than a UNION. I also replaced your TotalPrice * -1 using the unary operator.
SELECT t.*
FROM Accounts
CROSS APPLY (
VALUES('Asset', 'House', -TotalPrice)
,('Balance', 'Cover', TotalPrice)
) AS t(field1, field2, Price)
WHERE AvgAmount > 0
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 24, 2015 at 4:57 am
Sorry for the delay lads
Sean, apologies I'm on here long enough to know what I should have included with this one, please see below for sample data and existing query:
CREATE TABLE #AccData
(
RequestID INT
, AccNo INT
, AccTaskNo INT
, AccName VARCHAR(200)
, StartBalance NUMERIC(17,2)
, EndBalance NUMERIC(17,2)
)
INSERT INTO #Accdata
SELECT 1,1,100, 'Test1', 100.00, 200.00
INSERT INTO #Accdata
SELECT 2,2,90, 'Test2', 5000, 200.00
INSERT INTO #Accdata
SELECT 3,2,80, 'Test3', 10.00, 200.00
SELECT a.RequestID,
a.AccNo,
a.AccTaskNo,
a.AccName,
SUM(a.StartBalance - a.EndBalance) AS Total,
'Asset',
'',
'Loan Payable',
'Y',
'ALL',
'ALL'
FROM #AccData a
WHERE ISNULL(a.EndBalance,0) > 0
GROUP BY a.RequestID,
a.AccNo,
a.AccTaskNo,
a.AccName
UNION
SELECT a.RequestID,
a.AccNo,
a.AccTaskNo,
a.AccName,
SUM(a.StartBalance - a.EndBalance)*-1 AS Total,
'Balance',
'',
'Loan Total',
'Y',
'ALL',
'ALL'
FROM #AccData a
WHERE ISNULL(a.EndBalance,0) > 0
GROUP BY
a.RequestID,
a.AccNo,
a.AccTaskNo,
a.AccName
I'm thinking of something along the same lines as what Drew did in the cross apply, the only problem I see down the line is having to do a cross apply for every case. For example, I might find using the same hardcoded details for other conditions i.e. where EndBalance = 0 and Start > 0 or vice versa.
I'm thinking towards a config table where they hold all such hardcoded lines for each condition. I need something fairly robust as I think there's spec for a lot of new aggregations to be used based on the data i.e. grouping by requestid and accno for an overall view.
any help is appreciated, thanks guys
August 24, 2015 at 5:11 am
mitzyturbo (8/24/2015)
Sorry for the delay ladsSean, apologies I'm on here long enough to know what I should have included with this one, please see below for sample data and existing query:
CREATE TABLE #AccData
(
RequestID INT
, AccNo INT
, AccTaskNo INT
, AccName VARCHAR(200)
, StartBalance NUMERIC(17,2)
, EndBalance NUMERIC(17,2)
)
INSERT INTO #Accdata
SELECT 1,1,100, 'Test1', 100.00, 200.00
INSERT INTO #Accdata
SELECT 2,2,90, 'Test2', 5000, 200.00
INSERT INTO #Accdata
SELECT 3,2,80, 'Test3', 10.00, 200.00
SELECT a.RequestID,
a.AccNo,
a.AccTaskNo,
a.AccName,
SUM(a.StartBalance - a.EndBalance) AS Total,
'Asset',
'',
'Loan Payable',
'Y',
'ALL',
'ALL'
FROM #AccData a
WHERE ISNULL(a.EndBalance,0) > 0
GROUP BY a.RequestID,
a.AccNo,
a.AccTaskNo,
a.AccName
UNION
SELECT a.RequestID,
a.AccNo,
a.AccTaskNo,
a.AccName,
SUM(a.StartBalance - a.EndBalance)*-1 AS Total,
'Balance',
'',
'Loan Total',
'Y',
'ALL',
'ALL'
FROM #AccData a
WHERE ISNULL(a.EndBalance,0) > 0
GROUP BY
a.RequestID,
a.AccNo,
a.AccTaskNo,
a.AccName
I'm thinking of something along the same lines as what Drew did in the cross apply, the only problem I see down the line is having to do a cross apply for every case. For example, I might find using the same hardcoded details for other conditions i.e. where EndBalance = 0 and Start > 0 or vice versa.
I'm thinking towards a config table where they hold all such hardcoded lines for each condition. I need something fairly robust as I think there's spec for a lot of new aggregations to be used based on the data i.e. grouping by requestid and accno for an overall view.
any help is appreciated, thanks guys
If you like Drew's suggestion, then go for the config table. His APPLY block
VALUES('Asset', 'House', -TotalPrice)
,('Balance', 'Cover', TotalPrice)
returns a table, in this case two rows but that could be as many as you like. You don't need multiple APPLY blocks, just multiple elements in the VALUES clause. More than 5 or 6 and a table makes more sense. If there are no other queries making use of this logic then create the config table as a local temporary table.
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 24, 2015 at 9:16 am
The problem of using the config table is that you might need to change the code to dynamic SQL.
That's not necessarily a bad thing, but might need a careful planning before implementing.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply