22,300
社区成员




create proc j
@年月 datetime,
@乡镇编码 varchar(100)
as
create table #777 (AdministrativeName varchar(100),ClientNO varchar(100) ,AdministrativeNO varchar(100) ,Address varchar(100),SubcategoryNO varchar(100),MeterBookNO varchar(100),MeterCaliberID int, PriorCopy numeric ,CurrentCopy numeric , CurrentDay datetime ,PriorDay datetime ,CustomStateNO varchar(100),BuildDay datetime ,Name varchar(100) );
insert into #777 (BuildDay) select BuildDay from Client where Client.BuildDay=@年月
update #777 set ClientNO=Client.ClientNO,AdministrativeNO=Client.AdministrativeNO ,Address=Client.Address ,SubcategoryNO=Client.SubCategoryNO,MeterBookNO=Client.MeterBookNO,PriorCopy=Client.PriorCopy,CurrentCopy=Client.CurrentCopy,CurrentDay=Client.CurrentDay,PriorDay=Client.PriorDay,CustomStateNO=Client.CustomStateNO,Name=Client.Name from #777, Client where #777.BuildDay=Client.BuildDay
update #777 set AdministrativeName = b.Name
from #777 a ,Administrative b
where a.AdministrativeNO = b.NO
update #777 set MeterCaliberID = MeterCaliber.Caliber
from #777 right join Client on #777.ClientNO=Client.ClientNO right join Meter on Client.SealNO=Meter.SealNO right join MeterCaliber on Meter.MeterCaliberID=MeterCaliber.MeterCaliberID
if @乡镇编码 is null
select * from #777
else
select * from #777 where AdministrativeName=@乡镇编码
go
create proc j
@年月 datetime,
@乡镇编码 varchar(100) = null
as
begin
select
a.BuildDay,
a.ClientNO,
b.Name as AdministrativeNO,
a.[Address],
a.SubcategoryNO,
a.MeterBookNO,
d.Caliber as MeterCaliberID,
a.PriorCopy,
a.CurrentCopy,
a.CurrentDay,
a.PriorDay,
a.CustomStateNO,
a.Name
from Client a
join Administrative b on a.AdministrativeNO = b.[NO]
join Meter c on a.SealNO=c.SealNO
join MeterCaliber d on c.MeterCaliberID=d.MeterCaliberID
where
DatePart(year,a.BuildDay)= DatePart(year,@年月)
AND DatePart(MONTH,a.BuildDay)= DatePart(MONTH,@年月)
AND((@乡镇编码 is null) or (b.Name=@乡镇编码))
end
exec j '2011-08-08', nullCREATE PROC J
@年月 datetime,
@乡镇编码 varchar(100)
AS
BEGIN
create table #777 (/* 这个不变 */);
DECLARE @dt1 datetime
DECLARE @dt2 datetime
DECLARE @sql varchar(max)
-- 求出本月初和下月初
SET @dt1 = CONVERT(datetime,
CONVERT(varchar(7),@年月,120)+'-01',
120)
SET @dt1 = DATEADD(month,1,@dt2)
-- 借用#3的代码做成动态SQL
SET @sql = 'INSERT INTO #777
SELECT b.name AS AdministrativeName,
a.clientno,
a.administrativeno,
a.[address],
a.subcategoryno,
a.meterbookno,
d.caliber AS metercaliberid,
a.priorcopy,
a.currentcopy,
a.currentday,
a.priorday,
a.customstateno,
a.buildday,
a.[name]
FROM client a
JOIN administrative b
ON a.administrativeno = b.[no]
JOIN meter c
ON a.sealno = c.sealno
JOIN metercaliber d
ON c.metercaliberid = d.metercaliberid
WHERE a.buildday >= '''+CONVERT(varchar(10),dt1,120)+'''
AND a.buildday < '''+CONVERT(varchar(10),dt2,120)+''''
IF @乡镇编码 IS NOT NULL
SET @sql = @sql+'
AND b.name = '''+@乡镇编码+''''
EXEC(@sql)
SELECT * FROM #777
END
GO
select
a.BuildDay,
a.ClientNO,
b.Name as AdministrativeNO,
a.[Address],
a.SubcategoryNO,
a.MeterBookNO,
d.Caliber as MeterCaliberID,
a.PriorCopy,
a.CurrentCopy,
a.CurrentDay,
a.PriorDay,
a.CustomStateNO,
a.Name
from Client a
join Administrative b on a.AdministrativeNO = b.[NO]
join Meter c on a.SealNO=c.SealNO
join MeterCaliber d on c.MeterCaliberID=d.MeterCaliberID
where a.BuildDay='2011-08-08 00:00:00.000'
create proc j
@年月 datetime,
@乡镇编码 varchar(100)
as
begin
select
a.BuildDay,
a.ClientNO,
b.Name as AdministrativeNO,
a.[Address],
a.SubcategoryNO,
a.MeterBookNO,
d.Caliber as MeterCaliberID,
a.PriorCopy,
a.CurrentCopy,
a.CurrentDay,
a.PriorDay,
a.CustomStateNO,
a.Name
into #777
from Client a
join Administrative b on a.AdministrativeNO = b.[NO]
join Meter c on a.SealNO=c.SealNO
join MeterCaliber d on c.MeterCaliberID=d.MeterCaliberID
where a.BuildDay=@年月
if @乡镇编码 is null
select * from #777
else
select * from #777 where AdministrativeName=@乡镇编码
end
go