Loop through month in SQL Server

Question

Every year we have 12 month. I should write a query that select in one table for every month. For example I should make report that show me every month transaction count.

I did it but in wrong way.

I wrote 12 query for every 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 query like that.

Finally I fetch my result I put in temp table .

How can I do this dynamically?

How can I do it with 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 function month extracts the numeric month from a datetime column.