function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Subbu'sSubbu's 

Need help in SOQL Where clause.

Hello Everybody ,

 

My requirement is as follows .. plz go through and help me to solve this.

 

--->  I am having one leave object with custom fields leave_from_date and leave_to_Date of Date type .

 

--->  In the trigger which is on other object , i am  have a Set collection of Dates i.e; Set<date>

 

---->  I need to get all leave records using this set collection.

 

Example -- I am having the leave recpord with  leave_from_date = 3-10-2013 and leave_to_ date = 05-10-2013 . And in the Set collection the available dates are (02-10-2013 and 04-10- 2013)

 

then What is the SOQL to get the leave record using this set collection..?

 

Thanx in advance

 

 

Best Answer chosen by Admin (Salesforce Developers) 
firechimpfirechimp

Hi Subbu,

I don't believe you can do what you want in a query.

 

The good news is with a bit of code before hand you can make it work. Bad news is if you have very, very, large date ranges you may hit script statment limits.

 

Here's the code:

Set<Date> collectionOfDates = new Set<Date>();

Date lowestDate;
Date highestDate;

//find lowes and highest dates in set
for(Date d : collectionOfDates){
	if(lowestDate == null || d < lowestDate){
		lowestDate = d;
	}
	if(highestDate == null || d > highestDate){
		highestDate = d;
	}
}

//find all dates in range of highest to lowest
Set<Date> allDatesToCheck = new Set<Date>();
Date selectedDate = lowestDate;
while(selectedDate <= highestDate){
	allDatesToCheck.add(selectedDate);
	selectedDate = selectedDate.addDays(1);
}

//query for all records that have a crossover in this range of dates.
List<Leave__c> leaveList = [select Id from Leave__c where Leave_From_Date__c in :allDatesToCheck or Leave_To_Date__c in :allDatesToCheck];

 

Please be aware this was wrote in notepad++ so has not been compiled (so may need a little tweak or two) but it should be pretty close.

 

Hope this helps!

 

All Answers

firechimpfirechimp

Hi Subbu,

I am not sure if I fully understand what you want, but I think this should do it:

Set<Date> collectionOfDates = new Set<Date>();

List<Leave__c> leaveList = [select Id from Leave__c where Leave_From_Date__c in :collectionOfDates or Leave_To_Date__c in :collectionOfDates];

 

Hope this helps!

 
Ashish_SFDCAshish_SFDC

Hi Subbu, 

 

Check if the below is what you are looking for,

 

where Leave_from_Date >= :refDate1
and Leave_to_Date < :refDate2];
WHERE Leave_From_Date > 2005-10-08T00:00:00Z

 

Regards,

Ashish
Subbu'sSubbu's

@firechimp

 

I tried the same , but its not...
Subbu'sSubbu's

@Ashish

 

Can u plz let me know how to seperate refDate1 and  refDate 2 from my Set Date collection..
firechimpfirechimp

Hi Subbu,

What error do you get when trying that?
Subbu'sSubbu's

@firechimp

 

Thanx for your response , it is not throwing any error , but the query is not getting the record i required ...

This is my requirement..

 

Leave record is available in leave object with field values as Leave_From_Date = 03/10/2013 and Leave_To_date = 05/10/2013.

i.e; total no. of leave days is 3 days .

&

In my trigger I am having the date collection(Set) which contains two values 02/10/2013 and 04/10/2013.

 

my requirement is to get the existing leave record using this date collection , because In need the leave record on 04/10/2013 also.
firechimpfirechimp

Hi Subbu,

I don't believe you can do what you want in a query.

 

The good news is with a bit of code before hand you can make it work. Bad news is if you have very, very, large date ranges you may hit script statment limits.

 

Here's the code:

Set<Date> collectionOfDates = new Set<Date>();

Date lowestDate;
Date highestDate;

//find lowes and highest dates in set
for(Date d : collectionOfDates){
	if(lowestDate == null || d < lowestDate){
		lowestDate = d;
	}
	if(highestDate == null || d > highestDate){
		highestDate = d;
	}
}

//find all dates in range of highest to lowest
Set<Date> allDatesToCheck = new Set<Date>();
Date selectedDate = lowestDate;
while(selectedDate <= highestDate){
	allDatesToCheck.add(selectedDate);
	selectedDate = selectedDate.addDays(1);
}

//query for all records that have a crossover in this range of dates.
List<Leave__c> leaveList = [select Id from Leave__c where Leave_From_Date__c in :allDatesToCheck or Leave_To_Date__c in :allDatesToCheck];

 

Please be aware this was wrote in notepad++ so has not been compiled (so may need a little tweak or two) but it should be pretty close.

 

Hope this helps!

 

This was selected as the best answer
Subbu'sSubbu's

Thank u Gary , it worked ..but as you suggested I need  to check  this with large data sets ..
firechimpfirechimp

No Probs,

Glad I could help!