语句是这样的:
ALTER proc [dbo].[yuebao]@FYear int,
@fperiod intas
set nocount oncreate table #tb
( id int,
id1 int,
LX VARCHAR(255),
FEndBalance1 decimal(18,2),
FEndBalance2 decimal(18,2)
)
insert #tb(lx,id,id1,FEndBalance1)
SELECT '1.1成都片区项目',1,1,B.FAmountIncludeTaxFor FROM t_RPContract A
inner JOIN t_rpContractEntry B ON A.FContractID=B.FContractID
WHERE A.FBase='258'
AND DATENAME(YEAR,A.FDate)=@FYear
AND DATENAME(MONTH,A.FDate)=@fperiodinsert #tb(lx,id,id1,FEndBalance2)
SELECT '1.1成都片区项目',1,2,B.FAmountIncludeTaxFor FROM t_RPContract A
inner JOIN t_rpContractEntry B ON A.FContractID=B.FContractID
WHERE A.FBase='258'
AND A.FDate<=dateadd(day,-1,dateadd(month,1,cast(cast(@FYear as CHAR(4))+'-'+CAST(@fperiod as CHAR(2))+'-1' as DATE)))
ALTER proc [dbo].[yuebao]@FYear int,
@fperiod intas
set nocount oncreate table #tb
( id int,
id1 int,
LX VARCHAR(255),
FEndBalance1 decimal(18,2),
FEndBalance2 decimal(18,2)
)
insert #tb(lx,id,id1,FEndBalance1)
SELECT '1.1成都片区项目',1,1,B.FAmountIncludeTaxFor FROM t_RPContract A
inner JOIN t_rpContractEntry B ON A.FContractID=B.FContractID
WHERE A.FBase='258'
AND DATENAME(YEAR,A.FDate)=@FYear
AND DATENAME(MONTH,A.FDate)=@fperiodinsert #tb(lx,id,id1,FEndBalance2)
SELECT '1.1成都片区项目',1,2,B.FAmountIncludeTaxFor FROM t_RPContract A
inner JOIN t_rpContractEntry B ON A.FContractID=B.FContractID
WHERE A.FBase='258'
AND A.FDate<=dateadd(day,-1,dateadd(month,1,cast(cast(@FYear as CHAR(4))+'-'+CAST(@fperiod as CHAR(2))+'-1' as DATE)))
第一段
DATENAME(YEAR,A.FDate)=@FYear
DATENAME(MONTH,A.FDate)=@fperiod
或者是第二段
A.FDate<=dateadd(day,-1,dateadd(month,1,cast(cast(@FYear as CHAR(4))+'-'+CAST(@fperiod as CHAR(2))+'-1' as DATE)))其中的A.FDATE是datetime格式的 2016-01-04 00:00:00.000这种 上面这两种我不知道可以不可以这样用
参数@fperiod的范围应是1-12, 如果超出则将报错.