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
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