店铺销售同期对比表[年度/月份交叉表]

金蝶系统中提供的报表不能满足客户的需要时, 可以通过查询分析工具进行自定义报表的二次开发
查询分析工具相比其他第三方工具集成度更高, 权限管理设置也非常方便, 在需求比较简单的情况下, 极力推荐

需求展示

客户需要按照年度/月份统计店铺的销售业绩

直接查询分析代码

直接写到金蝶系统里面

SET nocount ON CREATE TABLE #SellAmount (FNumber varchar(55),
FName VARCHAR ( 55 ),
FYear INT,
FMonth VARCHAR ( 55 ),
FAmount DECIMAL ( 28, 10 ) 
) INSERT INTO #SellAmount
SELECT
    * 
FROM
    (
SELECT
    t3.FNumber,
    t3.FName,
    YEAR ( t1.FDate ) AS FYear,
    ( SELECT RIGHT ( 100+ cast( MONTH ( t1.FDate ) AS VARCHAR ( 2 ) ), 2 ) ) AS FMonth,
    sum( t2.FConsignAmount ) AS FAmount 
FROM
    ICStockBill T1
    LEFT JOIN icstockbillentry T2 ON t1.finterid = t2.finterid
    LEFT JOIN t_Department T3 ON t1.FDeptID = t3.FItemID 
WHERE
    t1.FTranType = 21 
    AND t1.FDeptID IN ( SELECT fitemid FROM t_Department WHERE FParentID = 11114 ) 
    AND t3.FName LIKE '%@DeptName@%' 
GROUP BY
    t3.fnumber,
    t3.FName,
    YEAR ( t1.FDate ),
    MONTH ( t1.FDate ) WITH ROLLUP 
    ) x1 
WHERE
    x1.FMonth IS NOT NULL UNION
SELECT
    x2.FNumber,
    x2.FName,
    x2.FYear,
    '合计:',
    x2.FAmount 
FROM
    (
SELECT
    t3.FNumber,
    t3.FName,
    YEAR ( t1.FDate ) AS FYear,
    ( SELECT RIGHT ( 100+ cast( MONTH ( t1.FDate ) AS VARCHAR ( 2 ) ), 2 ) ) AS FMonth,
    sum( t2.FConsignAmount ) AS FAmount 
FROM
    ICStockBill T1
    LEFT JOIN icstockbillentry T2 ON t1.finterid = t2.finterid
    LEFT JOIN t_Department T3 ON t1.FDeptID = t3.FItemID 
WHERE
    t1.FTranType = 21 
    AND t1.FDeptID IN ( SELECT fitemid FROM t_Department WHERE FParentID = 11114 ) 
    AND t3.FName LIKE '%@DeptName@%' 
GROUP BY
    t3.fnumber,
    t3.FName,
    YEAR ( t1.FDate ),
    MONTH ( t1.FDate ) WITH ROLLUP 
    ) x2 
WHERE
    x2.FYear IS NOT NULL 
    AND x2.FMonth IS NULL SELECT
    FNumber AS '代码',
    FName AS '部门',
    FMonth AS '月份',
    sum( CASE WHEN FYear = 2015 THEN FAmount ELSE NULL END ) AS '2015年',
    sum( CASE WHEN FYear = 2016 THEN FAmount ELSE NULL END ) AS '2016年',
    sum( CASE WHEN FYear = 2017 THEN FAmount ELSE NULL END ) AS '2017年',
    SUM( CASE WHEN FYear = 2018 THEN FAmount ELSE NULL END ) AS '2018年',
    SUM( CASE WHEN FYear = 2019 THEN FAmount ELSE NULL END ) AS '2019年',
    SUM( CASE WHEN FYear = 2020 THEN FAmount ELSE NULL END ) AS '2020年' 
    FROM #SellAmount
    
GROUP BY
    FNumber,
    FName,
    FMonth 
ORDER BY
    FNumber ASC DROP TABLE #SellAmount

SQL查询代码

写道sql查询分析器里面, 便于调试

select * from ICTransactionType --单据类型表
select * from t_Department where FParentID = 11114  --部门表
Select Right(100+Month(GetDate()),2)
select right(100+cast(month(GETDATE()) as varchar(2)),2) --月份前+0

