22,300
社区成员




IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FormatDate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FormatDate]
GO
CREATE FUNCTION [dbo].[FormatDate](@CurDate datetime)
RETURNS datetime
AS
BEGIN
return cast(year(@CurDate) as varchar(4))+'-'+cast(month(@CurDate) as varchar(2))+'-'+cast(day(@CurDate) as varchar(2))
END
GO
[/quote]
判断是否运行的语句日期加上函数 FormatDate 如下
IF EXISTS(Select 1 From cRunFlagTbl where dbo.FormatDate(run_date)=dbo.FormatDate(getdate()) and run_flag=1)
SET @run_flag=1
[/quote]
USE [CUService]
GO
/****** Object: StoredProcedure [dbo].[OA_LeaseContract_Finance_Proce_Init] Script Date: 05/25/2017 15:35:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter Proc [dbo].[OA_LeaseContract_Finance_Proce_Init]
@LTID int,
@LogName varchar(30)
-- @run_flag int
as
Begin
-- IF EXISTS (SELECT * FROM dbo.OA_LeaseContract_Finance WHERE @LTID=LTID )
DECLARE @run_flag int
--set @run_flag=0
IF EXISTS(Select 1 From OA_LeaseContract_ExMarked where dbo.FormatDate(run_date)=dbo.FormatDate(getdate()) and run_flag=1)
SET @run_flag=1
IF @run_flag=0 --今日未执行
-- Begin
declare @d1 datetime,@d2 datetime,@Term int,@PriceMonth numeric(18,2),@d3 int,@sysFinanceNumber bigint
-- ALTER TABLE [dbo].[OA_LeaseContract_Finance] drop [DF_OA_LeaseContract_Finance_LTID]
select @sysFinanceNumber=a.Sys_FinanceNumber
,@d1=a.BeginDate,@d2=a.EndDate
,@Term=a.Pay_Cyc
,@d3=a.Ahead_Date
,@PriceMonth=a.Price
from OA_LeaseContract a
where LTID=@LTID;
declare @TB table
(
[id] [int] IDENTITY(1,1) NOT NULL,
HF_Date datetime,
BeginDate datetime,
EndDate datetime,
Pay_Price numeric(18,2),
Pay_Price2 numeric(18,2),
Get_Price2 numeric(18,2),
updatee datetime
)
declare @dtmp datetime
set @dtmp=@d1
while @dtmp<=@d2
Begin
insert into @TB(BeginDate,EndDate,HF_Date,updatee)
values(@dtmp,dateadd(d,-1,DATEADD(m,@Term,@dtmp)),@dtmp-@d3,GETDATE())
set @dtmp=dateadd(m,@Term,@dtmp)
End
--update @TB set updatee = CONVERT(varchar(10), GETDATE(), 23)
update @TB set Pay_Price=@PriceMonth*@Term
update @TB set EndDate=@d2 where EndDate>@d2
update @TB set Pay_Price2 = -@PriceMonth*2 where EndDate>@d2 or EndDate=@d2
update @TB set Pay_Price2 = @PriceMonth*2 where id = 1
update @TB set Pay_Price2 = 0 where BeginDate>@d1 and EndDate<@d2
--update @TB set HF_Date = null where id = 1
update @TB set Get_Price2 = -Pay_Price2
insert into OA_LeaseContract_Finance(Sys_FinanceNumber,LTID,HF_Date,BeginDate,EndDate
,Pay_Price,Pay_Price2,Get_Price2
,CreateUser,updatetime)
select @sysFinanceNumber,Ltid=@LTID,HF_Date,BeginDate,EndDate
,Pay_Price,Pay_Price2,Get_Price2,@LogName,updatee
from @TB a
order by a.id
Insert into OA_LeaseContract_ExMarked values (1,GETDATE())
update OA_LeaseContract_Finance set Datelines = GETDATE()
update OA_LeaseContract_ExMarked set run_date=CONVERT(varchar(10), GETDATE(), 23)
End
-- End
--select top 1 * from @TB order by BeginDate desc
GO
exec OA_LeaseContract_Finance_Proce_Init 100001,'asd'
select * from OA_LeaseContract_Finance
还是不对啊,还是重复执行,那个表我也创建了
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FormatDate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FormatDate]
GO
CREATE FUNCTION [dbo].[FormatDate](@CurDate datetime)
RETURNS datetime
AS
BEGIN
return cast(year(@CurDate) as varchar(4))+'-'+cast(month(@CurDate) as varchar(2))+'-'+cast(day(@CurDate) as varchar(2))
END
GO
[/quote]
判断是否运行的语句日期加上函数 FormatDate 如下
IF EXISTS(Select 1 From cRunFlagTbl where dbo.FormatDate(run_date)=dbo.FormatDate(getdate()) and run_flag=1)
SET @run_flag=1
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FormatDate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FormatDate]
GO
CREATE FUNCTION [dbo].[FormatDate](@CurDate datetime)
RETURNS datetime
AS
BEGIN
return cast(year(@CurDate) as varchar(4))+'-'+cast(month(@CurDate) as varchar(2))+'-'+cast(day(@CurDate) as varchar(2))
END
GO
IF EXISTS(Select 1 From OA_LeaseContract_ExMark where run_date=getdate() and run_flag=1)
-- 这句写得有问题, getdate 函数取出来的值是带时间的,你要想比对当天的数据要把后面的时间去掉,可以试试下面的语句
IF EXISTS(Select 1 From OA_LeaseContract_ExMark where datediff(day,run_date,getdate()) = 0 and run_flag=1)
USE [CUService]
GO
Alter Proc [dbo].[OA_LeaseContract_Finance_Proce_Init]
@LTID int,
@LogName varchar(30)
-- @run_flag int
as
Begin
-- IF EXISTS (SELECT * FROM dbo.OA_LeaseContract_Finance WHERE @LTID=LTID )
DECLARE @run_flag int
set @run_flag=0
IF EXISTS(Select 1 From OA_LeaseContract_ExMarked where dbo.FormatDate(run_date)=dbo.FormatDate(getdate()) and run_flag=1)
SET @run_flag=1
IF @run_flag=0 --今日未执行
Begin
declare @d1 datetime,@d2 datetime,@Term int,@PriceMonth numeric(18,2),@d3 int,@sysFinanceNumber bigint
-- ALTER TABLE [dbo].[OA_LeaseContract_Finance] drop [DF_OA_LeaseContract_Finance_LTID]
select @sysFinanceNumber=a.Sys_FinanceNumber
,@d1=a.BeginDate,@d2=a.EndDate
,@Term=a.Pay_Cyc
,@d3=a.Ahead_Date
,@PriceMonth=a.Price
from OA_LeaseContract a
where LTID=@LTID;
declare @TB table
(
[id] [int] IDENTITY(1,1) NOT NULL,
HF_Date datetime,
BeginDate datetime,
EndDate datetime,
Pay_Price numeric(18,2),
Pay_Price2 numeric(18,2),
Get_Price2 numeric(18,2),
updatee datetime
)
declare @dtmp datetime
set @dtmp=@d1
while @dtmp<=@d2
Begin
insert into @TB(BeginDate,EndDate,HF_Date,updatee)
values(@dtmp,dateadd(d,-1,DATEADD(m,@Term,@dtmp)),@dtmp-@d3,GETDATE())
set @dtmp=dateadd(m,@Term,@dtmp)
End
--update @TB set updatee = CONVERT(varchar(10), GETDATE(), 23)
update @TB set Pay_Price=@PriceMonth*@Term
update @TB set EndDate=@d2 where EndDate>@d2
update @TB set Pay_Price2 = -@PriceMonth*2 where EndDate>@d2 or EndDate=@d2
update @TB set Pay_Price2 = @PriceMonth*2 where id = 1
update @TB set Pay_Price2 = 0 where BeginDate>@d1 and EndDate<@d2
--update @TB set HF_Date = null where id = 1
update @TB set Get_Price2 = -Pay_Price2
insert into OA_LeaseContract_Finance(Sys_FinanceNumber,LTID,HF_Date,BeginDate,EndDate
,Pay_Price,Pay_Price2,Get_Price2
,CreateUser,updatetime)
select @sysFinanceNumber,Ltid=@LTID,HF_Date,BeginDate,EndDate
,Pay_Price,Pay_Price2,Get_Price2,@LogName,updatee
from @TB a
order by a.id
Insert into OA_LeaseContract_ExMarked values (1,GETDATE())
update OA_LeaseContract_Finance set Datelines = GETDATE()
update OA_LeaseContract_ExMarked set run_date=CONVERT(varchar(10), GETDATE(), 23)
End
End
GO