带参数的，存储过程，
直接报表调用方法：exec up_xsje1 '########'
'########' 是k3关键字，其他用法类似。
/*
功能描述： 查询本周，本月，本季度，本年的销售金额
*/
CREATE procedure up_xsje1
@mydate datetime
as
declare @thisWeek datetime,
@thisMonth datetime,
@thisQuarter datetime,
@thisYear datetime,
@nextWeek datetime,
@nextMonth datetime,
@nextQuarter datetime,
@nextYear datetime
if( DATEADD(wk, DATEDIFF(wk,0,@mydate), 0) < @mydate)
begin
select @thisWeek = DATEADD(wk, DATEDIFF(wk,0,@mydate), 0) --本周的第一天
end
else
begin
select @thisWeek = DATEADD(wk, DATEDIFF(wk,0,@mydate), 0) - 7 --本周的第一天
end
select @thisMonth = DATEADD(mm, DATEDIFF(mm,0,@mydate), 0) --本月的第一天
select @thisQuarter = DATEADD(qq, DATEDIFF(qq,0,@mydate), 0) --本季度的第一天
select @thisYear = DATEADD(yy, DATEDIFF(yy,0,@mydate), 0) --本年的第一天
if( DATEADD(wk, DATEDIFF(wk,0,@mydate), 0) < @mydate)
begin
select @nextWeek = DATEADD(wk, DATEDIFF(wk,0,(@mydate + 7)), 0) --下周的第一天
end
else
begin
select @nextWeek = DATEADD(wk, DATEDIFF(wk,0,@mydate), 0) --下周的第一天
end
select @nextMonth = DATEADD(mm, DATEDIFF(mm,0,(@mydate + 31)), 0) --下月的第一天
select @nextQuarter = DATEADD(qq, DATEDIFF(qq,0,(@mydate + 124)), 0) --下季度的第一天
select @nextYear = DATEADD(yy, DATEDIFF(yy,0,(@mydate + 366)), 0) --下年的第一天
select o.fnumber as '客户编码',
o.fname as '客户名称',
sum(cs.br) as '本日内',
sum(cs.bw) as '本周内',
sum(cs.bm) as '本月内',
sum(cs.bq) as '本季度内',
sum(cs.byy) as '本年内'
from(
select t.FSupplyID,
sum(e.FConsignAmount) as 'br',0 as 'bw', 0 as 'bm',0 as 'bq', 0 as 'byy'
from ICStockBill t inner join
ICStockBillEntry e on
e.FInterID = t.FInterID
where e.FEntrySelfB0155 = @mydate
group by t.FSupplyID
union all
select t.FSupplyID,
0,sum(e.FConsignAmount) as 'bw', 0 as 'bm',0 as 'bq', 0 as 'byy'
from ICStockBill t inner join
ICStockBillEntry e on
e.FInterID = t.FInterID
where e.FEntrySelfB0155 >= @thisWeek and
e.FEntrySelfB0155 < @nextWeek
group by t.FSupplyID
union all
select t.FSupplyID,
0,0,sum(e.FConsignAmount) as 'bm', 0 as 'bq', 0 as 'byy'
from ICStockBill t inner join
ICStockBillEntry e on
e.FInterID = t.FInterID
where e.FEntrySelfB0155 >= @thisMonth and
e.FEntrySelfB0155 < @nextMonth
group by t.FSupplyID
union all
select t.FSupplyID,
0,0,0,sum(e.FConsignAmount) as 'bq', 0 as 'byy'
from ICStockBill t inner join
ICStockBillEntry e on
e.FInterID = t.FInterID
where e.FEntrySelfB0155 >= @thisQuarter and
e.FEntrySelfB0155 < @nextQuarter
group by t.FSupplyID
union all
select t.FSupplyID,
0,0,0,0,sum(e.FConsignAmount) as 'byy'
from ICStockBill t inner join
ICStockBillEntry e on
e.FInterID = t.FInterID
where e.FEntrySelfB0155 >= @thisYear and
e.FEntrySelfB0155 < @nextYear
group by t.FSupplyID
) as cs
inner join
t_Organization o on
o.FItemID = cs.FSupplyID
group by o.fnumber,o.fname
having sum(cs.bw)<> 0 or
sum(cs.bm)<> 0 or
sum(cs.bq)<> 0 or
sum(cs.byy)<> 0
union all
select '9999',
'合计',
sum(cs.br) as '本日内',
sum(cs.bw) as '本周内',
sum(cs.bm) as '本月内',
sum(cs.bq) as '本季度内',
sum(cs.byy) as '本年内'
from(
select t.FSupplyID,
sum(e.FConsignAmount) as 'br',0 as 'bw', 0 as 'bm',0 as 'bq', 0 as 'byy'
from ICStockBill t inner join
ICStockBillEntry e on
e.FInterID = t.FInterID
where e.FEntrySelfB0155 = @mydate
group by t.FSupplyID
union all
select t.FSupplyID,
0,sum(e.FConsignAmount) as 'bw', 0 as 'bm',0 as 'bq', 0 as 'byy'
from ICStockBill t inner join
ICStockBillEntry e on
e.FInterID = t.FInterID
where e.FEntrySelfB0155 >= @thisWeek and
e.FEntrySelfB0155 < @nextWeek
group by t.FSupplyID
union all
select t.FSupplyID,
0,0,sum(e.FConsignAmount) as 'bm', 0 as 'bq', 0 as 'byy'
from ICStockBill t inner join
ICStockBillEntry e on
e.FInterID = t.FInterID
where e.FEntrySelfB0155 >= @thisMonth and
e.FEntrySelfB0155 < @nextMonth
group by t.FSupplyID
union all
select t.FSupplyID,
0,0,0,sum(e.FConsignAmount) as 'bq', 0 as 'byy'
from ICStockBill t inner join
ICStockBillEntry e on
e.FInterID = t.FInterID
where e.FEntrySelfB0155 >= @thisQuarter and
e.FEntrySelfB0155 < @nextQuarter
group by t.FSupplyID
union all
select t.FSupplyID,
0,0,0,0,sum(e.FConsignAmount) as 'byy'
from ICStockBill t inner join
ICStockBillEntry e on
e.FInterID = t.FInterID
where e.FEntrySelfB0155 >= @thisYear and
e.FEntrySelfB0155 < @nextYear
group by t.FSupplyID
) as cs
inner join
t_Organization o on
o.FItemID = cs.FSupplyID
having sum(cs.bw)<> 0 or
sum(cs.bm)<> 0 or
sum(cs.bq)<> 0 or
sum(cs.byy)<> 0
order by fnumber
GO
[ 本帖最后由 tyczp 于 2008-5-19 16:41 编辑 ] |
发表于 2008-5-19 16:19:15
楼主
