Invoicing Module -- count number of completed tasks 1

[iuianj07]

Hello guys,

I created an invoicing module for one of our database in this office, what this does is if the Reporting Period is for Q the price is $45.00 per statement(job we do),and if a checkbox named 24HourRush is checked, the Q price would be $67.50 or if the checkbox named 1-3DayRush is checked, the Q price would be $56.25. We also have a Reporting Period of YE, the price for that is $50.00, if 24HourRush is checked the price would change to $75.00 and if 1-3DayRush is checked the price would change to $62.50.

The module I have for this is below:

Public Function GetAmount(O_ITV As Variant, Rush24 As Boolean, Rush36 As Boolean, RptPrd As String) As Currency

    Dim curO_ITV As Currency
    Dim curRush24 As Currency
    Dim curRush36 As Currency
    Dim curRptPrdQYE As Currency
    Dim curDefault As Currency
    Dim curRush24Q As Currency
    Dim CurRush36Q As Currency
    Dim curRptPrdQ As Currency
    

    curO_ITV = 12
    curRush24 = 75
    curRush36 = 62.5
    curRush24Q = 67.5
    CurRush36Q = 56.25
    curRptPrdQYE = 50
    curRptPrdQ = 45
    curDefault = 0


    Dim curAmt As Currency
    Select Case True
        Case IsNull(O_ITV) = False
            curAmt = curO_ITV
        Case RptPrd Like "*YE*" And Rush24 = True
            curAmt = curRush24
        Case RprPrd Like "*YE*" And Rush36 = True
            curAmt = curRush36
        Case RptPrd Like "*Q*" And Rush24 = True
            curAmt = curRush24Q
        Case RptPrd Like "*Q*" And Rush36 = True
            curAmt = CurRush36Q
        Case RptPrd Like "*YE*" And Rush36 = True
            curAmt = curRush36
        Case RptPrd Like "*YE*"
            curAmt = curRptPrdQYE
        Case RptPrd Like "*Q*"
            curAmt = curRptPrdQ
        Case Else
            curAmt = curDefault
    End Select
    GetAmount = curAmt
End Function

This is correctly pulling out the price for invoicing. However, things got complicated though that if the number of statements (jobs) reached 10,000 in one year (Jan-Dec) the price for Reporting Period Q will be reduced to $42.50, and if the 24HourRush for Reporting Period Q is checked the price will be $63.75 and if the 1-3DayRush is checked the price will be $53.13.

The field type to count to see if we already reached 10,000 statements completed within the year is Signed_Off (this is a date data type) we put the date here to mark it as complete and have been returned back to the client. So if the number of Signed_Off is 10,000 between January-December then the price will change to the above mentioned prices.


Any help is greatly appreciated.

Thanks

 

[Remou]

I am glad you are happy. Don't trust me too much! I just notice:

