CREATE PROCEDURE LPCHL_ZRHF_TONGJI
@ LastLeaderApproveTimeBegin datetime=NULL,
@ LastLeaderApproveTimeEnd datetime=NULL
AS
IF @ LastLeaderApproveTimeBegin IS NULL AND @ LastLeaderApproveTimeEnd IS NULL
The BEGIN
SELECT * FROM V_LPCHL_ZRHF_TONGJI
END
The ELSE
SET @ LastLeaderApproveTimeEnd=the CONVERT (DATETIME, @ LastLeaderApproveTimeEnd + '23:59:59')
The BEGIN
The SELECT B2 gs_name, B1 hj_gscdje, B1. Hj_zzrcdje, B1. Hj_zhjld_cdje, B1. Hj_jjld_cdje,
B2. Hj_masterid
The FROM
(the SELECT dbo. Gongsi. Name AS gs_name, SUM (ISNULL (al-qeada scdje, 0)) AS hj_gscdje,
The SUM (ISNULL (A.z zr1_cdje, 0)) + SUM (ISNULL (A.z zr2_cdje, 0)) + SUM (ISNULL (A.z zr3_cdje, 0)) AS hj_zzrcdje,
The SUM (ISNULL (A.z hjld_cdje, 0)) AS hj_zhjld_cdje, SUM (ISNULL (a. jld_cdje, 0)) AS hj_jjld_cdje
The FROM dbo. Gongsi RIGHT OUTER JOIN
(the 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

The 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
The FROM T_LPCHL_DETAIL_ZRHF_LDQP
GROUP BY masterid) B ON dbo. T_LPCHL_MASTER. Id=B.m asterid
The WHERE (dbo. T_LPCHL_MASTER. Zrhrispass=1) AND (B.z HQPSJ & gt;=@ LastLeaderApproveTimeBegin) AND (B.z HQPSJ & lt;=@ LastLeaderApproveTimeEnd) ON A
Dbo. Gongsi. Name=a. d. CGS
GROUP BY dbo. Gongsi. Name) B1
RIGHT OUTER JOIN
(the SELECT dbo. Gongsi. Name AS gs_name, COUNT (C.i d) AS hj_masterid
The FROM
(the SELECT dbo. T_LPCHL_MASTER. Id, dbo T_LPCHL_MASTER. SLGS
The FROM dbo. T_LPCHL_MASTER INNER JOIN
(SELECT MAX (shijian) AS ZHQPSJ, masterid
The FROM T_LPCHL_DETAIL_ZRHF_LDQP
GROUP BY masterid) B
ON dbo. T_LPCHL_MASTER. Id=B.m asterid
The WHERE (B.z HQPSJ & gt;=@ LastLeaderApproveTimeBegin) AND (B.z HQPSJ & lt; C=@ LastLeaderApproveTimeEnd))
RIGHT OUTER JOIN
Dbo. Gongsi ON dbo. C.s. LGS gongsi. Name=
GROUP BY dbo. Gongsi. Name) B2
ON B1. Gs_name=B2. Gs_name
END
GO

CodePudding user response:

Then add parameters bai
Add a starting time and end time

CodePudding user response:

Agree with the upstairs

CodePudding user response:

 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 
The BEGIN 
SELECT * 
The FROM 
V_LPCHL_ZRHF_TONGJI ENDELSE SET @ LASTLEADERAPPROVETIMEEND=the 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, 
The SUM (ISNULL (al-qeada SCDJE, 0)) AS HJ_GSCDJE, 
The SUM (ISNULL (A.Z ZR1_CDJE, 0)) + 
The SUM (ISNULL (A.Z ZR2_CDJE, 0)) + 
The SUM (ISNULL (A.Z ZR3_CDJE, 0)) AS HJ_ZZRCDJE, 
The SUM (ISNULL (A.Z HJLD_CDJE, 0)) AS HJ_ZHJLD_CDJE, 
The SUM (ISNULL (a. JLD_CDJE, 0)) AS HJ_JJLD_CDJE 
The 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 
The 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 
The FROM 
T_LPCHL_DETAIL_ZRHF_LDQP 
GROUP BY 
MASTERID) B 
ON DBO. T_LPCHL_MASTER. ID=B.M ASTERIDWHERE (DBO. T_LPCHL_MASTER. ZRHRISPASS=1) 
AND (B.Z HQPSJ & gt;=@ LASTLEADERAPPROVETIMEBEGIN) 
AND (B.Z HQPSJ & lt;=@ LASTLEADERAPPROVETIMEEND)) A 
ON DBO. GONGSI. NAME=a. d. CGS 
GROUP BY 
DBO. GONGSI. NAME) B1RIGHT OUTER JOIN (
SELECT 
DBO. GONGSI. NAME AS GS_NAME, 
COUNT (C.I D) 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 
The FROM 
T_LPCHL_DETAIL_ZRHF_LDQP 
GROUP BY 
MASTERID) BON DBO. T_LPCHL_MASTER. ID=B.M ASTERID 
WHERE 
(B.Z HQPSJ & gt; nullnullnullnullnull