March 4, 2022 at 1:58 am
Hi, I have a report that I want to configure that will be sending out a report monthly to users. The trouble I am having is trying to get the excel file to send data to those users on the first day of a month with data of the previous month. I am not sure what to put into the date format in the query of the report. Can anyone help?
March 4, 2022 at 3:05 am
Use DATEFROMPARTS ( year, month, day ) and EOMONTH ( start_date [, month_to_add ] ) to get the first day and the last day of a month and then use them to filter?
March 4, 2022 at 3:27 am
Thank you for trying to help. OK so this is what the query in the report looks like;
SELECT [ID]
,[MessageTypeID]
,[EventID]
,[Source]
,[Destination]
,[Subject]
,[MessageText]
,[MessagePlainText]
,[CreatedOn]
,[UpdatedOn]
,[Provider]
,[LanguageCode]
,[SmscMessageID]
,[SmscStatus]
,[SendRetryCount]
,[MessageStatusID]
FROM [UT_XXX_Core_XXX].[messaging].[MessageQueue]
WHERE Source = '277' AND (DATEADD(month, 1, UpdatedOn) >= GETDATE()) AND MessageStatusID = 3
In red is what i used for the date. it does throw back a few rows of data when i test it however i am not sure if i set up the subscription it will be sending the right data fulfilling what i described in my initial question.
March 4, 2022 at 7:16 pm
Calculate the first of last month - and the first of this month filtering on your UpdatedOn date greater than or equal to the first of last month and less than the first of this month.
WHERE UpdatedOn >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) --First of last month
AND UpdatedOn < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) --First of this month
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply