INSERT INTO #DateDim VALUES('09/01/2013',1)INSERT INTO #DateDim VALUES('09/02/2013',1)INSERT INTO #DateDim VALUES('09/03/2013',0)INSERT INTO #DateDim VALUES('09/04/2013',0)INSERT INTO #DateDim VALUES('09/05/2013',0)INSERT INTO #DateDim VALUES('09/06/2013',0)INSERT INTO #DateDim VALUES('09/07/2013',1)INSERT INTO #DateDim VALUES('09/08/2013',1)INSERT INTO #DateDim VALUES('09/09/2013',0)INSERT INTO #DateDim VALUES('09/10/2013',0)INSERT INTO #DateDim VALUES('09/11/2013',0)INSERT INTO #DateDim VALUES('09/12/2013',0)INSERT INTO #DateDim VALUES('09/13/2013',0)INSERT INTO #DateDim VALUES('09/14/2013',1)INSERT INTO #DateDim VALUES('09/15/2013',1)INSERT INTO #DateDim VALUES('09/16/2013',0)INSERT INTO #DateDim VALUES('09/17/2013',1)INSERT INTO #DateDim VALUES('09/18/2013',0)INSERT INTO #DateDim VALUES('09/19/2013',0)INSERT INTO #DateDim VALUES('09/20/2013',0)INSERT INTO #DateDim VALUES('09/21/2013',1)INSERT INTO #DateDim VALUES('09/22/2013',1)INSERT INTO #DateDim VALUES('09/23/2013',1)INSERT INTO #DateDim VALUES('09/24/2013',0)INSERT INTO #DateDim VALUES('09/25/2013',0)INSERT INTO #DateDim VALUES('09/26/2013',0)INSERT INTO #DateDim VALUES('09/27/2013',0)INSERT INTO #DateDim VALUES('09/28/2013',1)INSERT INTO #DateDim VALUES('09/29/2013',1)INSERT INTO #DateDim VALUES('09/30/2013',0)

DECLARE @CurrentDate datetime

SET @CurrentDate = CONVERT(varchar, GETDATE(), 101)

DECLARE @TransactionsFromDate date, @TransactionsToDate date

I want to calculate TransactionsFromDate and TransactionsToDate based on CurrentDate

2. If Day of @CurrentDate In (Tuesday) Then @TransactionsFromDate = @CurrentDate - 4 and @TransactionsToDate = @CurrentDate - 2 (Friday to Sunday)

3. If Day of @CurrentDate In (Monday) Then @TransactionsFromDate = @CurrentDate - 4 and @TransactionsToDate = @CurrentDate - 4 (as of Thursday)

This works fine if there are no holiday on prior day/s. If there is a holiday on prior day I need to consider transactions that were supposed to be sent yesterday + as of today using above logic. Some examples below: