CREATE PROCEDURE dbo.sp_stat (@d as datetime)
AS SELECT     
   '(' +  REPLICATE('0',  4  - LEN(convert(nchar( 4 ),KT.НомерВывода))) + convert(nvarchar,KT.НомерВывода) + ') - ' + KT.КатегорияТовара as KAT,
   T.ТоварID,
   T.Наименование,
   T.Количество as OST,
   isnull(PR.KLV, 0 ) as KLV,
   (isnull(L.tseh_vyr, 0 ) - isnull(L.KLV, 0 )) * T.nesdano as NSD,
   TSY.tseh_vyr, 
   S1.st_kolvo / S1.st_iter as N1,
   PR.KLV  + TS.tseh_vyr - PR.KLV  - S1.st_kolvo / S1.st_iter as S1,
   S2.st_kolvo / S2.st_iter as N2,
   TS.tseh_vyr - S2.st_kolvo / S2.st_iter + T.Количество - S1.st_kolvo / S1.st_iter as S2,
   S3.st_kolvo / S3.st_iter as N3,
   S3.st_kolvo / S3.st_iter - (TS.tseh_vyr - S2.st_kolvo / S2.st_iter + T.Количество - S1.st_kolvo / S1.st_iter) as S3,
   TS.tseh_zak,
   TS.tseh_vyp,
   TS.tseh_id

FROM  Товары T 
           left outer join (select * from dbo.t_tseh TH left outer join 
                                   (select PP.bpTovarID, sum(bpKolichestvo) as KLV from dbo.tabBorDataPriem PP where dbo.datevalue(PP.bpDocData) = @d group by PP.bpTovarID) P 
                                    on P.bpTovarID = TH.tseh_tovar where TH.tseh_date = @d) L on T.ТоварID = L.tseh_tovar
           left outer join (select * from dbo.t_stat ST where ST.st_day = datepart(dw, @d) +  1 ) S1 ON T.ТоварID = S1.st_tovar 
           left outer join (select * from dbo.t_stat ST where ST.st_day = datepart(dw, @d) +  2 ) S2 ON T.ТоварID = S2.st_tovar 
           left outer join (select * from dbo.t_stat ST where ST.st_day = datepart(dw, @d) +  3 ) S3 ON T.ТоварID = S3.st_tovar 
           left outer join (select * from dbo.t_tseh TH where dbo.datevalue(TH.tseh_date) = @d) TS on T.ТоварID = TS.tseh_tovar
           left outer join (select * from dbo.t_tseh THY where dbo.datevalue(THY.tseh_date) = @d- 1 ) TSY on T.ТоварID = TSY.tseh_tovar
           left outer join (select P.bpTovarID, sum(bpKolichestvo) as KLV from dbo.tabBorDataPriem P where dbo.datevalue(P.bpDocData) = @d group by P.bpTovarID) PR ON T.ТоварID = PR.bpTovarID 
           left outer join dbo.КатегорииТовара KT ON T.КатегорияТовараID = KT.КатегорияТовараID

where T.ПрайсID < 4 
order by KT.НомерВывода, T.НомерВывода
GO