Transaction from and to date. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Transaction from and to date.

Author

Topic

cvipin
Yak Posting Veteran

51 Posts

Posted - 2013-09-24 : 20:30:51

I have a dateDim table as below with IsHoliday flag:

CREATE TABLE #DateDim (Date date, IsHoliday int)

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

Calculation:

1. If Day of @CurrentDate In (Wednesday, Thursday, Friday) Then @TransactionsFromDate = @CurrentDate - 2 and @TransactionsToDate = @CurrentDate - 2

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:

Ex:
1. If Yesterday was holiday, lets say @CurrentDate = '09/24/2013' then I want
@TransactionsFromDate = '9/19/2013' and @TransactionsToDate = '9/22/2013'

2. If Yesterday was holiday, lets say @CurrentDate = '09/18/2013' then I want
@TransactionsFromDate = '9/13/2013' and @TransactionsToDate = '9/16/2013'

3. If @CurrentDate = '09/25/2013' then I want (Monday)
@TransactionsFromDate = '9/23/2013' and @TransactionsToDate = '9/23/2013'

4. If @CurrentDate = '09/10/2013' then I want (Tuesday)
@TransactionsFromDate = '9/06/2013' and @TransactionsToDate = '9/08/2013'

5. If @CurrentDate = '09/09/2013' then I want
@TransactionsFromDate = '9/05/2013' and @TransactionsToDate = '9/05/2013'

Can you help me achieve this logic.

Thanks
Vipin

cvipin
Yak Posting Veteran

51 Posts

Posted - 2013-09-25 : 18:52:39

No one?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources