Untitled

mail@pastecode.io avatar
unknown
sql
a month ago
3.7 kB
0
Indexable
Never
select
	opportunity."AccountId" as account_identificator,
    createdby."Name" as created_by,
    opportunity."CreatedOn"  + interval '3 hours' created_on,
    opportunity."Description" as description,
    modifiedby."Name" as modified_by,
    opportunity."ModifiedOn"  + interval '3 hours' as modified_on,
	owner."Name" as owner,
    opportunity."SmBrandId" as brand_identificator,
    content_status."Name" as content_status,
    opportunity."SmContractId" as contract_identificator,
    opportunity."SmDuplicateByNameId" as duplicated_by_name_id,
    opportunity."SmExternalCode" as sm_external_code,
    opportunity."SmIsShowcaseReady" as is_showcase_ready,
    opportunity."SmLeadFirstId" as first_lead_id,
    opportunity."SmLossComment" as loss_comment,
    loss_reason."Name" as loss_reason,
    opportunity."SmParentId" as sm_parent_id,
    segment."Name" as segment,
    stage."Name" as stage,
    opportunity."Title"  title, 
    type."Name" as type,
    opportunity."SmLinkPhoto" as link_photo,
    opportunity."SmLinkFoodTable" as link_food_table,
    opportunity."SmIntegrationChat" as integration_chat,
    opportunity."SmLinkFoodPhoto" as link_food_photo,
    opportunity."SmChatPhotoShoot" as chat_photoshoot,
    opportunity."SmIsIntegration" as is_integration,
    opportunity."SmIsPartnerLoadsMenu" as partner_loads_menu,
    opportunity."SmIsInstalledPhoto" as installed_photo,
    opportunity."SmIsDeliveryAreaLoaded" as delivery_area_loaded,
    opportunity."SmContentTask" as content_task,
    opportunity."SmLinkAdmin" as link_admin,
    connection_status."Name" as connection_status,
    opportunity."SmIsContentIntegrationComplete" as content_integration_complete,
    opportunity."Id" identificator,
    opportunity."SmIsAddConnectionRest" as add_conection_rest,
    opportunity."SmPhotoShootRequired" as photoshoot_required,
    contact."Name" as contact,
    category."Name" as category,
    vertical."Name" as vertical,
    opportunity."Notes"  as notes,
    activation.activated_at as activated_at
from "Opportunity" opportunity
    left join "Contact" createdby
        on createdby."Id" = opportunity."CreatedById"  
    left join "Contact" modifiedby
        on modifiedby."Id" = opportunity."ModifiedById"  
    left join "OpportunityType" type
        on type."Id" = opportunity."TypeId"  
    left join "OpportunityStage" stage
        on stage."Id" = opportunity."StageId"  
    left join "Contact" owner
        on owner."Id" = opportunity."OwnerId"  
    left join "OpportunityCategory" category
        on category."Id" = opportunity."CategoryId"  
    left join "Contact" contact
        on contact."Id" = opportunity."ContactId"  
    left join "SmSegment" segment
        on segment."Id" = opportunity."SmSegmentId"  
    left join "SmOpportunityContentStatus" content_status
        on content_status."Id" = opportunity."SmContentStatusId"  
    left join "SmOpportunityLossReason" loss_reason
        on loss_reason."Id" = opportunity."SmLossReasonId"  
    left join "SmOpportunityVertical" vertical
        on vertical."Id" = opportunity."SmVerticalId"  
    left join "SmConnectionStatus" connection_status
        on connection_status."Id" = opportunity."SmConnectionStatusId" 
    left join (select slr."SmrRecordId" opportunity_id, min(slr."CreatedOn"+ INTERVAL '3 hour') as activated_at from "SmrLoggingRecord" slr 
			left join "SmrLoggingSchema" sls 
				on slr."GoLoggingSchemaId" = sls."Id" 
			where sls."SmrSchemaName" = 'Opportunity'
			and slr."SmrColumnCaption" = 'Стадия'
			and slr."SmrNewValue" = 'Создание админки'
			group by 1) activation
		on opportunity."Id" = activation.opportunity_id
Leave a Comment