我在写一个存储过程(如下),要求功能按月实现数据插入,只插入现在的2月和3月的数据且数据是同一个月的话,比如“20070101”,“20070102”...“20070131”的话,所有的都只用一个日期,就是这个月的最后一天“20070131”。可是我的存储过程执行的只是把每条数据在这个月的每一天都要显示。
怎么修改这个存储过程?
CREATE PROCEDURE p_send_data/*送数据*/
AS
declare @cur_date varchar(8)
declare @last_date varchar(8)
declare @fin_date varchar(8)
declare @li_count int
select @fin_date=isnull(max(statdate),convert(char(10),getdate() -1,112)) from bidata.dbo.pserv
select @cur_date=convert(char(10),getdate() -1,112)
--print '1.fin_date:'+@fin_date
--print '2.cur_date:'+@cur_date
while @fin_date <@cur_date
begin
set @last_date=@fin_date
print '2.cur_date:'+@cur_date
set @fin_date =convert(char(8),cast(@fin_date as datetime) +1,112)
--print '2.cur_date:'+@cur_date
begin
print '1.fin_date:'+@fin_date
select @li_count=count(*) from boss0930.dbo.pcust where statdate=@fin_date
if @li_count=0
begin
insert into boss0930.dbo.pCust
( CustID,AreaID,LtanID,CustName,CustAddr,PersonContact,
Phone,CustTypeID,CustomTypeID,ChannelID,CustStateID,CreateDate,CreateStaff,InvalidDate,statdate )
select CONVERT(VARCHAR(20) , a.cnnCustID),NULL,a.cnvcAreaCode,a.cnvcCustName ,a.cnvcLinkAddress
a.cnvcLinkName,a.cnvcLinkPhone,isnull(b.cnvcUserKind,0),a.cnvcCustType,b.cnvcDeptID,NULL,
NULL,NULL,b.cndStopDate,@fin_date
from Boss0930.dbo.vwISCust a ,Boss0930.dbo.vwISUser b
where a.cnnCustID = b.cnnCustID
end
select @li_count=count(*) from bidata.dbo.pacct where statdate=@fin_date
if @li_count=0
begin
insert into bidata.dbo.pAcct
( AcctID,AreaID,LtanID,CustId,CardNbr,AcctName,AcctAddr,
PaymentMethos,ComboID,CreateDate,CreateStaff,statdate )
select CONVERT(VARCHAR(20) , a.cnnAcctID),NULL,a.cnvcAreaCode,CONVERT(VARCHAR(20) , a.cnnCustID),
NULL,a.cnvcAcctName,a.cnvcAcctAddress,a.cnvcPayType,b.cnvcDinnerID,NUll,NULL,@fin_date
from FROM Boss0930.dbo.vwISAcct a,FROM Boss0930.dbo.vwISUser b
where a.cnnCustID = b.cnnCustID
end
insert into bidata.dbo.pAcctItemCycle
(BillingCycleID,AcctItemID,ServID,LtanID,AcctID,ProductTypeID,ComboID,AcctItemID,CardNbr,Duration,
Charge1,Charge2,Charge,CreateDate,CreateStaff,callcount,CustID,ChannelID,ServiceTypeID)
select cnvcAcctMonth,NULL, CONVERT(VARCHAR(10) , a.cnnUserId),a.cnvcAreaCod, CONVERT(VARCHAR(10) , a.cnnAcctId),
ISNULL(b.cnvcUserKind,0),a.cnvcDinnerId,a.cnvcFeeCode,NULL,CONVERT(NUMERIC(19,0) , a.cnnBillDuration),
CONVERT(MONEY , a.cnnStandFee),CONVERT(MONEY , a.cnnFavFee),cnnFavFee-cnnAdjustFee-cnnDisctFee-cnnPromFee-cnnDerateFee,
NULL,NULL,CONVERT(VARCHAR(10) ,a.cnnMeter),b.cnnCustID,a.cnvcDeptID,a.cnvcSvcType
FROM Boss0930.dbo.vwISAcctItem a , Boss0930.dbo.vwISUser b
end
怎么修改这个存储过程?
CREATE PROCEDURE p_send_data/*送数据*/
AS
declare @cur_date varchar(8)
declare @last_date varchar(8)
declare @fin_date varchar(8)
declare @li_count int
select @fin_date=isnull(max(statdate),convert(char(10),getdate() -1,112)) from bidata.dbo.pserv
select @cur_date=convert(char(10),getdate() -1,112)
--print '1.fin_date:'+@fin_date
--print '2.cur_date:'+@cur_date
while @fin_date <@cur_date
begin
set @last_date=@fin_date
print '2.cur_date:'+@cur_date
set @fin_date =convert(char(8),cast(@fin_date as datetime) +1,112)
--print '2.cur_date:'+@cur_date
begin
print '1.fin_date:'+@fin_date
select @li_count=count(*) from boss0930.dbo.pcust where statdate=@fin_date
if @li_count=0
begin
insert into boss0930.dbo.pCust
( CustID,AreaID,LtanID,CustName,CustAddr,PersonContact,
Phone,CustTypeID,CustomTypeID,ChannelID,CustStateID,CreateDate,CreateStaff,InvalidDate,statdate )
select CONVERT(VARCHAR(20) , a.cnnCustID),NULL,a.cnvcAreaCode,a.cnvcCustName ,a.cnvcLinkAddress
a.cnvcLinkName,a.cnvcLinkPhone,isnull(b.cnvcUserKind,0),a.cnvcCustType,b.cnvcDeptID,NULL,
NULL,NULL,b.cndStopDate,@fin_date
from Boss0930.dbo.vwISCust a ,Boss0930.dbo.vwISUser b
where a.cnnCustID = b.cnnCustID
end
select @li_count=count(*) from bidata.dbo.pacct where statdate=@fin_date
if @li_count=0
begin
insert into bidata.dbo.pAcct
( AcctID,AreaID,LtanID,CustId,CardNbr,AcctName,AcctAddr,
PaymentMethos,ComboID,CreateDate,CreateStaff,statdate )
select CONVERT(VARCHAR(20) , a.cnnAcctID),NULL,a.cnvcAreaCode,CONVERT(VARCHAR(20) , a.cnnCustID),
NULL,a.cnvcAcctName,a.cnvcAcctAddress,a.cnvcPayType,b.cnvcDinnerID,NUll,NULL,@fin_date
from FROM Boss0930.dbo.vwISAcct a,FROM Boss0930.dbo.vwISUser b
where a.cnnCustID = b.cnnCustID
end
insert into bidata.dbo.pAcctItemCycle
(BillingCycleID,AcctItemID,ServID,LtanID,AcctID,ProductTypeID,ComboID,AcctItemID,CardNbr,Duration,
Charge1,Charge2,Charge,CreateDate,CreateStaff,callcount,CustID,ChannelID,ServiceTypeID)
select cnvcAcctMonth,NULL, CONVERT(VARCHAR(10) , a.cnnUserId),a.cnvcAreaCod, CONVERT(VARCHAR(10) , a.cnnAcctId),
ISNULL(b.cnvcUserKind,0),a.cnvcDinnerId,a.cnvcFeeCode,NULL,CONVERT(NUMERIC(19,0) , a.cnnBillDuration),
CONVERT(MONEY , a.cnnStandFee),CONVERT(MONEY , a.cnnFavFee),cnnFavFee-cnnAdjustFee-cnnDisctFee-cnnPromFee-cnnDerateFee,
NULL,NULL,CONVERT(VARCHAR(10) ,a.cnnMeter),b.cnnCustID,a.cnvcDeptID,a.cnvcSvcType
FROM Boss0930.dbo.vwISAcctItem a , Boss0930.dbo.vwISUser b
end
insert into tb(id) values('20070131')
insert into tb(id) values('20070101')
insert into tb(id) values('20070102')
insert into tb(id) values('20070103')
goupdate tb
set id = t.id1
from tb,(select left(id,6) , max(id) id1 from tb group by left(id,6)) t
where left(id,6) = t.left(id,6)
insert into tb(id) values('20070131')
insert into tb(id) values('20070101')
insert into tb(id) values('20070102')
insert into tb(id) values('20070103')
insert into tb(id) values('20070202')
insert into tb(id) values('20070228')
goupdate tb
set id = t.id1
from tb,(select left(id,6) id , max(id) id1 from tb group by left(id,6)) t
where left(tb.id,6) = left(t.id,6)select * from tbdrop table tbid
----------
20070131
20070131
20070131
20070131
20070228
20070228(所影响的行数为 6 行)
--------------------------------
declare @date datetime, @first datetime, end datetime
set @date='20070402'
--這月第一天
select @first=convert(char(06),@date,112)+'01'
--這月最後一天
select @end=dateadd(day,-1,convert(char(06),dateadd(month,1,@date),112)+'01')--刪除這月從第一天到最後一天(除外)的紀錄
delete T where [date] between [date]>=@first and [date]<@end
select dateadd(day,-1,convert(char(06),dateadd(month,1,'20070301'),112)+'01')
的时候,查询结果是:2007-03-31 00:00:00.000,我想要的是20070301
select dateadd(day,-1,convert(char(06),dateadd(month,1,'20070301'),112)+'01')
的时候,查询结果是:2007-03-31 00:00:00.000,我想要的是20070301select convert(varchar(8),dateadd(day,-1,convert(char(06),dateadd(month,1,'20070301'),112)+'01'),112) as dt/*
dt
--------
20070331(所影响的行数为 1 行)
*/