Am trying to calculate due date using invoice date by the following constraints
will have two text boxes one is to enter day of the month and another is to enter the day before due month
if the day of the month is 4 and day before due month is 5 we have to calculate the values by subtracting 4 and 5 if the subtracted value is -ve 1 and if the invoice date entered is 11/13/2016 so that the due date must be 12/04/2017 (here 04 is the day of month)
if the day of the month is 15 and day before due month is 5 and the subtracted value is 10 (not negative) the invoice date entered is 11/13/2016 means the due date must be 12/15/2016 (here 15 is the day of month) if the invoice date is less than or equal to subtracted value ie 10 like invoice date 11/09/2016 then the invoice date must be 11/15/2016
Note the above constraints must also satisfy the leap year while calculating the fue dates
Please help me am new bee to SQL server functions
The Code which I have Tried
DECLARE @INVOICEDATE DATETIME ='02/01/2016'
,@TENANTID BIGINT=29
,@PAYMENTTERMID BIGINT=2
BEGIN
DECLARE @DUEDATE DATETIME
DECLARE @ACTUALDUEDATE BIGINT
DECLARE @CALCULATEDDATE DATETIME
DECLARE @PAYMENTTYPES BIGINT
DECLARE @DAYSOFMONTH BIGINT
DECLARE @DAYSAFTERDUEDAY BIGINT
DECLARE @NOOFDAYS BIGINT
SELECT @PAYMENTTYPES = PAYMENT_TYPES
,@NOOFDAYS = NUMBER_OF_DAYS
,@DAYSOFMONTH = DAY_OF_MONTH
,@DAYSAFTERDUEDAY = DAYS_AFTER_DUE_DAY
FROM XC_PAYMENT_TERMS_MASTER
WHERE TENANT_ID = @TENANTID
AND PAYMENT_TERM_ID = @PAYMENTTERMID
IF @PAYMENTTYPES = 1
BEGIN
SET @CALCULATEDDATE = DATEADD(dd, @NOOFDAYS, @INVOICEDATE)
END
ELSE IF @PAYMENTTYPES = 2
BEGIN
SET @ACTUALDUEDATE = @DAYSOFMONTH - @DAYSAFTERDUEDAY
IF 1 = (
IIF(DATEPART(dd, (
EOMONTH(CONCAT (
DATEPART(yyyy, @INVOICEDATE)
,'0201'
))
)) = 29, 1, 0)
)
--LEAP YEAR
BEGIN
IF(@ACTUALDUEDATE <= 0)
BEGIN
IF DATEPART(dd, @INVOICEDATE) = 30
AND DATEPART(mm, @INVOICEDATE) =01
BEGIN
SET @CALCULATEDDATE=DATEADD(dd, 31, @INVOICEDATE)
END
ELSE IF DATEPART(dd, @INVOICEDATE) = 31
AND DATEPART(mm, @INVOICEDATE) =01
BEGIN
SET @CALCULATEDDATE=DATEADD(dd, 31, @INVOICEDATE)
END
ELSE
BEGIN
IF @DAYSOFMONTH = 30 OR @DAYSOFMONTH =31
BEGIN
SET @CALCULATEDDATE= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0, @INVOICEDATE)+2,0))
END
ELSE
BEGIN
DECLARE @NEWNEGATIVEDATE DATETIME
SET @NEWNEGATIVEDATE = DATEADD(DAY, @DAYSOFMONTH - DATEPART(DAY, @INVOICEDATE), @INVOICEDATE);
SET @DUEDATE = DATEADD(mm, 1, @NEWNEGATIVEDATE)
END
END
END
ELSE IF(@ACTUALDUEDATE > 0)
BEGIN
SELECT 2
DECLARE @COMBINEDDATE DATETIME
SET @COMBINEDDATE=DATEADD(DAY, @ACTUALDUEDATE - DATEPART(DAY, @INVOICEDATE), @INVOICEDATE);
SELECT @INVOICEDATE AS INVDATE
SELECT @COMBINEDDATE AS COMBDATE
IF @INVOICEDATE > @COMBINEDDATE
BEGIN
SELECT 115 AS TRUE
DECLARE @NEWDATE DATETIME
SET @NEWDATE = DATEADD(DAY, @DAYSOFMONTH - DATEPART(DAY, @INVOICEDATE), @INVOICEDATE);
SET @DUEDATE = DATEADD(mm, 1, @NEWDATE)
SELECT @DUEDATE AS DATES
END
ELSE
BEGIN
SET @DUEDATE=DATEADD(DAY, @DAYSOFMONTH - DATEPART(DAY, @INVOICEDATE), @INVOICEDATE);
SELECT @DUEDATE AS DATEDUE
END
END
END
ELSE
--NOT LEAP YEAR
BEGIN
END
END
END
Finally Solved it by creating the following functions
CREATE FUNCTION [dbo].[XC_CALCULATE_DUE_DATE] (
@INVOICEDATE DATETIME
,@TENANTID BIGINT
,@PAYMENTTERMID BIGINT
)
RETURNS DATETIME
AS
BEGIN
DECLARE @DAYOFMONTH BIGINT
DECLARE @DAYOFNEXTMONTH BIGINT
DECLARE @PAYMENTTYPES BIGINT
DECLARE @NOOFDAYS BIGINT
SELECT @PAYMENTTYPES = PAYMENT_TYPES
,@NOOFDAYS = NUMBER_OF_DAYS
,@DAYOFMONTH = DAY_OF_MONTH
,@DAYOFNEXTMONTH = DAYS_AFTER_DUE_DAY
FROM XC_PAYMENT_TERMS_MASTER
WHERE TENANT_ID = @TENANTID
AND PAYMENT_TERM_ID = @PAYMENTTERMID
DECLARE @DIFFERENCE BIGINT = ISNULL(@DAYOFMONTH, 0) - ISNULL(@DAYOFNEXTMONTH, 0)
DECLARE @DUEDATE DATETIME
DECLARE @ACTUALDUEDATE BIGINT
DECLARE @CALCULATEDDATE DATETIME
IF @PAYMENTTYPES = 1
BEGIN
SET @DUEDATE = DATEADD(dd, @NOOFDAYS, @INVOICEDATE)
END
ELSE IF @PAYMENTTYPES = 2
BEGIN
DECLARE @LEAPYEAR TINYINT = IIF(DATEPART(dd, (
EOMONTH(CONCAT (
DATEPART(yyyy, @INVOICEDATE)
,'0201'
))
)) = 29, 1, 0)
IF @DIFFERENCE <= 0
BEGIN
DECLARE @STARTINGDATE DATETIME
DECLARE @NEWDATE DATETIME
DECLARE @NEXTMONTH DATETIME
IF 1 = @LEAPYEAR
BEGIN
SET @STARTINGDATE = DATEADD(month, DATEDIFF(month, 0, @INVOICEDATE), 0)
SET @NEXTMONTH = DATEADD(MONTH, 1, @STARTINGDATE)
IF 1 = DATEPART(MONTH, @INVOICEDATE)
BEGIN
IF @DAYOFMONTH = 30
BEGIN
SET @DUEDATE = DATEADD(month, DATEDIFF(month, 0, DATEADD(MONTH, 2, @INVOICEDATE)), 0)
END
ELSE IF @DAYOFMONTH = 31
BEGIN
SET @DUEDATE = DATEADD(DAY, 1, DATEADD(month, DATEDIFF(month, 0, DATEADD(MONTH, 2, @INVOICEDATE)), 0))
END
ELSE
BEGIN
SET @DUEDATE = DATEADD(DAY, @DAYOFMONTH - 1, @NEXTMONTH)
END
END
ELSE
BEGIN
SET @DUEDATE = DATEADD(DAY, @DAYOFMONTH - 1, @NEXTMONTH)
END
END
ELSE
BEGIN
SET @STARTINGDATE = DATEADD(month, DATEDIFF(month, 0, @INVOICEDATE), 0)
SET @NEXTMONTH = DATEADD(MONTH, 1, @STARTINGDATE)
IF 1 = DATEPART(MONTH, @INVOICEDATE)
BEGIN
IF @DAYOFMONTH = 29
BEGIN
SET @DUEDATE = DATEADD(month, DATEDIFF(month, 0, DATEADD(MONTH, 2, @INVOICEDATE)), 0)
END
ELSE IF @DAYOFMONTH = 30
BEGIN
SET @DUEDATE = DATEADD(DAY, 1, DATEADD(month, DATEDIFF(month, 0, DATEADD(MONTH, 2, @INVOICEDATE)), 0))
END
ELSE IF @DAYOFMONTH = 31
BEGIN
SET @DUEDATE = DATEADD(DAY, 2, DATEADD(month, DATEDIFF(month, 0, DATEADD(MONTH, 2, @INVOICEDATE)), 0))
END
ELSE
BEGIN
SET @DUEDATE = DATEADD(DAY, @DAYOFMONTH - 1, @NEXTMONTH)
END
END
ELSE
BEGIN
SET @DUEDATE = DATEADD(DAY, @DAYOFMONTH - 1, @NEXTMONTH)
END
END
END
ELSE
BEGIN
DECLARE @SDATES DATETIME = DATEADD(month, DATEDIFF(month, 0, @INVOICEDATE), 0)
DECLARE @NMONTH DATETIME = DATEADD(MONTH, 1, @SDATES)
IF DATEPART(DD, @INVOICEDATE) > @DIFFERENCE
BEGIN
IF @DAYOFMONTH > 30
AND DATEPART(DD, @INVOICEDATE) <> @DIFFERENCE
BEGIN
SET @DUEDATE = DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, @NMONTH) + 1, 0))
END
ELSE
BEGIN
SET @DUEDATE = DATEADD(DAY, @DAYOFMONTH - 1, @NMONTH)
END
END
ELSE
BEGIN
IF @DAYOFMONTH > 30
AND DATEPART(DD, @INVOICEDATE) <> @DIFFERENCE
BEGIN
SET @DUEDATE = DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, @NMONTH) + 1, 0))
END
ELSE IF @DAYOFMONTH > 30
AND DATEPART(DD, @INVOICEDATE) = @DIFFERENCE
BEGIN
SET @DUEDATE = DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, @SDATES) + 1, 0))
END
ELSE
BEGIN
SET @DUEDATE = DATEADD(DAY, @DAYOFMONTH - 1, @SDATES)
END
END
IF 1 = @LEAPYEAR
BEGIN
IF 1 = DATEPART(MONTH, @INVOICEDATE)
AND DATEPART(DD, @INVOICEDATE) <> @DIFFERENCE
BEGIN
IF @DAYOFMONTH > 29
BEGIN
SET @DUEDATE = DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, @NMONTH) + 1, 0))
END
END
END
ELSE
BEGIN
IF 1 = DATEPART(MONTH, @INVOICEDATE)
AND DATEPART(DD, @INVOICEDATE) <> @DIFFERENCE
BEGIN
IF @DAYOFMONTH >= 29
BEGIN
SET @DUEDATE = DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, @NMONTH) + 1, 0))
END
END
END
END
END
RETURN @DUEDATE
END