Click here to Skip to main content
12,394,573 members (81,174 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: Javascript
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: bad
 
good
Please Sign up or sign in to vote.

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
  Permalink  
v2
Comments
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: bad
 
good
Please Sign up or sign in to vote.

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
  Permalink  
v5
Comments
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

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160721.1 | Last Updated 6 Aug 2014
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100