SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Transaction from and to date.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cvipin
Starting Member

USA
45 Posts

Posted - 09/24/2013 :  20:30:51  Show Profile  Reply with Quote
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
Starting Member

USA
45 Posts

Posted - 09/25/2013 :  18:52:39  Show Profile  Reply with Quote
No one?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000