Создание ежемесячного списка с одной строкой из нескольких записей для каждого клиента

Я пытаюсь написать код, который будет вытягивать список каждый месяц. Список извлекает несколько записей для каждого клиента, и мне нужно, чтобы результирующий набор возвращал одну строку для каждого клиента с различной агрегированной информацией.

Поэтому, когда я запускаю следующий код, я получаю правильный ответ на Maxдату, давая мне последний счет для этого клиента, и я получаю правильный ответ на сумму суммы, складывая все еще открытые счета-фактуры. Будут другие поля, отображающие старение счета. Но я получаю столько строк на одного клиента, сколько у меня есть счетов-фактур с каждой строкой, показывающей “правильный” ответ на два агрегированных поля.

Я начал с поиска Distinctи вижу, что большинство ответов возвращаются с оконными функциями. Я не использовал их раньше, и я запутываюсь, заставляя их работать. Выход должен быть в определенном порядке, поэтому у меня есть вещи, перечисленные так, как я делаю. На данный момент я прочитал так много справочных форумов, что чувствую себя пьяным, поэтому я думаю, что упускаю очевидный ответ.

SELECT  cl.[document type] AS [RecordType], 
        c.[no_], 
        c.[name], 
        c.[name 2], 
        c.[address], 
        c.[address 2], 
        c.city, 
        c.[post code] AS [Zip], 
        c.[county] AS [State], 
        c.[country_region code] AS CountyTrim, 
        Month(Getdate()) AS [FigMonth], 
        Day(Getdate()) AS [FigDay], 
        Year(Getdate()) AS [FigYear], 
        --Next 2 lines should pull last invoice for customer to fill LastSaleDate field 
        (
        SELECT Max(cl2.[posting date]) 
        FROM [dbo].[detailed cust_ ledg_ entry] AS cl2 
        WHERE  cl2.[document type] = 2 AND cl2.[customer no_] = c.[no_]
        ) AS [LastSaleDate], 
        c.[payment terms code] AS [Terms 1], 
        --iif instead of case to fill Terms1Open field 
        Iif (c.[payment terms code] = 'CreditCard/Pre-Auth', 'N', 'Y') AS [Terms1Open], 
        --Now totaling invoices per customer 
        (
        SELECT Sum(cl3.[amount]) 
        FROM [dbo].[detailed cust_ ledg_ entry] AS cl3 
        WHERE cl3.[customer no_] = c.[no_] 
        GROUP BY cl3.[customer no_]
        ) AS [DollarsTotal] 
FROM   [dbo].[customer] AS c 
JOIN [dbo].[detailed cust_ ledg_ entry] AS cl 
    ON c.[no_] = cl.[customer no_] 
JOIN [dbo].[cust_ ledger entry] AS l 
    ON l.[document no_] = cl.[document no_] 
WHERE   l.[open] = 1 
        AND cl.[document type] = 2 
        AND c.[customer posting group] IN ( 'BIKECUST', 'OUTDRCUST' ) 
ORDER  BY c.[no_] 

1 ответ

  1. Я не совсем уверен, что вы пытаетесь сделать, но, основываясь на вашем SQL, я переписал его, чтобы сделать то, что я думаю, что код лучше всего описать как логический.

    Если я могу предложить предложение, если это ваша собственная схема — я бы серьезно подумал о переименовании буквально всего, чтобы соответствовать лучшим практикам.

    SELECT   CL.[document type] AS [RecordType]
            ,C.[no_]
            ,C.[name]
            ,C.[name 2]
            ,C.[address] 
            ,C.[address 2] 
            ,C.city 
            ,C.[post code] AS [Zip]
            ,C.[county] AS [State] 
            ,C.[country_region code] AS CountyTrim
            ,Month(Getdate()) AS [FigMonth] 
            ,Day(Getdate()) AS [FigDay] 
            ,Year(Getdate()) AS [FigYear] 
            ,MAX(CL.[posting date]) AS [LastSaleDate]
            ,C.[payment terms code] AS [Terms 1]
            ,CASE  WHEN C.[payment terms code] = 'CreditCard/Pre-Auth' 
                   THEN 'N' 
                   ELSE 'Y' END AS [Terms1Open]
            ,SUM(CL.[amount]) AS [DollarsTotal] 
      FROM   dbo.Customer C
      JOIN   dbo.[detailed cust_ ledg_ entry] CL
        ON   C.[no_] = CL.[customer no_]
      JOIN   dbo.[cust_ ledger entry]  L 
        ON   L.[document no_] = CL.[document no_]
     WHERE   L.[open] = 1 
       AND   CL.[document type] = 2 
       AND   C.[customer posting group] IN ( 'BIKECUST', 'OUTDRCUST' ) 
     GROUP
        BY   CL.[document type]
            ,C.[no_]
            ,C.[name]
            ,C.[name 2]
            ,C.[address] 
            ,C.[address 2] 
            ,C.city 
            ,C.[post code]
            ,C.[county]   
            ,C.[country_region code]
            ,Month(Getdate())                                              
            ,Day(Getdate())                                                
            ,Year(Getdate())     
            ,CASE  WHEN C.[payment terms code] = 'CreditCard/Pre-Auth' 
                   THEN 'N' 
                   ELSE 'Y' END
      ORDER  
         BY  C.[no_]