March 25, 2010 at 2:37 pm
Trying to write a query to show all 5 digit GL accounts, except the accounts ending in ###10. Is there a way to say look at last 2 digits and pull out only the ones ending in "10"?
For example:
Except({ Descendants([GeneralLedger].[AcctLevel5 Name].[Associate Compensation], ,LEAVES)}, {Descendants( [GeneralLedger].[AcctLevel4 Name].[50410 - ASSOCIATE-REGULAR], ,Leaves)} )
Want to keep data for accounts
50420
55030
56640
etc
BUT do not pick up data for accounts ending in 10,
51010
53010
54410
March 25, 2010 at 2:45 pm
Something like this should work:
DECLARE @Table TABLE (GLnum int)
INSERT INTO @Table
SELECT 50420 UNION ALL
SELECT 55030 UNION ALL
SELECT 56640 UNION ALL
SELECT 51010 UNION ALL
SELECT 53010 UNION ALL
SELECT 54410
SELECT *
FROM @Table
WHERE RIGHT(GLnum,2) <> 10
March 25, 2010 at 2:51 pm
But what happens when a new account is added, then I have go and tweak the formula, too much work. Is there a way the system can look at only the last two digits and not caluculat them (for example, don't pick up any account that ends in "10")
March 25, 2010 at 2:51 pm
Something like this?
;WITH cte AS
(
SELECT 50420 AS val UNION ALL
SELECT 50421 UNION ALL
SELECT 50422 UNION ALL
SELECT 50423 UNION ALL
SELECT 55030 UNION ALL
SELECT 56640 UNION ALL
SELECT 51010 UNION ALL
SELECT 53010 UNION ALL
SELECT 54410
)
SELECT *
FROM cte
WHERE (val-10)%100 > 0 AND (val)%10 = 0
/* result set
val
50420
55030
56640
*/
March 25, 2010 at 2:56 pm
meena.patel (3/25/2010)
But what happens when a new account is added, then I have go and tweak the formula, too much work. Is there a way the system can look at only the last two digits and not caluculat them (for example, don't pick up any account that ends in "10")
That is exactly what my query does. It looks at the GL values and excludes rows where the last 2 digits = 10.
Can you explain, and give examples, of why this will not work?
March 25, 2010 at 2:57 pm
Thanks guys, these are great suggestions. However the problem I encounter is that this account is made up of over 50 accounts. I am trying to avoid writting out each account string. I want the formula to read " look at the associate comp account" - show all accounts except for those ending in 10.
Sorry if this isn't too clear. I'm just learning to write queries. Any help is appreciated. Thanks!
March 25, 2010 at 2:59 pm
John, for your query, I would have to list out all the accounts, correct? Is there a way to avoid that?
March 25, 2010 at 3:02 pm
meena.patel (3/25/2010)
John, for your query, I would have to list out all the accounts, correct? Is there a way to avoid that?
No, I listed out the accounts so that I could create an example for you. You need to take the SELECT query that I wrote and replace it with your table name.
SELECT *
FROM <YourTable>
WHERE RIGHT(GLColumn,2) <> 10
March 25, 2010 at 3:05 pm
Sorry this is all new to me. Awesome! Thanks, I will give it a shot.
March 25, 2010 at 3:37 pm
meena.patel (3/25/2010)
Sorry this is all new to me. Awesome! Thanks, I will give it a shot.
May I suggest that you read the first article I have referenced below in my signature block? It provides a very good structure for posting questions, including the table structure(s), sample data, and what you have tried to solve your problem. The only other thing I would add to a post not directly mentioned in the article is expected results based on the sample data.
Doing all that helps you to focus the question on your problem, provides us with a test rig for helping you, and in the end will provide you with tested code in return for your efforts up front.
March 26, 2010 at 9:46 am
Sorry for being vague early. Are you familiar with MDX query? I actually need the SQL query formatted to MDX.
John R. you suggested the below:
SELECT *
FROM @Table
WHERE RIGHT(GLnum,2) <> 10
I need something like the query above or the one below for MDX.
Select *
From @ Table
Where GLnum = 5_ _ 20
(besides excluding 5xx10, just asking to pull individual account strings like 5xx20, 5xx30, 5xx40)
(Our budget system is Clarity, which only reads MDX on certain row/column ranges)
March 29, 2010 at 3:06 pm
Ah, MDX. Well, I'm a novice with MDX at best. I would recommend creating a new thread in the Analysis Services forum. Make sure you have MDX help somewhere in the thread title. They don't have a specialized thread for MDX help, but I would guess your best chance of getting folks more familiar with MDX to look at this would be to post it in the AS forum. You may post this link inside your new thread for background.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply