SOQL query to list random cases

Question

Here is my query:

select id,casenumber from case where createddate > 2015-07-01T00:00:00Z limit 10 offset :rand

how do we write this query outside Apex code(like workbench,data loader). My main intention is to list 10 random cases created in a month.

Answer 1

This is a bit difficult to do in a single query. Normally someone would write a query where they'd use a random string generator to pick the day of the month and then limit the query to return 1 result.

Since it sounds as though you plan to do this in workbench so that you're not doing this via Apex, You may want to create a Random string generator of some type since I'm assuming you don't want all of the cases returned to be on the same day. Assuming that's the case, I think you'll need to take the following things into consideration and write separate queries for each of the 10 days, except that you'll want to generate the days of the month randomly:

As you do that, you'll want to take into consideration a few other things.

1. You'll want to decide how many separate days in the month you want data for (you could choose 5 with 2 records on each day for example).
2. If your cases don't occur 7 days/week, are they only on business days that are weekdays or are they weekdays plus Saturdays? In essence, you'll want to exclude the days you know you won't have cases.
3. If you don't have cases on holidays, you will need to exclude holidays that fall within a month.
4. If any of the above is true, then your formula needs to round up or down your random offsets so that it falls on a day that's a business day for your org.
5. The number of days in a month varies each month. Your formula will need to accommodate this.

With the above having been said, I'd assume that what you want is to get data on 10 different days which is why you chose the number 10.

I would generate a random number using something like TEXT(system.now()) as the "seed", then use Regex to strip away anything that's not a number. You can then use VALUE to convert it back to a number. I'd then take the square root of the result or do something similar and use the remainder (MOD) or 1/MOD as your random number. Use that to create a distribution for where your actual days will fall within the current month. You can make it as complex or as simple as you want. Even go to a web site that has a random number generator if you want to.

The Salesforce Useful Formula Fields provides a number of examples of how to use formula fields to calculate different days of the month, business days, days between, etc. I'd then use those to create the formulas to generate the random dates for each month.

As an example of finding the number of Business Days Between Two Dates:

(5 * ( FLOOR( ( date_1 - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD(
date_1 - DATE( 1900, 1, 8), 7 ) ) ) 
 -
(5 * ( FLOOR( ( date_2 - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD(
date_2 - DATE( 1900, 1, 8), 7 ) ) )

In this formula, date_1 is the more recent date and date_2 is the earlier date. If your work week runs shorter or longer than five days, replace all fives in the formula with the length of your week.

Once you have the dates, simply plug them into your query, each one with a limit of 1 using that date.