Я хотел бы знать, что вы можете сделать в следующем сценарии:
Допустим, я фильтрую дату в предложении 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
Если исходный код действительно длинный, можете ли вы упростить его, чтобы проиллюстрировать свой вопрос? Я бы сказал, что в настоящее время ваш вопрос слишком расплывчатый, чтобы на него можно было ответить. Нам нужно увидеть пример того, что вы пытаетесь сделать.
Вычисляемые поля - это свойства таблицы. не может быть изменен запросами .. я полагаю, вы имели в виду, что у вас есть подзапрос в вашем основном запросе, я прав?
Да, у меня есть один в основном запросе. Я также добавил основной запрос.
Удалите фильтр даты (SubmissionDates.cc_date_c BETWEEN COALESCE(EOMONTH(@StartDate), '01/31/2016') AND COALESCE(@EndDate, GETDATE(), -1)
) из вашего запроса и поместите его в набор данных отчета. Это позволит выполнить запрос со всеми данными для заполнения оконной функции. Затем вы отфильтруете позже нужные даты. ИЛИ вы можете поместить результаты последнего запроса в другую таблицу TEMP, а затем SELECT * из нее с предложением where с диапазоном дат.
Конечно, вы можете удалить любой фильтр по датам из запроса и применить их непосредственно в табликсе вашего отчета. Очевидно, это означает, что 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')
Потрясающе .. Это сработало отлично :) Я ценю вашу помощь
Покажите нам некоторые образцы данных и желаемый результат.