以下是我的SQL,现在日期控件有按日和按月,按日期分的现在显示没问题,按年月分的时候该如何在SQL里设置,想要获得的效果就是月份之间的对比,例如选2018年,1月和2月,显示的是1月和2月的数据,即两行
SELECT
X.日期 ,
CASE WHEN X.部门编号='0801'THEN 1
WHEN X.部门编号='0802'THEN 2
WHEN X.部门编号='0809'THEN 3
WHEN X.部门编号='0805'THEN 4
WHEN X.部门编号='0806'THEN 5
WHEN X.部门编号='0813'THEN 6
WHEN X.部门编号='0810'THEN 7
WHEN X.部门编号='0808'THEN 8
WHEN X.部门编号='0814'THEN 9
WHEN X.部门编号='0804'THEN 10
WHEN X.部门编号= '0803'THEN 11
WHEN X.部门编号='21' THEN 12
WHEN X.部门编号='0812'THEN 13 END
AS 部门编号1,
X.部门编号,
X.部门名称,
CAST(SUM (X.入库件数*X.支数)AS DECIMAL(16,0))AS 产能
FROM
(SELECT
CONVERT(VARCHAR(100),A.DOC_DATE ,23)AS 日期,
D.ADMIN_UNIT_CODE AS 部门编号,
D.ADMIN_UNIT_NAME AS 部门名称,
CASE WHEN C.UDF021='' THEN 1 ELSE CAST(C.UDF021 AS DECIMAL(16,0))END AS 支数,
CAST(B.ACCEPTED_INVENTORY_QTY AS DECIMAL(16,4))AS 入库件数
FROM MO_RECEIPT A
LEFT JOIN MO_RECEIPT_D B ON B.MO_RECEIPT_ID=A.MO_RECEIPT_ID
LEFT JOIN ITEM C ON C.ITEM_BUSINESS_ID =B.ITEM_ID
LEFT JOIN ADMIN_UNIT D ON D.ADMIN_UNIT_ID =A.Owner_Dept
where A.DOC_DATE BETWEEN '${开始日期}'AND '${结束日期}')X
GROUP BY
X.日期 ,
X.部门名称 ,
X.部门编号
ORDER BY 2,1 ASC