imp_yunwei/期初合同数据统计sql...

期初合同数据统计sql
SELECT CASE WHEN "placed" = 0 THEN '未确认' WHEN  "placed" = 3 THEN '已确认' END placed,
sd2.DEPT_NAME  ,COUNT("sono") 
FROM "salesContract" a
LEFT JOIN SYS_DEPARTMENT sd ON a.DEPT_ID = sd.ID 
LEFT JOIN SYS_DEPARTMENT sd2 ON sd2.DEPT_CODE = sd.f_id
WHERE "remark" LIKE '期初%' 
AND ifnull(a.del_flag, 0) <> 1
AND a."dept_id" not IN ('6EEC827642F611B296968E9A54163B68',
'8B9C826D42F611B2B4F446F64BAA7F44','EA0D828442F611B2B323708DAB1348AE')
GROUP BY "placed",sd2.DEPT_NAME
ORDER BY sd2.DEPT_NAME,placed;

SELECT CASE WHEN "placed" = 0 THEN '未确认' WHEN  "placed" = 3 THEN '已确认' END placed,
sd.DEPT_NAME ,COUNT(sono) 
FROM salesContract a
LEFT JOIN SYS_DEPARTMENT sd ON a.DEPT_ID = sd.ID 
WHERE "remark" LIKE '期初%' 
AND ifnull(a.del_flag, 0) <> 1
AND a."dept_id" IN ('6EEC827642F611B296968E9A54163B68',
'8B9C826D42F611B2B4F446F64BAA7F44','EA0D828442F611B2B323708DAB1348AE')
GROUP BY "placed",sd.DEPT_NAME 
ORDER BY sd.DEPT_NAME,placed;

SELECT CASE WHEN "placed" = 0 THEN '未确认' WHEN  "placed" = 3 THEN '已确认' END placed,
sd2.DEPT_NAME  ,COUNT("pono") 
FROM "purchaseContract" a
LEFT JOIN SYS_DEPARTMENT sd ON a.DEPT_ID = sd.ID 
LEFT JOIN SYS_DEPARTMENT sd2 ON sd2.DEPT_CODE = sd.f_id
WHERE "remark" LIKE '期初%' 
AND ifnull(a.del_flag, 0) <> 1
AND a."dept_id" not IN ('6EEC827642F611B296968E9A54163B68',
'8B9C826D42F611B2B4F446F64BAA7F44','EA0D828442F611B2B323708DAB1348AE')
GROUP BY "placed",sd2.DEPT_NAME
ORDER BY sd2.DEPT_NAME,placed;

SELECT CASE WHEN "placed" = 0 THEN '未确认' WHEN  "placed" = 3 THEN '已确认' END placed,
sd.DEPT_NAME ,COUNT(pono) 
FROM purchaseContract a
LEFT JOIN SYS_DEPARTMENT sd ON a.DEPT_ID = sd.ID 
WHERE "remark" LIKE '期初%' 
AND ifnull(a.del_flag, 0) <> 1
AND a."dept_id" IN ('6EEC827642F611B296968E9A54163B68',
'8B9C826D42F611B2B4F446F64BAA7F44','EA0D828442F611B2B323708DAB1348AE')
GROUP BY "placed",sd.DEPT_NAME 
ORDER BY sd.DEPT_NAME,placed;
期初合同统计sql 2.0
SELECT '采购合同' AS 合同类型,sd2.DEPT_NAME AS "部门名称",
    SUM(CASE WHEN pc."placed" = 0 THEN 1 ELSE 0 END) AS "未确认",
    SUM(CASE WHEN PC."placed" = 3 THEN 1 ELSE 0 END) AS "已确认",
    SUM(CASE WHEN pc."placed" = 2 THEN 1 ELSE 0 END) AS "已提交",
    SUM(CASE WHEN crl."ifFiling" = '已备案' THEN 1 ELSE 0 END) AS "已备案",
    SUM(CASE WHEN crl."ifFiling" = '未备案' THEN 1 ELSE 0 END) AS "未备案"
