DB2 SQL query to fetch last five month details

Discussions relating to writing software in ILE RPG (RPG IV). This includes both fixed and free format RPG.
Post Reply
THIYAGARAJAN
Posts: 6
Joined: Sat Jul 01, 2023 3:57 pm

DB2 SQL query to fetch last five month details

Post by THIYAGARAJAN »

I have request number created each month, I need to create sql query to show the count of request created for last 5 months, there might be scenario when no request is created but sql query should show that month with value as zero.

Below is the query which I am using it but it is not giving expected result:

With T1 as ( Select Ser# , month(TIMESTAMP_FORMAT(CHAR(DCR),'YYYYMMDD')) as MName FROM Lib/Mytable where date(TIMESTAMP_FORMAT(CHAR(DCR),'YYYYMMDD')) >= date(current_date - 4 month))
Select Count(*) , MName from T1 Group By Mname Order By Mname



This above query will giving me result as
Count Month
5 4
1 5


I want my result should look like this
Count Month
0 3
5 4
1 5
0 6
0 7

Any suggestion what changes I need to make to above query or how can I modify above query which will give me expected result. DCR field is decimal in YYYYMMDD Format.
Post Reply