CODEWITHSUNDEEP
c#sqlwinformsms-access
rahularyansharma
rahularyansharma

Reputation: 10775

get the amount from a price period table

I have a table price period tblPricePeriod in which I have the Amount to be charged between two dates for a particular apartment.

I have also StartDate and EndDate columns for the price period with ApartmentId.

Below is the structure of my table:

enter image description here

Now I want to calculate to charge the total amount for days of booking.

For example, if someone stays from 4feb to 7 feb then he has to pay according to following scheme:

4-     10
5-     10
6-     10  
7    -     nothing as this is checkout date 
-----------------------------------------------------
totamt--- 30$

I have startdate and enddate and apartmentid as parameter, I am using ms access db.

Now I am using c# code to traverse this with follow code but not successed.

I am getting the amount for all the dates one by one and adding them in one global variable.

The code is as follows:

decimal amount = 0;

// loop to traverse all days between start date and end date 
for (DateTime d = Convert.ToDateTime(StartDate); d < Convert.ToDateTime(EndDate); d = d.AddDays(1))
{
    string priceCalc = "select Amount from tblPricePeriod where ApartmentId=" + ApartmenId + " and cdate(StartDate) <='" + d.ToShortDateString() + "' and cdate(EndDate) >= '" + d.ToShortDateString() + "'";
    DataSet dsPriceCalc = SqlHelper.ExecuteDataset(Connection.ConnectionString, CommandType.Text, priceCalc);
    if (dsPriceCalc.Tables[0].Rows.Count > 0)
    {
        amount = amount + Convert.ToDecimal(dsPriceCalc.Tables[0].Rows[0]["Amount"]);
    }
}

Upvotes: 0

Views: 227

Answers (3)

Clint Good
Clint Good

Reputation: 850

SELECT
  SUM(( 
    IIF(EndDate < @EndDate, EndDate, IIF(@EndDate < StartDate, StartDate, @EndDate)) -
    IIF(StartDate > @StartDate, StartDate, IIF(@StartDate > EndDate, EndDate, @StartDate))
  ) * Amount)
FROM tblPricePeriod

This takes the same sort of approach as GarethD. It's certainly shorter but you could argue which shows the intent clearer. I couldn't test this as I don't have access but I believe it has the IIF statement. The @EndDate parameter needs to be your end date - 1. So in your example I would pass @End Date = 6 Feb.

The IIF statements basically calculate the number of days that need to be counted within each period.

Upvotes: 0

GarethD
GarethD

Reputation: 69789

You could adopt an entirely SQL approach:

SELECT  SUM(TotalAmount) AS Amount
FROM    (   SELECT  Amount * (1 + DATEDIFF('d', @StartDate, EndDate))  AS TotalAmount,  
                    (1 + DATEDIFF('d', @StartDate, EndDate)) AS Days, 
                    Amount AS DailyRate
            FROM    tblPricePeriod
            WHERE   @StartDate >= StartDate
            AND     @StartDate <= EndDate
            AND     @EndDate > EndDate
            AND     ApartmentID = @ApartmentID
            UNION ALL
            SELECT  Amount * (DATEDIFF('d', @StartDate, @EndDate)),  DATEDIFF('d', @StartDate, @EndDate), Amount
            FROM    tblPricePeriod
            WHERE   @StartDate >= StartDate
            AND     @StartDate <= EndDate
            AND     @EndDate >= StartDate
            AND     @EndDate <= EndDate
            AND     ApartmentID = @ApartmentID
            UNION ALL
            SELECT  Amount * (1 + DATEDIFF('d', StartDate, EndDate)),  (1 + DATEDIFF('d', StartDate, EndDate)) , Amount
            FROM    tblPricePeriod
            WHERE   @StartDate < StartDate
            AND     @EndDate > EndDate
            AND     ApartmentID = @ApartmentID
            UNION ALL
            SELECT  Amount * (DATEDIFF('d', StartDate, @EndDate)),  DATEDIFF('d', StartDate, @EndDate) , Amount
            FROM    tblPricePeriod
            WHERE   @StartDate < StartDate
            AND     @EndDate <= EndDate
            AND     @EndDate > StartDate
            AND     ApartmentID = @ApartmentID
        ) AS Data

Since Access does not allow comments I've not neen able to annotate the query. The subquery has 4 parts, the top part is for when the Price Period End Date occurs during a visit, the second for when the visit falls entirely within 1 price period, the 3rd for when the visit crosses an entire price period, and the 4th for when the price period starts during a visit. Hope this is clear.

All you would then need to do is add the parameters @ApartmentID, @StartDate and @EndDate to your ExecuteDataset Method which is a better approach than concatenating strings anyway.

Upvotes: 0

Viper
Viper

Reputation: 2236

Here is an example of the SUM Function in Sql:

SELECT SUM(salary) as "Total Salary"
FROM employees
WHERE salary > 25000;

//EDIT:

Instead of looping over your DateTimes you can select the Sum of the Amount directly:

DateTime dtStartDate = Convert.ToDateTime(StartDate);
DateTime dtEndDate = Convert.ToDateTime(EndDate); // maybe -1 day 

string priceCalc = "select SUM( Amount ) from tblPricePeriod where ApartmentId=" + ApartmenId + " and cdate(StartDate) <= '" + dtStartDate.ToShortDateString( ) + "' and cDate(EndDate) >= '" + dtEndDate.ToShortDateString( ) + "'";

Upvotes: 1

Related Questions