12,394,573 members (81,174 online)
Rate this:
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...

`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..
Posted 4-Aug-14 2:33am
p@y@l611
Updated 4-Aug-14 19:04pm
v7

Rate this:

## Solution 1

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```
v2
p@y@l 5-Aug-14 0:08am

this query works fine but produces the same result as i got before!
Syed Asif Iqbal 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
Syed Asif Iqbal 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
Rate this:

## Solution 2

```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```
v5
p@y@l 5-Aug-14 2:28am

error with DATEDIFF "its not a function".
p@y@l 5-Aug-14 6:09am

Thanks for the help i got it solved.
sandip_sagar 5-Aug-14 8:01am

try in sql 2008

Top Experts
Last 24hrsThis month
 OriginalGriff 480 ppolymorphe 285 Richard Deeming 269 Richard MacCutchan 219 Karthik Bangalore 180
 OriginalGriff 5,783 Karthik Bangalore 2,337 ppolymorphe 2,290 F-ES Sitecore 1,852 Richard MacCutchan 1,627