CREATE PROCEDURE LPCHL_ZRHF_TONGJI
@LastLeaderApproveTimeBegin datetime = NULL,
@LastLeaderApproveTimeEnd datetime = NULL
AS
IF @LastLeaderApproveTimeBegin IS NULL AND @LastLeaderApproveTimeEnd IS NULL
BEGIN
SELECT * FROM V_LPCHL_ZRHF_TONGJI
END
ELSE
SET @LastLeaderApproveTimeEnd = CONVERT(DATETIME, @LastLeaderApproveTimeEnd + '23:59:59')
BEGIN
SELECT B2.gs_name, B1.hj_gscdje, B1.hj_zzrcdje, B1.hj_zhjld_cdje, B1.hj_jjld_cdje,
B2.hj_masterid
FROM
(SELECT dbo.gongsi.name AS gs_name, SUM(ISNULL(A.gscdje, 0)) AS hj_gscdje,
SUM(ISNULL(A.zzr1_cdje, 0)) + SUM(ISNULL(A.zzr2_cdje, 0)) + SUM(ISNULL(A.zzr3_cdje, 0)) AS hj_zzrcdje,
SUM(ISNULL(A.zhjld_cdje, 0)) AS hj_zhjld_cdje, SUM(ISNULL(A.jjld_cdje, 0)) AS hj_jjld_cdje
FROM dbo.gongsi RIGHT OUTER JOIN
(SELECT dbo.T_LPCHL_DETAIL_ZHDDCH.gscdje,
dbo.T_LPCHL_DETAIL_ZHDDCH.zzr1_cdje,
dbo.T_LPCHL_DETAIL_ZHDDCH.zzr2_cdje,
dbo.T_LPCHL_DETAIL_ZHDDCH.zzr3_cdje,
dbo.T_LPCHL_DETAIL_ZHDDCH.zhjld_cdje,
dbo.T_LPCHL_DETAIL_ZHDDCH.jjld_cdje, dbo.T_LPCHL_DETAIL_ZHDDCH.masterid,dbo.T_LPCHL_DETAIL_ZHDDCH.lrshijian,
dbo.T_LPCHL_DETAIL_ZHDDCH.dcgs
FROM dbo.T_LPCHL_DETAIL_ZHDDCH INNER JOIN
dbo.T_LPCHL_MASTER ON
dbo.T_LPCHL_DETAIL_ZHDDCH.masterid = dbo.T_LPCHL_MASTER.id INNER JOIN
(SELECT MAX(shijian) AS zhqpsj, masterid
FROM T_LPCHL_DETAIL_ZRHF_LDQP
GROUP BY masterid) B ON dbo.T_LPCHL_MASTER.id = B.masterid
WHERE (dbo.T_LPCHL_MASTER.zrhrispass = 1) AND (B.zhqpsj >= @LastLeaderApproveTimeBegin) AND (B.zhqpsj <= @LastLeaderApproveTimeEnd)) A ON
dbo.gongsi.name = A.dcgs
GROUP BY dbo.gongsi.name) B1
RIGHT OUTER JOIN
(SELECT dbo.gongsi.name AS gs_name, COUNT(C.id) AS hj_masterid
FROM
(SELECT dbo.T_LPCHL_MASTER.id, dbo.T_LPCHL_MASTER.slgs
FROM dbo.T_LPCHL_MASTER INNER JOIN
(SELECT MAX(shijian) AS zhqpsj, masterid
FROM T_LPCHL_DETAIL_ZRHF_LDQP
GROUP BY masterid) B
ON dbo.T_LPCHL_MASTER.id = B.masterid
WHERE (B.zhqpsj >= @LastLeaderApproveTimeBegin) AND (B.zhqpsj <= @LastLeaderApproveTimeEnd)) C
RIGHT OUTER JOIN
dbo.gongsi ON dbo.gongsi.name = C.slgs
GROUP BY dbo.gongsi.name) B2
ON B1.gs_name = B2.gs_name
END
GO
uj5u.com熱心網友回復：
那就再加引數唄再加一個起始時間、終止時間
uj5u.com熱心網友回復：
同意樓上uj5u.com熱心網友回復：
CREATE PROCEDURE LPCHL_ZRHF_TONGJI
@LASTLEADERAPPROVETIMEBEGIN DATETIME = NULL ,
@LASTLEADERAPPROVETIMEEND DATETIME = NULL，
@starttime DATETIME = NULL，
@endtime DATETIME = NULL
AS
IF @LASTLEADERAPPROVETIMEBEGIN IS NULL AND @LASTLEADERAPPROVETIME END IS NULL
BEGIN
SELECT *
FROM
V_LPCHL_ZRHF_TONGJI ENDELSE SET @LASTLEADERAPPROVETIMEEND = CONVERT ( DATETIME , @LASTLEADERAPPROVETIMEEND + '23:59:59' ) BEGINSELECT B2.GS_NAME ,
B1.HJ_GSCDJE ,
B1.HJ_ZZRCDJE ,
B1.HJ_ZHJLD_CDJE ,
B1.HJ_JJLD_CDJE ,
B2.HJ_MASTERID FROM (
SELECT
DBO.GONGSI.NAME AS GS_NAME ,
SUM ( ISNULL ( A.GSCDJE , 0 ) ) AS HJ_GSCDJE ,
SUM ( ISNULL ( A.ZZR1_CDJE , 0 ) ) +
SUM ( ISNULL ( A.ZZR2_CDJE , 0 ) ) +
SUM ( ISNULL ( A.ZZR3_CDJE , 0 ) ) AS HJ_ZZRCDJE ,
SUM ( ISNULL ( A.ZHJLD_CDJE , 0 ) ) AS HJ_ZHJLD_CDJE ,
SUM ( ISNULL ( A.JJLD_CDJE , 0 ) ) AS HJ_JJLD_CDJE
FROM
DBO.GONGSI RIGHT OUTER JOIN (
SELECT
DBO.T_LPCHL_DETAIL_ZHDDCH.GSCDJE ,
DBO.T_LPCHL_DETAIL_ZHDDCH.ZZR1_CDJE ,
DBO.T_LPCHL_DETAIL_ZHDDCH.ZZR2_CDJE ,
DBO.T_LPCHL_DETAIL_ZHDDCH.ZZR3_CDJE ,
DBO.T_LPCHL_DETAIL_ZHDDCH.ZHJLD_CDJE ,
DBO.T_LPCHL_DETAIL_ZHDDCH.JJLD_CDJE ,
DBO.T_LPCHL_DETAIL_ZHDDCH.MASTERID ,
DBO.T_LPCHL_DETAIL_ZHDDCH.LRSHIJIAN ,
DBO.T_LPCHL_DETAIL_ZHDDCH.DCGS
FROM
DBO.T_LPCHL_DETAIL_ZHDDCH INNER JOIN DBO.T_LPCHL_MASTER
ON DBO.T_LPCHL_DETAIL_ZHDDCH.MASTERID = DBO.T_LPCHL_MASTER.ID INNER JOIN (
SELECT
MAX ( SHIJIAN ) AS ZHQPSJ ,
MASTERID
FROM
T_LPCHL_DETAIL_ZRHF_LDQP
GROUP BY
MASTERID ) B
ON DBO.T_LPCHL_MASTER.ID = B.MASTERIDWHERE ( DBO.T_LPCHL_MASTER.ZRHRISPASS = 1 )
AND ( B.ZHQPSJ >= @LASTLEADERAPPROVETIMEBEGIN )
AND ( B.ZHQPSJ <= @LASTLEADERAPPROVETIMEEND ) ) A
ON DBO.GONGSI.NAME = A.DCGS
GROUP BY
DBO.GONGSI.NAME ) B1RIGHT OUTER JOIN (
SELECT
DBO.GONGSI.NAME AS GS_NAME ,
COUNT ( C.ID ) AS HJ_MASTERIDFROM (
SELECT
DBO.T_LPCHL_MASTER.ID ,
DBO.T_LPCHL_MASTER.SLGSFROM DBO.T_LPCHL_MASTER INNER JOIN (
SELECT
MAX ( SHIJIAN ) AS ZHQPSJ ,
MASTERID
FROM
T_LPCHL_DETAIL_ZRHF_LDQP
GROUP BY
MASTERID ) BON DBO.T_LPCHL_MASTER.ID = B.MASTERID
WHERE
( B.ZHQPSJ >= @LASTLEADERAPPROVETIMEBEGIN )
AND ( B.ZHQPSJ <= @LASTLEADERAPPROVETIMEEND ) ) C RIGHT OUTER JOIN DBO.GONGSI
ON DBO.GONGSI.NAME = C.SLGSGROUP BY DBO.GONGSI.NAME ) B2ON B1.GS_NAME = B2.GS_NAME
END
GO
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/106267.html
標籤：數據庫相關