asked on
mysql select monthly report - 25th of the prior month thru 24th of current month
MYSQL 5.6.22
Hello everyone,
I need help converting a query from MSSQL to MYSQL. There is a report that needs to run every month on the 25th day that does a count from the 25th of the previous month to the 24th of the current month.
This currently works in MSSQL:
I know that comparing MSSQL to MYSQL is apples & oranges, but I need help with converting the date calculation to function in MYSQL.
The statement above should just come back with three counts, one for each site.
This will always run on the 25th day of each month.
thanks
Hello everyone,
I need help converting a query from MSSQL to MYSQL. There is a report that needs to run every month on the 25th day that does a count from the 25th of the previous month to the 24th of the current month.
This currently works in MSSQL:
select
(select COUNT (*)
from pacsdb.study
where study_custom1 like '%SITE1%'
and study_datetime >= convert(date, dateadd(month, -1, dateadd(day, 25-day(getdate()), getdate())))
and study_datetime < convert(date, dateadd(day, 25-day(getdate()), getdate()))) as SITE1,
(select COUNT (*)
from pacsdb.study
where study_custom1 like '%SITE2%'
and study_datetime >= convert(date, dateadd(month, -1, dateadd(day, 25-day(getdate()), getdate())))
and study_datetime < convert(date, dateadd(day, 25-day(getdate()), getdate()))) as SITE2,
(select COUNT (*)
from pacsdb.study
where study_custom1 like '%SITE3%'
and study_datetime >= convert(date, dateadd(month, -1, dateadd(day, 25-day(getdate()), getdate())))
and study_datetime < convert(date, dateadd(day, 25-day(getdate()), getdate()))) as SITE3
I know that comparing MSSQL to MYSQL is apples & oranges, but I need help with converting the date calculation to function in MYSQL.
The statement above should just come back with three counts, one for each site.
This will always run on the 25th day of each month.
thanks
MySQL Server
Last Comment
doc_jay
ASKER
Thanks. This does work as it always goes back one month. How could it be written if I needed to run this on a different date to ensure that it always counted between the 25th of the prior month & the 24th of the current month?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
thanks, this is just what I needed.
I had to change it a bit to get the 25th - the 24th
I had to change it a bit to get the 25th - the 24th
logdate > date_add(DATE_FORMAT(NOW() ,'%Y-%m-25'), interval -1 month) and
logdate < DATE_FORMAT(NOW() ,'%Y-%m-25')
where
logdate > date_add(DATE(NOW()), interval -1 month) - 1 and
logdate > date_add(DATE(NOW()), interval -1 day)
or
where
logdate > date_add(date_add(DATE(NOW
logdate > date_add(DATE(NOW()), interval -1 day)