SQL Example - Using Date Functions
This example shows how to use date functions to calculate the difference in months between the date when a customer complaint was reported and the date when the complaint was finally resolved.
For more information on date functions in InfoAsset Manager, see the SQL in InfoAsset Manager technical note.
The Query
set user_number_1 = ((yearpart(date_resolved) - yearpart(date_reported)) * 12) + (monthpart(date_resolved) - monthpart(date_reported))
Show me
Explanation and Examples
We are putting the calculating value into the customer complaint user number field 1. In the explanation below we will use the following example dates:
1. Date Reported = 1/6/2005 – Date Resolved = 1/4/2006
2. Date Reported = 1/6/2005 – Date Resolved = 1/4/2007
3. Date Reported = 1/6/2005 – Date Resolved = 1/8/2006
4. Date Reported = 1/6/2005 – Date Resolved = 1/8/2007
5. Date Reported = 1/6/2005 – Date Resolved = 1/6/2006
6. Date Reported = 1/6/2005 – Date Resolved = 1/10/2005
7. Date Reported = 1/6/2005 – Date Resolved = 25/6/2005
The yearpart(date_resolved) – returns the year number when the complaint was resolved.
The yearpart(date_reported) – returns the year number when the complaint was reported.
The monthpart(date_resolved) – returns the month number when the complaint was resolved.
The monthpart(date_reported) – returns the month number when the complaint was reported.
1. Date Reported = 1/6/2005 – Date Resolved = 1/4/2006
Set user_number_1 = ((2006 - 2005) * 12) + (4 - 6) = (1 * 12) + -2 = 10
2. Date Reported = 1/6/2005 – Date Resolved = 1/4/2007
Set user_number_1 = ((2007 - 2005) * 12) + (4 - 6) = (2 * 12) + -2 = 22
3. Date Reported = 1/6/2005 – Date Resolved = 1/8/2006
Set user_number_1 = ((2006 – 2005) * 12) + (8 - 6) = (1 * 12) + 2 = 14
4. Date Reported = 1/6/2005 – Date Resolved = 1/8/2007
Set user_number_1 = ((2007 – 2005) * 12) + (8 - 6) = (2 * 12) + 2 = 26
5. Date Reported = 1/6/2005 – Date Resolved = 1/6/2006
Set user_number_1 = ((2006 – 2005) * 12) + (6 - 6) = (1 * 12) + 0 = 12
6. Date Reported = 1/6/2005 – Date Resolved = 1/10/2005
Set user_number_1 = ((2005 – 2005) * 12) + (10 - 6) = (0 * 12) + 4 = 4
7. Date Reported = 1/6/2005 – Date Resolved = 25/6/2005
Set user_number_1 = ((2005 – 2005) * 12) + (6 - 6) = (0 * 12) + 0 = 0
If we would rather select any complaint object that has more than 12 months from report to resolution we can modify the SQL to:
((yearpart(date_resolved) - yearpart(date_reported))*12) + (monthpart(date_resolved) - monthpart(date_reported)) > 12
