08-08-2019 4:18 AM
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)
08-08-2019 1:54 PM
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)