Confirm

Expand all | Collapse all

SQL Help - How to make SQL code show previous Calendar month results only

  • 1.  SQL Help - How to make SQL code show previous Calendar month results only

    Posted 10-21-2020 11:10
    Edited by Louis Johnston 10-21-2020 11:13

    Hi,

    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:
    SELECT
    job.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_date
    FROM
    central_site,
    area,
    ward,
    Job_type,
    job,
    job_status_log,
    job_status,
    priority, action_officer,
    gang_job,
    gang

    WHERE
    central_site.site_code = job.site_code AND
    area.area_code = central_site.area_code AND
    ward.ward_code = central_site.ward_code AND
    job_type.job_type_key = job.job_type_key AND

    job.priority_code = priority.priority_code AND
    action_officer.officer_code = job_status_log.allocated_officer AND
    job.job_number = job_status_log.job_number AND
    job_status.status_code = job_status_log.status_code AND
    job_status_log.job_log_number = job.job_log_number
    AND gang_job.order_job_number = job.job_number
    AND gang_job.gang_code = gang.gang_code
    AND job.actual_comp_date IS NOT NULL
    AND 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-1
    ORDER BY job.actual_comp_date DESC


    & Also to change Cost codes also to the previous Month only:


    SELECT
    job.job_number,
    central_site.site_code,
    central_site.site_name,
    job.job_entry_date,
    job.cost_code
    FROM
    central_site,
    cost_code,
    feature,
    job,
    job_type
    WHERE
    central_site.site_code = job.site_code AND
    cost_code.cost_code = feature.cost_code AND
    feature.site_code = job.site_code AND
    feature.plot_number = job.plot_number AND
    job_type.job_type_key = job.job_type_key AND
    job.priority_code IN ('TRU','TRNR','TRE','TR3M','5TR','6TR','7TR','8TR','9TR') AND
    job.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?

    Any help would be extremely appreciated.

    ------------------------------
    Louis Johnston
    Knowledge Community Shared Account
    ------------------------------


  • 2.  RE: SQL Help - How to make SQL code show previous Calendar month results only

    Posted 10-22-2020 08:29
    Hello,

    I don't know if the below would help with the previous month - I use greater or equal to start of last month and less than the start of this month to get the whole of the previous month so using the actual comp date as the example:

    job.actual_comp_date >= trunc(trunc(SYSDATE,'MM')-1,'MM') AND job.actual_comp_date < trunc(SYSDATE,'MM')


    SQL Server:

    SQL Expression

    Result

    Example if run at: 9.46AM
    on Thu 20th Aug 2009

    GETDATE()

    Current date and time

    2009-08-20 09:46:00.000

    DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0)

    Start of last year

    2008-01-01 00:00:00.000

    DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)

    Start of this year

    2009-01-01 00:00:00.000

    DATEADD(qq,DATEDIFF(qq,0,GETDATE())-1,0)

    Start of last quarter

    2009-04-01 00:00:00.000

    DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0)

    Start of this quarter

    2009-07-01 00:00:00.000

    DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0)

    Start of last month

    2009-07-01 00:00:00.000

    DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

    Start of this month

    2009-08-01 00:00:00.000

    DATEADD(ww,DATEDIFF(ww,0,GETDATE())-1,0)

    Start of last week

    (note Mon) 2009-08-10 00:00:00.000

    DATEADD(ww,DATEDIFF(ww,0,GETDATE()),0)

    Start of this week

    (note Mon) 2009-08-17 00:00:00.000

    DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,0)

    Start of yesterday

    2009-08-19 00:00:00.000

    DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

    Start of today

    2009-08-20 00:00:00.000


    Oracle:

    SQL Expression

    Result

    Example if run at: 9.46AM
    on Thu 20th Aug 2009

    SYSDATE

    Current date and time

    2009-08-20 09:46:00.000

    trunc(trunc(SYSDATE,'Y')-1,'Y')

    Start of last year

    2008-01-01 00:00:00.000

    trunc(SYSDATE,'Y')

    Start of this year

    2009-01-01 00:00:00.000

    trunc(trunc(SYSDATE,'Q')-1,'Q')

    Start of last quarter

    2009-04-01 00:00:00.000

    trunc(SYSDATE,'Q')

    Start of this quarter

    2009-07-01 00:00:00.000

    trunc(trunc(SYSDATE,'MM')-1,'MM')

    Start of last month

    2009-07-01 00:00:00.000

    trunc(SYSDATE,'MM')

    Start of this month

    2009-08-01 00:00:00.000

    trunc(trunc(SYSDATE,'W')-1,'W')

    Start of last week

    (note Sat) 2009-08-08 00:00:00.000

    trunc(SYSDATE,'W')

    Start of this week

    (note Sat) 2009-08-15 00:00:00.000

    trunc(trunc(SYSDATE,'DD')-1,'DD')

    Start of yesterday

    2009-08-19 00:00:00.000

    trunc(SYSDATE,'DD')

    Start of today

    2009-08-20 00:00:00.000



    ------------------------------
    Confirm Support Ringway Jacobs
    Senior Technician
    Ringway Jacobs Ltd
    Chelmsford
    ------------------------------



  • 3.  RE: SQL Help - How to make SQL code show previous Calendar month results only

    Posted 30 days ago
    I thought I had mentioned it previously and also to Barry Jones, that both of these are really helpful in terms of knowledge/experience and with the ability to know the language inside of confirm will help in future SQL. Sorry for the late reply.

    ------------------------------
    Louis Johnston
    Knowledge Community Shared Account
    ------------------------------



  • 4.  RE: SQL Help - How to make SQL code show previous Calendar month results only

    Posted 30 days ago
    Hi Louis,

    I have some code that shows Street Lighting Jobs completed LAST WEEK.  It may not be the most efficient but works fine. See below :

    ======================
    SELECT
    job.job_number,
    central_site.site_code,
    central_site.site_name,
    town.town_name,
    area.area_name Parish,
    job.contract_area_code,
    -- action_officer.officer_code,
    action_officer.officer_name,
    job.job_entry_date Job_Entry_Date,
    TO_CHAR( TRUNC( job.job_entry_date, 'IW' ), 'Dy ddth Mon YYYY' )
    Job_Logged_Week_Beginning,
    TO_CHAR( TRUNC(job.actual_comp_date ) , 'Dy' ) Completed_Day,
    job.actual_comp_date Actual_Completion_Date,
    TO_CHAR( TRUNC( job.actual_comp_date, 'IW' ), 'Dy ddth Mon YYYY' )
    Job_Completed_Week_Beginning,
    ROUND( job.actual_comp_date - job.job_entry_date)
    Days_To_Completion,
    TO_CHAR( TRUNC( job.job_entry_date) , 'Dy ddth Mon YYYY' )
    Job_Entered_Date,
    TO_CHAR( TRUNC(job.actual_comp_date ) , 'Dy ddth Mon YYYY' )
    Completed_Date,
    SYSDATE,
    TO_CHAR( TRUNC( SYSDATE ) , 'Dy ddth Mon YYYY' ) Todays_Date,
    TO_CHAR( TRUNC( SYSDATE -1 ) , 'Dy ddth Mon YYYY' ) Yesterdays_Date,
    -- job_type.job_type_code,
    job_type.job_type_name,
    -- job_status.status_code,
    job_status.status_name,
    job_status_log.logged_date Status_Logged_Date,
    -- job.priority_code,
    priority.priority_name,
    TRUNC( job.actual_comp_date ) Comp_Date


    -- ----------------------------------------------------------------------------------------------------------------------------------------
    FROM job_status, job_status_log, action_officer, job_type, priority,
    central_site, area, locality, town, job
    -- ----------------------------------------------------------------------------------------------------------------------------------------
    WHERE job.job_number = job_status_log.job_number
    AND job_status.status_code = job_status_log.status_code
    AND action_officer.officer_code = job_status_log.allocated_officer
    AND job_status_log.job_log_number = job.job_log_number
    AND central_site.site_code = job.site_code
    AND central_site.area_code = area.area_code
    AND central_site.locality_id = locality.locality_id
    AND town.town_id = locality.town_id
    AND job.job_type_key = job_type.job_type_key
    AND job.priority_code = priority.priority_code
    AND job.job_status_flag = 'C' -- Committed jobs Only
    AND job.contract_code IN ('CWL') -- On SL Contract
    AND job_status.status_code IN ( 'J145' ) -- Completed jobs Only

    AND TRUNC (SYSDATE -6) <= job.actual_comp_date -- Completed in Last Week or so

    -- ----------------------------------------------------------------------------------------------------------------------------------------
    ORDER BY job.actual_comp_date DESC
    -- =========================== Code Ends Here ===========================


    ------------------------------
    Barry Jones
    Cheshire West and Cheshire
    Winsford
    ------------------------------