Как написать многопараметрический сценарий CTE?

Я пытаюсь написать сценарий TSQL для отчета SSRS, который использует CTE для выбора записей на основе выбранных параметров. Я ищу наиболее эффективный способ сделать это, используя TSQL и / или SSRS. У меня есть 4 параметра, которым можно присвоить значение NULL (все значения) или одно конкретное значение. Затем в моем CTE у меня есть следующая строка:

    ROW_NUMBER() over(partition by G.[program_providing_service],G.people_id
    order by G.[actual_date] desc) as rowID

Это приведенное выше CTE предназначено для случая, когда Program имеет значение NULL, а People не равно нулю. Мои 4 параметра:

Программа, объект, персонал и люди.

Поэтому я хочу разделять значения только тогда, когда они равны NULL. В настоящее время я реализую это одним CTE в зависимости от значений параметров. Например, если они выберут NULL для всех параметров, кроме People, то этот CTE будет выглядеть так:

    ROW_NUMBER() over(partition by G.people_id
    order by G.[actual_date] desc) as rowID

Или, если все 5 параметров равны нулю:

    ROW_NUMBER() over(partition by G.[program_providing_service], G.[site_providing_service], G.staff_id, G.people_id
    order by G.[actual_date] desc) as rowID

Если они не выбирают NULL для любого из 4 параметров, то мне, вероятно, не нужно разбивать по любому полю, так как мне просто нужна первая запись, упорядоченная по убыванию actual_date. Вот как выглядит мой CTE:

;with cte as
(
Select    distinct
    G.[actual_date],
    G.[site_providing_service],
    p.[program_name],
    G.[staff_id],
    G.program_providing_service,
    ROW_NUMBER() over(partition by G.[program_providing_service],G.people_id
    order by G.[actual_date] desc) as rowID

From 
event_log_rv G With (NoLock)

WHERE 
...
AND (@ClientID Is Null OR [people_id]=@ClientID)
AND (@StaffID Is Null OR [staff_id] = @StaffID)
AND (@FacilityID Is Null OR [site_providing_service] = @FacilityID)
AND (@ProgramID Is Null OR [program_providing_service] = @ProgramID)
and (@SupervisorID is NULL OR staff_id in (select staff_id from #supervisors))
)
SELECT 
    [actual_date],
    [site_providing_service],
    [program_name],
    [staff_id],
    program_providing_service,
    people_id,
    rowID

FROM cte WHERE rowid = 1
ORDER BY [Client_FullName]

где строка ROW_NUMBER меняется в зависимости от выбранных параметров. В настоящее время у меня есть 5 операторов IF в этом скрипте TSQL, которые выглядят так:

IF @ProgramID IS NOT NULL AND @ClientID IS NULL
BEGIN
...
END

with one CTE in each of these IF statements:

IF @FacilityID IS NOT NULL AND @ClientID IS NULL
BEGIN
...

END

IF @ProgramID IS NOT NULL AND @ClientID IS NULL
BEGIN
...
END

IF @StaffID IS NOT NULL AND @ClientID IS NULL
BEGIN
...

END

IF @ClientID IS NOT NULL
BEGIN
...
END

Как я могу закодировать все возможные варианты, независимо от того, выбирают ли они NULL или определенные значения?


tsql common-table-expression
person salvationishere    schedule 14.03.2013    source источник

Ответы (1)


arrow_upward
2
arrow_downward

OMG .... мне потребовалось много времени, чтобы понять, что ты хочешь сделать. В вашем описании есть противоречие. Прошу пересмотреть свое описание. Как вы сказали, вы хотите разделять значения только тогда, когда они равны NULL; затем вы также сказали, что когда они выбирают NULL для всех параметров, кроме людей, тогда вы разбиваете людей на людей ....

Независимо от того, какой способ вы хотите достичь, разделить на «null» или «not null», вы можете создать динамический sql для достижения этой цели вместо добавления большого количества [if ... else]

Следующий код является псевдонимом, определенно не тестировался. Просто намекну. В следующем коде есть одно предположение: ваши параметры имеют приоритет в порядке секционирования, например, если Program не является нулевым (или нулевым), Program находится в первом месте.

    declare @sql varchar(max)
    set @sql = '
    ;with cte as
    (
    Select    distinct
        G.[actual_date],
        G.[site_providing_service],
        p.[program_name],
        G.[staff_id],
        G.program_providing_service,
        ROW_NUMBER() over(partition by
    '
    if(@progarm is null)
       set @sql = @sql + 'G.[program_providing_service],'
    if(@facility is null) 
       set @sql = @sql + 'G.[site_providing_service],'
    if(@staff is null )
       set @sql = @sql + 'G.staff_id,'
    if(@people is null)
       set @sql = @sql + 'G.people_id'
    set @sql = @sql + '
        order by G.[actual_date] desc) as rowID
    From 
    event_log_rv G With (NoLock)
    WHERE 
    ...
    AND (@ClientID Is Null OR [people_id]=@ClientID)
    AND (@StaffID Is Null OR [staff_id] = @StaffID)
    AND (@FacilityID Is Null OR [site_providing_service] = @FacilityID)
    AND (@ProgramID Is Null OR [program_providing_service] = @ProgramID)
    and (@SupervisorID is NULL OR staff_id in (select staff_id from #supervisors))
    )
    SELECT 
        [actual_date],
        [site_providing_service],
        [program_name],
        [staff_id],
        program_providing_service,
        people_id,
        rowID
    FROM cte WHERE rowid = 1
    ORDER BY [Client_FullName]
    '
    exec(@sql)

person ljh    schedule 15.03.2013
comment
Спасибо. Да, после того, как я опубликовал эту проблему, я подумал, что ответом был динамический SQL. Что касается вашего вопроса, я хочу разделить значения только тогда, когда они равны NULL, но с одним исключением. Это исключение касается параметра люди. - person salvationishere; 15.03.2013