Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Query to calculate margin

0 Kudos

I have the following query but it is providing me two lines for every month and type because of the groupmask. I would like the Revenue and CGS to show on the same line. Is there a modification to my query that would yield this result?

select b.type, month(a.refdate), case when d.groupmask =4 then sum(a.credit-a.debit) end as 'Revenue', case when d.groupmask = 5 then sum(a.debit-a.credit) end as 'CGS'
from jdt1 a, enprise_jobcost_jobtype b, enprise_jobcost_job c, oact d
where a.Account = d.AcctCode and a.Project = c.project and c.JOBTYPE = b.SEQNO and c.jobtype in (28,30,35) and
year(a.refdate) = year(getdate()) and d.groupmask in (4,5)
group by b.type, month(a.refdate), d.groupmask
order by month(a.refdate)

  • SAP Managed Tags:
1 REPLY 1

KonradZaleski
Active Contributor
0 Kudos

In this case you need to remove groupmask column from group by clause and modify expression for sum. Maybe this will help:

select 
b.type, month(a.refdate), 
sum(case when d.groupmask =4 then a.credit-a.debit end) as 'Revenue', 
sum(case when d.groupmask = 5 then a.debit-a.credit end) as 'CGS' 
from 
jdt1 a, enprise_jobcost_jobtype b, enprise_jobcost_job c, oact d
where a.Account = d.AcctCode and a.Project = c.project and c.JOBTYPE = b.SEQNO and c.jobtype in (28,30,35) and 
year(a.refdate) = year(getdate()) and d.groupmask in (4,5)
group by b.type, month(a.refdate)
order by month(a.refdate)
  • SAP Managed Tags: