OGD-PI under outlet or session

select 
	distinct (A.LocationGroupDefinitionId) OutletGroupID,A.Name OutletGroupName,G.LocationId,H.Code CustomerCode,H.Name CustomerName,H.Assortment1,A.IsDeleted IsDeleted_OG,C.IsDeleted 'IsDeleted_OGD_from PI',/*G.IsDeleted IsDeleted_Outlet,*/D.ProgramID,E.Name ProgramName,/*E.IsDeleted IsDeleted_PR,*/
	convert(char,E.ValidFrom,120)Program_ValidFrom,
	convert(char,E.ValidTo,120)Program_ValidTo,E.Description Program_Description,
	C.ProgramItemId,D.Name ProgramItemName,D.Description PI_Description,
	case 
	when StatusId = '1' then 'Active' 
	when StatusId = '2' then 'Archived'
	when StatusId = '3' then 'In Preparation'
	end as PI_Status,D.IsDeleted IsDeleted_PI,/*StatusId,*/
	convert(char,D.ValidFrom,120)PI_ValidFrom,
	convert(char,D.ValidTo,120)PI_ValidTo,
	/*C.ProgramItemLocationGroupDefinitionId,*/
	convert(char,convert(datetime,D.CreatedOn at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120)PI_CreatedOn,
	convert(char,convert(datetime,E.CreatedOn at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120)PR_CreatedOn,
	convert(char,convert(datetime,D.ModifiedOn at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120)PI_ModifiedOn,F.Username PI_ModifiedByUser,
	convert(char,convert(datetime,A.ModifiedOn at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120)OGD_ModifiedOn,B.Username OGD_ModifiedByUser,I.*
	from LocationGroupDefinition A with (nolock) 
	inner join Security_User B with (nolock) on A.ModifiedByUserId=B.UserId 
	inner join ProgramItemLocationGroupDefinition C with (nolock) on A.LocationGroupDefinitionId=C.LocationGroupDefinitionId
	inner join ProgramItem D on C.ProgramItemId=D.ProgramItemId
	inner join vwProgramList E with (nolock) on D.ProgramId=E.ProgramId
	inner join Security_User F with (nolock) on D.ModifiedByUserId=F.UserId
	inner join LocationGroupDefinitionLocation G with (nolock) on A.LocationGroupDefinitionId=G.LocationGroupDefinitionId
	inner join Location H with (NOLOCK) on G.LocationId=H.LocationId
	full outer join ProgramPortfolio I with (NOLOCK) on C.ProgramItemId=I.ProgramItemId
	where A.ClientId = 100 /*and C.ProgramItemId = '11686'*/
	--and H.Code IN ('1140063'/*,'500093603','500266977''500094339','510399629','500004300','500354217'*/)
	/*A.LocationGroupDefinitionId IN 
	(select LocationGroupDefinitionId
	from LocationGroupDefinitionLocation with (nolock) 
	--where C.ProgramItemId = '10077' locationid = 
	--D.ClientID = 100 *//*and D.IsDeleted = 0 and C.IsDeleted = 0 and StatusId = 1*/ --and ProgramID = 1740 and D.ProgramItemID = 10241
	--and A.LocationGroupDefinitionId IN ('2423'/*,'4189','4205','4216','4231'*/) and StatusId = 1
		and G.locationid IN (select locationid 
		from Location with (nolock) 
		where code IN ('Dummy') /*and clientid = 100*/) --and IsDeleted = 1)
		/*(select locationid 
		from Survey with (nolock) 
		where SessionUid IN ('e1f8a2ec-b064-4877-97f5-a5c73841d248'))) --and IsDeleted = 1)500495630*/
		--and (F.Username In ('CFDEPRL1.A','CFDEPRL1.','JuaSot1.') or B.Username In ('CFDEPRL1.A','CFDEPRL1.','JuaSot1.'))
		and StatusId = 1 and E.ValidTo >= '2023-08-01' and D.ValidTo >= '2023-08-01' and A.IsDeleted = 0 and C.IsDeleted = 0 and D.IsDeleted = 0 --and G.IsDeleted = 0 and H.IsDeleted = 0 and H.IsActive = 1
		--and E.Description != 'Julio 23'
		--and convert(char,convert(datetime,A.ModifiedOn at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120) <'2023-08-01'
	order by /*LocationId,*/A.LocationGroupDefinitionId,PR_CreatedOn desc--A.LocationGroupDefinitionId asc,LocationId asc

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