Блог

Я использую средство чтения данных для получения значений из одной таблицы с использованием 3 разных запросов. Я получаю «System.Исключение IndexOutOfRangeException» для «billed030», которое находится во 2-м операторе select ниже.

Как я могу запросить базу данных здесь, потому что «где» отличается для каждого запроса.

 BEGIN
    SET NOCOUNT ON;

    SELECT SUM(CAST(AmountBilled AS decimal(18, 2))) AS billed,
           SUM(CAST(AmountPaid AS decimal(18, 2))) AS paid
    FROM OrderBilling
         LEFT OUTER JOIN Orders ON OrderBilling.LinkId = Orders.LinkId
                               AND Orders.OwnerId = @OwnerId
    WHERE OrderBilling.PaidInFull = 'False'
      AND OrderBilling.OwnerID = @OwnerID
      AND ClientId = @ClientId
    UNION ALL
    SELECT SUM(CAST(AmountBilled AS decimal(18, 2))) AS billed030,
           SUM(CAST(AmountPaid AS decimal(18, 2))) AS paid030
    FROM OrderBilling
         LEFT OUTER JOIN Orders ON OrderBilling.LinkId = Orders.LinkId
                               AND Orders.OwnerId = @OwnerId
    WHERE OrderBilling.InvoiceDate BETWEEN DATEADD(MONTH, -1, GETDATE()) AND GETDATE()
      AND OrderBilling.PaidInFull = 'False'
      AND OrderBilling.OwnerID = @OwnerID
      AND ClientId = @ClientId
    UNION ALL
    SELECT SUM(CAST(AmountBilled AS decimal(18, 2))) AS billed3060,
           SUM(CAST(AmountPaid AS decimal(18, 2))) AS paid3060
    FROM OrderBilling
         LEFT OUTER JOIN Orders ON OrderBilling.LinkId = Orders.LinkId
                               AND Orders.OwnerId = @OwnerId
    WHERE OrderBilling.InvoiceDate BETWEEN DATEADD(MONTH, -2, GETDATE()) AND DATEADD(MONTH, -1, GETDATE())
      AND OrderBilling.PaidInFull = 'False'
      AND OrderBilling.OwnerID = @OwnerID
      AND ClientId = @ClientId;
END;
  

Это не дает конкретного ответа на вопрос (который не является ошибкой SQL Server), однако я не вижу здесь необходимости в 3 UNION ALL запросах. Вы можете использовать некоторую условную агрегацию, а затем некоторое отключение для получения одного и того же набора данных:

 WITH CTE AS(
    SELECT SUM(CAST(O.AmountBilled AS decimal(18, 2))) AS billed, --If AmountBilled is in the table Orders, why a LEFT JOIN? If it isn't, why JOIN at all?
           SUM(CAST(O.AmountPaid AS decimal(18, 2))) AS paid,
           SUM(CASE WHEN OB.InvoiceDate BETWEEN DATEADD(MONTH, -1, GETDATE()) AND GETDATE() THEN CAST(O.AmountBilled AS decimal(18, 2)) END) AS billed030,
           SUM(CASE WHEN OB.InvoiceDate BETWEEN DATEADD(MONTH, -1, GETDATE()) AND GETDATE() THEN CAST(O.AmountPaid AS decimal(18, 2)) END) AS paid030,
           SUM(CASE WHEN OB.InvoiceDate BETWEEN DATEADD(MONTH, -2, GETDATE()) AND DATEADD(MONTH, -1, GETDATE()) THEN CAST(O.AmountBilled AS decimal(18, 2)) END) AS billed3060,
           SUM(CASE WHEN OB.InvoiceDate BETWEEN DATEADD(MONTH, -2, GETDATE()) AND DATEADD(MONTH, -1, GETDATE()) THEN CAST(O.AmountPaid AS decimal(18, 2)) END) AS paid3060
    FROM dbo.OrderBilling OB
         LEFT OUTER JOIN dbo.Orders O ON OB.LinkId = O.LinkId
                                     AND O.OwnerId = @OwnerId
    WHERE OB.PaidInFull = 'False'
      AND OB.OwnerID = @OwnerID
      AND OB.ClientId = @ClientId)
SELECT --V.category,
       V.billed,
       V.paid
FROM CTE C
     CROSS APPLY (VALUES('',C.billed,C.paid),
                        ('030',C.billed030,C.paid3060),
                        ('3060',C.billed3060,C.paid3060))V(category,billed,paid);