---
title: "Automating a Daily Transaction Report in SQL Server at 2 AM"  
description: "Generating reports manually every day is inefficient. With SQL Server, you can fully automate a daily transaction report to run at a fixed time—like 2"  
author: "Anubhav Sharma"  
published: 2026-04-28  
updated: 2026-04-29  
canonical: https://www.mindstick.com/blog/306892/automating-a-daily-transaction-report-in-sql-server-at-2-am  
category: "database"  
tags: ["database", "sql server"]  
reading_time: 3 minutes  

---

# Automating a Daily Transaction Report in SQL Server at 2 AM

Generating reports manually every day is inefficient. With **SQL Server**, you can fully automate a **daily transaction report** to run at a fixed time—like **2 AM**—using **[SQL Server Agent](https://www.mindstick.com/interview/879/what-is-sql-server-agent)**. This approach is reliable, scalable, and production-friendly.

## Use Case

You want to:

- Fetch **yesterday’s transactions**
- Generate a summary/report
- Store or send the report automatically at **2:00 AM daily**

## Step 1: Create the Report Query

First, write a query to extract daily transaction data.

```plaintext
SELECT
    CAST(TransactionDate AS DATE) AS ReportDate,
    COUNT(*) AS TotalTransactions,
    SUM(Amount) AS TotalAmount
FROM Transactions
WHERE CAST(TransactionDate AS DATE) = CAST(DATEADD(DAY, -1, GETDATE()) AS DATE)
GROUP BY CAST(TransactionDate AS DATE);
```

This query:

- Filters **yesterday’s data**
- Calculates total transactions and amount

## Step 2: Store Report in a Table (Optional)

You can persist reports for history:

```plaintext
INSERT INTO DailyTransactionReport (ReportDate, TotalTransactions, TotalAmount)
SELECT
    CAST(TransactionDate AS DATE),
    COUNT(*),
    SUM(Amount)
FROM Transactions
WHERE CAST(TransactionDate AS DATE) = CAST(DATEADD(DAY, -1, GETDATE()) AS DATE)
GROUP BY CAST(TransactionDate AS DATE);
```

## Step 3: Create a Stored Procedure

Wrap the logic into a [stored procedure](https://www.mindstick.com/articles/803/using-stored-procedure-in-asp-dot-net) for reuse:

```plaintext
CREATE PROCEDURE GenerateDailyTransactionReport
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO DailyTransactionReport (ReportDate, TotalTransactions, TotalAmount)
    SELECT
        CAST(TransactionDate AS DATE),
        COUNT(*),
        SUM(Amount)
    FROM Transactions
    WHERE CAST(TransactionDate AS DATE) = CAST(DATEADD(DAY, -1, GETDATE()) AS DATE)
    GROUP BY CAST(TransactionDate AS DATE);
END;
```

## Step 4: Schedule Job Using SQL Server Agent

Now automate it:

- Open **SQL Server [Management Studio](https://www.mindstick.com/forum/159668/what-is-the-difference-between-sql-server-and-sql-management-studio) (SSMS)**
- Go to **SQL Server Agent → Jobs → New Job**
- Give job name: `Daily Transaction Report Job`

### Add Step:

Type: **Transact-SQL Script**

Command:

```plaintext
EXEC GenerateDailyTransactionReport;
```

## Step 5: Set Schedule (2 AM Daily)

- Go to **Schedules → New**
- Name: `Daily 2AM Schedule`
- Frequency: **Daily**
- Time: **02:00:00 AM**

## Step 6: (Optional) Send Email Notification

You can configure **Database Mail** and send the report:

```plaintext
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'YourMailProfile',
    @recipients = 'admin@company.com',
    @subject = 'Daily Transaction Report',
    @body = 'Please find attached report.',
    @query = 'SELECT * FROM DailyTransactionReport WHERE ReportDate = CAST(GETDATE()-1 AS DATE)';
```

## Best Practices

- Add indexes on `TransactionDate` for performance
- Handle **time zones** (important for global apps)
- Add logging/[error handling](https://www.mindstick.com/forum/160168/error-handling-in-go) in the procedure
- Monitor job history in SQL Server Agent

## Common Issues

- SQL Server Agent not running → Job won’t execute
- Permission issues → Job fails silently
- [Large data](https://www.mindstick.com/interview/872/how-do-you-load-large-data-to-the-sqlserver-database) → Query may need [optimization](https://yourviews.mindstick.com/view/85459/what-is-conversion-rate-optimization-and-how-to-get-started)

## Final Thoughts

By combining **[stored procedures](https://www.mindstick.com/forum/540/using-stored-procedures-with-entity-framework-in-an-asp-dot-net-application) + SQL Server Agent**, you can fully automate daily reporting tasks. Scheduling the job at **2 AM** ensures minimal load on your system and delivers fresh data every morning without manual effort.

---

Original Source: https://www.mindstick.com/blog/306892/automating-a-daily-transaction-report-in-sql-server-at-2-am

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.