I am having a tough time trying to get what I thought was a simple task
Show a list of Customers, one line per customer, with a balance total of Past Due invoices; “Past Due” being based on a runtime parameter “DaysPastDue”
So the user enters the past due days - like 360. and each customer is shown with the invoice balance which is greater than 360days past due.
No matter how i setup the toplevel and subqueries - i get multiple lines per customer.
I’ve tried subquery/pivots - nothing seems to avoid multiple lines generated from the subquery which determines IF an invoice is past due - and sums the total.
Looking for any examples to try… TIA
select
[Customer].[Company] as [Customer_Company], [Customer].[Name] as [Customer_Name],
[Customer].[CustID] as [Customer_CustID], [Customer].[CustNum] as [Customer_CustNum],
[Customer].[CreditLimit] as [Customer_CreditLimit]
from Erp.Customer as Customer inner join (select
[InvcHead_PastDue].[Company] as [InvcHead_PastDue_Company], [InvcHead_PastDue].[CustNum] as [InvcHead_PastDue_CustNum], [InvcHead_PastDue].[CurrencyCode] as [InvcHead_PastDue_CurrencyCode],
[InvcHead_PastDue].[DueDate] as [InvcHead_PastDue_DueDate], [InvcHead_PastDue].[InvoiceBal] as [InvcHead_PastDue_InvoiceBal],
(case when isnull(DateDiff(“d”,InvcHead_PastDue.DueDate,Constants.Today),0) > @SMinDays then 1 else 0
end) as [Calculated_FlagPastDue], (SUM(InvcHead_PastDue.InvoiceBal)) as [Calculated_SumPastDue]
from Erp.InvcHead as InvcHead_PastDue
where (InvcHead_PastDue.OpenInvoice = 1 and InvcHead_PastDue.Posted = 1)
and (case when isnull(DateDiff(“d”,InvcHead_PastDue.DueDate,Constants.Today),0) > @SMinDays then 1 else 0 end) = 1
group by [InvcHead_PastDue].[Company],
[InvcHead_PastDue].[CustNum], [InvcHead_PastDue].[CurrencyCode],
[InvcHead_PastDue].[DueDate], [InvcHead_PastDue].[InvoiceBal],
(case when isnull(DateDiff(“d”,InvcHead_PastDue.DueDate,Constants.Today),0) > @SMinDays then 1 else 0
end)) as PastDue on Customer.Company = PastDue.InvcHead_PastDue_Company
and Customer.CustNum = PastDue.InvcHead_PastDue_CustNum
Maybe I am misunderstanding what you want but you should be able to just join the InvcHead to the Customer Table and put criteria on the InvcHead table for OpenInvoice=1 and greater than or equal to the expression: DATEADD(day,DATEDIFF(day,@DaysPastDue,GETDATE()),0).
You can then add a calculated field for Total Balance and just SUM(InvcHead.InvoiceBal) and group by your customer fields.
The BAQ ends up being:
[Customer].[Company] as [Customer_Company],
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
[Customer].[CreditLimit] as [Customer_CreditLimit],
(SUM(InvcHead.InvoiceBal)) as [Calculated_TotalBalance]
from Erp.Customer as Customer
inner join Erp.InvcHead as InvcHead on
Customer.Company = InvcHead.Company
and Customer.CustNum = InvcHead.CustNum
and ( InvcHead.OpenInvoice = 1 and InvcHead.DueDate >= DATEADD(day,DATEDIFF(day,@DaysPastDue,GETDATE()),0) )
group by [Customer].[Company],
[Customer].[CustID],
[Customer].[Name],
[Customer].[CreditLimit]
i think i tried that already and it was unsuccessful
(1) the date math needs to be based on the invoice date - and provide a value FROM the invoice date, not the current date
and
(2) when i do that i must include the calculated field in the summary - and that’s when the stuff hits the fan…
Which is why i tried various subquery options…
thank you
select
[Customer].[Company] as [Customer_Company],
[Customer].[Name] as [Customer_Name],
[Customer].[CustID] as [Customer_CustID],
[Customer].[CustNum] as [Customer_CustNum],
[Customer].[CreditLimit] as [Customer_CreditLimit],
(sum(InvcHead.InvoiceBal)) as [Calculated_PasatDue],
(isnull(DATEDIFF(day,InvcHead.InvoiceDate,Constants.Today),0)) as [Calculated_FlagPastDue]
from Erp.Customer as Customer
inner join Erp.InvcHead as InvcHead on
Customer.Company = InvcHead.Company
and Customer.CustNum = InvcHead.CustNum
and ( InvcHead.OpenInvoice = 1 )
where (isnull(DATEDIFF(day,InvcHead.InvoiceDate,Constants.Today),0)) >= @SMinDays
group by [Customer].[Company],
[Customer].[Name],
[Customer].[CustID],
[Customer].[CustNum],
[Customer].[CreditLimit],
(isnull(DATEDIFF(day,InvcHead.InvoiceDate,Constants.Today),0))
So your version DOES provide a single line result - but it doiesn’t provide the proper selection. And getting that selection is what throws a wrench in the resultset
Are you saying that you want the total open balance AND the number of days between the invoice date and today?
If you do not want to see the invoice date differences don’t include it as a calculated field. You can write the whole calculation in as an expression in the table criteria. If you do want to see the invoice date difference then you will always have multiple records if there are multiple invoice dates.
that is the issue - i dont think i can do this - or havent found a way
and yes, we’re looking for the total balance for items which are XXX number of days past their invoice date. So it varies on each invoice.
Since I have to group on the calculated field to meet syntax
I thought maybe the subquery was returning values for each datediff
that is why, i was using a binary flag to determine (past due) selection.
and then filtering subq on TRUE value or PIVOTing on the binary value and using only the True value field.
If all you want is the Customer information and the total open balance from that date you do not need to group by the date field. If you want to see the calculated number of days from the Invoice Date until today then yes, you will get multiple rows per customer.
Sorry, It’s not the total open balance from a date
it’s the total open balance for any invoice past a certain number of days old
the customer could owe $1,000,
but if $750 of that is for invoices created today
if the user called for items over 1 day due(invoiced) - the total reported would be only $250
NOTE: the total OPEN balance is actually reported on another column
thanks again for the help
I’m surprised how a simple request is so hard to complete
If you want over a certain number of days old you would use a <=SMinDays.
The Table Criteria is filtering the InvcHead table to only show records that are greater that number of days old and you are summing the returned records.
If you remove the SUM() and Group By you can add two additional calculated fields.
Int for DaysDifference: DateDiff(day,InvcHead.InvoiceDate,GetDate())
Date for QueryStart: DATEADD(day,DateDiff(day,@SMinDays,GetDate()),0)
Run that for the first 100 records and it should return the invoices greater than the parameter date with the calculated day difference. If you sum that you are just getting the sum of the Invoice Balances for invoices before the start date.
That’s what the table criteria does. It pulls all customers that have InvcHead records where the InvoiceDate is <= the date that is SMinDays ago.
I also included the OpenInvoice flag because I thought you only wanted open ones but you can remove that or add another date calculation if you only want overdue invoices.
OK - this version works.
Main query and single field return subquery with the filter condition applied in the single field - alone. No subquery filter.
“SumPastDue” = sum (case when isnull(DATEDIFF(day,InvcHead.InvoiceDate,Constants.Today),0) >= @SMinDays then InvcHead.InvoiceBal else 0 end)
No subquery filtering or grouping. I am dumbfounded as to why i couldnt get this earlier.
select
[Customer].[Company] as [Customer_Company],
[Customer].[Name] as [Customer_Name],
[Customer].[CustID] as [Customer_CustID],
[Customer].[CustNum] as [Customer_CustNum],
[Customer].[CreditLimit] as [Customer_CreditLimit],
(((select
(sum(case when isnull(DATEDIFF(day,InvcHead.InvoiceDate,Constants.Today),0) >= @SMinDays then InvcHead.InvoiceBal else 0 end)) as [Calculated_SumPastDue]
from Erp.InvcHead as InvcHead
where (InvcHead.OpenInvoice = 1)
and InvcHead.Company = Customer.Company and InvcHead.CustNum = Customer.CustNum))) as [Calculated_SumPastDue]
from Erp.Customer as Customer
group by [Customer].[Company],
[Customer].[Name],
[Customer].[CustID],
[Customer].[CustNum],
[Customer].[CreditLimit]
