Billing End Dates

HelenLLL

Registered User.
Local time
Today, 03:13
Joined
Mar 10, 2014
Messages
18
:banghead:I have a Contract Table:
Order ID
Client Name
Unit Daily Rate in GBP
No of Unit
Contract Start Date
Contract End Date

Requirement: To run a billing run every Month End. e.g. I will be running Feb-2014 billing report on 03-March-2014. This mean every month Billing End Date is different.

What is the best way of set up this query? - Please HELP!!!
 
The best way to do this is to explain further exactly what your issue is. It would also be helpful if gave congruent information: you listed a table and its fields, then in your description not only did you not reference that table or any fields, you told us about a field (Billing End Date) that we have no frame of reference for.
 
You mean, any time you run the Query in current month it should run from the start of previous month till end of previous month? Or in other words if you run the Query in March it should give result for 01/Feb/2014 - 28/Feb/2014?
 
I mean, I want to run Feb-2014 billing report (01/02/2014-28/02/2014) on any date as I like. I can run it on 03/03/2014 or even 03/03/2015. But I expect I have same report.

The Billing run Start and End dates I leave if for user to type in every time before they require a report.
 
Define billing date? You have contract start date and contract end date. so what is billing date?
 
Define billing date? You have contract start date and contract end date. so what is billing date?

Is there an echo in here?
 
For example, Contract ABC,The contract term is 5 years. However, the billing report can be requested on any day e.g. Jan-2014 (01/01/2014-31/01/2014), Feb-2014(01/02/2014-28/02/2014), It also can be 01/03/2014-10/03/2014 (only 10 days billing report.). So I want to leave the billing START and END Dates for the user to type in before they run a report.
 
No worries, I just meant I did the same thing you did--jumped into setting up the criteria she will need, then when I went to see what the exact field names to use were I realized she was using a few different terms.

The worst part is she still doesn't see it. That's one of my biggest pet peeves--synonyms get so ingrained people don't even realize they are using different terms for the same exact thing to the confusion of the person they are trying to communicate to.

I used to work at a hospital and everytime people would ask 'What's the code for X?' I would just cackle hysterically and say 7 random characters (7X42.3M) because everything had 11 different codes (billing, diagnosis, procedure, medicare...).
 
Last edited:
Hi Plog,

To answer your question, the Contract
Start Date: 02/01/2014
End Date: 01/01/2019
Billing Date: it varies depending on user's requirement, it can be 31/01/2014, or 02/02/2014 or 31/12/2014

I used DateDiff, but a problem: the billing date changes depending on what user requests. So, DataDiff is no use here. Any ideas?
 
You did not answer my question. You listed a table and its fields, then you mentioned a billing date which I have no frame of reference for in regards how it relates to your table.

Suppose I have a car table, with these fields:

tblCars
Make
Model
Doors
Price

Now, I ask you to give me a list of all the orange cars.

We have no idea how billing date relates to the table of information you have given us. Please provide a specific example. Give us starting date from your table (including field and table names), then what specific data from that sample set should be returned. Use this format for posting data:

TableNameHere
Field1Name, Field2Name, Field3Name
David, 14, 3/4/2011
Sally, 31, 3/6/2011
 
Hi Plog,

The Billing Dates are not in any table. Because This is being typed in by a user. I only know the Contract Start and End dates and a Daily Charge Rate. e.g.

Contract Name: AAA
Start Date: 01/02/2014
End Date: 31/01/2024
Daily Charge Rate: £10

If I want to run a billing report A :between 10/02/2014 to 31/03/2014
report B: Between 08/02/2014 to 28/02/2014

I set a Criteria: >=[Billing Run Date DD/MM/YYYY], which then pop out a screen to ask user to enter a date of Billing Start Date.

Then I used DataDiff to calculate No of day live, then *Daily Charge Rate.

Did you answer your question?
 
Its getting a little clearer, but I am going to need sample data liked I asked for. Give me a few records for starting data, then what the query should produce based on that sample data.
 
Here you go:

ContractIDClientNameStart DateEnd DateDaily Charge RateContract AClient A12/03/201312/03/2023£10.00Contract BClient B09/04/201309/04/2017£5.00Contract CClient C31/12/201231/12/2019£8.00Contract DClient D04/05/201304/05/2023£12.00Contract EClient E17/02/201417/02/2016£7.00Contract FClient F03/09/201303/09/2020£12.00Contract GClient G20/12/201320/03/2014£5.00Require:Billing Report A: 01/01/2014 - 31/01/2014Billing Report B: 10/01/2014 - 28/02/2014
 
ContractID Client StartDate EndDate DailyChargeUnit
Contract A Client A 09/09/2013 09/09/2023 10
Contract B Client B 12/01/2014 31/12/2019 10
Contract C Client C 02/02/2014 02/02/2019 10
Contract D Client D 01/01/2013 31/01/2013 10

Require:
Report A: 01/01/2014 - 10/02/2014
Report B: 01/03/2014 - 31/03/2013
 
Halfway there. What data should result from that?
 
Last edited:
Could you please help me help you? See post #13 and #16 in this thread and provide all that I ask for.
 
I want to see these:

Client Name
Order ID
Unit Charging Rate
Billing Start Date (but not the Contract Start Date)
Billing End Date (Not Contract End Date)
Contract End Date

E.g. Report A: Billing Report 01/01/2014 - 10/02/2014--->The Billing Start Date is 01/01/2014, End Date is 10/02/2014
 

Users who are viewing this thread

Back
Top Bottom