------------------------------------------------------------------------------------------------------------------------------------------------------------------------ /*Suite&Premium - Static General Info Fields Query*/ --Note: Use 'Client_' as prefix in an alias to indicate this field is specific to a client -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- /*====================Testing Parameters===========================*/ --Declare @SuiteContractID uniqueidentifier = (select top 1 kore_premiumdealid from kore_premiumdeal WHERE -- statecode=0 and statuscode=4) --kore_name = '1002')--'1021')--'1038')--'1035') --'1044')-- /*====================Testing Parameters===========================*/ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- /*Note: 1. Use 'Client_' as prefix in an alias to indicate this field is specific to a client */ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- /*========================Parameters&Common Values===========================*/ declare @today datetime = DATEADD(hour,-5,GETDATE()) declare @DateFormat varchar(12) = (select top 1 kore_value from kore_setting where kore_name = 'Date Format') --Premium Deal Events List-- declare @numofevents int = (select count(pde.kore_name) from kore_premiumdealevent pde where kore_premiumdealid = @SuiteContractID) DECLARE @PremiumEventsListOneLine VARCHAR(MAX), @PremiumEventDatesListOneLine VARCHAR(MAX) SELECT @PremiumEventsListOneLine = COALESCE(@PremiumEventsListOneLine+', ' ,'') + CASE WHEN ke.kore_name like 'vs. %' THEN SUBSTRING(ke.kore_name, 4, LEN(ke.kore_name)) -- removed the vs.% part ELSE ke.kore_name END, @PremiumEventDatesListOneLine = COALESCE(@PremiumEventDatesListOneLine+', ' ,'') + DATENAME(dw, ke.kore_date) + ', ' + FORMAT(ke.kore_date, 'MMMM d, yyyy hh:mm tt') FROM kore_event ke inner join kore_premiumdealevent pde on pde.kore_eventid = ke.kore_eventid WHERE pde.kore_premiumdealid = @SuiteContractID ORDER BY ke.kore_date --Premium Deal Ticket List-- DECLARE @TotalParking int = (select SUM(kore_numberofparking) from kore_premiumdeallocationbase where kore_premiumdealid = @SuiteContractID) DECLARE @TotalTicket int = (select case when SUM(kore_numberoftickets) is null then count(*) else SUM(kore_totalsrotickets) end from kore_premiumdeallocationbase where kore_premiumdealid = @SuiteContractID) DECLARE @TotalSRO int = (select SUM(kore_totalsrotickets) from kore_premiumdeallocationbase where kore_premiumdealid = @SuiteContractID) --Premium Deal Info-- declare @PremiumDealTerms int = (select Count(*) from kore_premiumdealcontractyear where kore_premiumdealid = @SuiteContractID) declare @FirstYearFee decimal = ( select distinct pdcy.kore_gross from kore_premiumdeal pd INNER JOIN kore_premiumdeallocationbase pdl on pd.kore_premiumdealid = pdl.kore_premiumdealid INNER JOIN kore_premiumdealcontractyear pdcy on pd.kore_premiumdealid = pdcy.kore_premiumdealid where pd.kore_premiumdealid = @SuiteContractID and year(pdcy.kore_yearstart) = year(pd.kore_contractstartdate) ) declare @FirstYearBillingTotal decimal = ( select sum(kore_estimatedamount) BillingAmt from kore_premiumdeal pd inner join kore_bill b on b.kore_premiumdealid = pd.kore_premiumdealid where pd.kore_premiumdealid = @SuiteContractID and b.kore_contractyearnumber = 0 ) --Premium Deal Location List One Line-- declare @SectionOrSeatNameListOneLine varchar(max) = ( select dbo.DistinctConcat( coalesce(pdl.kore_name, ss.kore_name, s.kore_name, null) , ', ', 1) from kore_premiumdeallocation pdl left join kore_seat s on s.kore_seatid = pdl.kore_seatid left join kore_section ss on ss.kore_sectionid = pdl.kore_suiteid where pdl.kore_premiumdealid = @SuiteContractID ) declare @SectionListOneLine varchar(max) = ( select dbo.DistinctConcat( isnull(s.kore_sectionidname, pdl.kore_suiteidname), ', ', 1) Section from kore_premiumdeallocation pdl left join kore_seat s on s.kore_seatid = pdl.kore_seatid where pdl.kore_premiumdealid = @SuiteContractID ) declare @TicketingSectionListOneLine varchar(max) = ( select dbo.SimpleDistinctConcat( isnull(s.kore_ticketingsection, substring(pdl.kore_suiteidname,2,10)) ) TicketingSection from kore_premiumdeallocation pdl left join kore_seat s on s.kore_seatid = pdl.kore_seatid where pdl.kore_premiumdealid = @SuiteContractID ) declare @SeatsListOneLine varchar(max) = ( select dbo.DistinctConcat(s.kore_number, ', ', 1) Seats from kore_premiumdeallocation pdl left join kore_seat s on s.kore_seatid = pdl.kore_seatid where pdl.kore_premiumdealid = @SuiteContractID ) declare @RowListOneLine varchar(max) = ( select dbo.SimpleDistinctConcat(s.kore_row) [Row] from kore_premiumdeallocation pdl left join kore_seat s on s.kore_seatid = pdl.kore_seatid where pdl.kore_premiumdealid = @SuiteContractID ) --Add on Related declare @AddOnListOneLine varchar(max) = ( select dbo.SimpleDistinctConcat(concat(pdl.kore_quantity, ' (', kore.NumberToWords(pdl.kore_quantity) ,') ', pdl.kore_premiumaddonidname)) from kore_premiumdealaddon pdl where kore_premiumdealid = @SuiteContractID ) --==Common Value Requests==-- --==Select Statement - Output Fields==-- select top 1 --General Format(@today,'MMMM d, yyyy') AS Today ,Format(@today,@DateFormat) AS TodayShort ,FORMAT(@today, '%d') AS CurrentDay ,CASE WHEN FORMAT(@today, '%d') IN ( 11, 12, 13 ) THEN CAST(FORMAT(@today, '%d') AS VARCHAR(10)) + 'th' WHEN FORMAT(@today, '%d') % 10 = 1 THEN CAST(FORMAT(@today, '%d') AS VARCHAR(10)) + 'st' WHEN FORMAT(@today, '%d') % 10 = 2 THEN CAST(FORMAT(@today, '%d') AS VARCHAR(10)) + 'nd' WHEN FORMAT(@today, '%d') % 10 = 3 THEN CAST(FORMAT(@today, '%d') AS VARCHAR(10)) + 'rd' ELSE CAST(FORMAT(@today, '%d') AS VARCHAR(10)) + 'th' END AS Today_OrdinaryDay ,FORMAT(@today, 'MMMM, yyyy') AS Today_MonthYear ,FORMAT(@today,'yyyy') AS Today_Year ,Format(dateadd(YEAR,1, @today),'yyyy') AS NextYear ,(select kore_year from kore_season where kore_default = 1) AS CurrentSeason --Deal Info ,pd.kore_name AS PremiumDealNumber ,pd.kore_accountnumber AS AccountNumber ,pd.kore_description AS Description ,pd.kore_contracttypename AS DealType ,pd.statuscodename AS DealStatus ,pd.kore_approvalstatusname AS ApprovalStatus ,case when pd.kore_contracttypename = 'Lease' then nchar(10004) end AS IsLease ,case when pd.kore_contracttypename = 'Rental' then nchar(10004) end AS IsRental ,pd.kore_leasetypeidname AS LeaseSubType ,pd.kore_orderstatusname AS OrderStatus ,kore_rentaltypeidname AS RentalType ,DATEPART(yyyy, kore_contractstartdate) AS ContractStartYear ,DATEPART(mm, kore_contractstartdate) AS ContractStartMonth ,DATEPART(dd, kore_contractstartdate) AS ContractStartDay ,FORMAT(kore_contractstartdate, 'MMMM, yyyy') AS ContractStartMonthYear ,CASE WHEN FORMAT(kore_contractstartdate, '%d') IN ( 11, 12, 13 ) THEN CAST(FORMAT(kore_contractstartdate, '%d') AS VARCHAR(10)) + 'th' WHEN FORMAT(kore_contractstartdate, '%d') % 10 = 1 THEN CAST(FORMAT(kore_contractstartdate, '%d') AS VARCHAR(10)) + 'st' WHEN FORMAT(kore_contractstartdate, '%d') % 10 = 2 THEN CAST(FORMAT(kore_contractstartdate, '%d') AS VARCHAR(10)) + 'nd' WHEN FORMAT(kore_contractstartdate, '%d') % 10 = 3 THEN CAST(FORMAT(kore_contractstartdate, '%d') AS VARCHAR(10)) + 'rd' ELSE CAST(FORMAT(kore_contractstartdate, '%d') AS VARCHAR(10)) + 'th' END AS ContractStartOrdinaryDay ,FORMAT(kore_contractstartdate, 'MMMM d, yyyy') AS ContractStartDate ,FORMAT(kore_contractenddate, 'MMMM d, yyyy') AS EndContractDate ,FORMAT(kore_contractenddate, 'yyyy') AS EndYear --Multi-location Deal Location Info ,@SectionOrSeatNameListOneLine AS SectionOrSeatNameListOneLine ,@SectionListOneLine AS SectionListOneLine ,@TicketingSectionListOneLine AS TicketingSectionListOneLine ,@SeatsListOneLine AS SeatsListOneLine ,@RowListOneLine AS RowListOneLine --Single Game Event Location Info ,pdl.kore_name AS LocationType ,pdl.kore_name AS LocationName ,SUBSTRING(pdl.kore_name,0,CHARINDEX('_', pdl.kore_name)) AS SectionSuiteClean ,ISNULL(pdl.kore_numberoftickets,0) AS NumofTicket ,ISNULL(pdl.kore_totalsrotickets,0) AS NumofSRO ,isnull(pdl.kore_numberofparking,0) AS ParkingSpaces ,ISNULL(sec.kore_maxcapacity,0) AS MaxCapacity -- Deal Value Info - Applicable for Single Game Event & Multi-Year Deals ,replace('$'+convert(nvarchar(max),cast(isnull(kore_depositamount, 0) AS money),1), '.00', '') AS SecurityDeposit ,case when kore_depositamount <> 0 then 'Yes' else 'No' end AS If_DepositPlaced ,case when pd.kore_escalationtype <> 0 then 'Yes' else 'No' end AS If_Escalation ,format(isnull(pd.kore_escalationrate, 0), 'P') AS EscalationRate ,FORMAT(pd.kore_total, 'C') AS TotalContractGross ,FORMAT(@FirstYearFee, 'C') AS FirstYearFee ,FORMAT(@FirstYearBillingTotal, 'C') AS FirstYearBillingTotal ,FORMAT(pdcy.kore_gross, 'C') AS AnnualFee --selecting the first year amount due to the order by and top 1 clause ,FORMAT(isnull(pdcy.kore_gross, 0) - isnull(kore_depositamount, 0), 'C') AS AmountDue --selecting the first year amount due to the order by and top 1 clause ,@PremiumDealTerms AS PremiumDealTerms ,@PremiumEventsListOneLine AS PremiumEventsListOneLine ,@PremiumEventDatesListOneLine AS PremiumEventDatesListOneLine ,(select top 1 kore_name from kore_venue) AS Venue ,@TotalTicket AS TotalTicket ,@TotalParking AS TotalParking ,@TotalSRO AS TotalSRO ,@AddOnListOneLine AS AddOnListOneLine --==Account or Contact Info==-- --Account ,sa.Name AS AccountName ,sa.kore_legalname AS AccountLegalName ,isnull(sa.kore_legalname, sa.Name) AS AccountLegalNameorAccountName ,isnull(sa.FinanceAccountNumber,'') AS AccountFinanceNumber ,si.Name AS AccountIndustry ,Isnull(sa.MailingStreet, '') AS AccountAddress1 ,Isnull(sa.MailingStreet2, '') AS AccountAddress2 ,Isnull(sa.city, '') AS AccountCity ,Isnull(sa.state, '') AS AccountState ,Isnull(sa.Country, '') AS AccountCountry ,Isnull(sa.zip, '') AS Accountzip ,Isnull(sa.fax, '') AS AccountFax ,RTRIM( case when isnull(sa.MailingStreet, '') <> '' then sa.MailingStreet else '' end + case when isnull(sa.MailingStreet2, '') <> '' then ', ' + sa.MailingStreet2 else '' end) AS AccountAddrLine1 ,Rtrim(Isnull(sa.city, '') + ', ' + Isnull(sa.state, '') + ', ' + Isnull(sa.zip, '')) AS AccountAddrLine2 --General Contact Info Only ,ISNULL(sc.FullName, sc.firstname +' '+ sc.lastname) AS ContactMember ,RTRIM( case when isnull(sc.addressline1, '') <> '' then sc.addressline1 else '' end + case when isnull(sc.addressline2, '') <> '' then ', ' + sc.addressline2 else '' end) AS ContactAddrLine1 ,Rtrim(Isnull(sc.city, '') + ', ' + Isnull(sc.state, '') + ', ' + Isnull(sc.zip, '')) AS ContactAddrLine2 ,coalesce(sc.workphone, sc.mobilephone, sc.homephone, '') AS ContactPhone ,isnull(sc.jobtitle,'') AS ContactJobTitle ,Isnull(sc.email, '') AS ContactEmail -- Billing Contact Info Only ,ISNULL(sb.FullName, sb.firstname +' '+ sb.lastname) AS BillingName ,RTRIM( case when isnull(sb.addressline1, '') <> '' then sb.addressline1 else '' end + case when isnull(sb.addressline2, '') <> '' then ', ' + sb.addressline2 else '' end) AS BillingAddrLine1 ,Rtrim(Isnull(sb.city, '') + ', ' + Isnull(sb.state, '') + ', ' + Isnull(sb.zip, '')) AS BillingAddrLine2 ,COALESCE(sb.workphone, sb.mobilephone, sb.homephone, '') AS BillingPhone ,ISNULL(sb.jobtitle,'') AS BillingJobTitle ,ISNULL(sb.email, '') AS BillingEmail -- Account or Contacts Info ,coalesce(sa.Name,sc.Fullname,sc.firstname +' '+ sc.lastname, '') AS Account_ContactName ,coalesce(sa.kore_legalname, sa.name, sc.Fullname,sc.firstname +' '+ sc.lastname,'') AS Account_ContactLegalName ,case when pd.sf_accountid is not null then isnull(pc.KORE_AccountNumbers,'') else isnull(sc.KORE_AccountNumbers,'') end AS Account_ContactNumber ,CASE WHEN pd.sf_accountid IS NOT NULL THEN RTRIM( case when isnull(sa.MailingStreet, '') <> '' then sa.MailingStreet else '' end + case when isnull(sa.MailingStreet2, '') <> '' then ', ' + sa.MailingStreet2 else '' end) ELSE RTRIM( case when isnull(sc.addressline1, '') <> '' then sc.addressline1 else '' end + case when isnull(sc.addressline2, '') <> '' then ', ' + sc.addressline2 else '' end) END AS Account_ContactAddrLine1 ,CASE WHEN pd.sf_accountid IS NOT NULL THEN ISNULL(RTRIM(sa.City),'') + ', ' + ISNULL(RTRIM(sa.state),'') + ' ' + ISNULL(RTRIM(sa.zip),'') ELSE ISNULL(RTRIM(sc.city),'') + ', ' + ISNULL(RTRIM(sc.state),'') + ' ' + ISNULL(RTRIM(sc.zip),'') END AS Account_ContactAddrLine2 ,CASE WHEN pd.sf_accountid IS NOT NULL THEN sa.Phone ELSE sc.workphone END AS Account_ContactPhoneNumber -- Primary Contact or General Contact Info , case when pd.sf_accountid is not null then coalesce(pc.FullName, pc.firstname +' '+ pc.lastname) else coalesce(sc.FullName, sc.firstname +' '+ sc.lastname) end AS PrimaryContact_GeneralContactName , case when pd.sf_accountid is not null then isnull(rtrim(pc.addressline1),'') else isnull(rtrim(sc.addressline1+sc.addressline2),'') end AS PrimaryContact_GeneralContactAddrLine1 , case when pd.sf_accountid is not null then isnull(rtrim(pc.City),'') + ', ' + isnull(rtrim(pc.[state]),'') + ' ' + isnull(rtrim(pc.zip),'') + ' ' + isnull(rtrim(pc.country),'') else isnull(rtrim(sc.City),'') + ', ' + isnull(rtrim(sc.[state]),'') + ' ' + isnull(rtrim(sc.zip),'') + ' ' + isnull(rtrim(sc.country),'') end AS PrimaryContact_GeneralContactAddrLine2 ,case when pd.sf_accountid is not null then coalesce(pc.workphone, pc.mobilephone, pc.homephone, '') else coalesce(sc.workphone, sc.mobilephone, sc.homephone, '') end AS PrimaryContact_GeneralContactPhone ,case when pd.sf_accountid is not null then isnull(pc.jobtitle,'') else isnull(sc.jobtitle,'') end AS PrimaryContact_GeneralContactJobTitle ,case when pd.sf_accountid is not null then Isnull(pc.email, '') else Isnull(sc.email, '') end AS PrimaryContact_GeneralContactEmail -- Info Pull From Billing OR Primary ,ISNULL(bp.FullName, bp.firstname +' '+ bp.lastname) AS BillingPrimaryName ,isnull(rtrim(bp.addressline1+bp.addressline2),'') AS BillingPrimaryAddr1 ,isnull(rtrim(bp.City),'') + ', ' + isnull(rtrim(bp.[state]),'') + ' ' + isnull(rtrim(bp.zip),'') + ' ' + isnull(rtrim(bp.country),'') AS BillingPrimaryAddr2 ,coalesce(bp.workphone, bp.mobilephone, bp.homephone, '') AS BillingPrimaryPhone ,isnull(bp.jobtitle,'') AS BillingPrimaryJobTitle ,Isnull(bp.email, '') AS BillingPrimaryEmail --SF user info ,ISNULL(sp.FullName, sp.firstname +' '+ sp.lastname) AS SalesPerson ,ISNULL(cood.FullName, cood.firstname +' '+ cood.lastname) AS Coordinator ,ssa.FullName AS SuitAdmin ,su.Name AS SalesRepName ,su.MainPhone AS SalesRepPhone ,su.Email AS SalesRepEmail from kore_premiumdeal pd INNER JOIN kore_premiumdeallocationbase pdl on pd.kore_premiumdealid = pdl.kore_premiumdealid INNER JOIN kore_premiumdealcontractyear pdcy on pd.kore_premiumdealid = pdcy.kore_premiumdealid LEFT JOIN kore_section sec on pdl.kore_name = sec.kore_name LEFT JOIN SyncedAccounts sa on sa.sf_accountid = pd.sf_accountid LEFT JOIN SyncedContacts sc on sc.sf_contactid = pd.sf_contactid LEFT JOIN SyncedContacts pc on pc.sf_contactid = pd.sf_primarycontactid LEFT JOIN SyncedContacts sb on sb.sf_contactid = pd.sf_billingcontactid LEFT JOIN SyncedContacts sp on sp.sf_contactid = pd.sf_salespersonid LEFT JOIN SyncedContacts cood on cood.sf_contactid = pd.sf_coordinatorid LEFT JOIN SyncedContacts sacp on sacp.sf_contactid = COALESCE(pd.sf_accountid, pd.sf_contactid, pd.sf_primarycontactid) -- Account or Contact LEFT JOIN SyncedContacts bp on bp.sf_contactid = COALESCE(pd.sf_billingcontactid, pd.sf_primarycontactid, pd.sf_contactid) -- Billing or PrimaryContact LEFT JOIN SyncedContacts ssa on ssa.sf_accountid = pd.sf_suiteadministratorid LEFT JOIN SyncedUsers su on su.SF_UserId = pd.sf_salespersonid left join SyncedAccounts_Extension ae on ae.sf_accountid = sa.sf_accountid left join syncedIndustries si on si.SF_IndustryId = sa.KOREIndustry where pd.kore_premiumdealid = @SuiteContractID order by pdcy.kore_yearstart --==Select Statement - Out Fields End==--