/*查询
SELECT
    t1.fbillno,
    t3.FName,
    fdate,
    YEAR ( t1.FDate ),
    ( SELECT RIGHT ( 100+ cast( MONTH ( t1.FDate ) AS VARCHAR ( 2 ) ), 2 ) ),
    t2.fqty,
    t2.FPrice,
    T2.FAmount,
    t2.FConsignPrice,
    T2.FConsignAmount,* 
FROM
    ICStockBill T1
    LEFT JOIN icstockbillentry T2 ON t1.finterid = t2.finterid
    LEFT JOIN t_Department T3 ON t1.FDeptID = t3.FItemID 
WHERE
    t1.FTranType = 21 
    AND t1.FDeptID IN ( SELECT fitemid FROM t_Department WHERE FParentID = 11114 ) 
    AND t3.FName LIKE '%网络-阿里澳沃%'
*/

/*-------↓实现过程↓-------*/
CREATE TABLE #SellAmount (FNumber varchar(55),
    FName VARCHAR ( 55 ),
    FYear INT,
    FMonth VARCHAR ( 55 ),
    FAmount DECIMAL ( 28, 10 ) 
    ) INSERT INTO #SellAmount
SELECT
    * 
FROM
    (
SELECT
    t3.FNumber,
    t3.FName,
    YEAR ( t1.FDate ) AS FYear,
    ( SELECT RIGHT ( 100+ cast( MONTH ( t1.FDate ) AS VARCHAR ( 2 ) ), 2 ) ) AS FMonth,
    sum( t2.FConsignAmount ) AS FAmount 
FROM
    ICStockBill T1
    LEFT JOIN icstockbillentry T2 ON t1.finterid = t2.finterid
    LEFT JOIN t_Department T3 ON t1.FDeptID = t3.FItemID 
WHERE
    t1.FTranType = 21 
    AND t1.FDeptID IN ( SELECT fitemid FROM t_Department WHERE FParentID = 11114 ) 
    AND t3.FName LIKE '%%' 
GROUP BY
    t3.fnumber,
    t3.FName,
    YEAR ( t1.FDate ),
    MONTH ( t1.FDate ) WITH ROLLUP 
    ) x1 
WHERE
    x1.FMonth IS NOT NULL UNION
SELECT
    x2.FNumber,
    x2.FName,
    x2.FYear,
    '合计:',
    x2.FAmount 
FROM
    (
SELECT
    t3.FNumber,
    t3.FName,
    YEAR ( t1.FDate ) AS FYear,
    ( SELECT RIGHT ( 100+ cast( MONTH ( t1.FDate ) AS VARCHAR ( 2 ) ), 2 ) ) AS FMonth,
    sum( t2.FConsignAmount ) AS FAmount 
FROM
    ICStockBill T1
    LEFT JOIN icstockbillentry T2 ON t1.finterid = t2.finterid
    LEFT JOIN t_Department T3 ON t1.FDeptID = t3.FItemID 
WHERE
    t1.FTranType = 21 
    AND t1.FDeptID IN ( SELECT fitemid FROM t_Department WHERE FParentID = 11114 ) 
    AND t3.FName LIKE '%%' 
GROUP BY
    t3.fnumber,
    t3.FName,
    YEAR ( t1.FDate ),
    MONTH ( t1.FDate ) WITH ROLLUP 
    ) x2 
WHERE
    x2.FYear IS NOT NULL 
    AND x2.FMonth IS NULL SELECT
    FNumber AS '代码',
    FName AS '部门',
    FMonth AS '月份',
    sum( CASE WHEN FYear = 2015 THEN FAmount ELSE NULL END ) AS '2015年',
    sum( CASE WHEN FYear = 2016 THEN FAmount ELSE NULL END ) AS '2016年',
    sum( CASE WHEN FYear = 2017 THEN FAmount ELSE NULL END ) AS '2017年',
    SUM( CASE WHEN FYear = 2018 THEN FAmount ELSE NULL END ) AS '2018年',
    SUM( CASE WHEN FYear = 2019 THEN FAmount ELSE NULL END ) AS '2019年',
    SUM( CASE WHEN FYear = 2020 THEN FAmount ELSE NULL END ) AS '2020年' 
    FROM #SellAmount
    
GROUP BY
    FNumber,
    FName,
    FMonth 
ORDER BY
    FNumber ASC DROP TABLE #SellAmount
 

实现效果