CREATE PROCEDURE [dbo].[USR_MBR_RenewalStatistics_SP_Modified]
@dteRenewalDate AS DATETIME, @Invoicedate AS DATETIME
AS
BEGIN
DECLARE @dteRenewMonth AS DATETIME
DECLARE @dteRenewMonthPriorSegment1 AS DATETIME
DECLARE @dteRenewMonthPriorSegment2 AS DATETIME
DECLARE @intRenewWithinDays AS INTEGER
SET @dteRenewMonth = dbo.udfn_FirstOfMonth(@dteRenewalDate, 0)
SET @dteRenewMonthPriorSegment1 = DATEADD(YEAR, -1, @dteRenewMonth)
SET @dteRenewMonthPriorSegment2 = DATEADD(YEAR, -2, @dteRenewMonth)
SET @intRenewWithinDays = DATEDIFF(DAY, @dteRenewMonth, @dteRenewalDate)
SELECT
ORDER_DETAIL.SHIP_MASTER_CUSTOMER_ID AS MasterCustomerID,
ORDER_DETAIL.SHIP_SUB_CUSTOMER_ID AS SubCustomerID,
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_END_DATE , 0) AS RenewDate,
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 (dbo.udfn_FirstOfMonth(CYCLE_BEGIN_DATE, 0) IN (@dteRenewMonth, @dteRenewMonthPriorSegment1, @dteRenewMonthPriorSegment2 ) ) AND @Invoicedate = ORDER_DETAIL.INVOICE_DATE
END
EXECUTE [USR_MBR_RenewalStatistics_SP_Modified] '01/01/2017', '03/27/2014'
How do i get all dates till the date i provided in stored procedure parameter. like till 03/27/2014.
Whenever i provide invoicedate, I need all dates till that date ??