Javascript: Websql query select case

Question

1.00/5 (1 vote)

See more:

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...

SQL

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

CSS

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..

Posted 4-Aug-14 2:33am

p@y@l

Updated 4-Aug-14 19:04pm

v7

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Answer 1 · 2014-08-04T04:12:00

Solution 1

Try this SQL

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

Posted 4-Aug-14 4:12am

_Asif_

v2

Comments

p@y@l 5-Aug-14 0:08am

this query works fine but produces the same result as i got before!

_Asif_ 5-Aug-14 0:47am

I have tested this code myself and it was producing the desired result. Show us the output

p@y@l 5-Aug-14 0:58am

Is it,ok i will try again...

name |code |total_30 | total_90 | total_91
dg |C1 | 589 | 0 | 0
gy |C0 | 668 | 0 | 0

I have tried it in console and shows the output as this

_Asif_ 5-Aug-14 1:09am

I have tested it like this. You can run the below script on the console and check what is wrong at your end.

DECLARE @TBL TABLE
(
DATE DATETIME,
CODE VARCHAR(10),
Amount int
)

INSERT INTO @TBL (DATE, CODE, Amount)
SELECT '2009/06/01', 'C1', 44
UNION ALL
SELECT '1963/09/06','C0',55
UNION ALL
SELECT '1963/09/06','C1',545
UNION ALL
SELECT '2014/06/07','C0',66
UNION ALL
SELECT '2014/08/04','C0',547

select * from @TBL

Select t1.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 @TBL t1 group by t1.code

p@y@l 5-Aug-14 6:10am

Thanks for your help i got it solved it was date issue,that the amount is not properly displayed

p@y@l 5-Aug-14 1:31am

i have tried your code in my console ,i guess my explanation is not clear for u..i need to get the output as below
Name | total_30 | total_90 | total_91
gy | 547 | 66 | 55
dg | 0 | 0 | 589

sandip_sagar · Answer 2 · 2014-08-04T20:00:00

SQL

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