Skip to content

Amend Report to use financial years and quarters

I need to amend this existing code to group by financial year (starts 1 July) and quarters, I realize that it is the below part of the query that requires amending but I can’t get it to work

datepart(yy,EnquiryRaised)*100+datepart(mm,EnquiryRaised) as MonthYear,
year(EnquiryRaised) as year,
datename(month,(EnquiryRaised)) as Month

The full query is

SELECT 
enquiry_number, 
service_code,
service_name,  
subject_code,
subject_name, 
EnquiryRaised, 
NoAction, 
DATEDIFF(dd, EnquiryRaised, NoAction) as NoActionDuration,
ActionScheduled,
DATEDIFF(dd, EnquiryRaised, ActionScheduled) as ScheduledDuration,
datepart(yy,EnquiryRaised)*100+datepart(mm,EnquiryRaised) as MonthYear,
year(EnquiryRaised) as year,
datename(month,(EnquiryRaised)) as Month

FROM
(SELECT 
dbo.central_enquiry.enquiry_number,
dbo.central_enquiry.service_code, 
dbo.type_of_service.service_name,
dbo.central_enquiry.subject_code,
dbo.enquiry_subject.subject_name,
(SELECT TOP 1 
             dbo.enquiry_status_log.logged_date 
             FROM dbo.enquiry_status_log 
             WHERE dbo.enquiry_status_log.enquiry_number = dbo.central_enquiry.enquiry_number AND 
               dbo.enquiry_status_log.enq_status_code = 'E010' 
             ORDER BY dbo.enquiry_status_log.enquiry_log_number) as EnquiryRaised, 
(SELECT TOP 1 
 dbo.enquiry_status_log.logged_date 
 FROM dbo.enquiry_status_log 
 WHERE dbo.enquiry_status_log.enquiry_number = dbo.central_enquiry.enquiry_number AND 
   dbo.enquiry_status_log.enq_status_code IN ('E030', 'I030', 'N010', 'R030', 'XXXX') 
 ORDER BY dbo.enquiry_status_log.enquiry_log_number) as NoAction,
(SELECT TOP 1 
 dbo.enquiry_status_log.logged_date 
 FROM dbo.enquiry_status_log 
 WHERE dbo.enquiry_status_log.enquiry_number = dbo.central_enquiry.enquiry_number AND 
   dbo.enquiry_status_log.enq_status_code = 'W010' 
 ORDER BY dbo.enquiry_status_log.enquiry_log_number) as ActionScheduled 
 FROM dbo.central_enquiry
LEFT JOIN dbo.type_of_service ON dbo.central_enquiry.service_code = dbo.type_of_service.service_code
LEFT JOIN dbo.enquiry_subject ON dbo.central_enquiry.subject_code = dbo.enquiry_subject.subject_code
) as EnquiryLog 
ORDER BY enquiry_number

Sample data

