Javascript：Websql查询选择案例 [英] Javascript: Websql query select case

问题描述

要获得一个不到30天，31天到90天，超过91天（从今天起）的日期列表，我写的查询如下：

To achieve a list of date less than 30 days,between 31 and 90 days,above 91 days (from todays date),the query I have written is as below...

Select t2.name,case WHEN (t1.date >="Mon Aug 04 2014'"-30 ) THEN sum(t1.amount) ELSE 0 END as total_30,case WHEN (t1.date >="Mon Aug 04 2014"-90) and (t1.date <="Mon Aug 04 2014"-31 ) THEN sum(t1.amount) ELSE 0 END as total_90,case WHEN (t1.date <="Mon Aug 04 2014"-91 ) THEN t1.amount ELSE 0 END as total_91 from invoice t1,customer t2 WHERE t1.cust_code=t2.code group by t2.code

原始发票表

original invoice table

 date     |   code |  amount
2009/06/01  |  C1   | 44
1963/09/06  |  C0   | 55
1963/09/06  |  C1   | 545
2014/06/07  |  C0   | 66
2014/08/04  |  C0   | 547

输出我

Output i get

Customer Name | Less than 1 month | 1-3 months | 3 & above months
gy | 668 |0 |0
dg |589 | 0 |0
Total: 1257 |0 | 0

这不是预期的输出..



预期输出如下所示

this is not the expected output..

Expected output is as below

Customer Name | Less than 1 month | 1-3 months | 3 &above months
gy | 547 |66 |55
dg | 0| 0 |589
Total: 547 |66 | 644

我如何更改查询以获得预期的输出..任何人都可以帮助解决这个问题..

How can i change the query to get the expected output..Can anybody please help to solve this..

推荐答案

试试这个SQL

Try this SQL

Select t2.name, t2.code,
	sum(case WHEN (t1.date >= cast('04-Aug-2014' AS datetime)-30 ) THEN t1.amount ELSE 0 END) as total_30,
	sum(case WHEN (t1.date >= cast('04-Aug-2014' AS datetime)-90) and (t1.date <=cast('04-Aug-2014' AS datetime)-31 ) THEN t1.amount ELSE 0 END) as total_90,
	sum(case WHEN (t1.date <= cast('04-Aug-2014' AS datetime)-91 ) THEN t1.amount ELSE 0 END) as total_91 
from invoice t1,customer t2 WHERE t1.cust_code=t2.code group by t2.name, t2.code

create table #invoice
(
date datetime,
code varchar(25),
amount numeric(18,2)
)


insert into  #invoice values('2009/06/01','C1',44)
insert into  #invoice values('1963/09/06','C0',55)
insert into  #invoice values('1963/09/06','C1',545)
insert into  #invoice values('2014/06/07','C0',66)
insert into  #invoice values('2014/08/04','C0',547)
declare @curdate datetime
set @curdate='2014/08/04'

select Code,sum(t.[Above 90])'Above 90',
sum(t.[30-90])'30-90',
sum(t.[below 30]) 'below 30'
from (select Code,
case 
when (DATEDIFF("dd",date,@curdate)>90) then SUM(amount) 
End 'Above 90' ,
case 
when (DATEDIFF("dd",date,@curdate)<=90 and DATEDIFF("dd",date,@curdate)>30) then SUM(amount) 
End '30-90',
case 
when (DATEDIFF("dd",date,@curdate)<30) then SUM(amount) 
End 'below 30'

 from #invoice
group by Code,date) T
group by code



select * from #invoice
drop Table #invoice

这篇关于Javascript：Websql查询选择案例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！