--表 tb_Records
cusId amount currentbalance createddate
--------------------------------------------
/*
cusId: 客户的ID号(与表tb_Customer.cusId 相对应)
amount:汇入或支付的金额(若为正表示客户的余额增多,负则表示减少)
currentbalance:汇入或支出后的余额 (当前行的currentbalance总是为上一行的currentbalance+当前后之后的值)
createddate:该条记录创建的时间(值总是getdate())
*/
/*需求:---------------
根据createddate字段,统计2007-12-26 到 2008-12-25 这一个中每一个月的欠款总数,和一整个的总和*********重要说明:***************************************************从Jan到Dec 的每一个月份的起始时间是从上一个月的26日到该月的25日记为一个月.
即 2007-12-26日就应该是2008年1月(Jan),2008-1-24和2008-1-25都是2008-1月
而 2008-1-26 就应该是 2008-2月(Feb),依些类推 2008-11-26~2008-12-25记为2008-12(Dec)以前在做统计时,有位大侠教我用了
SELECT sum(quantity) as 'quantity',
(CASE WHEN datepart(dd, OrderDate) > 25 THEN dateadd(dd, 8, OrderDate) ELSE OrderDate END) AS OrderDate,
FROM tb_SaleRecords ……
后用发现有问题,统计的结果(数量)是对的。但日期不对.比如说明明是 2008-11-29 买了 100个,
结果它显示的时间是2008-12-07买的。而我们系统是的。时间不变,只是这个销售量要记到Feb的销售总量中去/*另一个重要说明:
上月结帐欠款截止本月5日尚未回收部分为欠款。
例:若2月份(2008-1-26 到 2008-2-25) 统计时的欠款为 2000元
在3月5日前又汇入一1500元,那么他2月欠款就为500,其它依此类推。如果月统计出来如果不欠款(欠款为0或有多的余额)全记为 0
*/*********************************************************************
统计结果应这样:cusId Jan Feb Mar Apr May …… Dec Total
---------------------------------------------
1011 100 50 50 100 0 …… 0 总和
*/
cusId amount currentbalance createddate
--------------------------------------------
/*
cusId: 客户的ID号(与表tb_Customer.cusId 相对应)
amount:汇入或支付的金额(若为正表示客户的余额增多,负则表示减少)
currentbalance:汇入或支出后的余额 (当前行的currentbalance总是为上一行的currentbalance+当前后之后的值)
createddate:该条记录创建的时间(值总是getdate())
*/
/*需求:---------------
根据createddate字段,统计2007-12-26 到 2008-12-25 这一个中每一个月的欠款总数,和一整个的总和*********重要说明:***************************************************从Jan到Dec 的每一个月份的起始时间是从上一个月的26日到该月的25日记为一个月.
即 2007-12-26日就应该是2008年1月(Jan),2008-1-24和2008-1-25都是2008-1月
而 2008-1-26 就应该是 2008-2月(Feb),依些类推 2008-11-26~2008-12-25记为2008-12(Dec)以前在做统计时,有位大侠教我用了
SELECT sum(quantity) as 'quantity',
(CASE WHEN datepart(dd, OrderDate) > 25 THEN dateadd(dd, 8, OrderDate) ELSE OrderDate END) AS OrderDate,
FROM tb_SaleRecords ……
后用发现有问题,统计的结果(数量)是对的。但日期不对.比如说明明是 2008-11-29 买了 100个,
结果它显示的时间是2008-12-07买的。而我们系统是的。时间不变,只是这个销售量要记到Feb的销售总量中去/*另一个重要说明:
上月结帐欠款截止本月5日尚未回收部分为欠款。
例:若2月份(2008-1-26 到 2008-2-25) 统计时的欠款为 2000元
在3月5日前又汇入一1500元,那么他2月欠款就为500,其它依此类推。如果月统计出来如果不欠款(欠款为0或有多的余额)全记为 0
*/*********************************************************************
统计结果应这样:cusId Jan Feb Mar Apr May …… Dec Total
---------------------------------------------
1011 100 50 50 100 0 …… 0 总和
*/
SELECT sum(quantity) as 'quantity',
(CASE WHEN datepart(dd, OrderDate) > 25 THEN dateadd(mm, 1, OrderDate) ELSE OrderDate END) AS OrderDate,
FROM tb_SaleRecords ……
只是一个查询中的处理,目的仅仅是在统计结果中将25日之后的数据归类到下一个月;因此,在统计的SQL里不会显示购买日期吧?
SELECT A.cusId,CONVERT(VARCHAR(7),A.createddate,102) AS mon,
(
SELECT CASE WHEN SUM(amount)>=0 THEN 0 ELSE SUM(amount) END FROM tb_Records
WHERE createddate <DATEADD(M,1,CONVERT(VARCHAR(7),A.createddate,102)+'-5')
) AS 欠款
FROM tb_Records A
GROUP BY
A.cusId,CONVERT(VARCHAR(7),A.createddate,102)
请给出表结构,测试数据,相关算法和需要的结果.谢谢!
如果是行列转换,你这个可以用静态的.参考:
/*
按月进行行列转换并加合计(2007-11-19于海南三亚)例如有表tb某些人每月消费数据如下:
id data month
001 11 1
001 12 2
001 13 3
001 14 4
001 15 5
001 16 6
001 17 7
001 18 8
001 19 9
001 110 10
001 111 11
001 112 12
002 21 1
002 22 2
002 23 3
002 24 4
002 25 5
002 26 6
002 27 7
002 28 8
002 29 9
002 210 10
002 211 11
002 212 12
要实现如下结果:
人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
001 11 12 13 14 15 16 17 18 19 110 111 112
002 21 22 23 24 25 26 27 28 29 210 211 212
*/create table tb
(
id char(3),
data int,
month int
)
insert into tb(id,data,month) values('001',11,1)
insert into tb(id,data,month) values('001',12,2)
insert into tb(id,data,month) values('001',13,3)
insert into tb(id,data,month) values('001',14,4)
insert into tb(id,data,month) values('001',15,5)
insert into tb(id,data,month) values('001',16,6)
insert into tb(id,data,month) values('001',17,7)
insert into tb(id,data,month) values('001',18,8)
insert into tb(id,data,month) values('001',19,9)
insert into tb(id,data,month) values('001',110,10)
insert into tb(id,data,month) values('001',111,11)
insert into tb(id,data,month) values('001',112,12)
insert into tb(id,data,month) values('002',21,1)
insert into tb(id,data,month) values('002',22,2)
insert into tb(id,data,month) values('002',23,3)
insert into tb(id,data,month) values('002',24,4)
insert into tb(id,data,month) values('002',25,5)
insert into tb(id,data,month) values('002',26,6)
insert into tb(id,data,month) values('002',27,7)
insert into tb(id,data,month) values('002',28,8)
insert into tb(id,data,month) values('002',29,9)
insert into tb(id,data,month) values('002',210,10)
insert into tb(id,data,month) values('002',211,11)
insert into tb(id,data,month) values('002',212,12)
goSELECT id as '人员' ,
SUM(CASE month WHEN 1 THEN data ELSE 0 END) AS '1月' ,
SUM(CASE month WHEN 2 THEN data ELSE 0 END) AS '2月' ,
SUM(CASE month WHEN 3 THEN data ELSE 0 END) AS '3月' ,
SUM(CASE month WHEN 4 THEN data ELSE 0 END) AS '4月' ,
SUM(CASE month WHEN 5 THEN data ELSE 0 END) AS '5月' ,
SUM(CASE month WHEN 6 THEN data ELSE 0 END) AS '6月' ,
SUM(CASE month WHEN 7 THEN data ELSE 0 END) AS '7月' ,
SUM(CASE month WHEN 8 THEN data ELSE 0 END) AS '8月' ,
SUM(CASE month WHEN 9 THEN data ELSE 0 END) AS '9月' ,
SUM(CASE month WHEN 10 THEN data ELSE 0 END) AS '10月' ,
SUM(CASE month WHEN 11 THEN data ELSE 0 END) AS '11月' ,
SUM(CASE month WHEN 12 THEN data ELSE 0 END) AS '12月'
FROM tb
GROUP BY ID drop table tb/*
人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
---- --- --- --- --- --- --- --- --- --- ---- ---- ----
001 11 12 13 14 15 16 17 18 19 110 111 112
002 21 22 23 24 25 26 27 28 29 210 211 212(所影响的行数为 2 行)
*/--------------------------------------------------------------
/*
合计每个人每年的数据
人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 合计
001 11 12 13 14 15 16 17 18 19 110 111 112 468
002 21 22 23 24 25 26 27 28 29 210 211 212 858
*/SELECT id as '人员' ,
SUM(CASE month WHEN 1 THEN data ELSE 0 END) AS '1月' ,
SUM(CASE month WHEN 2 THEN data ELSE 0 END) AS '2月' ,
SUM(CASE month WHEN 3 THEN data ELSE 0 END) AS '3月' ,
SUM(CASE month WHEN 4 THEN data ELSE 0 END) AS '4月' ,
SUM(CASE month WHEN 5 THEN data ELSE 0 END) AS '5月' ,
SUM(CASE month WHEN 6 THEN data ELSE 0 END) AS '6月' ,
SUM(CASE month WHEN 7 THEN data ELSE 0 END) AS '7月' ,
SUM(CASE month WHEN 8 THEN data ELSE 0 END) AS '8月' ,
SUM(CASE month WHEN 9 THEN data ELSE 0 END) AS '9月' ,
SUM(CASE month WHEN 10 THEN data ELSE 0 END) AS '10月' ,
SUM(CASE month WHEN 11 THEN data ELSE 0 END) AS '11月' ,
SUM(CASE month WHEN 12 THEN data ELSE 0 END) AS '12月' ,
SUM(data) as '合计'
FROM tb
GROUP BY ID /*
人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 合计
---- --- --- --- --- --- --- --- --- --- ---- ---- ---- ----
001 11 12 13 14 15 16 17 18 19 110 111 112 468
002 21 22 23 24 25 26 27 28 29 210 211 212 858
*/
我反要求重新整理了一下。请看http://www.chenminghuaxia.cn/demo.html
楼主的意思是不是这样SELECT 'quantity' = sum(quantity) as ,
'欠款' =
( case when
(select sum(amount) - sum(currentbalance) from tb_SaleRecords where orderDate between convert(nvarchar(7),dateadd(mm,-1,orderdate),120)+'-26' and convert(nvarchar(7,orderdate,120)+'-25')) > 0
then 0
else
(select sum(amount) - sum(currentbalance) from tb_SaleRecords where orderDate between convert(nvarchar(7),dateadd(mm,-1,orderdate),120)+'-26' and convert(nvarchar(7,orderdate,120)+'-25')) > 0
)
FROM tb_SaleRecords
怎么case 和else 语句是一样的??
case when
(select sum(amount) - sum(currentbalance) from tb_SaleRecords where orderDate between convert(nvarchar(7),dateadd(mm,-1,orderdate),120)+'-26' and convert(nvarchar(7,orderdate,120)+'-25')) > 0
then 0
else
(select sum(amount) - sum(currentbalance) from tb_SaleRecords where orderDate between convert(nvarchar(7),dateadd(mm,-1,orderdate),120)+'-26' and convert(nvarchar(7,orderdate,120)+'-25')) > 0