表数据如下: H_sendDate H_CargoType H_IsAtoE H_BWeight H_Amount
2009-11-16 00:00:00.000| 1 0 120.00 1440.00
2009-11-16 00:00:00.000| 1 0 700.00 833.00
2009-11-17 00:00:00.000| 1 0 52.00 847.00
2009-11-17 00:00:00.000| 1 0 2580.00 6687.00
2009-11-18 00:00:00.000| 1 0 60.00 132.00
2009-11-18 00:00:00.000| 1 0 50.00 2092.00
2009-11-10 00:00:00.000| 1 0 50.00 2092.00
2009-11-09 00:00:00.000| 1 0 50.00 75.00
2009-11-10 00:00:00.000| 1 0 60.00 78.00
2009-11-11 00:00:00.000| 1 0 60.00 132.00
2009-11-12 00:00:00.000| 1 0 50.00 165.00
2009-11-08 00:00:00.000| 1 0 1509.00 1810.80
2009-11-05 00:00:00.000| 1 0 45.00 20385.00
2009-09-01 00:00:00.000| 1 0 100.00 381.00
2009-11-04 00:00:00.000| 1 0 157.00 25977.00
2009-11-03 00:00:00.000| 1 0 34.00 516.00
2009-11-02 00:00:00.000| 1 0 1.00 0.00
2009-11-01 00:00:00.000| 1 0 157.00 1658.00
2009-11-13 00:00:00.000| 1 0 156.00 305.20
2009-08-01 00:00:00.000| 1 0 12.00 17.00
2009-08-01 00:00:00.000| 1 0 12.00 18.00
2009-08-01 00:00:00.000| 1 0 12.00 0.00
2009-08-01 00:00:00.000| 1 0 14.00 8.00
2009-11-20 00:00:00.000| 1 0 65.00 306.50
2009-11-20 00:00:00.000| 1 0 30.00 203.00
2009-11-20 00:00:00.000| 1 0 122.00 146.40
2009-11-20 00:00:00.000| 2 0 500.00 1002.00
2009-11-20 00:00:00.000| 1 0 100.00 307.00
2009-11-20 00:00:00.000| 1 0 100.00 307.00
2009-11-20 00:00:00.000| 1 0 200.00 632.00
2009-11-20 00:00:00.000| 1 0 50.00 150.00
2009-11-20 00:00:00.000| 1 0 30.00 42.00
2009-08-01 00:00:00.000| 1 0 32.00 454718.00
2009-11-17 00:00:00.000| 2 0 12.00 0.00
2009-11-17 00:00:00.000| 2 1 157.00 241.00
2009-11-17 00:00:00.000| 2 1 1212.00 12.00
2009-11-16 00:00:00.000| 2 1 1212.00 12.00
2009-11-16 00:00:00.000| 2 1 1212.00 12.00
2009-11-16 00:00:00.000| 2 1 1212.00 12.00
2009-11-10 00:00:00.000| 2 1 12.00 1440.00
2009-11-10 00:00:00.000| 2 0 12.00 144.00
2009-11-10 00:00:00.000| 2 0 12.00 12.00
2009-11-11 00:00:00.000| 2 0 1212.00 14544.00
2009-07-01 00:00:00.000| 2 0 1212.00 1212.00
2009-06-01 00:00:00.000| 2 0 3.00 0.00
2009-06-01 00:00:00.000| 2 0 4.00 0.00
2009-06-01 00:00:00.000| 2 0 4.00 0.00
2009-06-01 00:00:00.000| 2 0 2.00 0.00
2009-06-01 00:00:00.000| 2 0 3.00 0.00
2009-06-01 00:00:00.000| 2 0 3.00 0.00
2009-05-01 00:00:00.000| 1 0 80.00 171.00
2009-05-01 00:00:00.000| 1 0 700.00 1404.00
2009-05-01 00:00:00.000| 1 0 25.00 112.00
2009-05-01 00:00:00.000| 1 0 55.00 1029.50
2009-05-01 00:00:00.000| 1 0 500.00 10200.00
2009-05-01 00:00:00.000| 1 0 700.00 1600.00
2009-05-01 00:00:00.000| 1 0 687.00 1265.50
2009-04-01 00:00:00.000| 1 1 4545.00 18928.00
2009-04-01 00:00:00.000| 1 1 3434.00 10302.00
2009-04-01 00:00:00.000| 1 0 2000.00 4006.00
2009-04-01 00:00:00.000| 1 0 46.00 101.20
2009-11-19 00:00:00.000| 1 1 200.00 630.00
2009-11-20 00:00:00.000| 1 1 200.00 842.00
2009-10-20 00:00:00.000| 1 0 80.00 120.00
2009-11-20 00:00:00.000| 1 0 118.00 190.00
2009-11-20 00:00:00.000| 1 0 100.00 230.00
2009-11-20 00:00:00.000| 2 0 500.00 1248.00
2009-11-20 00:00:00.000| 1 1 3.00 1694.00
需求:搜索条件:时间区间(月/季度)。
搜索的数据:该区间的鲜货货量、普货货量、空转陆货量、销售总额;上一区间的鲜货货量、普货货量、空转陆货量、销售总额;去年同一区间的鲜货货量、普货货量、空转陆货量、销售总额;
计算得出的数据:鲜货货量的同比环比;普货货量的同比环比;空转陆货量的同比环比;销售总额的同比环比。字段说明:H_sendDate-检索时间字段,H_CargoType-货物类型,1为普货,2为鲜货
H_IsAtoE-是否为空转路,0为否,1为是
H_BWeight 货量 H_Amount 金额
根据输入的两个时间参数调用存储过程,查询出上述要求的结果。各位大哥们帮帮忙 急用。
2009-11-16 00:00:00.000| 1 0 120.00 1440.00
2009-11-16 00:00:00.000| 1 0 700.00 833.00
2009-11-17 00:00:00.000| 1 0 52.00 847.00
2009-11-17 00:00:00.000| 1 0 2580.00 6687.00
2009-11-18 00:00:00.000| 1 0 60.00 132.00
2009-11-18 00:00:00.000| 1 0 50.00 2092.00
2009-11-10 00:00:00.000| 1 0 50.00 2092.00
2009-11-09 00:00:00.000| 1 0 50.00 75.00
2009-11-10 00:00:00.000| 1 0 60.00 78.00
2009-11-11 00:00:00.000| 1 0 60.00 132.00
2009-11-12 00:00:00.000| 1 0 50.00 165.00
2009-11-08 00:00:00.000| 1 0 1509.00 1810.80
2009-11-05 00:00:00.000| 1 0 45.00 20385.00
2009-09-01 00:00:00.000| 1 0 100.00 381.00
2009-11-04 00:00:00.000| 1 0 157.00 25977.00
2009-11-03 00:00:00.000| 1 0 34.00 516.00
2009-11-02 00:00:00.000| 1 0 1.00 0.00
2009-11-01 00:00:00.000| 1 0 157.00 1658.00
2009-11-13 00:00:00.000| 1 0 156.00 305.20
2009-08-01 00:00:00.000| 1 0 12.00 17.00
2009-08-01 00:00:00.000| 1 0 12.00 18.00
2009-08-01 00:00:00.000| 1 0 12.00 0.00
2009-08-01 00:00:00.000| 1 0 14.00 8.00
2009-11-20 00:00:00.000| 1 0 65.00 306.50
2009-11-20 00:00:00.000| 1 0 30.00 203.00
2009-11-20 00:00:00.000| 1 0 122.00 146.40
2009-11-20 00:00:00.000| 2 0 500.00 1002.00
2009-11-20 00:00:00.000| 1 0 100.00 307.00
2009-11-20 00:00:00.000| 1 0 100.00 307.00
2009-11-20 00:00:00.000| 1 0 200.00 632.00
2009-11-20 00:00:00.000| 1 0 50.00 150.00
2009-11-20 00:00:00.000| 1 0 30.00 42.00
2009-08-01 00:00:00.000| 1 0 32.00 454718.00
2009-11-17 00:00:00.000| 2 0 12.00 0.00
2009-11-17 00:00:00.000| 2 1 157.00 241.00
2009-11-17 00:00:00.000| 2 1 1212.00 12.00
2009-11-16 00:00:00.000| 2 1 1212.00 12.00
2009-11-16 00:00:00.000| 2 1 1212.00 12.00
2009-11-16 00:00:00.000| 2 1 1212.00 12.00
2009-11-10 00:00:00.000| 2 1 12.00 1440.00
2009-11-10 00:00:00.000| 2 0 12.00 144.00
2009-11-10 00:00:00.000| 2 0 12.00 12.00
2009-11-11 00:00:00.000| 2 0 1212.00 14544.00
2009-07-01 00:00:00.000| 2 0 1212.00 1212.00
2009-06-01 00:00:00.000| 2 0 3.00 0.00
2009-06-01 00:00:00.000| 2 0 4.00 0.00
2009-06-01 00:00:00.000| 2 0 4.00 0.00
2009-06-01 00:00:00.000| 2 0 2.00 0.00
2009-06-01 00:00:00.000| 2 0 3.00 0.00
2009-06-01 00:00:00.000| 2 0 3.00 0.00
2009-05-01 00:00:00.000| 1 0 80.00 171.00
2009-05-01 00:00:00.000| 1 0 700.00 1404.00
2009-05-01 00:00:00.000| 1 0 25.00 112.00
2009-05-01 00:00:00.000| 1 0 55.00 1029.50
2009-05-01 00:00:00.000| 1 0 500.00 10200.00
2009-05-01 00:00:00.000| 1 0 700.00 1600.00
2009-05-01 00:00:00.000| 1 0 687.00 1265.50
2009-04-01 00:00:00.000| 1 1 4545.00 18928.00
2009-04-01 00:00:00.000| 1 1 3434.00 10302.00
2009-04-01 00:00:00.000| 1 0 2000.00 4006.00
2009-04-01 00:00:00.000| 1 0 46.00 101.20
2009-11-19 00:00:00.000| 1 1 200.00 630.00
2009-11-20 00:00:00.000| 1 1 200.00 842.00
2009-10-20 00:00:00.000| 1 0 80.00 120.00
2009-11-20 00:00:00.000| 1 0 118.00 190.00
2009-11-20 00:00:00.000| 1 0 100.00 230.00
2009-11-20 00:00:00.000| 2 0 500.00 1248.00
2009-11-20 00:00:00.000| 1 1 3.00 1694.00
需求:搜索条件:时间区间(月/季度)。
搜索的数据:该区间的鲜货货量、普货货量、空转陆货量、销售总额;上一区间的鲜货货量、普货货量、空转陆货量、销售总额;去年同一区间的鲜货货量、普货货量、空转陆货量、销售总额;
计算得出的数据:鲜货货量的同比环比;普货货量的同比环比;空转陆货量的同比环比;销售总额的同比环比。字段说明:H_sendDate-检索时间字段,H_CargoType-货物类型,1为普货,2为鲜货
H_IsAtoE-是否为空转路,0为否,1为是
H_BWeight 货量 H_Amount 金额
根据输入的两个时间参数调用存储过程,查询出上述要求的结果。各位大哥们帮帮忙 急用。
if object_id('tb') is not null drop table tb
go
create table tb (H_sendDate datetime,H_CargoType int,H_IsAtoE int,H_BWeight numeric(6,2),H_Amount numeric(8,2))
insert into tb
select '2009-11-16 00:00:00.000',1,0,120.00,1440.00 union all
select '2009-11-16 00:00:00.000',1,0,700.00,833.00 union all
select '2009-11-17 00:00:00.000',1,0,52.00,847.00 union all
select '2009-11-17 00:00:00.000',1,0,2580.00,6687.00 union all
select '2009-11-18 00:00:00.000',1,0,60.00,132.00 union all
select '2009-11-18 00:00:00.000',1,0,50.00,2092.00 union all
select '2009-11-10 00:00:00.000',1,0,50.00,2092.00 union all
select '2009-11-09 00:00:00.000',1,0,50.00,75.00 union all
select '2009-11-10 00:00:00.000',1,0,60.00,78.00 union all
select '2009-11-11 00:00:00.000',1,0,60.00,132.00 union all
select '2009-11-12 00:00:00.000',1,0,50.00,165.00 union all
select '2009-11-08 00:00:00.000',1,0,1509.00,1810.80 union all
select '2009-11-05 00:00:00.000',1,0,45.00,20385.00 union all
select '2009-09-01 00:00:00.000',1,0,100.00,381.00 union all
select '2009-11-04 00:00:00.000',1,0,157.00,25977.00 union all
select '2009-11-03 00:00:00.000',1,0,34.00,516.00 union all
select '2009-11-02 00:00:00.000',1,0,1.00,0.00 union all
select '2009-11-01 00:00:00.000',1,0,157.00,1658.00 union all
select '2009-11-13 00:00:00.000',1,0,156.00,305.20 union all
select '2009-08-01 00:00:00.000',1,0,12.00,17.00 union all
select '2009-08-01 00:00:00.000',1,0,12.00,18.00 union all
select '2009-08-01 00:00:00.000',1,0,12.00,0.00 union all
select '2009-08-01 00:00:00.000',1,0,14.00,8.00 union all
select '2009-11-20 00:00:00.000',1,0,65.00,306.50 union all
select '2009-11-20 00:00:00.000',1,0,30.00,203.00 union all
select '2009-11-20 00:00:00.000',1,0,122.00,146.40 union all
select '2009-11-20 00:00:00.000',2,0,500.00,1002.00 union all
select '2009-11-20 00:00:00.000',1,0,100.00,307.00 union all
select '2009-11-20 00:00:00.000',1,0,100.00,307.00 union all
select '2009-11-20 00:00:00.000',1,0,200.00,632.00 union all
select '2009-11-20 00:00:00.000',1,0,50.00,150.00 union all
select '2009-11-20 00:00:00.000',1,0,30.00,42.00 union all
select '2009-08-01 00:00:00.000',1,0,32.00,454718.00 union all
select '2009-11-17 00:00:00.000',2,0,12.00,0.00 union all
select '2009-11-17 00:00:00.000',2,1,157.00,241.00 union all
select '2009-11-17 00:00:00.000',2,1,1212.00,12.00 union all
select '2009-11-16 00:00:00.000',2,1,1212.00,12.00 union all
select '2009-11-16 00:00:00.000',2,1,1212.00,12.00 union all
select '2009-11-16 00:00:00.000',2,1,1212.00,12.00 union all
select '2009-11-10 00:00:00.000',2,1,12.00,1440.00 union all
select '2009-11-10 00:00:00.000',2,0,12.00,144.00 union all
select '2009-11-10 00:00:00.000',2,0,12.00,12.00 union all
select '2009-11-11 00:00:00.000',2,0,1212.00,14544.00 union all
select '2009-07-01 00:00:00.000',2,0,1212.00,1212.00 union all
select '2009-06-01 00:00:00.000',2,0,3.00,0.00 union all
select '2009-06-01 00:00:00.000',2,0,4.00,0.00 union all
select '2009-06-01 00:00:00.000',2,0,4.00,0.00 union all
select '2009-06-01 00:00:00.000',2,0,2.00,0.00 union all
select '2009-06-01 00:00:00.000',2,0,3.00,0.00 union all
select '2009-06-01 00:00:00.000',2,0,3.00,0.00 union all
select '2009-05-01 00:00:00.000',1,0,80.00,171.00 union all
select '2009-05-01 00:00:00.000',1,0,700.00,1404.00 union all
select '2009-05-01 00:00:00.000',1,0,25.00,112.00 union all
select '2009-05-01 00:00:00.000',1,0,55.00,1029.50 union all
select '2009-05-01 00:00:00.000',1,0,500.00,10200.00 union all
select '2009-05-01 00:00:00.000',1,0,700.00,1600.00 union all
select '2009-05-01 00:00:00.000',1,0,687.00,1265.50 union all
select '2009-04-01 00:00:00.000',1,1,4545.00,18928.00 union all
select '2009-04-01 00:00:00.000',1,1,3434.00,10302.00 union all
select '2009-04-01 00:00:00.000',1,0,2000.00,4006.00 union all
select '2009-04-01 00:00:00.000',1,0,46.00,101.20 union all
select '2009-11-19 00:00:00.000',1,1,200.00,630.00 union all
select '2009-11-20 00:00:00.000',1,1,200.00,842.00 union all
select '2009-10-20 00:00:00.000',1,0,80.00,120.00 union all
select '2009-11-20 00:00:00.000',1,0,118.00,190.00 union all
select '2009-11-20 00:00:00.000',1,0,100.00,230.00 union all
select '2009-11-20 00:00:00.000',2,0,500.00,1248.00 union all
select '2009-11-20 00:00:00.000',1,1,3.00,1694.00
----------------------------------------------------------------------->
if object_id('pp') is not null drop proc pp
go
create proc pp
@begin datetime,
@end datetime
as
--1--本区间
select
[鲜货货量]=sum(case when H_CargoType=2 then H_BWeight end),
[普货货量]=sum(case when H_CargoType=1 then H_BWeight end),
[空转陆货量]=sum(case when H_IsAtoE=1 then H_BWeight end),
[销售总额]=sum(H_Amount)
from tb
where H_sendDate between @begin and @end --1--上一区间
select
[鲜货货量]=sum(case when H_CargoType=2 then H_BWeight end),
[普货货量]=sum(case when H_CargoType=1 then H_BWeight end),
[空转陆货量]=sum(case when H_IsAtoE=1 then H_BWeight end),
[销售总额]=sum(H_Amount)
from tb
where H_sendDate between dateadd(day,-datediff(day,@begin,@end),@begin) and @begin --1--去年本区间
select
[鲜货货量]=sum(case when H_CargoType=2 then H_BWeight end),
[普货货量]=sum(case when H_CargoType=1 then H_BWeight end),
[空转陆货量]=sum(case when H_IsAtoE=1 then H_BWeight end),
[销售总额]=sum(H_Amount)
from tb
where H_sendDate between dateadd(year,-1,@begin) and dateadd(year,-1,@end)
go
exec pp '2009-11-01','2009-11-05'鲜货货量 普货货量 空转陆货量 销售总额
--------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
NULL 394.00 NULL 48536.00
警告: 聚合或其他 SET 操作消除了空值。(1 行受影响)鲜货货量 普货货量 空转陆货量 销售总额
--------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
NULL 157.00 NULL 1658.00
警告: 聚合或其他 SET 操作消除了空值。(1 行受影响)鲜货货量 普货货量 空转陆货量 销售总额
--------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
NULL NULL NULL NULL(1 行受影响)