OraFAQ Forum: SQL & PL/SQL » Need to derive YTD,MAT,MQT

Home » SQL & PL/SQL » SQL & PL/SQL » Need to derive YTD,MAT,MQT (oracle)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Need to derive YTD,MAT,MQT [message #686288]

Wed, 20 July 2022 10:54

samiran_cts
Messages: 52
Registered: January 2012

Member

Sir, I need one help where i need to derive some query, Please help me.

YTD - Year TO Date
Start of the current year - till latest data available for current year
For Example
Current Time Period Past Time Period
1. Jun 2022 (YTD 01-Jan-2022 to 30-June-2022) (YTD 01-Jan-2021 to 30-June-2021)
2. July 2022 (YTD 01-Jan-2022 to 31-July-2022) (YTD 01-Jan-2021 to 31-July-2021)
3. Dec 2022 (YTD 01-Jan-2022 to 31-Dec-2022) (YTD 01-Jan-2021 to 31-Dec-2021)
4. Jan 2023 (YTD 01-Jan-2023 to 31-Jan - 2023) (YTD 01-Jan-2022 to 31-Jan - 2022)

MAT - Moving Annual Turnover
Completed last 12 months
For Example
Current Time Period Past Time Period
1. Jun 2022 (MAT 01-July-2021 to 30-June-2022) (MAT 01-July-2020 to 30-June-2021)
2. Dec 2022 (MAT 01-Jan-2022 to 31-Dec-2022) (MAT 01-Jan-2021 to 31-Dec-2021)
3. Jan 2023 (MAT 01-Feb-2022 to 31-Jan-2023) (MAT 01-Feb-2021 to 31-Jan-2022)

MQT - Moving Quarter turnover
Completed last 3 months
For Example
Current Time Period Past Time Period
1. Jun 2022 (MQT 01-Apr-2022 to 30-June-2022) (MQT 01-Apr-2021 to 30-June-2021)
2. Nov 2022 (MQT 01-Sep-2022 to 30-Nov-2022) (MQT 01-Sep-2021 to 30-Nov-2021)
3. Jan 2023 (MQT 01-Nov-2022 to 31-Jan-2023) (MQT 01-Nov-2021 to 31-Jan-2021)

I have a data set for last 3 years:
source time country area sales
Monthly Jan-20 Au Schizophrenia 100000
Monthly Feb-20 Au Schizophrenia 200000
Monthly Mar-20 Au Schizophrenia 300000
Monthly Apr-20 Au Schizophrenia 400000
Monthly May-20 Au Schizophrenia 500000
Monthly Jun-20 Au Schizophrenia 600000
Monthly Jul-20 Au Schizophrenia 700000
Monthly Aug-20 Au Schizophrenia 800000
Monthly Sep-20 Au Schizophrenia 900000
Monthly Oct-20 Au Schizophrenia 1000000
Monthly Nov-20 Au Schizophrenia 1100000
Monthly Dec-20 Au Schizophrenia 1200000
Monthly Jan-21 Au Schizophrenia 1300000
Monthly Feb-21 Au Schizophrenia 1400000
Monthly Mar-21 Au Schizophrenia 1500000
Monthly Apr-21 Au Schizophrenia 1600000
Monthly May-21 Au Schizophrenia 1700000
Monthly Jun-21 Au Schizophrenia 1800000
Monthly Jul-21 Au Schizophrenia 1900000
Monthly Aug-21 Au Schizophrenia 2000000
Monthly Sep-21 Au Schizophrenia 2100000
Monthly Oct-21 Au Schizophrenia 2200000
Monthly Nov-21 Au Schizophrenia 2300000
Monthly Dec-21 Au Schizophrenia 2400000
Monthly Jan-22 Au Schizophrenia 2500000
Monthly Feb-22 Au Schizophrenia 2600000
Monthly Mar-22 Au Schizophrenia 2700000
Monthly Apr-22 Au Schizophrenia 2800000
Monthly May-22 Au Schizophrenia 2900000
Monthly Jun-22 Au Schizophrenia 3000000
Monthly Jul-22 Au Schizophrenia 3100000

My requirement, i need to derive data in the following manner based upon the current month and Year. Say for current month july and year 2022 my data would become:

Source	dura     year	state	YYYYMMDD	YYYYMMDD
Monthly	MAT	2022	Current	20210801	20220731
Monthly	YTD	2022	Current	20220101	20220731
Monthly	MQT	2022	Current	20220501	20210731
Monthly	MAT	2021	Past	20200801	20210731
Monthly	YTD	2021	Past	20210101	20210731
Monthly	MQT	2021	Past	20210501	20210731
Quarter	MAT	2022	Current	20210701	20220630
Quarter	YTD	2022	Current	20220101	20220630
Quarter	Quarter(vs LastYear)	2022	Current	20220401	20220630
Quarter	Quarter(vs PreviousQtr)	2022	Current	20220401	20220630
Quarter	MAT	2021	Past	20200701	20210630
Quarter	YTD	2021	Past	20210101	20210630
Quarter	Quarter(vs LastYear)	2021	Past	20210401	20210630
Quarter	Quarter(vs PreviousQtr)	2022	Past	20220101	20210331
Full_Year	FULLYEAR	2021	Current	20210101	20211231
Full_Year	FULLYEAR	2020	Past	20200101	20201231

Regards,
Samiran

[Updated on: Wed, 20 July 2022 14:09] by Moderator

Report message to a moderator

Re: Need to derive YTD,MAT,MQT [message #686289 is a reply to message #686288]

Wed, 20 July 2022 10:59

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Align the columns in result.

With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

Report message to a moderator

Re: Need to derive YTD,MAT,MQT [message #686290 is a reply to message #686289]

Wed, 20 July 2022 11:19

samiran_cts
Messages: 52
Registered: January 2012

Member

The data set is :

SELECT 'Monthly' ,'Jan-20','Au','Schizophrenia',100000 from dual
UNION
SELECT 'Monthly' ,'Feb-20','Au','Schizophrenia',200000 from dual
UNION
SELECT 'Monthly' ,'Mar-20','Au','Schizophrenia',300000 from dual
UNION
SELECT 'Monthly' ,'Apr-20','Au','Schizophrenia',400000  from dual
UNION
SELECT 'Monthly' ,'May-20','Au','Schizophrenia',500000  from dual
UNION
SELECT 'Monthly' ,'Jun-20','Au','Schizophrenia',600000  from dual
UNION
SELECT 'Monthly' ,'Jul-20','Au','Schizophrenia',700000 from dual
UNION
SELECT 'Monthly' ,'Aug-20','Au','Schizophrenia',800000 from dual
UNION
SELECT 'Monthly' ,'Sep-20','Au','Schizophrenia',900000 from dual
UNION
SELECT 'Monthly' ,'Oct-20','Au','Schizophrenia',1000000 from dual
UNION
SELECT 'Monthly' ,'Nov-20','Au','Schizophrenia',1100000 from dual
UNION
SELECT 'Monthly' ,'Dec-20','Au','Schizophrenia',1200000 from dual
UNION
SELECT 'Monthly' ,'Jan-21','Au','Schizophrenia',1300000 from dual
UNION
SELECT 'Monthly' ,'Feb-21','Au','Schizophrenia',1400000 from dual
UNION
SELECT 'Monthly' ,'Mar-21','Au','Schizophrenia',1500000 from dual
UNION
SELECT 'Monthly' ,'Apr-21','Au','Schizophrenia',1600000 from dual
UNION
SELECT 'Monthly' ,'May-21','Au','Schizophrenia',1700000 from dual
UNION
SELECT 'Monthly' ,'Jun-21','Au','Schizophrenia',1800000 from dual
UNION
SELECT 'Monthly' ,'Jul-21','Au','Schizophrenia',1900000 from dual
UNION
SELECT 'Monthly' ,'Aug-21','Au','Schizophrenia',2000000 from dual
UNION
SELECT 'Monthly' ,'Sep-21','Au','Schizophrenia',21'00000 from dual
UNION
SELECT 'Monthly' ,'Oct-21','Au','Schizophrenia',22'00000 from dual
UNION
SELECT 'Monthly' ,'Nov-21','Au','Schizophrenia',2300000 from dual
UNION
SELECT 'Monthly' ,'Dec-21','Au','Schizophrenia',2400000 from dual
UNION
SELECT 'Monthly' ,'Jan-22','Au','Schizophrenia',2500000 from dual
UNION
SELECT 'Monthly' ,'Feb-22','Au','Schizophrenia',2600000 from dual
UNION
SELECT 'Monthly' ,'Mar-22','Au','Schizophrenia',2700000 from dual
UNION
SELECT 'Monthly' ,'Apr-22','Au','Schizophrenia',2800000 from dual
UNION
SELECT 'Monthly' ,'May-22','Au','Schizophrenia',2900000 from dual
UNION
SELECT 'Monthly' ,'Jun-22','Au','Schizophrenia',3000000 from dual
UNION
SELECT 'Monthly' ,'Jul-22','Au','Schizophrenia',3100000 from dual
;

Report message to a moderator

Re: Need to derive YTD,MAT,MQT [message #686291 is a reply to message #686290]

Wed, 20 July 2022 11:44

John Watson
Messages: 8977
Registered: January 2010
Location: Global Village

Senior Member

Your sample data set is going to be difficult to work with because you are using strings for dates. This is a basic design flaw: SQL has no way of knowing that 'Jan-21' comes before 'Feb-22' if they are stored as strings. Can you not use a DATE data type?

Report message to a moderator

Re: Need to derive YTD,MAT,MQT [message #686292 is a reply to message #686291]

Wed, 20 July 2022 11:59

samiran_cts
Messages: 52
Registered: January 2012

Member

Yes Sir, DATE data type can be used. All the date can be started as e.g. 1-JAN-2021: (TO_DATE('2020/01/01', 'yyyy/mm/dd')), as per sutability. I have just prepared a sample data set.

[Updated on: Wed, 20 July 2022 12:02]

Report message to a moderator

Previous Topic:	Plsql using NVL
Next Topic:	Plsql function testing years

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 16 09:18:57 CDT 2025