2条回答 默认 最新
- bbwolf 2017-12-10 07:39关注
select ISNULL(T1.月份,T2.月份) as 月份,
装配总数,合同金额,装配成本,装配利润,装配利润率,
销售台数,开票金额,销售成本,销售利润,销售利润率
FROM
(
select cast(YEAR(EXTEND50) as nvarchar(4)) + '-' + cast(MONTH(EXTEND50) as nvarchar(2)) as 月份,
COUNT(sub_account_id) as 装配总数,
SUM(discount_money) as 合同金额 ,
SUM(EXTEND36) as 装配成本 ,
SUM(discount_money) / 1.17 - SUM(EXTEND36) as 装配利润 ,
(SUM(discount_money) / 1.17 - SUM(EXTEND36) ) / SUM(discount_money) as 装配利润率
from T_CONTRACT_ORDER
WHERE EXTEND1 = '装配工程单' AND EXTEND50 >= @KSRQ AND EXTEND50 <= @JSRQ
Group By cast(YEAR(EXTEND50) as nvarchar(4)) + '-' + cast(MONTH(EXTEND50) as nvarchar(2))
) T1
FULL OUTER JOIN
(
Select cast(YEAR(O.EXTEND50) as nvarchar(4)) + '-' + cast(MONTH(O.EXTEND50) as nvarchar(2)) as 月份,
SUM(D.INVOICED_NUM) as 销售台数,
SUM(O.INVOICED_MONEY) as 开票金额 ,
SUM(O.EXTEND36) as 销售成本 ,
SUM(O.INVOICED_MONEY) / 1.17 - SUM(O.EXTEND36) as 销售利润 ,
(SUM(O.INVOICED_MONEY) / 1.17 - SUM(O.EXTEND36) ) / SUM(O.INVOICED_MONEY) as 销售利润率
FROM T_CONTRACT_ORDER O INNER JOIN T_CONTRACT_ORDER_detail D ON O.sub_account_id = D.sub_account_id
where O.EXTEND50 >= @KSRQ AND O.EXTEND50 <= @JSRQ and O.EXTEND1 = '工程销售'
GROUP BY cast(YEAR(O.EXTEND50) as nvarchar(4)) + '-' + cast(MONTH(O.EXTEND50) as nvarchar(2))
) T2
ON T1.月份 = T2.月份本回答被题主选为最佳回答 , 对您是否有帮助呢?解决 无用评论 打赏 举报
悬赏问题
- ¥15 安卓adb backup备份应用数据失败
- ¥15 eclipse运行项目时遇到的问题
- ¥15 关于#c##的问题:最近需要用CAT工具Trados进行一些开发
- ¥15 南大pa1 小游戏没有界面,并且报了如下错误,尝试过换显卡驱动,但是好像不行
- ¥15 没有证书,nginx怎么反向代理到只能接受https的公网网站
- ¥50 成都蓉城足球俱乐部小程序抢票
- ¥15 yolov7训练自己的数据集
- ¥15 esp8266与51单片机连接问题(标签-单片机|关键词-串口)(相关搜索:51单片机|单片机|测试代码)
- ¥15 电力市场出清matlab yalmip kkt 双层优化问题
- ¥30 ros小车路径规划实现不了,如何解决?(操作系统-ubuntu)