统计问题

[sunfor]

有一表:TB1(tot,in_date1,in_date2,in_date3,out_date1,out_date2,out_date3,total)
tot,in_date1,in_date2,in_date3,out_date1,out_date2,out_date3,total
10 NULL 2012-10-11 NULL NULL NULL NULL
20 2012-09-15 2012-10-21 NULL 2012-10-20 NULL NULL
30 NULL 2012-10-23 NULL NULL NULL NULL

tot是入货数量
in_date1-3是入货日期
out_date1-3是出货日期
total是剩余数量
只要有入货日期输入(不为空),就等于有货进入(加),只要有出货日期输入(不为空),就等于有货进出(减),
最后得到剩余数量total.
tot,in_date1,in_date2,in_date3,out_date1,out_date2,out_date3,total
10 NULL 2012-10-11 2012-10-15 NULL NULL NULL 20
10 2012-09-15 2012-10-21 NULL 2012-10-20 NULL NULL 10
10 NULL 2012-10-23 NULL NULL NULL 2012-10-25 0

[Mr_Nice]

if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (tot int,in_date1 datetime,in_date2 datetime,in_date3 sql_variant,out_date1 datetime,out_date2 sql_variant,out_date3 sql_variant,total sql_variant)
insert into [TB]
select 10,null,'2012-10-11',null,null,null,null,null union all
select 20,'2012-09-15','2012-10-21',null,'2012-10-20',null,null,null union all
select 30,null,'2012-10-23',null,null,null,null,null

select * from [TB]


SELECT *,Newtotal = 
CASE WHEN in_date1 IS NULL THEN 0 ELSE tot END
+CASE WHEN in_date2 IS NULL THEN 0 ELSE tot END
+CASE WHEN in_date3 IS NULL THEN 0 ELSE tot END
-CASE WHEN out_date1 IS NULL THEN 0 ELSE tot END
-CASE WHEN out_date2 IS NULL THEN 0 ELSE tot END
-CASE WHEN out_date3 IS NULL THEN 0 ELSE tot END
FROM dbo.TB

/*
tot         in_date1                in_date2                in_date3                                                                                                                                                                                                                                                         out_date1               out_date2                                                                                                                                                                                                                                                        out_date3                                                                                                                                                                                                                                                        total                                                                                                                                                                                                                                                            Newtotal
----------- ----------------------- ----------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------
10          NULL                    2012-10-11 00:00:00.000 NULL                                                                                                                                                                                                                                                             NULL                    NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             10
20          2012-09-15 00:00:00.000 2012-10-21 00:00:00.000 NULL                                                                                                                                                                                                                                                             2012-10-20 00:00:00.000 NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             20
30          NULL                    2012-10-23 00:00:00.000 NULL                                                                                                                                                                                                                                                             NULL                    NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             30

(3 行受影响)
*/

[sunfor]

引用 1 楼 OrchidCat 的回复:

SQL code? if object_id('[TB]') is not null drop table [TB] go create table [TB] (tot int,in_date1 datetime,in_date2 datetime,in_date3 sql_va……

我想用UPDAT语句,更新TOTAL字段. 公式: total=tot*入仓日期个数-tot*出货日期个数

[Mr_Nice]

引用 2 楼 sunfor 的回复:

引用 1 楼 OrchidCat 的回复:SQL code? if object_id('[TB]') is not null drop table [TB] go create table [TB] (tot int,in_date1 datetime,in_date2 datetime,in_date3 sql_va…… 我想用UPDAT语句,更新TOTAL字……

UPDATE dbo.TB
SET total =CASE WHEN in_date1 IS NULL THEN 0 ELSE tot END
+CASE WHEN in_date2 IS NULL THEN 0 ELSE tot END
+CASE WHEN in_date3 IS NULL THEN 0 ELSE tot END
-CASE WHEN out_date1 IS NULL THEN 0 ELSE tot END
-CASE WHEN out_date2 IS NULL THEN 0 ELSE tot END
-CASE WHEN out_date3 IS NULL THEN 0 ELSE tot END

SELECT * FROM dbo.TB

[坚_持]

declare @TBA table
(
tot int,
in_date1 datetime,
in_date2 datetime,
in_date3 sql_variant,
out_date1 datetime,
out_date2 sql_variant,
out_date3 sql_variant,
total sql_variant
)
insert into @TBA
select 10,null,'2012-10-11',null,null,null,null,null union all
select 20,'2012-09-15','2012-10-21',null,'2012-10-20',null,null,null union all
select 30,null,'2012-10-23',null,null,null,null,null

select t.tot,t.in_date1,t.in_date2,t.in_date3,t.out_date1,t.out_date2,t.out_date3,
case when t.in_date1 is null then 0 else t.tot end
+ case when t.in_date2 is null then 0 else t.tot end
+case when t.in_date3 is null then 0 else t.tot end
- case when t.out_date1 is null then 0 else t.tot end
- case when t.out_date2 is null then 0 else t.tot end
- case when t.out_date3 is null then 0 else t.tot end as total
from @TBA as t