sqlservice存储过程

--create tms_day_report
CREATE TABLE tmsdb_status(
id int identity(1,1) primary key not null,
status char(1) NULL ,
name varchar(50) NULL 
);
INSERT INTO tmsdb_status (status, name) VALUES ( 0, 'dbing');
--create tms_day_report
CREATE TABLE tms_day_report (
id int identity(1,1) primary key not null,
parkid int NULL ,
parkname nvarchar(50) NULL ,
poll nvarchar(50) NULL ,
freight nvarchar(50) NULL ,
membernum nvarchar(50) NULL ,
membercount nvarchar(50) NULL ,
create_time date NULL ,
parkcreatedate date NULL 
);
Create PROCEDURE P_InsertSubject3
as
declare @en_dt datetime
select @en_dt = CONVERT(varchar(10) ,getdate(),120) 
declare @dt datetime
select @dt = '2015-01-01'
declare @en datetime
select @en=DATEADD(DAY,-1,@en_dt)
update tmsdb_status  set status='1' where name='dbing'
while  @dt < @en_dt
begin
if EXISTS(select * from tms_day_report where create_time=@dt)
begin
with hisvipnum as(
  select cp.parkid,count(cc.companyid) hisnum from Common_Park cp
         LEFT JOIN common_company cc  on cp.parkid=cc.parkid 
          where cc.createdate<=@dt
         GROUP BY cp.parkid
),
  nowvipnum as(
  select cp.parkid,count(cc.companyid) nosnum from Common_Park cp
         LEFT JOIN common_company cc  on cp.parkid=cc.parkid 
          where cc.createdate=@dt
         GROUP BY cp.parkid
),
wbnum as(
select  cp.parkid,count(bi.invoiceid) wbnum,sum(bi.TotalCarriage) wbpris from Common_Park cp 
        LEFT JOIN Common_Company cc on cp.ParkId=cc.ParkId 
        LEFT JOIN Common_Stores cs on cs.companyid=cc.companyid
        LEFT JOIN broker_invoice bi on bi.storeid=cs.storeid
        where   CONVERT(varchar(10) ,bi.createdate,120)=@dt
        and cc.createdate > dateadd(month,-3,@dt)--前三个月
        and (bi.IsCancellation is null or bi.IsCancellation=0)
        group by cp.parkid
),
allinfo as(
        select cp.parkid,cp.parkname,ISNULL(wb.wbnum, '0') poll,ISNULL(wb.wbpris, '0.00') freight,
         ISNULL(hp.hisnum, '0') membernum,ISNULL(np.nosnum, '0') membercount,@dt create_time
         from Common_Park cp LEFT JOIN wbnum wb  on cp.parkid=wb.parkid
                             LEFT JOIN hisvipnum hp on cp.parkid=hp.parkid
                             LEFT JOIN nowvipnum np on cp.parkid=np.parkid
) 
UPDATE
    tdr
SET
    tdr.poll =ai.poll,
    tdr.freight =ai.freight,
    tdr.membernum =ai.membernum,
    tdr.membercount =ai.membercount
FROM
    tms_day_report tdr
INNER JOIN allinfo ai ON ai.parkid = tdr.parkid and tdr.create_time=@dt;


--插入有新增的
 with hisvipnum as(
  select cp.parkid,count(cc.companyid) hisnum from Common_Park cp
         LEFT JOIN common_company cc  on cp.parkid=cc.parkid 
          where cc.createdate<=@dt
         GROUP BY cp.parkid
),
  nowvipnum as(
  select cp.parkid,count(cc.companyid) nosnum from Common_Park cp
         LEFT JOIN common_company cc  on cp.parkid=cc.parkid 
          where cc.createdate=@dt
         GROUP BY cp.parkid
),
wbnum as(
select  cp.parkid,count(bi.invoiceid) wbnum,sum(bi.TotalCarriage) wbpris from Common_Park cp 
        LEFT JOIN Common_Company cc on cp.ParkId=cc.ParkId 
        LEFT JOIN Common_Stores cs on cs.companyid=cc.companyid
        LEFT JOIN broker_invoice bi on bi.storeid=cs.storeid
        where   CONVERT(varchar(10) ,bi.createdate,120)=@dt
        and cc.createdate > dateadd(month,-3,@dt)--前三个月
        and (bi.IsCancellation is null or bi.IsCancellation=0)
        group by cp.parkid
) 
insert into tms_day_report(parkid,parkname,poll,freight,membernum,membercount,create_time,parkcreatedate)   
    select cp.parkid,cp.parkname,ISNULL(wb.wbnum, '0') poll,ISNULL(wb.wbpris, '0.00') freight,
    ISNULL(hp.hisnum, '0') membernum,ISNULL(np.nosnum, '0') membercount,@dt create_time,cp.createdate
    from Common_Park cp LEFT JOIN wbnum wb  on cp.parkid=wb.parkid
                        LEFT JOIN hisvipnum hp on cp.parkid=hp.parkid
                        LEFT JOIN nowvipnum np on cp.parkid=np.parkid
                        where  cp.ParkId not in (select ParkId from tms_day_report where create_time='2015-06-19')
end
else
begin
 with hisvipnum as(
  select cp.parkid,count(cc.companyid) hisnum from Common_Park cp
         LEFT JOIN common_company cc  on cp.parkid=cc.parkid 
          where cc.createdate<=@dt
         GROUP BY cp.parkid
),
  nowvipnum as(
  select cp.parkid,count(cc.companyid) nosnum from Common_Park cp
         LEFT JOIN common_company cc  on cp.parkid=cc.parkid 
          where cc.createdate=@dt
         GROUP BY cp.parkid
),
wbnum as(
select  cp.parkid,count(bi.invoiceid) wbnum,sum(bi.TotalCarriage) wbpris from Common_Park cp 
        LEFT JOIN Common_Company cc on cp.ParkId=cc.ParkId 
        LEFT JOIN Common_Stores cs on cs.companyid=cc.companyid
        LEFT JOIN broker_invoice bi on bi.storeid=cs.storeid
        where   CONVERT(varchar(10) ,bi.createdate,120)=@dt
        and cc.createdate > dateadd(month,-3,@dt)--前三个月
        and (bi.IsCancellation is null or bi.IsCancellation=0)
        group by cp.parkid
) 
insert into tms_day_report(parkid,parkname,poll,freight,membernum,membercount,create_time,parkcreatedate)   
    select cp.parkid,cp.parkname,ISNULL(wb.wbnum, '0') poll,ISNULL(wb.wbpris, '0.00') freight,
    ISNULL(hp.hisnum, '0') membernum,ISNULL(np.nosnum, '0') membercount,@dt create_time,cp.createdate
    from Common_Park cp LEFT JOIN wbnum wb  on cp.parkid=wb.parkid
                        LEFT JOIN hisvipnum hp on cp.parkid=hp.parkid
                        LEFT JOIN nowvipnum np on cp.parkid=np.parkid;
end
select @dt = DATEADD(DAY,1,@dt)
end
update tmsdb_status set status='0' where name='dbing';
go