Michael George Michael George - 1 year ago 92
SQL Question

T-SQL : calculate by month and day

I have to be over complicating this I think. First off I wanted to greatly thank everyone for helping me with this.

Ok so what I am trying to do is calculate the following correctly [Tenant Pro-Rated Rent Custom] and it is not working properly. I can not figure out how to get this to work.

Rules are:

  1. All months are divided into 30 days... for Pro Rated rent calculation (Including February)

  2. Months where there is a 31 day and the contract was signed on the 31st day that day will be free on the contract. So Only include next month's rent. (T.sRent )

  3. Months where there is a 31st day and the contract was signed before the 31st the 31st is included in the prorated days of calculation... even though it was divided by 30 days.

  4. February even though there is 28 or 29 days, it gets calculated as if it had 30 days. Also the last day of the month only includes next months rent.

  5. The 30th and 31st of every month includes next months rent in the prorate.

I am fine with building a function to handle this.

Here is a simplified SQL Statement I can not get to work:

Declare @LeaseFromDate as DateTime
Declare @Rent as decimal (10,2)

Set @LeaseFromDate = '9/10/2016'
Set @Rent = '1000.00'

--When isnull(DATEDIFF(DAY,DATEADD(DAY, 0, DATEADD(m, ((year(@LeaseFromDate) - 1900) * 12) + month(@LeaseFromDate) - 1, 0)),
--DATEADD(DAY, 0, DATEADD(m, ((year(@LeaseFromDate) - 1900) * 12) + month(@LeaseFromDate), 0))
-- ) - day(@LeaseFromDate),0) = false
-- Then 0
When (month(@LeaseFromDate) in (1,3,5,7,8,10,12) and (Day(@LeaseFromDate)) = 31) -- get this day free just add next months rent to the prorate
Then CAST(ROUND((@Rent / 30),2) AS decimal(10,2))+3333333
When (month(@LeaseFromDate) in (1,3,5,7,8,10,12) and (Day(@LeaseFromDate)) = 30) --Add Next Months rent to the prorate
Then CAST( ( (ROUND((@Rent / 30) * ((30 - day(@LeaseFromDate))+1),2)) + @Rent ) AS decimal(10,2) )+4444444
When (month(@LeaseFromDate) in (1,3,5,7,8,10,12)) --Just Prorate the rent
Then CAST(ROUND((@Rent / 30) * ((30 - day(@LeaseFromDate))+1),2) AS decimal(10,2))+5555555

When ((Day(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@LeaseFromDate)+1,0)))) <= (Day(@LeaseFromDate)) and month(@LeaseFromDate) in (2,4,6,9,11))
THEN CAST( ( (ROUND((@Rent / 30) * ((30 - day(@LeaseFromDate))+1),2)) + @Rent ) AS decimal(10,2) )+1111111
When (month(@LeaseFromDate) in (2,4,6,9,11) and (Day(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@LeaseFromDate)+1,0)))) <= (Day(@LeaseFromDate)-1))
THEN CAST(ROUND((@Rent / 30) * ((30 - day(@LeaseFromDate))+1),2) AS decimal(10,2))+2222222

Else 0
end as [Tenant Pro-Rated Rent Custom]

Answer Source

I took a pretty wild guess at this...

I am assuming the +111111 and +222222 are just so you can see which case is firing... if not, I'll delete this answer.

I broke this down into determining the days in the month, and date of signing, and then how many days are left from there for proration. If they sign on 31st, we do nothing, if they sign before 31st, we add a day. If that nets a value over 30, we just set it to 30.

Then I determined if we needed to add next months rent based on the date of signing.

Once you have all that, the calculations are pretty easy.

/* Set User Variables */
SET @LeaseFromDate = '2016-10-30'
SET @Rent = 1000.00

/* Determine Key Values */
DECLARE @DaysLeftInMonth INT, @LastOfMonth DATETIME, @DaysInMonth INT
SET @LastOfMonth = DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,0,@LeaseFromDate)+1,0))
SET @DaysInMonth = DATEPART(DD,@LastOfMonth)
SET @DaysLeftInMonth = DATEPART(DD,@LastOfMonth)-DATEPART(DD,@LeaseFromDate)

/* Calculate Days Left in Month to Prorate */
DECLARE @DaysToProrate INT, @AddNextMonth BIT
SET @DaysToProrate = @DaysLeftInMonth

/* If 31 days in month, and leasing from 31st, free day*/
IF DATEPART(DD,@LastOfMonth)=31 AND DATEPART(DD,@LeaseFromDate)=31
    PRINT 'Free Day'
/* If 31 days in month, and leasing before 31st add a day */
IF DATEPART(DD,@LastOfMonth)=31 AND DATEPART(DD,@LeaseFromDate)<31
    SET @DaysToProrate = @DaysToProrate+1

/* If we're over 30 days, reduce to 30 days */
IF @DaysToProrate > 30 SET @DaysToProrate=30

/* If renting on 30/31 of month or from Feb28/29 then add a month to rent calculation */
IF DATEPART(DD,@LeaseFromDate) IN (30,31) OR (DATEPART(MM,@LeaseFromDate)=2 AND DATEPART(DD,@LeaseFromDate) IN (28,29))
    SET @AddNextMonth = 1
    SET @AddNextMonth = 0

/* Show our Values */
--SELECT @DaysToProrate, @AddNextMonth

/* Do the Math */
SELECT CONVERT(DECIMAL(10,2),(@Rent/30.00)*@DaysToProrate), 'Prorated Amount'
SELECT CONVERT(DECIMAL(10,2),@Rent*@AddNextMonth), 'Next Months Inclusion'

/* Output */
       + (@Rent*@AddNextMonth)), 'Total Due At Signing'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download