Coldfusion: нужна помощь с выводом запроса по одной строке на систему вместо нескольких строк

ИСТОРИЯ: Система прошла от 1 до 19 или около того статусов, прежде чем была переведена в производство. Мне нужно построить отчет, показывающий дату, когда система прошла через статус, и NA, если система не прошла через статус.

ТРЕБОВАНИЯ: Отчет должен выглядеть примерно так:

System      Initial     Operations  PIM_Assigned    PIM_Complete    Database    Application 
Server001   9/1/2011    NA          9/2/2011        NA              NA          9/1/2011
Server002   9/10/2011   NA          9/5/2011        9/25/2011       NA          9/9/2011
Server003   9/21/2011   9/22/2011   NA              NA              9/24/2011   NA
Server004   9/23/2011   9/19/2011   9/23/2011       9/20/2011       9/23/2011   9/1/2011

Вот запрос с образцом дампа данных (дамп не соответствует приведенному выше — приведенное выше для иллюстрации):

select status, convert(varchar,effectivedate,101) e, systemname  
from si_statushistory
where systemname='SERVER052'  
order by e desc, history_id desc

с выводом моего запроса, выглядящим так:

PSI            09/09/2011   SERVER052  
Application    09/09/2011   SERVER052  
Operations     09/09/2011   SERVER052  
Application    07/14/2011   SERVER052  
Operations     07/13/2011   SERVER052  
Operations     07/13/2011   SERVER052  
PSI            07/13/2011   SERVER052  
PIM Assigned   06/08/2011   SERVER052  
PSI            06/08/2011   SERVER052  
SD_Verify      01/15/2012   SERVER052  
PSI Operations 01/08/2012   SERVER052  
Frame Team     01/01/2011   SERVER052

Пример того, как будет выглядеть ОДНА строка:

something is missing here

Надеюсь понятно и понятно...

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


sql-server-2005 coldfusion-8 cfquery
person HPWD    schedule 22.09.2011    source источник
comment
Система может проходить через статус более одного раза, поэтому мне понадобится самая последняя дата.   -  person HPWD    schedule 22.09.2011
comment
Каковы все возможные значения статуса? Например, ваши требования показывают столбец с именем Initial, но я не вижу никаких этих данных в вашем выводе ниже этого...   -  person Aaron Bertrand    schedule 22.09.2011

Ответы (1)


arrow_upward
0
arrow_downward 

CREATE PROCEDURE dbo.ReturnPivotedSystemInfo
AS
BEGIN
    SET NOCOUNT ON;

    ;WITH x AS
    (
        SELECT 
            [system] = systemname, 
            [status], 
            ed = CONVERT(CHAR(10), effectivedate, 101), -- not varchar w/o length
            rn = ROW_NUMBER() OVER 
                (PARTITION BY systemname, status ORDER BY effectivedate DESC)
        FROM dbo.si_statushistory
        -- where clause here
    )
    SELECT [system], 
        Initial      = COALESCE(MAX(CASE WHEN [status] = 'Initial'      THEN ed END), 'NA'),
        Operations   = COALESCE(MAX(CASE WHEN [status] = 'Operations'   THEN ed END), 'NA'),
        PIM_Assigned = COALESCE(MAX(CASE WHEN [status] = 'PIM Assigned' THEN ed END), 'NA')
        --, repeat for other possible values of status
    FROM x
    WHERE rn = 1
    GROUP BY [system];
END
GO

Теперь вашему ColdFusion просто нужно выполнить хранимую процедуру dbo.ReturnPivotedSystemInfo, и с этого момента он сможет вести себя так же, как если бы вы вызвали SELECT * FROM sometable...

person Aaron Bertrand    schedule 22.09.2011
comment
@ Аарон-Бертрад, эй. Я не уверен, как реализовать это с помощью Coldfusion, но я попробую. :) - person HPWD; 22.09.2011
comment
Что ж, запихивать любой сложный запрос в ColdFusion довольно грязно. Разве вы не можете создать хранимую процедуру и попросить ColdFusion вызвать вашу хранимую процедуру? Тогда, независимо от того, насколько сложен ваш запрос, код ColdFusion, который вызывает запрос, всегда выглядит одинаково... - person Aaron Bertrand; 22.09.2011
comment
как мне запустить это, чтобы проверить это? Кроме того, почему система в скобках? Является ли имя системы зарезервированным словом? - person HPWD; 22.09.2011
comment
Да, я могу поместить его в сохраненный процесс. Я думал, что смогу выполнить cfoutput group=systemname, а затем зациклиться на столбцах состояния. Мальчик был я далеко. - person HPWD; 22.09.2011
comment
@aaron-betrand - Спасибо за создание для меня процедуры создания процедуры, но когда я пытаюсь ее запустить, я получаю следующую ошибку: Msg 170, уровень 15, состояние 1, процедура si_st_ASH_Report, строка 7 строка 7: неправильный синтаксис рядом ';'. Сообщение 195, уровень 15, состояние 10, процедура si_st_ASH_Report, строка 13 «ROW_NUMBER» не является распознанным именем функции. - person HPWD; 22.09.2011
comment
system и status оба ok, но я по умолчанию прилагаю любые ключевые слова/зарезервированные слова, которые по-разному отображаются в SSMS. Что касается ошибки, вы используете SQL Server 2005, верно? Возможно ли, что ваша база данных настроена на режим совместимости 2000 (80)? - person Aaron Bertrand; 22.09.2011
comment
@ aaron-betrand - я новичок в работе с этой базой данных, но да, вполне возможно, что для нее установлено значение 2000. Подтверждено, да, для нее установлен режим совместимости 2000. - person HPWD; 22.09.2011
comment
Думаю, мне нужно более простое решение. Я не могу заставить это работать. :( - person HPWD; 23.09.2011
comment
Что значит не могу заставить это работать? Я не могу это исправить, извините. - person Aaron Bertrand; 23.09.2011
comment
Мне удалось зациклить значения в массиве, а затем отсортировать массив. Я действительно ценю вашу помощь. Я извиняюсь за то, что не могу заставить это работать. Я знаю, что это было бесполезно, и я стараюсь быть более тщательным с моими деталями. Я расстроился и обленился своим ответом. - person HPWD; 01.10.2011