--1.判断存储过程是否存在，存在则删除
IF OBJECT_ID('存储过程名称', 'P') IS NOT NULLDROP PROCEDURE dbo.usp_GetMeasureAnalysisIssueReminder_Query（存储过程名） GO
--2.这些是 SQL-92 设置语句，使 SQL Server 2000/2005 遵从 SQL-92 规则。可不写
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
--3.创建存储过程并声明传入参数 CREATE PROCEDURE usp_GetMeasureAnalysisIssueReminder_Query(@companyCode NVARCHAR(30)) AS
--4.编写存储过程内容
BEGINdeclare @lastMonth varchar(20)declare @currentMonth varchar(20)set @lastMonth=(select CONVERT(varchar(7), dateadd(mm,-1,getdate()) , 120) + '-1')set @currentMonth=(select CONVERT(varchar(7), getdate() , 120) + '-1');with cr as(select distinct PassNo from MS_AssetGroupMeasure agminner join MS_MeasureAnalysis ma on agm.Id = ma.AssetGroupMeasureIdwhere ma.MeasureResult='Fail' and agm.PassDate between @lastMonth and @currentMonth AND agm.CompanyCode = @companyCode)select ag.Name,agm.PassNo,agm.BatchNo,ma.SampleName,ma.Item,ma.AddedDate,mr.CodeDescription AS MeasureResult,CONVERT(varchar(20),CONVERT(DECIMAL(13,3),ma.RandR)) AS RandR,CONVERT(varchar(20),CONVERT(DECIMAL(13,3),ma.Ndc)) AS Ndcfrom MS_AssetGroupMeasure agminner join AMG_AssetGroup ag on ag.Id = agm.AssetGroupId and ag.IsDeleted=0inner join MS_MeasureAnalysis ma on agm.Id=ma.AssetGroupMeasureId and ma.IsDeleted=0LEFT JOIN dbo.SYS_CodeTable mr ON ma.MeasureResult=mr.Code AND mr.IsDeleted=0 AND mr.CodeCategory = 'MeasureResult' where agm.IsDeleted=0 and agm.PassNo in (select * from cr)order by agm.PassNo DESC,ma.AddedDate DESCEND
转载于:https://www.cnblogs.com/wang-min/p/11196005.html
本文链接：https://my.lmcjl.com/post/12568.html
展开阅读全文
4 评论