November 1, 2005 at 12:04 pm
I have the following sql statement:
drop table #temp
select 'L' as RECTYPE, identity(int, 1, 1)as JELINENO, '800002' as JENO, 'AJE' as JECODE,
'2006' as FY,
PD = case
when datepart(month, GetDate()) = '1' then '10'
when datepart(month, GetDate()) = '2' then '11'
when datepart(month, GetDate()) = '3' then '12'
when datepart(month, GetDate()) = '4' then '1'
when datepart(month, GetDate()) = '5' then '2'
when datepart(month, GetDate()) = '6' then '3'
when datepart(month, GetDate()) = '7' then '4'
when datepart(month, GetDate()) = '8' then '5'
when datepart(month, GetDate()) = '9' then '6'
when datepart(month, GetDate()) = '10' then '7'
when datepart(month, GetDate()) = '11' then '8'
when datepart(month, GetDate()) = '12' then '9'
else 'error'
end,
'1' as SUBPD, 'Journal Entry from MAXIMO' as HEADDESC, 'N' as REVFLAG
AMOUNT = case
when m.linecost < 0 then m.linecost * -1
else m.linecost
end,
ACCOUNT = case
when left(m.gldebitacct, 6) = '700048' then '40700-497-00'
when left(m.gldebitacct, 6) = '011195' then '60700-470-05'
when left(m.glcreditacct, 6) = '700048' then '40700-497-00'
when left(m.glcreditacct, 6) = '011195' then '60700-470-05'
else 'error'
end,
'195.011.0001.002.' + substring (m.gldebitacct, 16, 3) as ORG,
'MAXIMO - MATUSETRANSID = ' + convert(varchar(4), m.matusetransid)as TRANSDESC,
substring (m.gldebitacct, 1, 6)+ '.' + w.wo2 + '.' + w.wolo5 + '.' + substring (m.gldebitacct, 8, 14) + '.' +
w.wonum as PROJ,
'Y' as BILLABLEFLAG,
'From LOSC issues and returns to CostPoint to charge WOs' as NOTES
into #temp
from matusetrans m, workorder w
where (m.it1 <> 'Y' or m.it1 is null)
and m.issuetype in ('ISSUE', 'RETURN')
and m.storeloc in ('B631OT', 'B642GB', 'B641GB', 'B681AS', 'B681BS', 'B681CA', 'B681SP')
and (w.wonum = m.refwo)
go
select * from #temp
I need to have a summary row at the end of this statement that meets the following requirements:
RECTYPE = 'L'
JELINENO = the next number
JENO = '80002'
JECODE = 'AJE'
FY = '2006'
PD = '8'
SBPD = '1'
REVFLAG = 'N'
HEADERDESC = 'Journal Entry From MAXIMO'
AMOUNT = sum of amount column * -1
ACCOUNT = '20100-006-00'
ORG = 195.011
TRANDESC = 'VENDOR OWNED STOCK USED'
BILLABLEFLAG = 'Y'
NOTES = 'Maximo Issies and Returns for CostPoint'
Can anyone help with this? I am pretty new at this and can't figure out a summary row.
Thanks
November 1, 2005 at 12:06 pm
I think your best bet is to return the "summary" row as a separate recordset (separate query). Is this something that can be done?
A.J.
DBA with an attitude
November 2, 2005 at 4:22 pm
I have done something similar with a union. It would require a separate query to create the summary line and union it on the detail. In order to make it work, I had to also add a column with the value of 1 hardcoded in the first query and a value of 2 in the summary so that when I included an order by, the summation would always print last.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply