0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for Ubuntu users and developers

DECLARE @workflowTable AS TABLE (company varchar(50), workflow varchar(50), [date] datetime, cancel varchar(50))
INSERT INTO @workflowTable VALUES
('CompanyA',      'Workflow27',     'June 5 2011','N')
,('CompanyA',      'Workflow27',     'June 5 2011','Y')
,('CompanyA',      'Workflow27',     'June 8 2011','N')
,('CompanyA',      'Workflow27',     'June 12 2011','N')
,('CompanyB',      'Workflow13',     'Apr 4 2011','N')
,('CompanyB',      'Workflow13',     'Apr 9 2011','N')
,('CompanyB',      'Workflow20',     'Dec 11 2011','N')
,('CompanyB',      'Workflow20',     'Dec 17 2011','N')

;WITH partitioned AS (
        SELECT company
            ,workflow
            ,date
            ,cancel
            ,ROW_NUMBER() OVER(PARTITION BY company, workflow
                                ORDER BY date) AS seq
            ,count(*) over (partition by workflow, date order by date) as seq2
        FROM @workflowTable
    )
    SELECT a.company, a.workflow, a.date, a.seq, b.cancel, sum(a.seq2)
    FROM partitioned a
    left join (select workflow, date, cancel from partitioned where cancel = 'Y' group by workflow, date, cancel) b
    on b.workflow = a.workflow
    and b.date = a.date
    WHERE seq = 1
    group by a.company, a.workflow, a.date, a.seq, b.cancel

fork query

edited mar 5

created mar 5 anonymous