How to add month and date to get data automatically in SQL?

Question

Hello,

I'm new to SQL. I would like to setup below codes in SSMS to automictically run once a month without changing date, month and year? How would I do it? Can I turn into a Stored procedure?

select

T.INVDTE as 'Invoice Date',

T.INVNO as 'Invoice Number',

T.SSTATE as 'State',

LEFT(T.SZIP,5) as 'Zip Code',

RIGHT(T.SZIP,4) as 'Plus 4',

T.SCITY as 'City',

T.COUNTY as 'County',

T.ORIGIN as 'Sold From',

REPLACE(L.ZIP, '-', '') as 'Sold From Zip Code',

L.CITY as 'Sold from City',

T.EXEMPT as ' Excempt Amt',

T.TAXABLE as 'Taxable',

T.NONTXABLE as 'Non-Taxable',

T.EXEMPT + T.NONTXABLE as 'Total of Exempt and Non-Taxable',

1 as 'T',

'N' as 'X',

'N' as 'Y',

T.TAX as 'Tax Collected'

from tblST T

INNER JOIN tblIC L ON T.LOCTID=L.LOCTID

where INVDTE BETWEEN '2022-07-01' and '2022-07-31'

order by STATE

Thank you for guidance

Answer 1

Hi @Pelican

Yes, you could wrap your code into a stored procedure and then create a SQL Server Agent job that executes this stored procedure on a monthly schedule.

Considering that there is a date filter in the where condition, you might need to modify it. Cause you did not clarify the detailed logic, so here is the code depending on my guess that you execute this code on the first day of each month to query data from the previous month.

CREATE PROCEDURE [dbo].[MonthlyAutoTask]
AS
BEGIN
    select
	......
	from tblST T INNER JOIN tblIC L ON T.LOCTID=L.LOCTID
	where INVDTE BETWEEN DATEADD(DAY, 1, EOMONTH(GETDATE(), -2)) and EOMONTH(GETDATE(), -1)
	order by STATE
END

Then you could create a SQL Server agent job to run this SP.

You may refer to this guide: Create A SQL Job to Run a Stored Procedure.

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".