DB2 SQL query to fetch last five month details
Posted: Mon Jul 10, 2023 5:54 am
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.
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.