0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for mechanics and DIY enthusiast owners of cars, trucks, and motorcycles

declare @SampleTable table (
[RefNo] [int] NOT NULL,
[TagFullName] [nvarchar](50) NULL,
[TagClassName] [nvarchar](50) NULL,
[Дата] [date] NULL,
[Процент_завершения] [int] NULL)

--Populate the table with values
INSERT INTO  @SampleTable   VALUES
('1', 'GT-001', 'E-TK Crane', '2011-01-08', '30'), 
('2', 'GT-002', 'E-TK Crane', '2011-01-11', '40'), 
('3', 'GT-003', 'E-TK Crane', '2011-02-02', '30'), 
('4', 'GT-001', 'E-TK Crane', '2011-02-03', '50'), 
('5', 'GT-003', 'E-TK Crane', '2011-02-15', '50'), 
('6', 'GT-004', 'E-TK Crane', '2011-02-15', '30'), 
('7', 'GT-002', 'E-TK Crane', '2011-02-15', '60'), 
('8', 'GT-001', 'E-TK Crane', '2011-02-20', '60'), 
('9', 'GT-003', 'E-TK Crane', '2011-03-01', '60'), 
('10', 'GT-004', 'E-TK Crane', '2011-03-05', '50'), 
('11', 'GT-001', 'E-TK Crane', '2011-03-10', '70'), 
('12', 'GT-004', 'E-TK Crane', '2011-03-15', '60'),
('13', 'GT-001', 'E-TK Crane', '2011-04-02', '100'),
('14', 'GT-002', 'E-TK Crane', '2011-04-03', '100'),
('15', 'GT-003', 'E-TK Crane', '2011-04-04', '100'),
('16', 'GT-004', 'E-TK Crane', '2011-04-05', '100'),
('17', 'T-001', 'PIPELINE', '2011-01-08', '30'),
('18', 'T-001', 'PIPELINE', '2011-02-08', '50'),
('19', 'T-001', 'PIPELINE', '2011-04-01', '70');

select  t1.RefNo
,       t1.TagFullname
,       t1.Дата
,       t1.Процент_завершения
,       Average = avg(1.0*(last_per_machine.Процент_завершения))
from     @SampleTable  t1
outer apply
        (
        select  *
        from    (
                select  row_number() over (partition by TagFullname order by RefNo desc) as rn
                ,       *
                from     @SampleTable t2
                where   t2.Дата <= t1.Дата and t2.TagClassName = 'E-TK Crane'
                ) as numbered
        where   rn = 1
        ) as last_per_machine
group by
        t1.RefNo
,       t1.TagFullname
,       t1.Дата
,       t1.Процент_завершения

fork query

created jul 6 11 anonymous