how to display null rows in my sql

vara reddy

14y2.3kSQL

With the help of this query i was unable to show null rows when date was changed.
please help me

select p.partnerid,
sum(case when c.amount is not null then c.amount else 0 end) as amount,
sum(case when c.netamt is not null then c.netamt else 0 end) as netamt,
sum(case when c.netamt - c.amount is not null then c.netamt - c.amount else 0 end) as interest,
sum(case when c.installment = 20 then c.amount else 0 end) as Twenty,
sum(case when c.installment = 10 then c.amount else 0 end) as Ten,
sum(case when c.installment = 5 then c.amount else 0 end) as Five,
sum(case when c.installment = 2 then c.amount else 0 end) as Two
from partnerinfo p left outer join customerinfo c on p.partnerid = c.partnerid
where (c.startdate is null OR (c.startdate >= '2012-3-15' and c.startdate <= '2012-12-30'))
and (c.partnerid is null or c.partnerid) and p.manager = 1
group by p.partnerid

For better understanding here are the images

1. http://i46.tinypic.com/155l4cp.jpg

2. http://i49.tinypic.com/357ki3b.jpg

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Santhosh Kumar Jayaraman14y ago
I dont think any other options. But you can try with Union all also. The below query might not be correct syntax wise, but try somthin like this

select p.partnerid,
sum(case when c.amount is not null then c.amount else 0 end) as amount,
sum(case when c.netamt is not null then c.netamt else 0 end) as netamt,
sum(case when c.netamt - c.amount is not null then c.netamt - c.amount else 0 end) as interest,
sum(case when c.installment = 20 then c.amount else 0 end) as Twenty,
sum(case when c.installment = 10 then c.amount else 0 end) as Ten,
sum(case when c.installment = 5 then c.amount else 0 end) as Five,
sum(case when c.installment = 2 then c.amount else 0 end) as Two
from partnerinfo p left outer join customerinfo c on p.partnerid = c.partnerid
where (c.startdate is null OR (c.startdate >= '2012-3-15' and c.startdate <= '2012-12-30'))
and (c.partnerid is null or c.partnerid) and p.manager = 1
group by p.partnerid
unionall

select p.partnerid,
0,
0,
0,
0,
0,
0,
0
from partnerinfo p left outer join customerinfo c on p.partnerid = c.partnerid
where (c.startdate is null OR (c.startdate >= '2011-3-15' and c.startdate <= '2012-3-15'))
and (c.partnerid is null or c.partnerid) and p.manager = 1
group by p.partnerid
0
vara reddy14y ago
Hi santosh your idea is correct. But writing for every sum(-----) is a good query?

But i had a problem with count.

when placed like below 1,145 are getting 0 but 12,14, 24 they are showing previous count

count((c.startdate>= '2012-3-15' and c.startdate <= '2012-12-30')) as accounts,

any way to change?????
0
Santhosh Kumar Jayaraman14y ago
This is because the start dates for 12,14, 24 are before 2012-3-15 and in the where clause u explicitly given the condition to fetch records only after 2012-3-15.

You have to remove the condition in where clause and set it in select clause.
For eg,
select p.partnerid,
sum(case when c.amount is not null and (c.startdate>= '2012-3-15' and c.startdate <= '2012-12-30') then c.amount else 0 end) as amount,

The syntax might not be correct, but this is the idea
0