Routes

select RouteId,RT.LocationId,LOC.Code,LOC.Name,RT.IsDeleted,RT.UserId,SU2.Username AppUser,
convert(char,convert(datetime,RT.CreatedOn at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120)CreatedOn,RT.CreatedByUserId,
convert(char,convert(datetime,RT.ModifiedOn at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120)ModifiedOn,RT.ModifiedByUserId,SU.Username Modifiedby,
convert(char,convert(datetime,RT.NextVisit at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120)NextVisit,
convert(date,RT.RouteDate,120)RouteStartDate,convert(date,RT.RouteEndDate,120)RouteEndDate,RT.*
from Route RT with (nolock)
inner join Security_User SU with (nolock) on RT.ModifiedbyUserId=SU.UserId
inner join Security_User SU2 with (nolock) on RT.UserId=SU2.UserId
inner join Location LOC with (nolock) on RT.LocationId=LOC.LocationId
where LOC.ClientId = '100' --and SU2.Username IN ('merida1@storecheck.com','merida2@storecheck.com')
/*and LOC.LocationId IN (
Select 
    LocationId
	from vwLocationList A with (nolock) 
	where ClientId = 138 --and SalesGroupName = 'Universidad' and Assortment1 = 1
	and Code IN
	('10040309'
	)
	--and IsActive = 0
	)*/
--and RT.CreatedByUserId = '57179'--LOC.code = '10040309'
/*'601499409')*/
--ORDER by RT.modifiedon DESC
--where RT.UserId = '64324' and RT.createdbyuserid = '64301'
/*where RT.UserId IN ('62016',
'60437')*/
--and RT.clientid = 100
--and RT.ModifiedByUserId = '64301' --'64301' '59793' 
and RT.RouteDate >='2024-01-01'--between '2022-01-01' and '2023-01-31'
--and RouteEndDate >= '2023-01-01'
and SU2.UserName IN ('AUDCUU05','AUDCUU04','AUDCUU03','AUDCUU02','AUDCUU01')
--and RT.RouteId = '17427412'
--order by RT.RouteDate asc
--and RT.ModifiedOn >= '2023-03-31' --and RT.ModifiedByUserId = '59793'
--and SU.Username NOT IN ('iaguilar@storecheck.com',
--and convert(char,convert(datetime,RT.CreatedOn at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120) >= '2023-08-12 00:00:00'
--and convert(char,convert(datetime,RT.CreatedOn at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120) < '2023-08-15 22:00:00'
--and RouteID = '24215327'
--and RT.IsDeleted = 0 
order by SU2.UserName asc, RT.RouteDate asc, RT.IsDeleted asc

--------------------------------------------------