sqldatetime - Need assistance in calculating Due Date using SQL Server Functions -
am trying calculate due date using invoice date following constraints
will have 2 text boxes 1 enter day of month , enter day before due month
if day of month 4 , day before due month 5 have calculate values subtracting 4 , 5 if subtracted value -ve 1 , if invoice date entered 11/13/2016 due date must 12/04/2017 (here 04 day of month)
if day of month 15 , day before due month 5 , subtracted value 10 (not negative) invoice date entered 11/13/2016 means due date must 12/15/2016 (here 15 day of month) if invoice date less or equal subtracted value ie 10 invoice date 11/09/2016 invoice date must 11/15/2016
note above constraints must satisfy leap year while calculating fue dates
please me new bee sql server functions
the code 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 xc_payment_terms_master tenant_id = @tenantid , 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 , datepart(mm, @invoicedate) =01 begin set @calculateddate=dateadd(dd, 31, @invoicedate) end else if datepart(dd, @invoicedate) = 31 , 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 invdate select @combineddate combdate if @invoicedate > @combineddate begin select 115 true declare @newdate datetime set @newdate = dateadd(day, @daysofmonth - datepart(day, @invoicedate), @invoicedate); set @duedate = dateadd(mm, 1, @newdate) select @duedate dates end else begin set @duedate=dateadd(day, @daysofmonth - datepart(day, @invoicedate), @invoicedate); select @duedate datedue end end end else --not leap year begin end end end
finally solved creating following functions
create function [dbo].[xc_calculate_due_date] ( @invoicedate datetime ,@tenantid bigint ,@paymenttermid bigint ) returns datetime 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 xc_payment_terms_master tenant_id = @tenantid , 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 , 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 , datepart(dd, @invoicedate) <> @difference begin set @duedate = dateadd(s, - 1, dateadd(mm, datediff(m, 0, @nmonth) + 1, 0)) end else if @dayofmonth > 30 , 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) , 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) , 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
Comments
Post a Comment