May 23, 2016 at 12:32 am
Hello,
I am trying to sum by GL Account Description when I run the query I do get sum, but broken out by Gl Account Number '023300150115' etc. For example I will have the following GL Account number '023300150115', '023300050115', '041000150115', but what I really want to see is the total of the three GL Account Numbers in GL Account Description - Salaries. I may have from January - March each account will have it's own amount and period.
023300150115 5000 Salaries
023300050115 2000 Salaries
041000150115 6000 Salaries
What I really want to see is 13000 for the amount and Salaries all in one line. Below is my code.
If anybody and help I would really appreciate. Than you in advance
SELECT RTRIM(OAPLUS7FLP.GLMST.GMGLAC) AS "GL Account", CASE WHEN RTRIM(GMGLAC) = '041000054600' THEN 'Royalty/Brand Fees' WHEN RTRIM(GMGLAC)
IN ('023300150115', '023300050115', '041000150115', '041000050115', '051000150115', '051000050115', '081000150115', '081000050115', '023300050103',
'041000050103', '051000050103', '081000050103', '023300050102', '041000050102', '051000050102', '081000050102', '023300050108', '041000050108',
'051000050108', '081000050108') THEN 'Salaries ' END AS "GL Description", CASE WHEN RTRIM(GMGLAC) = '041000054600' THEN 10 WHEN RTRIM(GMGLAC)
IN ('023300150115', '023300050115', '041000150115', '041000050115', '051000150115', '051000050115', '081000150115', '081000050115', '023300050103',
'041000050103', '051000050103', '081000050103', '023300050102', '041000050102', '051000050102', '081000050102', '023300050108', '041000050108',
'051000050108', '081000050108') THEN 20 ELSE 9999 END AS "Order", OAPLUS7FLP.GLDET.GTIACN AS InternalGLNumberDetail,
FROM OAPLUS7FLP.GLMST, OAPLUS7FLP.GLDET
WHERE OAPLUS7FLP.GLMST.GMIACN = OAPLUS7FLP.GLDET.GTIACN
GROUP BY RTRIM(OAPLUS7FLP.GLMST.GMGLAC), OAPLUS7FLP.GLMST.GMGLAC, APLUS7FLP.GLDET.GTPOYR, OAPLUS7FLP.GLDET.GTPOPR, OAPLUS7FLP.GLDET.GTIACN
HAVING (RTRIM(OAPLUS7FLP.GLMST.GMGLAC) IN ('041000054600', '023300150115', '023300050115', '041000150115', '041000050115', '051000150115', '051000050115',
'081000150115', '081000050115', '023300050103', '041000050103', '051000050103', '081000050103', '023300050102', '041000050102', '051000050102',
'081000050102', '023300050108')) AND (OAPLUS7FLP.GLDET.GTPOYR = ?) AND (OAPLUS7FLP.GLDET.GTPOPR BETWEEN 1 AND ?)
ORDER BY "Order"
May 23, 2016 at 7:00 am
You are grouping by the account number OAPLUS7FLP.GLMST.GMGLAC which gives one row per account number. You need to group by the description.
You might find it easier to create a new table of the account codes, their descriptions and sort order and join to that. It would make the code easier to read and would be more resilient to new GL accounts as they would only need to be added to the new table rather than changing the code.
Jez
May 23, 2016 at 9:41 am
It looks like some columns are missing from the SELECT above, so I can't try to do the full query, but here's a general approach for using a lookup table for the GL Account Descriptions and Order:
CREATE TABLE dbo.GMGLAC_Descriptions (
GMGLAC varchar(30) NOT NULL,
[GL Description] varchar(40) NOT NULL,
[Order] smallint NULL
)
INSERT INTO dbo.GMGLAC_Descriptions
VALUES('041000054600', 'Royalty/Brand Fees', 10),
('023300150115', 'Salaries ', 20),
('023300050115', 'Salaries ', 20),
('041000150115', 'Salaries ', 20),
('041000050115', 'Salaries ', 20),
('051000150115', 'Salaries ', 20),
('051000050115', 'Salaries ', 20),
('081000150115', 'Salaries ', 20),
('081000050115', 'Salaries ', 20),
('023300050103', 'Salaries ', 20),
('041000050103', 'Salaries ', 20),
('051000050103', 'Salaries ', 20),
('081000050103', 'Salaries ', 20),
('023300050102', 'Salaries ', 20),
('041000050102', 'Salaries ', 20),
('051000050102', 'Salaries ', 20),
('081000050102', 'Salaries ', 20),
('023300050108', 'Salaries ', 20),
('041000050108', 'Salaries ', 20),
('051000050108', 'Salaries ', 20),
('081000050108', 'Salaries ', 20)
SELECT [GL Account], gd.[GL Description], ISNULL(gd.[Order], 9999) AS [Order],
FROM OAPLUS7FLP.GLMST, OAPLUS7FLP.GLDET
CROSS APPLY (
SELECT RTRIM(OAPLUS7FLP.GLMST.GMGLAC) AS "GL Account"
) AS assign_alias_names1
LEFT OUTER JOIN dbo.GMGLAC_Descriptions gd ON gd.GMGLAC = [GL Account]
WHERE OAPLUS7FLP.GLMST.GMIACN = OAPLUS7FLP.GLDET.GTIACN
...
ORDER BY [Order]
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 23, 2016 at 10:05 am
Thanks for your help I get what you mean. Thanks again.
May 23, 2016 at 3:36 pm
Thank you
May 24, 2016 at 2:23 pm
A suggestion - probably nit-picking here ...
FROM OAPLUS7FLP.GLMST, OAPLUS7FLP.GLDET
WHERE OAPLUS7FLP.GLMST.GMIACN = OAPLUS7FLP.GLDET.GTIACN
is the "old" way of doing things.
The "modern" way is
FROM OAPLUS7FLP.GLMST
INNER JOIN OAPLUS7FLP.GLDET ON OAPLUS7FLP.GLDET.GTIACN = OAPLUS7FLP.GLMST.GMIACN
Better yet, use aliases the make the code more readable by humans ...
SELECT
m.col1,
m.col4,
d.colA ...
FROM OAPLUS7FLP.GLMST m
INNER JOIN OAPLUS7FLP.GLDET d ON d.GTIACN = m.GMIACN
May 24, 2016 at 3:23 pm
j-1064772 , any code imporovement is always welcome. Thank you.
May 24, 2016 at 3:27 pm
alex_martinez (5/24/2016)
j-1064772 , any code imporovement is always welcome. Thank you.
🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply