declare @d datetime =getdate()
    declare @dn datetime = DATEADD(MONTH,-1,getdate()) 
    declare @drash datetime = DATEADD(MONTH,3,@dn) 

select rr.*
from(
    select p.*, p.kol-p.KOL_pos as ost, ksm.N_RES_DOC, pod.NAIM_P
		,KSM.PR_DO , TAPEI.NEDI
		, ISNULL( KSM.NZAPK,0) NZAPK
		, (select MIN (TNRM) 
			from( select n.TNRM as TNRM  
					from NRMD n
					where n.TIPNRM ='601' and n.KMATGP=p.KMAT and GETDATE() between n.DATEFROM and n.DATETO 
					union 
					select k.NZAPK  as TNRM  
					from KSM k 
					where k.KMAT=p.KMAT 
						and not exists (select n.TNRM as TNRM 
									    from NRMD n 
									    where n.TIPNRM ='601' and n.KMATGP=p.KMAT and GETDATE() between n.DATEFROM 
									            and n.DATETO) )rez
			) zapas					
				
    from(
	    select r.kmat, r.gruppa ,SUM(r.KOL) kol, SUM(r.KOL_pos) KOL_pos
                                    
	    from (
		    select  isnull((select SUM(DMZO.KOL_CR) as KOL_CR 
						    from PLAE 
						    inner join DMZ 
							    on PLAE.UNDOC=DMZ.UNDOC 
						    inner join DMZO 
							    on DMZO.UNDOC_CR=DMZ.UNDOC and DMZO.NPP_CR =PLAE.NPP 
						    inner join DMZ as dmz1 
							    on dmz1.UNDOC=DMZO.UNDOC_DB and DMZ1.KDMT='1   ' 
						    where DMZ.KDMT='_ЗЗП'  and PLAE.KZAP =p.kzap 	and dmz1.PR_RZN='+' 
						    group by PLAE.KZAP, PLAE.KMAT, PLAE.KOL 
						        ),0) KOL_pos 
				    , isnull((select top 1 PLAE.KOL
						    from PLAE 
						    where  PLAE.KZAP =p.kzap  	
						    group by PLAE.KZAP, PLAE.KMAT, PLAE.KOL 
						        ),P.PL_M) KOL				 			                
			,p.kmat
					,(select top 1 sp2.NAIM from sp2 
						inner join ksm 
							on SP2.KOD_C = ksm.ksbg
								and p.KMAT=ksm.kmat 
						where sp2.spr = 'sbg'
							and sp2.NAIM is not null) as gruppa
                                                            ,(select rrr.RR
									from(
									SELECT TOP 1 CASE when exists
						                        (select 1 
						                            from PLAE 
						                            inner join DMZ 
							                        on PLAE.UNDOC=DMZ.UNDOC 
						                            where DMZ.KDMT='_ЗЗП' and PLAE.KZAP=p.kzap and not DMZ.DOSTATUS='И') 
					                        then 1 
					                        else  ISNULL(
								                            (SELECT TOP 1 CASE  WHEN 85*KOL/100<=KOL_CR THEN 1 ELSE 0 	END  AS R 
									                        FROM( 
										                        select top 1000 PLAE.KZAP, PLAE.KMAT, PLAE.KOL, SUM(DMZO.KOL_CR) as KOL_CR 
										                        from PLAE 
										                        inner join DMZ 
											                        on PLAE.UNDOC=DMZ.UNDOC 
										                        inner join DMZO 
											                        on DMZO.UNDOC_CR=DMZ.UNDOC and DMZO.NPP_CR =PLAE.NPP 
										                        inner join DMZ as dmz1 
											                        on dmz1.UNDOC=DMZO.UNDOC_DB and DMZ1.KDMT='1   '  
										                        where DMZ.KDMT='_ЗЗП'  and PLAE.KZAP =p.kzap and dmz1.PR_RZN='+' 
										                        group by PLAE.KZAP, PLAE.KMAT, PLAE.KOL
										                        ) AS REZ	
								                            ), 0 
								                        ) end AS RR
														)rrr
														where rr < 1
                        ) as pr_v
											

		    from PLA p
		    inner join KSM
				on p.KMAT=ksm.KMAT
		    where p.KPLT='611' 
		            and  DATEADD(DAY, ksm.NZAPD , P.DATE_ISP) ------------------------------------------------*******
		                    between @dn and @d
							--and p.pr_utv = ''
			    and not exists(select PLAE.KZAP, PLAE.KMAT, PLAE.KOL, SUM(DMZO.KOL_CR) as KOL_CR 
						    from PLAE 
						    inner join DMZ 
							    on PLAE.UNDOC=DMZ.UNDOC 
						    inner join DMZO 
							    on DMZO.UNDOC_CR=DMZ.UNDOC and DMZO.NPP_CR =PLAE.NPP 
						    inner join DMZ as dmz1 
							    on dmz1.UNDOC=DMZO.UNDOC_DB  and DMZ1.KDMT='1   ' 
						    where DMZ.KDMT='_ЗЗП'  and PLAE.KZAP =p.kzap 	and dmz1.PR_RZN='+' 
						    group by PLAE.KZAP, PLAE.KMAT, PLAE.KOL 
						    having  PLAE.KOL <= SUM(DMZO.KOL_CR) 
							)
		    ) r
		where gruppa not like '%Савельев КАНБАН%' and gruppa not like '%КАНБАН Кольцова%' 
			and gruppa not like '%Чечева%' and gruppa not like '%Вспомогательные материалы Савельев%'
            and gruppa not like '%Вспомогательные материалы Козырева%' and gruppa not like '%Вспомогательные материалы Кузьмин%' 
            and gruppa not like '%Вспомогательные материалы Кольцова%'   and gruppa not like '%Вспомогательные материалы Демин%' 
            and gruppa not like '%Вспомогательные материалы Смольский%'   and gruppa not like '%Вспомогательные материалы Медведева%'
            and gruppa not like '%Вспомогательные материалы Чечева%'   and gruppa not like '%Оснастка Смольский%'
            and gruppa not like '%Оснастка Демин%'   and gruppa not like '%Оснастка Кольцова%'
            and gruppa not like '%Оснастка Резерв%'   and gruppa not like '%Крепеж по ГОСТ%'
            and gruppa not like '%Сварочные материалы%'   and gruppa not like '%Технические газы%'
            and gruppa not like '%Группа С Козырева%'   and gruppa not like '%Группа С Кольцова%'
	    group by r.KMAT,  r.gruppa
        )p
    inner join ksm 
	    on p.KMAT=ksm.KMAT 
	            and ksm.KSKL in (11103, 11102, 11101)
	        -- and ksm.KSKL in ( 11102)	                             
    inner join pod

	    on ksm.KSKL=pod.CEH
    inner join TAPEI 
		on KSM.EDI=TAPEI.EDI
	--where p.kol-p.KOL_pos - ISNULL( KSM.NZAPK,0)>0
	)rr
					
	where rr.ost - ISNULL( rr.zapas,0)>0

     ,(select rrr.RR
									from(
									SELECT TOP 1 CASE when exists
						                        (select 1 
						                            from PLAE 
						                            inner join DMZ 
							                        on PLAE.UNDOC=DMZ.UNDOC 
						                            where DMZ.KDMT='_ЗЗП' and PLAE.KZAP=p.kzap and not DMZ.DOSTATUS='И') 
					                        then 1 
					                        else  ISNULL(
								                            (SELECT TOP 1 CASE  WHEN 85*KOL/100<=KOL_CR THEN 1 ELSE 0 	END  AS R 
									                        FROM( 
										                        select top 1000 PLAE.KZAP, PLAE.KMAT, PLAE.KOL, SUM(DMZO.KOL_CR) as KOL_CR 
										                        from PLAE 
										                        inner join DMZ 
											                        on PLAE.UNDOC=DMZ.UNDOC 
										                        inner join DMZO 
											                        on DMZO.UNDOC_CR=DMZ.UNDOC and DMZO.NPP_CR =PLAE.NPP 
										                        inner join DMZ as dmz1 
											                        on dmz1.UNDOC=DMZO.UNDOC_DB and DMZ1.KDMT='1   '  
										                        where DMZ.KDMT='_ЗЗП'  and PLAE.KZAP =p.kzap and dmz1.PR_RZN='+' 
										                        group by PLAE.KZAP, PLAE.KMAT, PLAE.KOL
										                        ) AS REZ	
								                            ), 0 
								                        ) end AS RR
														)rrr
														where rr < 1
                        ) as pr_v