35 lines
934 B
Transact-SQL
35 lines
934 B
Transact-SQL
|
|
||
|
|
CREATE OR ALTER PROCEDURE [dbo].[Sp_CheckProblem_Safety_Statistics]
|
||
|
|
@unitId NVARCHAR(200)=NULL,
|
||
|
|
@projectId NVARCHAR(200)=NULL
|
||
|
|
AS
|
||
|
|
BEGIN
|
||
|
|
SET NOCOUNT ON;
|
||
|
|
WITH CTE AS (
|
||
|
|
SELECT
|
||
|
|
--ROW_NUMBER() OVER (
|
||
|
|
-- PARTITION BY tba.Description
|
||
|
|
-- ORDER BY CheckDate desc
|
||
|
|
--) as rn,
|
||
|
|
--tbb.ProjectName,
|
||
|
|
bspn.NatureId ,
|
||
|
|
bspn.NatureName
|
||
|
|
FROM CheckProblem_Safety AS tba WITH(NOLOCK)
|
||
|
|
--LEFT JOIN Base_Project AS tbb WITH(NOLOCK) ON tbb.ProjectId = tba.ProjectId
|
||
|
|
LEFT JOIN Base_SafetyProblemNature AS bspn WITH(NOLOCK) ON bspn.NatureId = tba.NatureId
|
||
|
|
WHERE 1=1
|
||
|
|
--AND tba.State = 1
|
||
|
|
--单位条件：@unitId为空查所有，有值则过滤单位
|
||
|
|
AND (@unitId IS NULL OR @unitId = '' OR tba.UnitId = @unitId)
|
||
|
|
--项目条件：为空不过滤
|
||
|
|
AND (@projectId IS NULL OR @projectId = '' OR tba.ProjectId = @projectId)
|
||
|
|
)
|
||
|
|
SELECT NatureName AS Title,COUNT(1) AS Num
|
||
|
|
FROM CTE
|
||
|
|
--WHERE rn = 1
|
||
|
|
GROUP BY NatureName
|
||
|
|
END
|
||
|
|
GO
|
||
|
|
|
||
|
|
|