现在已有代码,希望再加两列是该科目的期初余额,就是分别计算期间之前的总计借贷金额。求助
select '1' AS SORTNO
, a.SegValue1 + ' ' + d.SegmentName AS 계정과목
, CAST(YEAR(a.JEDate) AS VARCHAR) + '.' + SUBSTRING(CONVERT(VARCHAR, a.JEDate,126),6,2) + '.' + SUBSTRING(CONVERT(VARCHAR, a.JEDate,126),9,2) as 회계일자
, a.JournalCode + '-' + CAST(YEAR(a.JEDate) AS VARCHAR) + '.' + SUBSTRING(CONVERT(VARCHAR, a.JEDate,126),6,2) + '.' + SUBSTRING(CONVERT(VARCHAR, a.JEDate,126),9,2) + '-' + cast(a.JournalNum as varchar(10)) as 전표번호
, a.SegValue4 AS 거래처코드
, e.SegmentName as 거래처명
, a."Description" as 적요
, a.BookDebitAmount AS 借方金额
, a.BookCreditAmount AS 贷方金额
, a.BookDebitAmount - a.BookCreditAmount as 잔액
,a.CurrencyCode as 原币编码
,(case CurrencyCode when 'RMB' then null else a.DebitAmount end) as 原币借方金额
,(case CurrencyCode when 'RMB' then null else a.CreditAmount end)as 原币贷方金额
from GLJrnDtl a left join (select SegmentCode, SegmentName from COASegValues where SegmentNbr = 1 and Company = 'JH_CN_01') d on a.SegValue1 = d.SegmentCode
left join (select SegmentCode, SegmentName from COASegValues where SegmentNbr = 4 and Company = 'JH_CN_01') e on a.SegValue4 = e.SegmentCode
where a.JEDate >= '20130801'
and a.JEDate <= '20130831'
and a.SegValue1 BETWEEN '1' AND '111111111111111111111111111111111111'
select '1' AS SORTNO
, a.SegValue1 + ' ' + d.SegmentName AS 계정과목
, CAST(YEAR(a.JEDate) AS VARCHAR) + '.' + SUBSTRING(CONVERT(VARCHAR, a.JEDate,126),6,2) + '.' + SUBSTRING(CONVERT(VARCHAR, a.JEDate,126),9,2) as 회계일자
, a.JournalCode + '-' + CAST(YEAR(a.JEDate) AS VARCHAR) + '.' + SUBSTRING(CONVERT(VARCHAR, a.JEDate,126),6,2) + '.' + SUBSTRING(CONVERT(VARCHAR, a.JEDate,126),9,2) + '-' + cast(a.JournalNum as varchar(10)) as 전표번호
, a.SegValue4 AS 거래처코드
, e.SegmentName as 거래처명
, a."Description" as 적요
, a.BookDebitAmount AS 借方金额
, a.BookCreditAmount AS 贷方金额
, a.BookDebitAmount - a.BookCreditAmount as 잔액
,a.CurrencyCode as 原币编码
,(case CurrencyCode when 'RMB' then null else a.DebitAmount end) as 原币借方金额
,(case CurrencyCode when 'RMB' then null else a.CreditAmount end)as 原币贷方金额
from GLJrnDtl a left join (select SegmentCode, SegmentName from COASegValues where SegmentNbr = 1 and Company = 'JH_CN_01') d on a.SegValue1 = d.SegmentCode
left join (select SegmentCode, SegmentName from COASegValues where SegmentNbr = 4 and Company = 'JH_CN_01') e on a.SegValue4 = e.SegmentCode
where a.JEDate >= '20130801'
and a.JEDate <= '20130831'
and a.SegValue1 BETWEEN '1' AND '111111111111111111111111111111111111'
, a.SegValue1 + ' ' + d.SegmentName AS 계정과목
, CAST(YEAR(a.JEDate) AS VARCHAR) + '.' + SUBSTRING(CONVERT(VARCHAR, a.JEDate,126),6,2) + '.' + SUBSTRING(CONVERT(VARCHAR, a.JEDate,126),9,2) as 회계일자
, a.JournalCode + '-' + CAST(YEAR(a.JEDate) AS VARCHAR) + '.' + SUBSTRING(CONVERT(VARCHAR, a.JEDate,126),6,2) + '.' + SUBSTRING(CONVERT(VARCHAR, a.JEDate,126),9,2) + '-' + cast(a.JournalNum as varchar(10)) as 전표번호
, a.SegValue4 AS 거래처코드
, e.SegmentName as 거래처명
, a."Description" as 적요
, a.BookDebitAmount AS 借方金额
, a.BookCreditAmount AS 贷方金额
, a.BookDebitAmount - a.BookCreditAmount as 잔액
,a.CurrencyCode as 原币编码
,(case CurrencyCode when 'RMB' then null else a.DebitAmount end) as 原币借方金额
,(case CurrencyCode when 'RMB' then null else a.CreditAmount end)as 原币贷方金额
,期初余额1=(select SUM(金额) from GLJrnDtl t where a.SegValue1=t.SegValue1 and t.JEDate<a.JEDate) -->里面的sum(金额)对应哪个余额,就用哪个字段
,期初余额2=(select SUM(金额) from GLJrnDtl t where a.SegValue1=t.SegValue4 and t.JEDate<a.JEDate) -->同上
from GLJrnDtl a
left join (select SegmentCode, SegmentName from COASegValues where SegmentNbr = 1 and Company = 'JH_CN_01') d on a.SegValue1 = d.SegmentCode
left join (select SegmentCode, SegmentName from COASegValues where SegmentNbr = 4 and Company = 'JH_CN_01') e on a.SegValue4 = e.SegmentCode
where a.JEDate >= '20130801'
and a.JEDate <= '20130831'
and a.SegValue1 BETWEEN '1' AND '111111111111111111111111111111111111'现在是用日期比较,不知道是否还有其他条件?
SELECT '1' AS SORTNO ,
a.SegValue1 + ' ' + d.SegmentName AS 계정과목 ,
CAST(YEAR(a.JEDate) AS VARCHAR) + '.'
+ SUBSTRING(CONVERT(VARCHAR, a.JEDate, 126), 6, 2) + '.'
+ SUBSTRING(CONVERT(VARCHAR, a.JEDate, 126), 9, 2) AS 회계일자 ,
a.JournalCode + '-' + CAST(YEAR(a.JEDate) AS VARCHAR) + '.'
+ SUBSTRING(CONVERT(VARCHAR, a.JEDate, 126), 6, 2) + '.'
+ SUBSTRING(CONVERT(VARCHAR, a.JEDate, 126), 9, 2) + '-'
+ CAST(a.JournalNum AS VARCHAR(10)) AS 전표번호 ,
a.SegValue4 AS 거래처코드 ,
e.SegmentName AS 거래처명 ,
a."Description" AS 적요 ,
a.BookDebitAmount AS 借方金额 ,
a.BookCreditAmount AS 贷方金额 ,
a.BookDebitAmount - a.BookCreditAmount AS 잔액 ,
a.CurrencyCode AS 原币编码 ,
( CASE CurrencyCode
WHEN 'RMB' THEN NULL
ELSE a.DebitAmount
END ) AS 原币借方金额 ,
( CASE CurrencyCode
WHEN 'RMB' THEN NULL
ELSE a.CreditAmount
END ) AS 原币贷方金额,
原币借方期初余额=(SELECT SUM(CASE CurrencyCode WHEN 'RMB' THEN NULL ELSE m.DebitAmount end) FROM GLJrnDtl m WHERE m.SegValue1 = d.SegmentCode AND m.JEDate < '20130801'), --这里WHERE后面的条件,可能需要楼主根据业务改一下
原币贷方期初余额=(SELECT SUM(CASE CurrencyCode WHEN 'RMB' THEN NULL ELSE m.CreditAmount end) FROM GLJrnDtl m WHERE m.SegValue1 = d.SegmentCode AND m.JEDate < '20130801')
FROM GLJrnDtl a
LEFT JOIN ( SELECT SegmentCode ,
SegmentName
FROM COASegValues
WHERE SegmentNbr = 1
AND Company = 'JH_CN_01'
) d ON a.SegValue1 = d.SegmentCode
LEFT JOIN ( SELECT SegmentCode ,
SegmentName
FROM COASegValues
WHERE SegmentNbr = 4
AND Company = 'JH_CN_01'
) e ON a.SegValue4 = e.SegmentCode
WHERE a.JEDate >= '20130801'
AND a.JEDate <= '20130831'
AND a.SegValue1 BETWEEN '1'
AND '111111111111111111111111111111111111'