FROM SGGMIIP."purchaseContract" pc 
LEFT JOIN SYS_DEPARTMENT sd ON PC."dept_id" = sd.ID 
LEFT JOIN SYS_DEPARTMENT sd2 ON sd2.DEPT_CODE = sd.f_id
LEFT JOIN (SELECT * FROM SGGMIIP."contrRegLedger" WHERE ifnull("del_flag", 0)<>1) crl ON CRL."sourceid" = pc."id" 
WHERE pc."remark" LIKE '期初%' 
AND ifnull(pc."del_flag" , 0) <> 1
AND pc."dept_id" not IN ('6EEC827642F611B296968E9A54163B68',
'8B9C826D42F611B2B4F446F64BAA7F44','EA0D828442F611B2B323708DAB1348AE')
GROUP BY sd2.DEPT_NAME
UNION 
SELECT '采购合同' AS 合同类型,sd.DEPT_NAME AS "部门名称",
    SUM(CASE WHEN pc."placed" = 0 THEN 1 ELSE 0 END) AS "未确认",
    SUM(CASE WHEN PC."placed" = 3 THEN 1 ELSE 0 END) AS "已确认",
    SUM(CASE WHEN pc."placed" = 2 THEN 1 ELSE 0 END) AS "已提交",
    SUM(CASE WHEN crl."ifFiling" = '已备案' THEN 1 ELSE 0 END) AS "已备案",
    SUM(CASE WHEN crl."ifFiling" = '未备案' THEN 1 ELSE 0 END) AS "未备案"
FROM SGGMIIP."purchaseContract" pc 
LEFT JOIN SYS_DEPARTMENT sd ON PC."dept_id" = sd.ID 
LEFT JOIN (SELECT * FROM SGGMIIP."contrRegLedger" WHERE ifnull("del_flag", 0)<>1 ) crl ON CRL."sourceid" = pc."id" 
WHERE pc."remark" LIKE '期初%' 
AND ifnull(pc."del_flag" , 0) <> 1
AND pc."dept_id" IN ('6EEC827642F611B296968E9A54163B68',
'8B9C826D42F611B2B4F446F64BAA7F44','EA0D828442F611B2B323708DAB1348AE')
GROUP BY sd.DEPT_NAME
UNION 
SELECT '销售合同' AS 合同类型,sd2.DEPT_NAME AS "部门名称",
    SUM(CASE WHEN sc."placed" = 0 THEN 1 ELSE 0 END) AS "未确认",
    SUM(CASE WHEN sc."placed" = 3 THEN 1 ELSE 0 END) AS "已确认",
    SUM(CASE WHEN sc."placed" = 2 THEN 1 ELSE 0 END) AS "已提交",
    SUM(CASE WHEN crl."ifFiling" = '已备案' THEN 1 ELSE 0 END) AS "已备案",
    SUM(CASE WHEN crl."ifFiling" = '未备案' THEN 1 ELSE 0 END) AS "未备案"
FROM SGGMIIP."salesContract" sc 
LEFT JOIN SYS_DEPARTMENT sd ON sc."dept_id" = sd.ID 
LEFT JOIN SYS_DEPARTMENT sd2 ON sd2.DEPT_CODE = sd.f_id
LEFT JOIN (SELECT * FROM SGGMIIP."contrRegLedger" WHERE ifnull("del_flag", 0)<>1 ) crl ON CRL."sourceid" = sc."id" 
WHERE sc."remark" LIKE '期初%' 
AND ifnull(sc."del_flag" , 0) <> 1
AND sc."dept_id" not IN ('6EEC827642F611B296968E9A54163B68',
'8B9C826D42F611B2B4F446F64BAA7F44','EA0D828442F611B2B323708DAB1348AE')
GROUP BY sd2.DEPT_NAME
UNION 
SELECT '销售合同' AS 合同类型,sd.DEPT_NAME AS "部门名称",
    SUM(CASE WHEN sc."placed" = 0 THEN 1 ELSE 0 END) AS "未确认",
    SUM(CASE WHEN sc."placed" = 3 THEN 1 ELSE 0 END) AS "已确认",
    SUM(CASE WHEN sc."placed" = 2 THEN 1 ELSE 0 END) AS "已提交",
    SUM(CASE WHEN crl."ifFiling" = '已备案' THEN 1 ELSE 0 END) AS "已备案",
    SUM(CASE WHEN crl."ifFiling" = '未备案' THEN 1 ELSE 0 END) AS "未备案"
FROM SGGMIIP."salesContract" sc 
LEFT JOIN SYS_DEPARTMENT sd ON sc."dept_id" = sd.ID 
LEFT JOIN (SELECT * FROM SGGMIIP."contrRegLedger" WHERE ifnull("del_flag", 0)<>1 ) crl ON CRL."sourceid" = sc."id" 
WHERE sc."remark" LIKE '期初%' 
AND ifnull(sc."del_flag" , 0) <> 1
AND sc."dept_id" IN ('6EEC827642F611B296968E9A54163B68',
'8B9C826D42F611B2B4F446F64BAA7F44','EA0D828442F611B2B323708DAB1348AE')
GROUP BY sd.DEPT_NAME;