title: Building report using SQL Server stored procedure
body: <p>What are the best practices for building a report that shows data from several tables and which can be searchable by some fields, e.g by date?</p>
  
  <p>The structure of the report is following:</p>
  
  <pre><code>                Today    Yesterday  Last 7 days     All
  New Users         0         10        20           1000
  Sold Products     11        21        31           25000
  </code></pre>
  
  <p>All I can think of is just building the report with stored procedure and OUTPUT parameters, it works quite fine, but it is a tedious work both on a SQL Server side and in application. I just don't know any other way of doing this.</p>
  
  <p>Maybe it can be done by using cursor or using different stored procedures or some other technique. Can you please navigate me to a better approach? I appreciate any help.</p>
  
  <p>Here is my current approach</p>
  
  <pre><code>CREATE PROCEDURE sp_Statistics
      @DateFrom datetime = NULL, 
      @DateTo datetime = NULL,
  
      @UsersRegisteredToday int = 0 OUTPUT,
      @UsersRegisteredYesterday int = 0 OUTPUT, 
      @UsersRegisteredLastSevenDays int = 0 OUTPUT,
      @UsersRegisteredTotal int = 0 OUTPUT,
      @UsersRegisteredDate int = 0 OUTPUT,
  
      @SoldProductsToday int = 0 OUTPUT,
      @SoldProductsYesterday int = 0 OUTPUT, 
      @SoldProductsLastSevenDays int = 0 OUTPUT,
      @SoldProductsTotal int = 0 OUTPUT,
      @SoldProductsDate int = 0 OUTPUT
  AS
  BEGIN
  
  --------------------- REGISTERED USERS -----------------------
  IF (@DateFrom IS NOT NULL OR @DateTo IS NOT NULL)
      BEGIN
          SELECT @UsersRegisteredDate = COUNT(*)
          FROM [User] u
          WHERE DATEADD(DAY, DATEDIFF(D, 0, u.Date), 0) BETWEEN @DateFrom AND @DateTo
      END
  ELSE IF @DateFrom IS NULL AND @DateTo IS NULL
      BEGIN
          SELECT @UsersRegisteredToday = COUNT(DISTINCT u.idUser)
          FROM [User] u
          LEFT JOIN [Order] e ON u.idUser = e.idUser
          WHERE DATEDIFF(d, GETDATE(), u.Date) = 0
  
          SELECT @UtilizadoresYesterday = COUNT(DISTINCT u.idUser)
          FROM [User] u
          LEFT JOIN [Order] e ON u.idUser = e.idUser
          WHERE DATEDIFF(d, GETDATE(), u.Date) = -1
  
          SELECT @UsersRegisteredLastSevenDays = COUNT(DISTINCT u.idUser)
          FROM [User] u
          LEFT JOIN [Order] e ON u.idUser = e.idUser
          WHERE DATEDIFF(d, GETDATE(), u.Date) &gt;= -7
  
          SELECT @UsersRegisteredTotal = COUNT(DISTINCT u.idUser)
          FROM [User] u
          LEFT JOIN [Order] e ON u.idUser = e.idUser
      END
  
  --------------------- SOLD PRODUCTS --------------------------
  IF (@DateFrom IS NOT NULL OR @DateTo IS NOT NULL)
      BEGIN
          SELECT @SoldProductsDate = COUNT(*)
          FROM [Order] e
          WHERE e.idPaymentState = 2
                AND
                DATEADD(DAY, DATEDIFF(D, 0, e.Date), 0) BETWEEN @DateFrom AND @DateTo
      END
  ELSE IF @DateFrom IS NULL AND @DateTo IS NULL
      BEGIN
  
          SELECT @SoldProductsToday = COUNT(*) 
          FROM [Order] e 
          LEFT JOIN [User] u ON e.idUser = u.idUser
          WHERE e.idPaymentState = 2 AND DATEDIFF(d, GETDATE(), e.Date) = 0
  
          SELECT @SoldProductsYesterday = COUNT(*)
          FROM [Order] e
          LEFT JOIN [User] u ON e.idUser = u.idUser
          WHERE e.idPaymentState = 2 AND DATEDIFF(d, GETDATE(), e.Date) = -1
  
          SELECT @SoldProductsLastSevenDays = COUNT(*)
          FROM [Order] e
          LEFT JOIN [User] u ON e.idUser = u.idUser
          WHERE e.idPaymentState = 2 AND DATEDIFF(d, GETDATE(), e.Date) &gt;= -7
  
          SELECT @SoldProductsTotal = COUNT(*)
          FROM [Order] e
          LEFT JOIN [User] u ON e.idUser = u.idUser
          WHERE e.idPaymentState = 2
      END
  END
  </code></pre>