enquiry_number,service_code,service_name,subject_code,subject_name,enquiryraised,noaction,noactionduration,actionscheduled,scheduledduration,monthyear,year,month
144033,BP,Buildings and Properties,BPBM,Building Maintenance,1/01/2019 17:52:30,,,7/01/2019 15:38:44,6,201901,2019,January
144034,PS,Park Services,PSPI,Park Infrastructure,2/01/2019 06:59:16,21/01/2019 15:40:23,19,,,201901,2019,January
144035,PSIN,Park Services Internal Request,PIOT,Other,2/01/2019 08:32:55,,,13/02/2019 07:45:57,42,201901,2019,January
144036,PS,Park Services,TRIO,Inspection of Tree,2/01/2019 08:36:16,2/01/2019 09:42:20,0,,,201901,2019,January
144037,PS,Park Services,PSSG,Sports Ground Maintenance,2/01/2019 08:41:08,,,3/01/2019 09:38:22,1,201901,2019,January
144038,GW,Glenworks,GWDR,Drainage Glenworks,2/01/2019 08:47:45,,,8/01/2019 09:54:10,6,201901,2019,January
144039,PSIN,Park Services Internal Request,PIOT,Other,2/01/2019 08:48:37,,,13/02/2019 07:44:33,42,201901,2019,January
144040,BP,Buildings and Properties,BPBM,Building Maintenance,2/01/2019 08:53:41,,,2/01/2019 09:22:26,0,201901,2019,January
144041,PSIN,Park Services Internal Request,PIOT,Other,2/01/2019 09:02:53,,,13/02/2019 07:43:22,42,201901,2019,January
144042,PSIN,Park Services Internal Request,PIRB,Rubbish,2/01/2019 09:13:18,,,21/01/2019 15:41:18,19,201901,2019,January
144043,PS,Park Services,PSPI,Park Infrastructure,2/01/2019 09:16:48,,,2/01/2019 13:17:32,0,201901,2019,January
144044,PSIN,Park Services Internal Request,PISP,Supplies,2/01/2019 09:18:13,3/01/2019 12:03:42,1,,,201901,2019,January
144045,GW,Glenworks,GWGP,Graffiti Private Property,2/01/2019 09:32:37,,,2/01/2019 10:04:26,0,201901,2019,January
144046,PS,Park Services,TRIO,Inspection of Tree,2/01/2019 09:31:58,2/01/2019 10:00:49,0,,,201901,2019,January
144047,PS,Park Services,TRIO,Inspection of Tree,2/01/2019 09:34:11,,,3/01/2019 10:49:46,1,201901,2019,January
144048,GW,Glenworks,GWGP,Graffiti Private Property,2/01/2019 09:40:04,,,3/01/2019 08:02:54,1,201901,2019,January
144049,GW,Glenworks,GWGP,Graffiti Private Property,2/01/2019 09:41:20,2/01/2019 13:50:10,0,,,201901,2019,January
144050,GW,Glenworks,GWGP,Graffiti Private Property,2/01/2019 09:42:07,,,3/01/2019 07:21:35,1,201901,2019,January
144051,GW,Glenworks,GWGP,Graffiti Private Property,2/01/2019 09:43:13,3/01/2019 07:00:55,1,,,201901,2019,January
144052,GW,Glenworks,GWGP,Graffiti Private Property,2/01/2019 09:44:09,3/01/2019 07:02:47,1,,,201901,2019,January
144053,GW,Glenworks,GWGP,Graffiti Private Property,2/01/2019 09:46:02,,,3/01/2019 07:44:25,1,201901,2019,January

Lyaout as per below but with finacial yaer ^ qtrs shown instead of months

Report layout

Advertisement

Answer

Below is the code for computing quarter and year_quarter columns. Since you did not provide the exact definition of quarter notation, I did both possibilities (starting from January (def. 1) or July (def. 2)).

Note the year - 1 in the year_quarter_2 definition when starting from July. This ensures that 2018Q3 is chronically later than 2018Q2, which makes more sense like how school quarters are defined. You may change them easily if you use a different definition.

Code:

select 
    /* quarter*/
    -- ordinary
    floor( (month(EnquiryRaised) - 1) / 3 + 1) as quarter_1,
    -- start from July
    case
        when month(EnquiryRaised) in (1,2,3) then 3
        when month(EnquiryRaised) in (4,5,6) then 4
        when month(EnquiryRaised) in (7,8,9) then 1
        when month(EnquiryRaised) in (10,11,12) then 2
    end as quarter_2,

    /* year-quarter */
    -- ordinary
    case 
        when month(EnquiryRaised) in (1,2,3) then cast(year(EnquiryRaised) as varchar) + 'Q1'
        when month(EnquiryRaised) in (4,5,6) then cast(year(EnquiryRaised) as varchar) + 'Q2'
        when month(EnquiryRaised) in (7,8,9) then cast(year(EnquiryRaised) as varchar) + 'Q3'
        when month(EnquiryRaised) in (10,11,12) then cast(year(EnquiryRaised) as varchar) + 'Q4'
    end as year_quarter_1,
    -- start from July
    case 
        when month(EnquiryRaised) in (1,2,3) then cast(year(EnquiryRaised)-1 as varchar) + 'Q3'
        when month(EnquiryRaised) in (4,5,6) then cast(year(EnquiryRaised)-1 as varchar) + 'Q4'
        when month(EnquiryRaised) in (7,8,9) then cast(year(EnquiryRaised) as varchar) + 'Q1'
        when month(EnquiryRaised) in (10,11,12) then cast(year(EnquiryRaised) as varchar) + 'Q2'
    end as year_quarter_2
from #raw;  -- #raw is the data you provide

Output (duplicate rows are ignored):

quarter_1 quarter_2 year_quarter_1 year_quarter_2
1         3         2019Q1         2018Q3
User contributions licensed under: CC BY-SA
6 People found this is helpful