select
CompanyID,WorkYear,WorkMonth,AccountCode,DeAment,BeAment,
case Natrue when 'D' then DeAment+BeAment
when 'C' then DeAment-BeAment
else null
end as Total,
(select sum(case Natrue when 'D' then DeAment+BeAment
when 'C' then DeAment-BeAment
else null
end)
from
glvAccountSummary
where
CompanyID=a.CompanyID
and (
WorkYear<a.WorkYear
or
WorkYear=a.WorkYear
and
WorkMonth<=a.WorkMonth
)
) as YTD_Total
from
glvAccountSummary a
CompanyID,WorkYear,WorkMonth,AccountCode,DeAment,BeAment,
case Natrue when 'D' then DeAment+BeAment
when 'C' then DeAment-BeAment
else null
end as Total,
(select sum(case Natrue when 'D' then DeAment+BeAment
when 'C' then DeAment-BeAment
else null
end)
from
glvAccountSummary
where
CompanyID=a.CompanyID
and (
WorkYear<a.WorkYear
or
WorkYear=a.WorkYear
and
WorkMonth<=a.WorkMonth
)
) as YTD_Total
from
glvAccountSummary a
解决方案 »
- 请问如何自动同步数据库结构
- 【日期问题】根据日期获取最新的纪录
- 关于时间的查询问题!
- sql server用函数生成字段默认值的小问题
- 高手帮忙:本人遇到一个SQLSERVER2000里的小问题,能帮帮我,分不够,解决了给你加。 帮帮忙啊?
- SQL SEVER能装在WIN98下吗?
- 讨论:如何设计一个在应用程序和数据库之间的中间件?(请大家踊跃发言,谢谢!!!)
- 我怎样配置BDE,才能使DELPHI调用SQL SERVER数据库,请帮帮我???
- 高手请进!!!!!!!!!!有关汇总语句!!!!!
- 如何给 SQL Server的事务日志减肥?
- 急!!!sql server2000 数据库连接问题
- 帮忙给写个SQL语句
Total=DeAment+BeAment*(case Natrue when 'D' then 1 when 'C' then -1 end) ,
YTD_Total=(select sum(case Natrue when 'D' then DeAment+BeAment when 'C' then DeAment-BeAment end)
from glvAccountSummary where workyear<a.workyear or (workyear=a.workyear and workmonth<=a.workmonth) )
from glvAccountSummary a
sum(CASE nature WHEN 'D' THEN a.DeAmount+a.BeAmount
WHEN 'C' THEN a.DeAment - a.BeAment else 0 END) AS Total,
( sum(CASE nature WHEN 'D' THEN a.DeAment+a.BeAment
WHEN 'C' THEN a.DeAment - a.BeAment else 0 END)
+isnull((select sum(CASE nature WHEN 'D' THEN DeAment+BeAment
WHEN 'C' THEN DeAment - BeAment else 0 END)
from glvAccountSummary
where Workyear*100+Workmonth<a.Workyear*100+a.Workmonth),0)) as YTDTotal
from glvAccountSummary a
group by a.Workmonth,a.Workyear