So we get files loaded into our database daily and automate what happens to a lot of them. I now need to get the monthly figures for total files received and total files automated. I have a select query as follows (just for total number of files received):
select (type) UC_Record_Type, count (type) As total_records
from hbucdsnotification
where batch_date > '31.12.2021'
and batch_date < '01.01.2023'
group by type
order by 1
;
this returns the total of each file type has been received in that period e.g.
new claims 634
changes 3898
termination 200
What I need is for the above to be split into months,I have tried to add month(batch_date) AS month to the query, which adds the month number to each file recieved based on batch_date, and then add a where clause to say where the month = 1, but this returns all the files for the month in a list.
I'm sure I am missing something obvious. I did create a new table so the month number would be included against each file received and then tried to run a similar query to the above, but it returns every file received and totals something up (not entirely sure what)
so my ideal result would be: like the above, but split into months for example:
new claims 634 month 1
changes 3898 month 1
termination 200 month 1
new claims 634 month 2
changes 3898 month 2
termination 200 month 2
any suggestions on the direction/what I need to look at would be very much appreciated! I am going round in circles and dont want to have to run the above for 12 different months individually and then again with my other SQL to find automated cases.
I have tried to add month(batch_date) AS month to the query;
select (type) UC_Record_Type, count (type) As total_records, MONTH(batch_date) AS month
from hbucdsnotification
where batch_date > '31.12.2021'
and batch_date < '01.01.2023'
and month = 1'1
group by type, batch_date
order by 1
;
which adds the month number to each file recieved based on batch_date, and then add a where clause to say where the month = 1, but this returns all the files for the month in a list like this
new claims 2
new claims 4
new claims 7
changes 139
changes 189
changes 92
changes 3
I think its returning every batch date with a 1 and listing them and totalling them for that date.
You need to GROUP BY month also.
I would additional Group By year, because it would without count evervy january of evry year and not seperated by year#
select (type) UC_Record_Type, count (type) As total_records, MONTH(batch_date) AS month
from hbucdsnotification
where batch_date > '31.12.2021'
and batch_date < '01.01.2023'
and month = 1'1
group by type, MONTH(batch_date)
order by 1
;