IIf([JobCount]>10

That should, of course, be IIf([JobCount]>10000

I needed a low number for testing

http://lessthandot.com

 

[iuianj07]

I changed it to become:

IIf([JobCount]>10000

But when I ran it, the prices charged was for if we have completed less than 10,000 statements (ex $45.00 for Q statements). when I tried to change it to

IIf([JobCount]<10000

It pulls out the correct prices (ex $42.50 for Q statements)

Will it be safe to change it to <10000?

Thanks again

 

[Remou]

No. There is probably a problem with the charges table.

http://lessthandot.com

 

[Remou]

You say above that q = curRptPrdQ = 45

and

"if the number of statements (jobs) reached 10,000 in one year (Jan-Dec) the price for Reporting Period Q will be reduced to $42.50"

So unless you have more than 10,000 statements the price should be 45, not 42.50 yesno?

http://lessthandot.com

 

[iuianj07]

Yes, if the number of statements reached 10,000 and more in one year, the price should be 42.50, but if it's less than 10,000 the price should be 45. Below is my charges table copied and pasted here:


ID ReptType10K Rush Amount
1 O_ITV N/A N/A $12.00
2 YE N/A N/A $50.00
3 Q N/A N/A $45.00
4 YE N/A Rush24 $75.00
5 Q N/A Rush24 $67.50
6 YE N/A Rush36 $62.50
7 Q N/A Rush36 $56.25
8 YE 10K N/A $50.00
9 Q 10K N/A $42.50
10 YE 10K Rush24 $75.00
11 Q 10K Rush24 $63.75
12 YE 10K Rush36 $62.50
13 Q 10K Rush36 $53.13

 

[Remou]

So why do you then say above "It pulls out the correct prices (ex $42.50 for Q statements)"?

http://lessthandot.com

 

[Remou]

See comment above.

Note that in design view you can add the three columns ReptType, 10K and Rush which will allow you to see how the rows are being processed.

http://lessthandot.com

 

[iuianj07]

it pulls out the correct prices (ex $42.50 for Q statements)--this $42.50 price is based on more than 10,000 statements completed, it was correct when I changed to IIf([JobCount]<10000

your post above was to change it to IIf([JobCount]>10000 -- which when I ran the query it pulled out the price of $45.00 for Q Statements even though we already have more than 10,000 statements completed.

I apologize if I caused confusion, it could be my english is confusing....

 

[Remou]

Tell me, how exactly are you getting the 10,000? is it 10,000 altogether or per customer? This is how I am counting the 10,000 - it is per investor:

SELECT j.InvestorNumber, 
Count(j.InvestorNumber) AS JobCount
FROM Job_Tracking j
WHERE j.[Signed Off] Between DateSerial(Year(Date()),1,1) 
   And DateSerial(Year(Date()),12,31)
GROUP BY j.InvestorNumber

http://lessthandot.com

 

[iuianj07]

Ok, this could be the part that I might have failed to explain fully, and if yes, advance apologies...

We have the Job_Tracking Table, where it stores all statements we have for this project (completed, assigned) since it started in 2007.

The 10,000 statements we are trying to get is the number of statements completed per year (altogether). We bill the client per month based on how many statements we completed for the prior month.

when I run this query, it asked for a paramater [Select all records signed off before this date], the HR will put the current date (today) and it should only return statements that has been completed after the HR last ran this query(every start of the month) When I ran this query it returned 1,018 records (statements) which means for the month of August we have completed 1,018 statements.

Currently we completed 1,018 statements for the month of August, but when we sum up all statements completed from January 2010 - August 26 the total number of statements already reached 10,696. Which mean that the price should already be changed to the discounted price of $42.50 for Q statements.

We count the number of statements by the number of records in the table, although I guess you will say that I should have a column ID for this table/primary key to be able to count total number of records.

Our client is a bank, so the statement (job) I am referring to are loans. example:

There could be Investor number 89 Loan Number 123456
Investor number 89 Loan Number 234567
Investor number 90 Loan Number 345678
Investor number 90 Loan Number 456789
Investor number 91 Loan Number 765432

Based on the above example, we have 5 statements completed.


Please let me know if you have more questions and if I already confused you too much, and I apologize at the same time very greatful for your patience in dealing this problem with me.

thanks again

 

[Remou]

Okay, I think this should do it, but with the 10K rule applying across the whole file, I think this can be simplified, but it is late, so I will leave you with this:

SELECT jt.InvestorNumber,
jt.LoanNumber,
jt.ProspectusID,
jt.PropertyNumber,
jt.InvoiceNumber,
jt.InvoiceDate,
jt.Quarter,
jt.[Payment Type],
jt.Respread,
jt.[Completed Date],
jt.ConsolidatedStatement,
jt.[24HourRush],
jt.[1-3DayRush],
jt.[East-West],
c.Amount

FROM Job_TrackingCharges c 

RIGHT JOIN (SELECT j.InvestorNumber, 
                   j.LoanNumber, 
                   j.ProspectusID, 
                   j.PropertyNumber, 
                   j.InvoiceNumber, 
                   j.InvoiceDate, 
                   j.ReportingPeriod AS Quarter, 
                   Switch([OSAR_ImagetoVendor] Is Not Null,"OSAR Submitted by Sub for Data Entry",
                        [ReportingPeriod] Like "*Q*","Quarterly Financial Statement Spread",
                        [ReportingPeriod] Like "*YE*","Annual Financial Statement Spread") 
                        AS [Payment Type], 
                   "" AS Respread, 
                   j.[Signed Off] AS [Completed Date], 
                   j.ConsolidatedStatement, j.[24HourRush], 
                   j.[1-3DayRush], j.[East-West], 
                   
                   IIf(IsNull([OSAR_ImageToVendor]),IIf([ReportingPeriod] Like "*YE*","YE",
                        IIf([ReportingPeriod] Like "*Q*","Q","N/A")),"O_ITV") AS RptType, 
                        
                   IIf([24HourRush] And IsNull([OSAR_ImageToVendor]),"Rush24",
                        IIf([1-3DayRush] And IsNull([OSAR_ImageToVendor]),"Rush36","N/A")) AS Rush, 
                        
                   IIf((SELECT Count(*) FROM Job_Tracking)>10000 And IsNull([OSAR_ImageToVendor]),"10K","N/A") AS 10K
                   
            FROM Job_Tracking AS j 
            
             WHERE (((j.PropertyNumber)<>"SUM") 
                      AND ((j.InvoiceNumber) Is Null) 
                      AND ((j.[Signed Off])<[Select all records signed off before this date]) 
                      AND ((j.ConsolidatedStatement)<>-1) 
                      AND ((j.[East-West])="CMS-East")) 
                OR (((j.PropertyNumber)="SUM") 
                      AND ((j.InvoiceNumber) Is Null) 
                      AND ((j.[Signed Off])<[Select all records signed off before this date]) 
                      AND ((j.ConsolidatedStatement)=-1) 
                      AND ((j.[East-West])="CMS-East")))  AS jt 

ON (c.[10K] = jt.[10K]) 
AND (c.Rush = jt.Rush) 
AND (c.ReptType = jt.RptType);

http://lessthandot.com

 

[iuianj07]

Thanks!

It is working correctly... so the 10K per year count will still apply? I appreciate it very much, especially this last minute changes that you made.

Thanks again!

"I think this can be simplified, but it is late" -- I assume that you live somewhere in Europe to already be late...? Thanks sir for allotting time in helping me out.



So in case we need to debug this next year, I hope I could still get in touch with you! Thanks again!

 

[Remou]

Oops.

Change this:

IIf((SELECT Count(*) FROM Job_Tracking)>10000 And IsNull([OSAR_ImageToVendor]),"10K","N/A") AS 10K

To

IIf((SELECT Count(*) FROM Job_Tracking 
     WHERE j.[Signed Off] Between DateSerial(Year(Date()),1,1)   
       And DateSerial(Year(Date()),12,31))>10000 
       And IsNull([OSAR_ImageToVendor]),"10K","N/A") AS 10K

Always stop before midnight

http://lessthandot.com

 

[iuianj07]

Hello,

I tried to change to the above correction you made, however whenever I run the query, the database freezes up and Not Responding...

Is there any way you could help me again?

Thanks again...

 

[Remou]

Okay

SELECT jt.InvestorNumber,
jt.LoanNumber,
jt.ProspectusID,
jt.PropertyNumber,
jt.InvoiceNumber,
jt.InvoiceDate,
jt.Quarter,
jt.[Payment Type],
jt.Respread,
jt.[Completed Date],
jt.ConsolidatedStatement,
jt.[24HourRush],
jt.[1-3DayRush],
jt.[East-West],
c.Amount

FROM  (SELECT
                   jc.[10KYN], 
                   jc.ReptType, 
                   jc.Rush, 
                   jc.Amount

                   FROM Job_TrackingCharges jc
                  WHERE jc.[10KYN]=((SELECT Count(*) 
                                                        FROM Job_Tracking   
                                                        WHERE [Signed Off] Between DateSerial(Year(Date()),1,1)  
                                                                    And DateSerial(Year(Date()),12,31) )>=10))  AS c

RIGHT JOIN (SELECT
                   j.InvestorNumber, 
                   j.LoanNumber, 
                   j.ProspectusID, 
                   j.PropertyNumber, 
                   j.InvoiceNumber, 
                   j.InvoiceDate, 
                   j.ReportingPeriod AS Quarter, 
                   Switch([OSAR_ImagetoVendor] Is Not Null,"OSAR Submitted by Sub for Data Entry",
                        [ReportingPeriod] Like "*Q*","Quarterly Financial Statement Spread",
                        [ReportingPeriod] Like "*YE*","Annual Financial Statement Spread") 
                        AS [Payment Type], 
                   "" AS Respread, 
                   j.[Signed Off] AS [Completed Date], 
                   j.ConsolidatedStatement, j.[24HourRush], 
                   j.[1-3DayRush], j.[East-West], 
                   
                   IIf(IsNull([OSAR_ImageToVendor]),IIf([ReportingPeriod] Like "*YE*","YE",
                        IIf([ReportingPeriod] Like "*Q*","Q","N/A")),"O_ITV") AS RptType, 
                        
                   IIf([24HourRush] And IsNull([OSAR_ImageToVendor]),"Rush24",
                        IIf([1-3DayRush] And IsNull([OSAR_ImageToVendor]),"Rush36","N/A")) AS Rush
                        
             FROM Job_Tracking AS j 
            
             WHERE (((j.PropertyNumber)<>"SUM") 
                      AND ((j.InvoiceNumber) Is Null) 
                      AND ((j.[Signed Off])<[Select all records signed off before this date]) 
                      AND ((j.ConsolidatedStatement)<>-1) 
                      AND ((j.[East-West])="CMS-East")) 
                OR (((j.PropertyNumber)="SUM") 
                      AND ((j.InvoiceNumber) Is Null) 
                      AND ((j.[Signed Off])<[Select all records signed off before this date]) 
                      AND ((j.ConsolidatedStatement)=-1) 
                      AND ((j.[East-West])="CMS-East")))  AS jt 

ON  (c.Rush = jt.Rush) 
AND (c.ReptType = jt.RptType)

The Job_TrackingCharges table now looks like this:

[tt]
ID ReptType Rush Amount 10KYN
1 O_ITV N/A €12.00 No
2 YE N/A €50.00 No
3 Q N/A €45.00 No
4 YE Rush24 €75.00 No
5 Q Rush24 €67.50 No
6 YE Rush36 €62.50 No
7 Q Rush36 €56.25 No
8 YE N/A €50.00 Yes
9 Q N/A €42.50 Yes
10 YE Rush24 €75.00 Yes
11 Q Rush24 €63.75 Yes
12 YE Rush36 €62.50 Yes
13 Q Rush36 €53.13 Yes
14 O_ITV N/A €12.00 Yes[/tt]

The No/Yes column is an ordinary YesNo data type, but tickboxes don't translate to Tek-tips.

http://lessthandot.com

 

[iuianj07]

Hello Remou--

Thanks for the updated query and table, I did all of this and it looks like it's working correctly now.

Thank you again for your help, your patience and helpfulness is highly appreciated!

I'll let you know if there are any problems that arise.

Thanks again

 

[iuianj07]

Hi Remou-- I hope you see this and be able to help me out.

the query runs correctly, although there is one thing that came up, here is an example..

As of July 31st -- there were 8,951 loans completed, and as of August 15th -- there were 10,001 loans signed off..

When this query was ran today, all prices were at the discounted price because the total number of loans has already surpassed 10,000. The problem is, for all the loans that were completed before the total count is 10,000 should be with the original price.

Basically for loans that were completed before August 15th that is being invoiced, it should be priced as the original amounts(ex $45 for Q, $50 for YE etc) and the loans that were completed after August 15th, the price should be the discounted ones...

I hope it made sense... Please let me know if you need anything.

Thank you