Convert day of week based on settlement date

[tsouth4]

I am using CRXI and reporting on an oracle db. I want to first apoligize for having such a long post however i wanted to include as much useful information as possible.

I have done this before and somehow my report was deleted and I cannot remember the formula I used. What I am trying to do is create a formula that converts an existing date based on settlement time of various credit cards. All credit cards have different settlement times based on the date placed. Settlement times range anywhere from 1 to 5 days. Batches are always grouped and settled daily based on these transaction times. So any orders that are placed between 10pm on 06/26 and 09:59:59pm on 06/27 will be batched together in our our account no matter what credit card is used. These are batched together however Some credit cards will settle sooner than others.


Transaction Date CC Orderid total Batchid
6/26/2007 9:39:25 PM Visa 381343 251.06 110
6/26/2007 9:39:40 PM Visa 381344 225.24 110
6/26/2007 9:43:52 PM Mastercard 381346 429.20 110
6/26/2007 9:46:04 PM Mastercard 381347 409.70 110
6/26/2007 9:53:54 PM Amex 381348 228.69 110
6/26/2007 9:58:43 PM Mastercard 381349 292.95 110
6/26/2007 10:06:49 PM Visa 381350 292.00 111
6/26/2007 10:10:55 PM Mastercard 381351 223.38 111
6/26/2007 10:18:31 PM Visa 381352 179.34 111
6/26/2007 10:27:04 PM Amex 381354 367.50 111

This is just a very small portion of data and I will usually be running this report over an entire month. I need to create a formula that converts the settlement date based on the date the transactions were batched.

For instance orderid 381349 was placed on 06/26 @ 9:58:43 PM. The cc used was a mastercard and this was batched in Batch 110 along with all orders placed between 06/25 10pm ->06/26 09:59:59PM. This particular order will take 1 day to settle and will settle on 06/27. what i need is a formula which I can enter different settlement days or lag days based on the day of week the order was placed. I would like to use 1 formula which I could enter my data for all credit cards.

Like I previously stated all cc's have different lag times based on the day of the week the transaction was processed or batched. I've provided a few examples below.

Amex:

batch date settlement days
06/25(MON) 4
06/26(Tue) 4
06/27(Wed) 5
06/28(Thur) 4
06/29(Fru) 4
06/30(Sat) 4
07/01(Sun) 4

Visa and Mastercard

batch date settlement days
06/25(MON) 1
06/26(Tue) 1
06/27(Wed) 1
06/28(Thur) 1
06/29(Fru) 1
06/30(Sat) 2
07/01(Sun) 1

First off What I did was created a new transaction date by using the following formula :

DateAdd ("h",2,{ACCT_TRANSACTION.DT_INITIATE})

this will give me a new transaction date which I can determine a day of week the order was batched by using following formula to determine a day of week for the initial transaction date:

Dayofweek ({@newtransactiondate})

I would then somehow show

if the credit card used was "amex" and Dayofweek ({@newtransactiondate}) = 1 then ({@newtransactiondate})+4. if d Dayofweek ({@newtransactiondate}) = 2 then ({@newtransactiondate})+4 and so on....... I just don't know how to create this formula. I would have to do this for each credit card.

I keep getting errors when i attempt create this formula and need someone to point me in the right direction. Any help will be greatly appreciated

-Tim

 

[lbass]

Is settlement days a field in the database?

-LB

 

[tsouth4]

Nope, this is not a field in the database. This is something I had to calculate separately however the settlement days are always the same based on the day of week for each batch. Each weekday always has the same # of lag days from week to week however this varies from card to card.

-Tim

 

[lbass]

Are you saying you have to hard code the settlement days or are you saying that you had to use a formula to calculate them? If so, show the contents of the formula.

-LB

 

[tsouth4]

I'm somewhat confused. I have a lag chart which I created in an excel spreadsheet for each CC. Over time I had to determine the exact lag from the date the transaction was initiated or batch date until settlement. I was able to determine the # of lag days from here based on the # of days between date initiated and settlement date. So I believe the answer to your question is I will have to hard code these in the formula.

 

[lbass]

You could use a case statement like:

select {table.CC}
case "Visa","Mastercard" :
(
select dayofweek({@newtransactiondate})
case 1 to 6 : 1
case 7 : 2
)
case "Amex" :
(
select dayofweek({@newtransactiondate})
case 1,2,3,5 to 7 : 4
case 4 : 5
)
//etc.

-LB

 

[tsouth4]

Thanks LB I named the formuala that you provided settlement days and created the following formuala which I inserted into the details section.

{@newtransactiondate}+{@Settlement Days}

I am looking through the data now and it appears to be what I'm looking for. I'll let you know if I have any problems.

-tim