Фильтровать по датам, но сохранять расчет

Я хотел бы знать, что вы можете сделать в следующем сценарии:

Допустим, я фильтрую дату в предложении where(between eomonth(@StartDate) and eomonth(getdate()-1). У меня есть вычисляемый столбец, который является правильным, когда я запускаю запрос без какого-либо фильтра, но проблема в том, что когда я фильтрую, скажем, @StartDate = 06/30/2017, тогда вычисления, очевидно, изменятся. Есть ли способ сделать это?

Вычисляемый столбец - это оконная функция.

Отредактировано:

Я добавил изображение данных. Поэтому я использую оконную функцию для вычисления четырехмесячного периода агента. Вы увидите, что он суммирует единицы за этот месяц и 3 предыдущих месяца. Это я не хочу менять при фильтрации. Таким образом, столбцы units и agentfourmonthperiod должны оставаться неизменными после фильтрации по @StartDate. См. Данные ниже:

Фильтровать по датам, но сохранять расчет

Я хочу разработать отчет SSRS, который пользователь будет фильтровать по @StartDate, но тогда он должен показывать расчет, как если бы фильтр не использовался.

Надеюсь, это имеет смысл. В противном случае я могу добавить код. Хотя это довольно долго.

Код:

  WITH DATA
                AS
                (
                SELECT  
            EOMONTH(SubmissionDates.original_date_c) AS IntakeMonth,

            ProvincialArea.SAD_ProvMananger AS ProvManager,
            RegionalArea.SAD_RegMananger AS RegManager,
            SalesArea.SAD_SalesManager AS AreaSalesManager,

            ConsultantUserExt.name AS Consultant,

            COUNT(LeadsLink.LeadsID) OVER(PARTITION BY ConsultantUserExt.name, EOMONTH(SubmissionDates.original_date_c,0) ORDER BY EOMONTH(SubmissionDates.original_date_c,0)) AS Unit,
            ROW_NUMBER() OVER(PARTITION BY ConsultantUserExt.name, EOMONTH(SubmissionDates.original_date_c,0) ORDER BY EOMONTH(SubmissionDates.original_date_c,0)) AS rn

FROM Import.OobaApplication as Application

LEFT OUTER JOIN Import.OobaApplicant applicant ON application.ApplicationID = applicant.ApplicationID 
                AND applicant.PrincipleApplication = 'Y'

LEFT OUTER JOIN usr_userext_cstm ON Application.Consultant = usr_userext_cstm.comcorp_key_c
    or Application.Consultant = usr_userext_cstm.deal_maker_key_c
    or Application.Consultant = usr_userext_cstm.ops_key_c

LEFT OUTER JOIN usr_userext AS ConsultantUserExt ON usr_userext_cstm.id_c = ConsultantUserExt.id AND ConsultantUserExt.deleted = 0
LEFT OUTER JOIN usr_userext_cstm AS ConsultantUserExtCstm on ConsultantUserExt.id = ConsultantUserExtCstm.id_c

LEFT OUTER JOIN CapcubedInternalDB.dbo.ProvincialArea AS ProvincialArea ON ConsultantUserExtCstm.sad_provincialmanager_c = ProvincialArea.ID
LEFT OUTER JOIN CapcubedInternalDB.dbo.RegionalArea AS RegionalArea ON ConsultantUserExtCstm.sad_regionalmanager_c = RegionalArea.ID
LEFT OUTER JOIN CapcubedInternalDB.dbo.SalesArea AS SalesArea ON ConsultantUserExtCstm.sad_salesmanager_c = SalesArea.ID

LEFT OUTER JOIN CapcubedInternalDB.dbo.LeadsLink AS LeadsLink ON Application.ApplicationID = LeadsLink.GroupCode
LEFT OUTER JOIN suitecrmprod.dbo.leads AS SuiteLeads ON LeadsLink.LeadsID = SuiteLeads.ID

--Latest Bank Submission
LEFT OUTER JOIN (SELECT 
                    bankSub.ApplicationID As BankSubAppID, bankSub.SubmissionDate,
                    bankSub.Bank, bankSub.RequiredLoanAmount,
                    bankSub.BankCode AS BankSubBankCode

                    FROM Import.OobaBankSubmission bankSub

                    LEFT OUTER JOIN Import.OobaBankSubmission later ON bankSub.ApplicationID = later.ApplicationID
                                    AND bankSub.SubmissionDate > later.SubmissionDate
                    WHERE later.applicationID IS NULL) AS BankSub ON Application.ApplicationID = BankSub.BankSubAppID

LEFT OUTER JOIN ccrep_calendar_cstm AS SubmissionDates ON CONVERT(VARCHAR(10),BankSub.SubmissionDate,101) = SubmissionDates.original_date_c

WHERE SubmissionDates.cc_date_c BETWEEN COALESCE(EOMONTH(@StartDate), '01/31/2016') AND COALESCE(@EndDate, GETDATE(), -1)
AND ConsultantUserExtCstm.consultantstatus_c NOT LIKE 2
                )
     SELECT *
            INTO #Rn
            FROM DATA
            WHERE rn = 1

                SELECT i.IntakeMonth, c.ProvManager, c.RegManager, c.AreaSalesManager, c.Consultant, COALESCE(#Rn.Unit, 0) AS Unit
                INTO #FillData
                FROM (SELECT DISTINCT IntakeMonth FROM #Rn) AS i
                        CROSS JOIN
                    (SELECT DISTINCT Consultant, ProvManager, RegManager, AreaSalesManager FROM #Rn) AS c
                LEFT OUTER JOIN #Rn ON #Rn.IntakeMonth = i.IntakeMonth AND #Rn.Consultant = c.Consultant
                ORDER BY Consultant, IntakeMonth

                SELECT 
                    IntakeMonth,
                    Consultant,
                    Unit,
                    SUM(Unit) OVER(PARTITION BY Consultant ORDER BY Consultant ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS agentfourmonthperiod

                FROM #FillData

                WHERE ('(All)' IN (@ProvincialManager) OR (ProvManager IN (@ProvincialManager)))
                    AND ('(All)' IN (@RegionalManager) OR (RegManager IN (@RegionalManager)))
                    AND ('(All)' IN (@AreaSalesManager) OR (AreaSalesManager IN (@AreaSalesManager)))
                    AND ('(All)' IN (@Consultant) OR (Consultant IN (@Consultant)))

                DROP TABLE #Rn
                DROP TABLE #FillData

Покажите нам некоторые образцы данных и желаемый результат.

SQL_M 07.09.2018 15:59

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

Tab Alleman 07.09.2018 16:10

Вычисляемые поля - это свойства таблицы. не может быть изменен запросами .. я полагаю, вы имели в виду, что у вас есть подзапрос в вашем основном запросе, я прав?

Serkan Ekşioğlu 07.09.2018 16:50

Да, у меня есть один в основном запросе. Я также добавил основной запрос.

Ruahl Behr 07.09.2018 16:53

Удалите фильтр даты (SubmissionDates.cc_date_c BETWEEN COALESCE(EOMONTH(@StartDate), '01/31/2016') AND COALESCE(@EndDate, GETDATE(), -1)) из вашего запроса и поместите его в набор данных отчета. Это позволит выполнить запрос со всеми данными для заполнения оконной функции. Затем вы отфильтруете позже нужные даты. ИЛИ вы можете поместить результаты последнего запроса в другую таблицу TEMP, а затем SELECT * из нее с предложением where с диапазоном дат.

Hannover Fist 07.09.2018 23:06
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
5
59
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Конечно, вы можете удалить любой фильтр по датам из запроса и применить их непосредственно в табликсе вашего отчета. Очевидно, это означает, что SQL Server должен возвращать все данные при каждом запуске отчета, поэтому я думаю, что это не то, что вам нужно.

Чтобы оконная функция имела доступ к предыдущим 3 строкам, вам нужно будет включить в расчет предыдущие 3 месяца. Для этого измените первое условие в предложении WHERE в cte (data) на что-то вроде этого:

SubmissionDates.cc_date_c 
BETWEEN 
  ISNULL(DATEADD(month, DATEDIFF(month, 0, @StartDate)-3, 0), '01/10/2015')
AND
  ISNULL(@EndDate, DATEADD(day, DATEDIFF(day, 0, GETDATE())-1, 0))

As I thought that your date filter logic was wrong, I changed it to include the dates from the beginning of the month rather than from the end.

Теперь, когда включены предыдущие 3 месяца, мы можем применить фильтр в конце, чтобы исключить предыдущие месяцы из отображения, но это нужно сделать после, используя оконную функцию, например, с другим cte:

WITH calc AS (
    SELECT 
        IntakeMonth,
        Consultant,
        Unit,
        SUM(Unit) OVER(PARTITION BY Consultant ORDER BY Consultant ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS agentfourmonthperiod

    FROM #FillData

    WHERE ('(All)' IN (@ProvincialManager) OR (ProvManager IN (@ProvincialManager)))
      AND ('(All)' IN (@RegionalManager) OR (RegManager IN (@RegionalManager)))
      AND ('(All)' IN (@AreaSalesManager) OR (AreaSalesManager IN (@AreaSalesManager)))
      AND ('(All)' IN (@Consultant) OR (Consultant IN (@Consultant)))
    )

SELECT IntakeMonth, Consultant, Unit, agentfourmonthperiod
FROM calc 
WHERE IntakeMonth >= ISNULL(EOMONTH(@StartDate), '01/31/2016')

Потрясающе .. Это сработало отлично :) Я ценю вашу помощь

Ruahl Behr 10.09.2018 09:30

Другие вопросы по теме