следующий фон: у меня есть функция с табличным значением, которая возвращает таблицу, которая является своего рода отфильтрованным представлением (представления не могут быть параметризованы) для большой таблицы. Теперь мне нужно много значений количества строк в разных условиях. Возможно ли получить все значения счетчика в одном запросе, не запрашивая эту функцию для каждого условия?
Для полноты (это на самом деле не имеет значения, вы можете прочитать это), это функция:
CREATE FUNCTION [dbo].[getRuleConfigBySetSlsRules] (
@fiRuleSet AS TinyInt,
@fiServiceLocations AS NVARCHAR(4000),
@fiRules AS NVARCHAR(400)
)
RETURNS TABLE AS RETURN (
SELECT ruleChangeTo.fiSet, ruleChangeTo.fiClaimStatus, ruleChangeTo.fiRule, ruleChangeTo.fiSL, ruleChangeTo.fiResult,
dimClaimStatus.ClaimStatusName AS OldStatus, dimClaimStatus_1.ClaimStatusName AS NewStatus, locSL.SLName, ruleSet.SetName,
ruleResult.ResultName, locMarketUnit.MarketUnitName, locCountry.CountryName, ruleRule.RuleKey, ruleRule.RuleName, ruleRule.RuleDescription,
locGSP.WCMSName AS GSP_WCMSName, locGSP.Active AS GSP_Active, locGSP.WCMSKeyNumber AS GSP_WCMSKeyNumber,
locSL.Active AS SL_Active, locSL.WCMSName AS SL_WCMSName, locSL.WCMSKeyNumber AS SL_WCMSKeyNumber, locSL.fiSLType,
locSLType.SLTypeName, ruleChangeTo.fiClaimStatus_ChangeTo
FROM locGSP INNER JOIN
locCountry ON locGSP.fiCountry = locCountry.idCountry INNER JOIN
locMarketUnit ON locCountry.fiMarketUnit = locMarketUnit.idMarketUnit INNER JOIN
locSL ON locGSP.idGSP = locSL.fiGSP INNER JOIN
ruleChangeTo ON locSL.idSL = ruleChangeTo.fiSL INNER JOIN
dimClaimStatus ON ruleChangeTo.fiClaimStatus = dimClaimStatus.idClaimStatus INNER JOIN
ruleResult ON ruleChangeTo.fiResult = ruleResult.idResult INNER JOIN
ruleRule ON ruleChangeTo.fiRule = ruleRule.idRule INNER JOIN
ruleSet ON ruleChangeTo.fiSet = ruleSet.idSet INNER JOIN
locSLType ON locSL.fiSLType = locSLType.idSLType INNER JOIN
dimClaimStatus AS dimClaimStatus_1 ON ruleChangeTo.fiClaimStatus_ChangeTo = dimClaimStatus_1.idClaimStatus INNER JOIN
dbo.Split(@fiServiceLocations, ',') AS ServiceLocations ON ServiceLocations.Item = ruleChangeTo.fiSL INNER JOIN
dbo.Split(@fiRules, ',') AS Rules ON ruleChangeTo.fiRule = Rules.Item
WHERE (ruleChangeTo.fiSet = @fiRuleSet)
)
И следующим образом я могу получить одно значение счетчика, но на самом деле мне нужно 30 условий для одного и того же запроса (предложение WHERE).
Как лучше всего добиться этого в строго типизированном наборе данных без одного запроса для каждого значения? Я этого не понимаю. Это запрос, который должен быть расширен (из приведенного выше изображения) с различными условиями состояния и их значениями подсчета:
SELECT COUNT(*) AS CountValidReject
FROM dbo.getRuleConfigBySetSlsRules(@fiSet, @ServiceLocations, @Rules) AS RuleConfigFilter
WHERE (fiClaimStatus = 1) AND (fiClaimStatus_ChangeTo = 8)
Чтобы перейти к сути: нужна ли мне хранимая процедура с временной таблицей или что-то в этом роде, или я могу создать и запросить временную таблицу в этом sql в наборе данных? Я не хочу вызывать getRuleConfigBySetSlsRules для каждого значения.
Спасибо
ИЗМЕНИТЬ: это последний запрос, который возвращает 1 строку со многими значениями счетчика в виде столбцов. Еще раз спасибо Стиву за подсказку CASE WHEN. Это очень эффективно.
SELECT COUNT(CASE WHEN (fiResult = 1) AND (fiClaimStatus = 5) AND (fiClaimStatus_ChangeTo = 2) THEN 1 ELSE NULL END) AS RuleAppliesApprovedInvalid,
COUNT(CASE WHEN (fiResult = 1) AND (fiClaimStatus = 5) AND (fiClaimStatus_ChangeTo = 8) THEN 1 ELSE NULL END) AS RuleAppliesApprovedReject,
COUNT(CASE WHEN (fiResult = 1) AND (fiClaimStatus = 5) AND (fiClaimStatus_ChangeTo = 7) THEN 1 ELSE NULL END) AS RuleAppliesApprovedReview,
COUNT(CASE WHEN (fiResult = 1) AND (fiClaimStatus = 9) AND (fiClaimStatus_ChangeTo = 2) THEN 1 ELSE NULL END) AS RuleAppliesEscalatedInvalid,
COUNT(CASE WHEN (fiResult = 1) AND (fiClaimStatus = 9) AND (fiClaimStatus_ChangeTo = 8) THEN 1 ELSE NULL END) AS RuleAppliesEscalatedReject,
COUNT(CASE WHEN (fiResult = 1) AND (fiClaimStatus = 9) AND (fiClaimStatus_ChangeTo = 7) THEN 1 ELSE NULL END) AS RuleAppliesEscalatedReview,
COUNT(CASE WHEN (fiResult = 1) AND (fiClaimStatus = 9) AND (fiClaimStatus_ChangeTo = 1) THEN 1 ELSE NULL END) AS RuleAppliesEscalatedValid,
COUNT(CASE WHEN (fiResult = 1) AND (fiClaimStatus = 8) AND (fiClaimStatus_ChangeTo = 2) THEN 1 ELSE NULL END) AS RuleAppliesRejectInvalid,
COUNT(CASE WHEN (fiResult = 1) AND (fiClaimStatus = 8) AND (fiClaimStatus_ChangeTo = 1) THEN 1 ELSE NULL END) AS RuleAppliesRejectValid,
COUNT(CASE WHEN (fiResult = 1) AND (fiClaimStatus = 7) AND (fiClaimStatus_ChangeTo = 2) THEN 1 ELSE NULL END) AS RuleAppliesReviewInvalid,
COUNT(CASE WHEN (fiResult = 1) AND (fiClaimStatus = 7) AND (fiClaimStatus_ChangeTo = 8) THEN 1 ELSE NULL END) AS RuleAppliesReviewReject,
COUNT(CASE WHEN (fiResult = 1) AND (fiClaimStatus = 7) AND (fiClaimStatus_ChangeTo = 1) THEN 1 ELSE NULL END) AS RuleAppliesReviewValid,
COUNT(CASE WHEN (fiResult = 1) AND (fiClaimStatus = 1) AND (fiClaimStatus_ChangeTo = 5) THEN 1 ELSE NULL END) AS RuleAppliesValidApproved,
COUNT(CASE WHEN (fiResult = 1) AND (fiClaimStatus = 1) AND (fiClaimStatus_ChangeTo = 2) THEN 1 ELSE NULL END) AS RuleAppliesValidInvalid,
COUNT(CASE WHEN (fiResult = 1) AND (fiClaimStatus = 1) AND (fiClaimStatus_ChangeTo = 8) THEN 1 ELSE NULL END) AS RuleAppliesValidReject,
COUNT(CASE WHEN (fiResult = 1) AND (fiClaimStatus = 1) AND (fiClaimStatus_ChangeTo = 7) THEN 1 ELSE NULL END) AS RuleAppliesValidReview,
COUNT(CASE WHEN (fiResult = 2) AND (fiClaimStatus = 5) AND (fiClaimStatus_ChangeTo = 2) THEN 1 ELSE NULL END) AS RuleApplNotApprovedInvalid,
COUNT(CASE WHEN (fiResult = 2) AND (fiClaimStatus = 5) AND (fiClaimStatus_ChangeTo = 8) THEN 1 ELSE NULL END) AS RuleApplNotApprovedReject,
COUNT(CASE WHEN (fiResult = 2) AND (fiClaimStatus = 5) AND (fiClaimStatus_ChangeTo = 7) THEN 1 ELSE NULL END) AS RuleApplNotApprovedReview,
COUNT(CASE WHEN (fiResult = 2) AND (fiClaimStatus = 9) AND (fiClaimStatus_ChangeTo = 2) THEN 1 ELSE NULL END) AS RuleApplNotEscalatedInvalid,
COUNT(CASE WHEN (fiResult = 2) AND (fiClaimStatus = 9) AND (fiClaimStatus_ChangeTo = 8) THEN 1 ELSE NULL END) AS RuleApplNotEscalatedReject,
COUNT(CASE WHEN (fiResult = 2) AND (fiClaimStatus = 9) AND (fiClaimStatus_ChangeTo = 7) THEN 1 ELSE NULL END) AS RuleApplNotEscalatedReview,
COUNT(CASE WHEN (fiResult = 2) AND (fiClaimStatus = 9) AND (fiClaimStatus_ChangeTo = 1) THEN 1 ELSE NULL END) AS RuleApplNotEscalatedValid,
COUNT(CASE WHEN (fiResult = 2) AND (fiClaimStatus = 8) AND (fiClaimStatus_ChangeTo = 2) THEN 1 ELSE NULL END) AS RuleApplNotRejectInvalid,
COUNT(CASE WHEN (fiResult = 2) AND (fiClaimStatus = 8) AND (fiClaimStatus_ChangeTo = 1) THEN 1 ELSE NULL END) AS RuleApplNotRejectValid,
COUNT(CASE WHEN (fiResult = 2) AND (fiClaimStatus = 7) AND (fiClaimStatus_ChangeTo = 2) THEN 1 ELSE NULL END) AS RuleApplNotReviewInvalid,
COUNT(CASE WHEN (fiResult = 2) AND (fiClaimStatus = 7) AND (fiClaimStatus_ChangeTo = 8) THEN 1 ELSE NULL END) AS RuleApplNotReviewReject,
COUNT(CASE WHEN (fiResult = 2) AND (fiClaimStatus = 7) AND (fiClaimStatus_ChangeTo = 1) THEN 1 ELSE NULL END) AS RuleApplNotReviewValid,
COUNT(CASE WHEN (fiResult = 2) AND (fiClaimStatus = 1) AND (fiClaimStatus_ChangeTo = 5) THEN 1 ELSE NULL END) AS RuleApplNotValidApproved,
COUNT(CASE WHEN (fiResult = 2) AND (fiClaimStatus = 1) AND (fiClaimStatus_ChangeTo = 2) THEN 1 ELSE NULL END) AS RuleApplNotValidInvalid,
COUNT(CASE WHEN (fiResult = 2) AND (fiClaimStatus = 1) AND (fiClaimStatus_ChangeTo = 8) THEN 1 ELSE NULL END) AS RuleApplNotValidReject,
COUNT(CASE WHEN (fiResult = 2) AND (fiClaimStatus = 1) AND (fiClaimStatus_ChangeTo = 7) THEN 1 ELSE NULL END) AS RuleApplNotValidReview,
COUNT(CASE WHEN (fiResult = 1) AND (fiClaimStatus = 1) THEN 1 ELSE NULL END) AS AllValidApplies,
COUNT(CASE WHEN (fiResult = 2) AND (fiClaimStatus = 1) THEN 1 ELSE NULL END) AS AllValidNotApplies,
COUNT(CASE WHEN (fiResult = 1) AND (fiClaimStatus = 5) THEN 1 ELSE NULL END) AS AllApprovedApplies,
COUNT(CASE WHEN (fiResult = 2) AND (fiClaimStatus = 5) THEN 1 ELSE NULL END) AS AllApprovedNotApplies,
COUNT(CASE WHEN (fiResult = 1) AND (fiClaimStatus = 7) THEN 1 ELSE NULL END) AS AllReviewApplies,
COUNT(CASE WHEN (fiResult = 2) AND (fiClaimStatus = 7) THEN 1 ELSE NULL END) AS AllReviewNotApplies,
COUNT(CASE WHEN (fiResult = 1) AND (fiClaimStatus = 7) THEN 1 ELSE NULL END) AS AllRejectApplies,
COUNT(CASE WHEN (fiResult = 2) AND (fiClaimStatus = 7) THEN 1 ELSE NULL END) AS AllRejectNotApplies,
COUNT(CASE WHEN (fiResult = 1) AND (fiClaimStatus = 9) THEN 1 ELSE NULL END) AS AllEscalatedApplies,
COUNT(CASE WHEN (fiResult = 2) AND (fiClaimStatus = 9) THEN 1 ELSE NULL END) AS AllEscalatedNotApplies
FROM dbo.getRuleConfigBySlsRules(@ServiceLocations, @Rules) AS RuleConfig
WHERE (fiSet = @fiSet)