Settlement Query Language (SQL)
The Settlement Query Language allows one to filter settlements using different modifiers.
Some modifiers support multiple comma-separated values.
Multiple modifiers in one query are supported, e.g. settled:true currency:DKK,EUR.
A bad request will be returned for unknown modifiers or invalid input.
Settled
The settled property can be used to differentiate between closed and open settlements.
| Query | Will search for… |
|---|---|
| settled:true | settlements that are closed |
| settled:t | idem |
| settled:false | settlements that are open |
| settled:f | idem |
Currency
| Query | Will search for… |
|---|---|
| currency:DKK | settlements in currency DKK |
| currency:NOK,SEK,EUR | settlements in currency NOK, SEK or EUR |
Dates
The following date properties are available: period.start_date, period.end_date, payout.date and reserve.date.
And the following options are available, shown with payout.date as the example:
| Query | Will search for… |
|---|---|
| payout.date:today | settlements with payout today |
| payout.date:yesterday | settlements with payout yesterday |
| payout.date:last_week | settlements with payout last week |
| payout.date:last_month | settlements with payout last month |
| payout.date:last_year | settlements with payout last year |
| payout.date:this_week | settlements with payout from the beginning of this week until now |
| payout.date:this_month | settlements with payout from the beginning of this month until now |
| payout.date:this_year | settlements with payout from the beginning of this year until now |
| payout.date:>=2016-01-20 | settlements with payout after or during January 20th, 2016 |
| payout.date:>2016-01-20 | settlements with payout after January 20th, 2016 |
| payout.date:<=2016-01-20 | settlements with payout before or during January 20th, 2016 |
| payout.date:<2016-01-20 | settlements with payout before January 20th, 2016 |
| payout.date:*..2016-01-20 | settlements with payout before or during January 20th, 2016 |
| payout.date:2016-01-20..* | settlements with payout after or during January 20th, 2016 |
| payout.date:2016-01-18..2016-01-20 | settlements with payout after or during January 18th and before or during January 20th, 2016 |
Merchant ID
Also known as account number or MID.
| Query | Will search for… |
|---|---|
| merchant_id:3000001 | settlements with merchant id 3000001 |
| mid:3000001 | settlements with merchant id 3000001 |
| mid:3000001,3000002 | settlements with merchant id 3000001 or 3000002 |
Payout reference numbers
The reference number properties can uniquely identify a payout.
| Query | Will search for… |
|---|---|
| payout.reference_number:73423589061 | settlement with payout reference number 73423589061 |
| reserve.reference_number:73423589064 | settlement with reserve payout reference number 73423589064 |
ID
The id property can uniquely identify a settlement.
| Query | Will search for… |
|---|---|
| id:a51a3abe-8eee-4a92-b941-e89f18c5bf66 | settlement with id a51a3abe-8eee-4a92-b941-e89f18c5bf66 |