How to get total bill for the month from a date range (1 Viewer)

[abzalali]

Truly it's out of my frequency Because I need to generate a monthly bill from this recordset with between date parameter.

interval_start	interval_end	rate	space_sft
09/09/2016	08/09/2017	95	312
09/09/2017	08/09/2018	105	312
09/09/2018	08/09/2019	115	312

Such as my param_startdate 01/09/2017 and param_enddate 30/09/2017. With this param I want to make a bill like

date                                rate             space_sft             amount
1 Sep 2017  to 8 Sep 2017            95               312            (95*312)/30*8
9 Sep 2017  to 30 Sep 2017           105              312           (105*312)/30*22

Condition: rate come from the row where param date match if param match in a single row then bills also cover by single rate.

Any idea would be appreciated.

 

[mike60smart]

Hi abzalali

Why have you got the results split into 2 date Ranges:-

1 Sept - 8 Sept
9 Sept - 30 Sept ???

 

[Ranman256]

you make a query with both tables, DO NOT JOIN them,
then limit the dates to inside the range.

you would build a form with date range boxes if you want to pick the range.

 

[abzalali]

Because param_startdate goes in previous year agreement range and param_enddate comes another year agreement range that why we need to calculate the bill two different years rate.

If both param in between a single year range then we need not split this anymore.

 

[abzalali]

@Ranman256, could you please brief for the purpose of tExpense subquery? That would help me to make this subquery.

 

[plog]

The way I would accomplish this is with a subquery and a few custom functions. Here is what the SQL of the subquery should be like:

SELECT get_BillStart([interval_start], [param_startdate]) AS StartDate
	, get_BillEnd([interval_end], [param_enddate]) AS EndDate
	, [rate]
	, [space_sft]
	, [rate]*[space_sft] AS MonthlyAmount
	, get_DaysInMonth([param_startdate]) AS DaysInMonth
FROM YourTableNameHere
WHERE ([param_startdate]<=[interval_end]) AND ([param_enddate]>=[interval_start])

That limits the results to the correct number of records and prepares your data for the further calculations you require. It also means you will need to write 3 custom functions:

get_BillStart() - this will determine the first date of your bill range by taking the lower of the interval_start and param_start dates

get_BillEnd() - this will determine the last date of your bill range by taking the higher of the interval_end and param_end dates

get_DaysInMonth() - this will determine how many days are in the month you are billing (using the param_start] date). That way you can multiple your [MonthlyAmount] to determine the exact [amount]. If your parameters span more than 1 month, this breaks.

You would then take that subquery and use it as the source of another query where you would build your final results using math and DateDiff functions.

 

[Ranman256]

my tExpense was a table with expense entries.
I didnt know what yours looked like.
But you can make a query to pull only those items inside the date range.

 

[abzalali]

@plog, thank you so much, I'm really sorry that I delayed replying. but finally get the result the way you show.