Очистить цены

ВЫБРАТЬ
	КОЛИЧЕСТВО(*) КАК Поле1
ИЗ
	РегистрСведений.Цены КАК Цены

ВЫБРАТЬ
	ЦеныСрезПоследних.ТипЦен,
	ЦеныСрезПоследних.Номенклатура,
	ЦеныСрезПоследних.ЕдиницаИзмерения,
	ЦеныСрезПоследних.ПодразделениеКомпании,
	ЦеныСрезПоследних.Контрагент,
	ЦеныСрезПоследних.Цена
ИЗ
	РегистрСведений.Цены.СрезПоследних КАК ЦеныСрезПоследних

SEL ECT
T1.Fld5012RRef,
T1.Fld5013RRef,
T1.Fld6523RRef,
T1.Fld6524RRef,
T1.Fld6525RRef,
T1.Fld5014RRef,
T1.Fld5015_
FR OM (SEL ECT
T6._Fld6524RRef AS Fld6524RRef,
T6._Fld6523RRef AS Fld6523RRef,
T6._Fld5013RRef AS Fld5013RRef,
T6._Fld6525RRef AS Fld6525RRef,
T6._Fld5015 AS Fld5015_,
T6._Fld5012RRef AS Fld5012RRef,
T6._Fld5014RRef AS Fld5014RRef
FR OM (SEL ECT
T3.Fld5012RRef AS Fld5012RRef,
T3.Fld5013RRef AS Fld5013RRef,
T3.Fld6523RRef AS Fld6523RRef,
T3.Fld6524RRef AS Fld6524RRef,
T3.Fld6525RRef AS Fld6525RRef,
T3.Fld5014RRef AS Fld5014RRef,
T3.MAXPERIOD_ AS MAXPERIOD_,
SUBSTRING(MAX(T5._RecorderTRef + T5._RecorderRRef),1,4) AS MAXRECORDERTRef,
SUBSTRING(MAX(T5._RecorderTRef + T5._RecorderRRef),5,16) AS MAXRECORDERRRef
FROM (SELECT
T4._Fld5012RRef AS Fld5012RRef,
T4._Fld5013RRef AS Fld5013RRef,
T4._Fld6523RRef AS Fld6523RRef,
T4._Fld6524RRef AS Fld6524RRef,
T4._Fld6525RRef AS Fld6525RRef,
T4._Fld5014RRef AS Fld5014RRef,
MAX(T4._Period) AS MAXPERIOD_
FR OM dbo._InfoRg5011 T4 WITH(NOLOCK)
WH ERE T4._Active = 0x01
GROUP BY T4._Fld5012RRef,
T4._Fld5013RRef,
T4._Fld6523RRef,
T4._Fld6524RRef,
T4._Fld6525RRef,
T4._Fld5014RRef) T3
INNER JOIN dbo._InfoRg5011 T5 WITH(NOLOCK)
ON T3.Fld5012RRef = T5._Fld5012RRef AND T3.Fld5013RRef = T5._Fld5013RRef AND T3.Fld6523RRef = T5._Fld6523RRef AND T3.Fld6524RRef = T5._Fld6524RRef AND T3.Fld6525RRef = T5._Fld6525RRef AND T3.Fld5014RRef = T5._Fld5014RRef AND T3.MAXPERIOD_ = T5._Period
WH ERE T5._Active = 0x01
GROUP BY T3.Fld5012RRef,
T3.Fld5013RRef,
T3.Fld6523RRef,
T3.Fld6524RRef,
T3.Fld6525RRef,
T3.Fld5014RRef,
T3.MAXPERIOD_) T2
INNER JOIN dbo._InfoRg5011 T6 WITH(NOLOCK)
ON T2.Fld5012RRef = T6._Fld5012RRef AND T2.Fld5013RRef = T6._Fld5013RRef AND T2.Fld6523RRef = T6._Fld6523RRef AND T2.Fld6524RRef = T6._Fld6524RRef AND T2.Fld6525RRef = T6._Fld6525RRef AND T2.Fld5014RRef = T6._Fld5014RRef AND T2.MAXPERIOD_ = T6._Period AND T2.MAXRECORDERTRef = T6._RecorderTRef AND T2.MAXRECORDERRRef = T6._RecorderRRef) T1

select
t1._Fld6524RRef,
t1._Fld5014RRef,
t1._Fld5013RRef,
t1._Fld6525RRef,
t1._Fld5012RRef,
t1._Fld5015
fr om
_InfoRg5011 as t1
inner join
(
select
_Fld6524RRef,
_Fld5014RRef,
_Fld5013RRef,
_Fld6525RRef,
_Fld5012RRef,
MAX(_Period) as _Period
fr om  
_InfoRg5011
group by 
_Fld6524RRef,
_Fld5014RRef,
_Fld5013RRef,
_Fld6525RRef,
_Fld5012RRef
) as t2
on t1._Fld6524RRef = t2._Fld6524RRef
and t1._Fld5014RRef = t2._Fld5014RRef
and t1._Fld5013RRef = t2._Fld5013RRef
and t1._Fld6525RRef = t2._Fld6525RRef
and t1._Fld5012RRef = t2._Fld5012RRef
and t1._Period = t2._Period