/* This query creates start and stop dates for delivery schedules and has all of the options in two sections,
OldorCurSchID, OldOrigSchedStartDate, OldSchedEndDate  or in NewScheduleID, NewSchedStartDate, NewSchedEndDate */
WITH delivery AS (
	SELECT st.SubscriptionID,
			st.TranNumber,
			st.TranTypeID,
			st.ReasonCode,
			dsc.OldScheduleID,
			LAG(st.TranDate) OVER (PARTITION BY st.SubscriptionID ORDER BY st.TranDate) AS OldSchedStartDate,
			st.TranDate-1 AS OldSchedEndDate,
			dsc.NewScheduleID,
			st.TranDate AS NewSchedStartDate,
			LEAD(st.TranDate) OVER (PARTITION BY st.SubscriptionID ORDER BY st.TranDate)-1 AS NewSchedEndDate
		FROM SubscriptionTran AS st LEFT JOIN DeliveryScheduleChange AS dsc
		ON st.TranNumber = dsc.TranNumber
		WHERE st.TranTypeID IN ('DelivSched', 'ComboChange', 'MoveOut')
			AND dsc.OldScheduleID IS NOT NULL
)

SELECT sub.SubscriptionID,
		CASE		/* When there are not any delivery schedule changes, uses the current schedule from the Subscription table */
			WHEN delivery.OldScheduleID IS NULL THEN sub.DeliveryScheduleID
			ELSE delivery.OldScheduleID
		END AS OldorCurSchID,
		CASE		/* When there is no start date, pulls in original start date for the customer.  This doesn't mean there weren't previous schedules, just none since 1-1-09 when transactions begin*/
			WHEN delivery.OldSchedStartDate IS NULL THEN sub.OriginalStartDate
			ELSE delivery.OldSchedStartDate
		END AS OldOrigSchedStartDate,
		delivery.OldSchedEndDate,
		delivery.NewScheduleID,
		delivery.NewSchedStartDate,
		delivery.NewSchedEndDate,
		delivery.TranNumber,
		delivery.TranTypeID,
		delivery.ReasonCode,
		sub.Subscriber AS CurrentSubscriber,
		sub.DeliveryScheduleID AS CurrentDelSched,
		sub.OriginalStartDate
	FROM Subscription AS sub
	LEFT JOIN delivery
		ON sub.SubscriptionID = delivery.SubscriptionID
	WHERE sub.ProductID = 'PM'	/* Limits to Buffalo News */
		ORDER BY sub.SubscriptionID