SELECT 
	 
      asutNbd_violations.IncidentTypeID
      ,asutNbd_violations.IncidentID
      ,asutNbd_violations.ViolationDate as Date_NAR
      ,asutNbd_violations.BrigadeEnterpriseID
      ,asutNbd_violations.ViolationID as ID_SP_NAR,

	  incidents.IncidentDate,
      incidents.RouteID,

      incidents.PersID,
      -- incidents.CasseteID,

      incidents.MIPersID as 'машинист инструктор'

	 ,routes_personal.RouteID
      ,routes_personal.PersRegID
      ,routes_personal.RoutePostID
      ,routes_personal.BeginDate
      ,routes_personal.EndDate
      ,routes_personal.RouteDate,
	  
	 /* brigades_personal.BrigadeID
      ,brigades_personal.PersID
      ,brigades_personal.RoutesPostID
	  
	,routes.CommID
   ,routes.Date
,routes.TypeID
   ,routes.EnterpriseID
  ,routes.PolygonID
     ,routes.TimeS
      ,routes.BrigadeID*/
	  
	
     personal_registration.EnterpriseID

      --,personal_registration.DateFrom 
	 , CASE 
		WHEN DATEDIFF(yyyy, personal_registration.DateFrom, getdate()) < 1 THEN 'group_0to1y'
		WHEN DATEDIFF(yyyy, personal_registration.DateFrom, getdate()) >= 1 AND DATEDIFF(yyyy, personal_registration.DateFrom, getdate()) < 4 THEN 'group_1to3y'
		WHEN DATEDIFF(yyyy, personal_registration.DateFrom, getdate()) >= 4 AND DATEDIFF(yyyy, personal_registration.DateFrom, getdate()) < 8 THEN 'group_3to8y'
		WHEN DATEDIFF(yyyy, personal_registration.DateFrom, getdate()) >= 8 AND DATEDIFF(yyyy, personal_registration.DateFrom, getdate()) < 12 THEN 'group_8to12y'
		WHEN DATEDIFF(yyyy, personal_registration.DateFrom, getdate()) >= 12 AND DATEDIFF(yyyy, personal_registration.DateFrom, getdate()) < 20 THEN 'group_12to20y'
		WHEN DATEDIFF(yyyy, personal_registration.DateFrom, getdate()) >= 20 THEN 'group_20y'
		ELSE NULL 
	  END AS DateFrom
	  ,personal_registration.DateTo
      ,personal_registration.TabNum
      ,personal_registration.IsDetached as 'уволен или нет'


	  
	/*  CONCAT('+', sps.SeriesID, sps.Number, Litera, sps.Section) AS LokSeries
      -- ,[SeriesID] as LokSeries
      -- Number
      -- Litera
	  -- Section
      ,sps.SpsTypeID
      ,sps.Number8
      ,sps.RoadID as KOD_DOR
      ,sps.EnterpriseID
      ,sps.IsWorksFromKrsps
	  
  	   -- nsi_roads.ID
      ,nsi_roads.CaptionLong*/




      ,personal.LastName
      ,personal.FirstName
      ,personal.PatrName
      ,personal.CurrEnterpriseID  as KOD_DEPO
      ,personal.CurrDateTo
      ,personal.CurrTabNum
      ,personal.CurrIsDetached

      ,personal.MainTabNum as 'основной персонал это табельники людей'
      /*,personal.CurrPostID
      ,personal.CurrPostDate
      ,personal.CurrPostIsUsed
      ,personal.CurrPostIsMash
      ,personal.CurrPostIsPom
      ,personal.CurrPostIsMashInstr*/

	/*,report_send230Fail.*
	
	,personal_notAllowedReport_roads.**/

	FROM asutNbd_violations 
		inner JOIN incidents ON asutNbd_violations.CasseteID = incidents.CasseteID
		inner JOIN routes_personal ON incidents.RouteID = routes_personal.RouteID
		inner JOIN [report_routesIssue] ON routes_personal.[RouteDate] = [report_routesIssue].[RouteDate]
		------ 
		inner JOIN brigades_personal ON routes_personal.RoutePostID = brigades_personal.RoutesPostID
		---- 
	 inner JOIN routes ON brigades_personal.BrigadeID = routes.BrigadeID
	inner JOIN personal_registration ON routes.EnterpriseID = personal_registration.EnterpriseID
		--INNER JOIN sps ON personal_registration.EnterpriseID = sps.EnterpriseID
		--INNER JOIN nsi_roads ON sps.RoadID = nsi_roads.ID
				inner JOIN personal_registration AS PR5 ON incidents.PersID = PR5.[PersID]
		inner JOIN personal ON personal_registration.[DateTo] = personal.[CurrDateTo]
		and personal_registration.[TabNum] = personal.[CurrTabNum]
		--left JOIN personal ON personal_registration.[TabNum] = personal.[CurrTabNum]
		--	AND personal_registration.LastName = personal.LastName
			--AND personal_registration.FirstName = personal.FirstName
		--	AND personal_registration.PatrName = personal.PatrName
		--INNER JOIN report_send230Fail ON incidents.spsID = report_send230Fail.spsID
		--INNER JOIN personal_notAllowedReport_roads ON report_send230Fail.RoadID = personal_notAllowedReport_roads.RoadID
	WHERE incidents.MIPersID > 1
	and asutNbd_violations.ViolationDate >'2019-07-07 00:00:00.000'
		and personal.CurrEnterpriseID is not null
	and personal.[CurrTabNum] is not null