There's more to experience when you log in!
I have written some code that for example shows all tree jobs for the last thirty days. Instead I was wondering if it is at all possible for it to show the previous calendar month. So, as we are currently in October it would show all jobs done in September. Then when it becomes November it would automatically show all jobs done in october?
I feel like its something once you know it'd be easy to replicate but i can't seem to find the correct terminology for the monthly date.
I have the SQL for the various areas incase anyone can help with them or if they'd be different depending on enquiry or job specification:Tree Jobs Completed Monthly:SELECTjob.job_number,job.site_code,central_site.site_name,job.job_notes,job.job_location,area.area_name,ward.ward_name,job.job_entry_date,job.cost_code,job.contract_code,job.job_complete_date,job.job_type_key,job_type.job_type_name,job.actual_start_date,job.job_status_flag,job_status_log.status_code,job_status.status_name,job.priority_code,priority.priority_name,job_status_log.follow_up_date,TRUNC(target_comp_date - sysdate) Days,TRUNC((target_comp_date - sysdate) * 24) Hours,job.target_comp_date,job_status_log.allocated_officer,action_officer.officer_name,gang_job.gang_code,gang.gang_name,job.actual_comp_dateFROMcentral_site,area,ward,Job_type,job,job_status_log,job_status,priority, action_officer,gang_job,gangWHEREcentral_site.site_code = job.site_code ANDarea.area_code = central_site.area_code ANDward.ward_code = central_site.ward_code ANDjob_type.job_type_key = job.job_type_key ANDjob.priority_code = priority.priority_code ANDaction_officer.officer_code = job_status_log.allocated_officer ANDjob.job_number = job_status_log.job_number ANDjob_status.status_code = job_status_log.status_code ANDjob_status_log.job_log_number = job.job_log_numberAND gang_job.order_job_number = job.job_numberAND gang_job.gang_code = gang.gang_codeAND job.actual_comp_date IS NOT NULLAND gang_job.gang_code in ('TR01', 'TR02', 'TR03','ARB3','TR2M','TR3M','TRCT','TRLF','TRLW','TRMT','TRSG','TRTT','TRO1')AND job_status_log.status_code IN('0500')AND job.actual_comp_date > sysdate-1ORDER BY job.actual_comp_date DESC& Also to change Cost codes also to the previous Month only:
SELECTjob.job_number,central_site.site_code,central_site.site_name,job.job_entry_date,job.cost_codeFROMcentral_site,cost_code,feature,job,job_typeWHEREcentral_site.site_code = job.site_code ANDcost_code.cost_code = feature.cost_code ANDfeature.site_code = job.site_code ANDfeature.plot_number = job.plot_number ANDjob_type.job_type_key = job.job_type_key ANDjob.priority_code IN ('TRU','TRNR','TRE','TR3M','5TR','6TR','7TR','8TR','9TR') ANDjob.job_entry_date >= add_months(sysdate, -3)
Also, if possible could this also be done/explained if you wanted it for the previous week only or the previous year only?
Example if run at: 9.46AMon Thu 20th Aug 2009
Current date and time
Start of last year
Start of this year
Start of last quarter
Start of this quarter
Start of last month
Start of this month
Start of last week
(note Mon) 2009-08-10 00:00:00.000
Start of this week
(note Mon) 2009-08-17 00:00:00.000
Start of yesterday
Start of today
(note Sat) 2009-08-08 00:00:00.000
(note Sat) 2009-08-15 00:00:00.000