One month cycle in SQL Server

Question

Every year we have 12 months. I have to write a query that is selected in one table for each month. For example, I have to make a report that shows me the number of transactions per month.

I did it, but wrong.

I wrote 12 queries for each month.

Like this:

SET @MONTH12M = (SELECT SUM(Amount) AS TOT FROM [fidilio].[dbo].[CardTransactionLog] CL JOIN CardTransaction CT ON CT.CardTransactionLogId = CL.CardTransactionLogId WHERE (cl.TransactionPersianTimeStamp > N'1393/12/01' AND cl.TransactionPersianTimeStamp< N'1393/12/31') ) INSERT INTO #TEMP(MonthValue, CountValue, TypeValue) SELECT 12, CASE WHEN @MONTH12M IS NULL THEN 0 ELSE @MONTH12M END,4 

I have 11 more such requests.

Finally, I get my result, which I entered in the temp table.

How can I do this dynamically?

How to do this with a loop?

Answer 1

You can use group by to generate statistics per month:

 select month(date_column) , sum(amount) from YourTable group by month(date_column) 

The T-SQL month function retrieves the numeric month from the datetime column.