Skip to Content
0
Aug 08, 2019 at 03:18 AM

Query to calculate margin

47 Views

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)