June 13, 2017 at 12:38 pm
I am trying to eliminate the highlighted code below from this...
SELECT i.bill_cust_id,
sum(case when DATEDIFF(day, isnull(p.firstprint,isnull(i.printdate,i.dateship)), GetDate()) between 0 and 30 then b.balance else 0.0 end) as current_balance,
sum(case when DATEDIFF(day, isnull(p.firstprint,isnull(i.printdate,i.dateship)), GetDate()) between 31 and 60 then b.balance else 0.0 end) as thirty_balance,
sum(case when DATEDIFF(day, isnull(p.firstprint,isnull(i.printdate,i.dateship)), GetDate()) between 61 and 90 then b.balance else 0.0 end) as sixty_balance,
sum(case when DATEDIFF(day, isnull(p.firstprint,isnull(i.printdate,i.dateship)), GetDate()) between 91 and 120 then b.balance else 0.0 end) as ninetyplus_balance,
sum(case when DATEDIFF(day, isnull(p.firstprint,isnull(i.printdate,i.dateship)), GetDate()) between 121 and 365 then b.balance else 0.0 end) as hundred20plus_balance,
sum(case when DATEDIFF(day, isnull(p.firstprint,isnull(i.printdate,i.dateship)), GetDate()) > 365 then b.balance else 0.0 end) as yearplus_balance,
e.credit,
a.call_date
FROM invoices i left outer join v_InvoiceBalance b on i.invid = b.invid
left outer join (select p2.invid, min(p2.printdate) as firstprint from InvoicePrintLog p2 group by p2.invid) p on i.invid = p.invid
left outer join
(select cr.cust_id, sum(s.total-cr.used_amount) as credit
from credits cr, v_CreditSummary s
where cr.creditid = s.creditid and cr.credittype= 2
group by cr.cust_id) e on i.bill_cust_id = e.cust_id,
(select acctno, max(collection_call_date) as call_date from adjuster group by acctno)
a
WHERE i.bill_cust_id = a.acctno
and i.invtype = 'I'
and i.voided = 0
AND i.invoiced = 1
AND b.balance > 0
group by i.bill_cust_id, e.credit, a.call_date
go
to this ...
SELECT i.bill_cust_id,
sum(case when DATEDIFF(day, isnull(p.firstprint,isnull(i.printdate,i.dateship)), GetDate()) between 0 and 30 then b.balance else 0.0 end) as current_balance,
sum(case when DATEDIFF(day, isnull(p.firstprint,isnull(i.printdate,i.dateship)), GetDate()) between 31 and 60 then b.balance else 0.0 end) as thirty_balance,
sum(case when DATEDIFF(day, isnull(p.firstprint,isnull(i.printdate,i.dateship)), GetDate()) between 61 and 90 then b.balance else 0.0 end) as sixty_balance,
sum(case when DATEDIFF(day, isnull(p.firstprint,isnull(i.printdate,i.dateship)), GetDate()) between 91 and 120 then b.balance else 0.0 end) as ninetyplus_balance,
sum(case when DATEDIFF(day, isnull(p.firstprint,isnull(i.printdate,i.dateship)), GetDate()) between 121 and 365 then b.balance else 0.0 end) as hundred20plus_balance,
sum(case when DATEDIFF(day, isnull(p.firstprint,isnull(i.printdate,i.dateship)), GetDate()) > 365 then b.balance else 0.0 end) as yearplus_balance,
e.credit,
MAX(a.collection_call_date) AS call_date
FROM invoices i
left outer join v_InvoiceBalance b on i.invid = b.invid
left outer join (select p2.invid, min(p2.printdate) as firstprint from InvoicePrintLog p2 group by p2.invid) p on i.invid = p.invid
left outer join
( select cr.cust_id,
sum(s.total-cr.used_amount) as credit
from credits cr, v_CreditSummary s
where cr.creditid = s.creditid and cr.credittype= 2
group by cr.cust_id) e
on i.bill_cust_id = e.cust_id
JOIN adjuster a on i.bill_cust_id = a.acctno -- joining directly
WHERE
i.invtype = 'I'
and i.voided = 0
AND i.invoiced = 1
AND b.balance > 0
GROUP BY i.bill_cust_id, e.credit, a.acctno
But I am getting wrong results. # of rows are still the same but I am getting different values for ninetyplus_balance, hundred20plus_balance and yearplus_balance.
Can someone please tell me why?
June 13, 2017 at 1:21 pm
Do you have duplicate account numbers in the adjuster table?
Check this and see what comes back, if you are getting anything back your second query will end up joining to all of those rows so the sums would end up inflated as the records in the other tables will be included more than once. You can also just do a simply COUNT(*) on the first query compared to the second with no group by and see if the row counts change.
select acctno, COUNT(*) from adjuster group by acctno HAVING COUNT(*) > 1
June 13, 2017 at 1:42 pm
ZZartin - Tuesday, June 13, 2017 1:21 PMDo you have duplicate account numbers in the adjuster table?Check this and see what comes back, if you are getting anything back your second query will end up joining to all of those rows so the sums would end up inflated as the records in the other tables will be included more than once. You can also just do a simply COUNT(*) on the first query compared to the second with no group by and see if the row counts change.
select acctno, COUNT(*) from adjuster group by acctno HAVING COUNT(*) > 1
You are correct, I do have duplicate counts per acctno
June 14, 2017 at 7:44 am
Zzartin sent me to the right direction. I had duplicate values so I had to group it anyway.
i rewrote the query :
select acctno, max(collection_call_date) as call_date
into #adjuster
from adjuster a
group by acctno
SELECT i.bill_cust_id,
sum(case when DATEDIFF(day, isnull(p.firstprint,isnull(i.printdate,i.dateship)), GetDate()) between 0 and 30 then b.balance else 0.0 end) as current_balance,
sum(case when DATEDIFF(day, isnull(p.firstprint,isnull(i.printdate,i.dateship)), GetDate()) between 31 and 60 then b.balance else 0.0 end) as thirty_balance,
sum(case when DATEDIFF(day, isnull(p.firstprint,isnull(i.printdate,i.dateship)), GetDate()) between 61 and 90 then b.balance else 0.0 end) as sixty_balance,
sum(case when DATEDIFF(day, isnull(p.firstprint,isnull(i.printdate,i.dateship)), GetDate()) between 91 and 120 then b.balance else 0.0 end) as ninetyplus_balance,
sum(case when DATEDIFF(day, isnull(p.firstprint,isnull(i.printdate,i.dateship)), GetDate()) between 121 and 365 then b.balance else 0.0 end) as hundred20plus_balance,
sum(case when DATEDIFF(day, isnull(p.firstprint,isnull(i.printdate,i.dateship)), GetDate()) > 365 then b.balance else 0.0 end) as yearplus_balance,
e.credit,
call_date
FROM invoices i
left outer join v_InvoiceBalance b on i.invid = b.invid
left outer join (select p2.invid, min(p2.printdate) as firstprint from InvoicePrintLog p2 group by p2.invid) p on i.invid = p.invid
left outer join
( select cr.cust_id,
sum(s.total-cr.used_amount) as credit
from credits cr, v_CreditSummary s
where cr.creditid = s.creditid and cr.credittype= 2
group by cr.cust_id) e
on i.bill_cust_id = e.cust_id
INNER JOIN #adjuster a on i.bill_cust_id = a.acctno -- joining directly
WHERE
i.invtype = 'I'
and i.voided = 0
AND i.invoiced = 1
AND b.balance > 0
group by i.bill_cust_id, e.credit, a.call_date
go
drop table #adjuster
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply