I have a created a stored procedure which two parameters. InvoiceDate and BeginDate.
When I am giving Invoice Date in parameter. I should Get result for every present year, and past two years InvoiceDate. It should not be greater than InvoiceDate.
For Example; I have invoiceDate = 08/20/2016
The result should get like this for every year. For 2016: Invoice Date shouls not be greater than 08/20/2015
For 2015: InvoiceDate should not be greater than 08/20/2014
For 2014: InvoiceDate should not be greater than 08/20/2013.
My Stored Procedure: Begin Date is working Good.
ALTER PROCEDURE [dbo].[USR_MBR_RenewalStatistics_SP]
@BeginDate AS DATETIME
, @Invoicedate AS DATETIME
AS
BEGIN
DECLARE @dteRenewMonth AS DATETIME
DECLARE @dteRenewMonthPriorSegment1 AS DATETIME
DECLARE @dteRenewMonthPriorSegment2 AS DATETIME
DECLARE @intRenewWithinDays AS INTEGER
--DECLARE @datePrior1 as DATETIME
--DECLARE @datePrior2 as DATETIME
SET @dteRenewMonth = dbo.udfn_FirstOfMonth(@BeginDate, 0)
SET @dteRenewMonthPriorSegment1 = DATEADD(YEAR, -1, @dteRenewMonth)
SET @dteRenewMonthPriorSegment2 = DATEADD(YEAR, -2, @dteRenewMonth)
SET @intRenewWithinDays = DATEDIFF(DAY, @dteRenewMonth, @BeginDate)
SELECT
ORDER_DETAIL.SHIP_MASTER_CUSTOMER_ID AS MasterCustomerID,
ORDER_DETAIL.SHIP_SUB_CUSTOMER_ID AS SubCustomerID,
ORDER_DETAIL.SUBSYSTEM,RATE_CODE,
ORDER_DETAIL.ORDER_NO AS OriginalOrderNumber,
ORDER_DETAIL.ORDER_LINE_NO AS OriginalOrderLineNumber,
ORDER_DETAIL.ORDER_DATE AS OriginalOrderDate,
ORDER_DETAIL.INVOICE_DATE AS OrigialInvoiceDate,
ORDER_DETAIL.PRODUCT_CODE AS OriginalProductCode,
ORDER_DETAIL.LINE_STATUS_CODE AS OriginalLineStatusCode,
ORDER_DETAIL.FULFILL_STATUS_CODE AS OriginalFulfillStatusCode,
ORDER_DETAIL.CYCLE_BEGIN_DATE AS OriginalCycleBeginDate,
ORDER_DETAIL.CYCLE_END_DATE AS OriginalCycleEndDate,
ORDER_DETAIL.ACTUAL_TOTAL_AMOUNT AS OriginalPaid,
ORDER_DETAIL.MARKET_CODE AS OriginalMarketCode,
PRODUCT.SHORT_NAME AS OriginalProduct,
dbo.udfn_FirstOfMonth(CYCLE_BEGIN_DATE , 0) AS BeginDate,
RenewalInformation.Renewed,
RenewalInformation.Paid AS RenewalPaid,
RenewalInformation.MarketCode AS RenewalMarketCode,
RenewalInformation.OrderNumber AS RenewalOrderNumber,
RenewalInformation.OrderLineNumber AS RenewalOrderLineNumber
FROM
ORDER_DETAIL
INNER JOIN PRODUCT ON ORDER_DETAIL.PRODUCT_CODE = PRODUCT.PRODUCT_CODE
CROSS APPLY dbo.udtfn_RenewalWithinTimeframe(SHIP_MASTER_CUSTOMER_ID, SHIP_SUB_CUSTOMER_ID, ORDER_NO, dbo.udfn_FirstOfMonth(CYCLE_BEGIN_DATE, 0), @intRenewWithinDays) AS RenewalInformation
WHERE
(ORDER_DETAIL.PRODUCT_CODE LIKE 'SOC%')
AND (ORDER_DETAIL.SUBSYSTEM = 'MBR') AND(RATE_CODE = 'RENEW')
--AND (ORDER_DETAIL.LINE_STATUS_CODE in('A','C'))
--AND (ORDER_DETAIL.FULFILL_STATUS_CODE IN ('A', 'G', 'E', 'P'))
AND (dbo.udfn_FirstOfMonth(CYCLE_BEGIN_DATE, 0) IN (@dteRenewMonth, @dteRenewMonthPriorSegment1, @dteRenewMonthPriorSegment2 ) )
AND ORDER_DETAIL.INVOICE_DATE <= (@dteRenewMonth, @dteRenewMonthPriorSegment1, @dteRenewMonthPriorSegment2 )
AND ( ORDER_DETAIL.INVOICE_DATE IS NULL OR ORDER_DETAIL.INVOICE_DATE <= @Invoicedate)
END
Please any help is appreciated.Thanks