Untitled
unknown
plain_text
6 months ago
40 kB
3
Indexable
SELECT dbo.tblhomestayinfo.familyid as ID, dbo.tblhomestayinfo.familyid as FamilyID, CONCAT( tblhomestayinfo.[Her First Name], CASE WHEN ISNULL(tblhomestayinfo.[Her First Name], '') = '' OR LTRIM(LTRIM(tblhomestayinfo.[Her First Name])) = '' OR ISNULL(tblhomestayinfo.[Her Last Name], '') = '' OR LTRIM(LTRIM(tblhomestayinfo.[Her Last Name])) = '' THEN '' ELSE ' ' END, tblhomestayinfo.[Her Last Name], CASE WHEN (ISNULL(tblhomestayinfo.[Her First Name], '') = '' OR LTRIM(LTRIM(tblhomestayinfo.[Her First Name])) = '') AND (ISNULL(tblhomestayinfo.[Her Last Name], '') = '' OR LTRIM(LTRIM(tblhomestayinfo.[Her Last Name])) = '') OR (ISNULL(tblhomestayinfo.[His First Name], '') = '' OR LTRIM(LTRIM(tblhomestayinfo.[His First Name])) = '') AND (ISNULL(tblhomestayinfo.[His Last Name], '') = '' OR LTRIM(LTRIM(tblhomestayinfo.[His Last Name])) = '') THEN '' ELSE ' - ' END, tblhomestayinfo.[His First Name], CASE WHEN ISNULL(tblhomestayinfo.[His Last Name], '') = '' OR LTRIM(LTRIM(tblhomestayinfo.[His Last Name])) = '' OR ISNULL(tblhomestayinfo.[His First Name], '') = '' OR LTRIM(LTRIM(tblhomestayinfo.[His First Name])) = '' THEN '' ELSE ' ' END, tblhomestayinfo.[His Last Name] ) AS Homestay, CASE WHEN isnull(ISP_Cloud_Homestey_Additional_Info.numberOfDogs,'')='' THEN '' ELSE iif(ISP_Cloud_Homestey_Additional_Info.numberOfDogs=1, ltrim(rtrim(try_convert(char ,ISP_Cloud_Homestey_Additional_Info.numberOfDogs)))+' dog' , ltrim(rtrim(try_convert(char ,ISP_Cloud_Homestey_Additional_Info.numberOfDogs)))+' dogs' ) + CASE WHEN isnull(ISP_Cloud_Homestey_Additional_Info.dogSizeAndBreed,'')='' THEN '' ELSE ', '+ISP_Cloud_Homestey_Additional_Info.dogSizeAndBreed END END + ', '+ CASE WHEN isnull(ISP_Cloud_Homestey_Additional_Info.numberOfCats,'')='' THEN '' ELSE iif(ISP_Cloud_Homestey_Additional_Info.numberOfCats=1, ltrim(rtrim(try_convert(char ,ISP_Cloud_Homestey_Additional_Info.numberOfCats)))+' cat' , ltrim(rtrim(try_convert(char ,ISP_Cloud_Homestey_Additional_Info.numberOfCats)))+' cats' ) + CASE WHEN isnull(ISP_Cloud_Homestey_Additional_Info.catSizeAndBreed,'')='' THEN '' ELSE ', '+ISP_Cloud_Homestey_Additional_Info.catSizeAndBreed END END as PetsInfo, CASE WHEN tblStudentHomestayLinking.isPrivateRoom=1 then 'Private' ELSE 'Shared' end as [Room Type], IIF(tblhomestayinfo.WithMeals IS NULL ,'3 meals','') AS [Meals], (CASE WHEN tblhomestayinfo.[WithMeals] = 1 THEN 'Yes' ELSE 'No' END) AS [WithMeals], (CASE WHEN tblhomestayinfo.[WithoutMeals] = 1 THEN 'Yes' ELSE 'No' END) AS [WithoutMeals], (select count(*) from tblHomestayFamilyInfo where HSFamilyID = tblhomestayinfo.FamilyID) as [Number of Family Members], CONCAT (ISNULL(tblhomestayinfo.[Her First Name],'') , ', ' ,ISNULL(tblhomestayinfo.[Her Last Name],'')) as [HS Parent1 Name], CONCAT (ISNULL(tblhomestayinfo.[His First Name],'') , ', ' ,ISNULL(tblhomestayinfo.[His Last Name],'')) as [HS Parent2 Name], tblhomestayinfo.[Her First Name] AS [HS Parent1 First Name], tblhomestayinfo.[Her Last Name] AS [HS Parent1 Last Name], tblhomestayinfo.[His First Name] AS [HS Parent2 First Name], tblhomestayinfo.[His Last Name] AS [HS Parent2 Last Name], tblhomestayinfo.[Her Occupation] AS [HS Parent1 Occupation], tblhomestayinfo.[His Occupation] AS [HS Parent2 Occupation], ISP_Cloud_Homestey_Additional_Info.[letterForHs] as [Letter for Homestay], ISP_Cloud_Homestey_Additional_Info.[accessPublicTransport?] as [Access Public Transport] , ISP_Cloud_Homestey_Additional_Info.neighbourhoodInfo as [Get to school], ISP_Cloud_Homestey_Additional_Info.[MaritalStatus?] as HisRelationship, ISP_Cloud_Homestey_Additional_Info.[HerMaritalStatus?] as HerRelationship, CASE WHEN isnull(CAST(ISP_Cloud_Homestey_Additional_Info.primaryHobbies as NVARCHAR(max)),'')='' THEN '' ELSE LTRIM(RTRIM(CAST(ISP_Cloud_Homestey_Additional_Info.primaryHobbies as NVARCHAR(max)))) + CASE WHEN isnull(CAST(ISP_Cloud_Homestey_Additional_Info.hostHobbiesAndInterests as NVARCHAR(max)),'')='' THEN '' ELSE ', '+LTRIM(RTRIM(CAST(ISP_Cloud_Homestey_Additional_Info.hostHobbiesAndInterests as NVARCHAR(max)))) END END AS Interests, ------------------------------------------------------- New fields-------------------------------- dbo.tblhomestayinfo.[active inactive], ( CASE WHEN tblhomestayinfo.[active?] = '1' THEN 'Yes' WHEN tblhomestayinfo.[active?] = '0' THEN 'No' ELSE Cast(tblhomestayinfo.[active?] AS NVARCHAR(max)) END ) AS [Active?], dbo.tblhomestayinfo.[address city], dbo.tblhomestayinfo.[address pc], dbo.tblhomestayinfo.[address street], Concat(dbo.tblhomestayinfo.[address city], ' ', dbo.tblhomestayinfo.[address pc]) AS AddressCityPC, dbo.tblhomestayinfo.[adult only?], ( CASE WHEN tblhomestayinfo.[adult/teacher] = '1' THEN 'Yes' WHEN tblhomestayinfo.[adult/teacher] = '0' THEN 'No' ELSE Cast(tblhomestayinfo.[adult/teacher] AS NVARCHAR(max)) END ) AS [Adult/Teacher], dbo.tblhomestayinfo.agepref, dbo.agentdemographic.[agency name], ( CASE WHEN tblhomestayinfo.agreement = '1' THEN 'Yes' WHEN tblhomestayinfo.agreement = '0' THEN 'No' ELSE Cast(tblhomestayinfo.agreement AS NVARCHAR(max)) END ) AS Agreement, dbo.tblhomestayinfo.allergies, Cast(dbo.tblhomestayinfo.[amenities detail] AS NVARCHAR(max)) AS [Amenities Detail], Cast(dbo.tblhomestayinfo.amenities AS NVARCHAR(max)) AS Amenities, ( CASE WHEN tblhomestayinfo.anysmokers = '1' THEN 'Yes' WHEN tblhomestayinfo.anysmokers = '0' THEN 'No' ELSE Cast(tblhomestayinfo.anysmokers AS NVARCHAR(max)) END ) AS AnySmokers, Format(dbo.tblhomestayinfo.[application date], 'dd-MMM-yyyy') AS [Application Date], Format(dbo.tblhomestayinfo.[application processed date], 'dd-MMM-yyyy') AS [Application Processed Date], ( CASE WHEN tblhomestayinfo.application = '1' THEN 'Yes' WHEN tblhomestayinfo.application = '0' THEN 'No' ELSE Cast(tblhomestayinfo.application AS NVARCHAR(max)) END ) AS Application, Format(dbo.names.[arrival date in district], 'dd-MMM-yyyy') AS [Arrival Date], dbo.tblhomestayinfo.[at home?], Format(dbo.tblhomestayinfo.[available from], 'dd-MMM-yyyy') AS [Available From], Format(dbo.tblhomestayinfo.[available to], 'dd-MMM-yyyy') AS [Available To], dbo.tblhomestayinfo.blank, ( CASE WHEN tblhomestayinfo.co2detectors = '1' THEN 'Yes' WHEN tblhomestayinfo.co2detectors = '0' THEN 'No' ELSE Cast(tblhomestayinfo.co2detectors AS NVARCHAR(max)) END ) AS CO2Detectors, Format(dbo.tblhomestayinfo.[crc issue date], 'dd-MMM-yyyy') AS [CRC Issue Date], Format(dbo.tblhomestayinfo.[crc renewal date], 'dd-MMM-yyyy') AS [CRC Renewal Date], CASE WHEN dbo.tblhomestayinfo.[parent names preferred] <> '' THEN tblhomestayinfo.[parent names preferred] ELSE CASE WHEN tblhomestayinfo.[his last name] <> '' AND tblhomestayinfo.[her last name] <> '' THEN CASE WHEN tblhomestayinfo.[his last name] = tblhomestayinfo.[his last name] THEN Concat('Mr. & Mrs. ', tblhomestayinfo.[his first name], tblhomestayinfo.[his last name]) ELSE Concat(tblhomestayinfo.[his name prefix], tblhomestayinfo.[his first name], tblhomestayinfo.[his last name], ' and ', tblhomestayinfo.[her name prefix], tblhomestayinfo.[her first name], tblhomestayinfo.[her last name]) END ELSE Concat(tblhomestayinfo.[her name prefix], tblhomestayinfo.[her first name], ' ', tblhomestayinfo.[her last name], tblhomestayinfo.[his name prefix], tblhomestayinfo.[his first name], ' ', tblhomestayinfo.[his last name]) END END AS [Calculated Family Name], CASE WHEN tblhomestayinfo.[parent salutation preferred] <> '' THEN tblhomestayinfo.[parent salutation preferred] ELSE CASE WHEN tblhomestayinfo.[his first name] IS NOT NULL AND tblhomestayinfo.[her last name] IS NOT NULL THEN Concat( tblhomestayinfo.[his first name], ' & ', tblhomestayinfo.[her first name]) WHEN tblhomestayinfo.[her first name] IS NOT NULL THEN tblhomestayinfo.[her first name] WHEN tblhomestayinfo.[his first name] IS NULL OR tblhomestayinfo.[his first name] = '' THEN tblhomestayinfo.[his first name] END END AS [Calculated Salutation], ( CASE WHEN tblhomestayinfo.cat = '1' THEN 'Yes' WHEN tblhomestayinfo.cat = '0' THEN 'No' ELSE Cast(tblhomestayinfo.cat AS NVARCHAR(max)) END ) AS Cat, dbo.tblhomestayinfo.zone AS [Catchment Area Elementary School], dbo.tblhomestayinfo.zonehighschool AS [Catchment Area High School], dbo.tblhomestayinfo.[cell phone], Format(dbo.tblhomestayinfo.[child abuse registry date], 'dd-MMM-yyyy') AS [Child Abuse Registry Date], ( CASE WHEN tblhomestayinfo.[child abuse registry] = '1' THEN 'Yes' WHEN tblhomestayinfo.[child abuse registry] = '0' THEN 'No' ELSE Cast(tblhomestayinfo.[child abuse registry] AS NVARCHAR(max)) END ) AS [Child Abuse Registry], dbo.tblhomestayinfo.[children count], Cast(dbo.tblhomestayinfo.comments AS NVARCHAR(max)) AS Comments, dbo.tblhomestayinfo.[computer?], dbo.tblhomestayinfo.computer, Format(dbo.tblhomestayinfo.[contract signed], 'dd-MMM-yyyy') AS [Contract Signed], ( CASE WHEN tblhomestayinfo.[couple orientation] = '1' THEN 'Yes' WHEN tblhomestayinfo.[couple orientation] = '0' THEN 'No' ELSE Cast(tblhomestayinfo.[couple orientation] AS NVARCHAR(max)) END ) AS [Couple Orientation], ( CASE WHEN tblhomestayinfo.[criminal record] = '1' THEN 'Yes' WHEN tblhomestayinfo.[criminal record] = '0' THEN 'No' ELSE Cast(tblhomestayinfo.[criminal record] AS NVARCHAR(max)) END ) AS [Criminal Record], dbo.tblhomestayinfo.[culturegrams?], dbo.tblhomestayinfo.[current?], dbo.tblhomestayinfo.[dvd?], dbo.tblhomestayinfo.date, Format(dbo.tblhomestayinfo.[declaration signed], 'dd-MMM-yyyy') AS [Declaration Signed], dbo.names.[departure date], Cast(dbo.tblhomestayinfo.description AS NVARCHAR(max)) AS Description, dbo.tblhomestayinfo.directdepositcode, Cast(dbo.tblhomestayinfo.direction AS NVARCHAR(max)) AS Direction, dbo.tblhomestayinfo.[does any family member smoke?], ( CASE WHEN tblhomestayinfo.dog = '1' THEN 'Yes' WHEN tblhomestayinfo.dog = '0' THEN 'No' ELSE Cast(tblhomestayinfo.dog AS NVARCHAR(max)) END ) AS Dog, dbo.tblhomestayinfo.[eat vegetarian?], dbo.tblhomestayinfo.[elementary catchment], dbo.tblhomestayinfo.email, ( CASE WHEN tblhomestayinfo.emergency = '1' THEN 'Yes' WHEN tblhomestayinfo.emergency = '0' THEN 'No' ELSE Cast(tblhomestayinfo.emergency AS NVARCHAR(max)) END ) AS Emergency, Format(dbo.tblhomestayinfo.[entry date], 'dd-MMM-yyyy') AS [Entry Date], dbo.tblhomestayinfo.[envelope label], Cast(dbo.tblhomestayinfo.[family interests/hobbies] AS NVARCHAR(max)) AS [Family Interests/Hobbies], Cast(dbo.tblhomestayinfo.familyinfo AS NVARCHAR(max)) AS FamilyInfo, dbo.tblhomestayinfo.familymembers, Cast(dbo.tblhomestayinfo.faxcovercomments AS NVARCHAR(max)) AS FaxCoverComments, dbo.tblhomestayinfo.fax, ( CASE WHEN tblhomestayinfo.fireextinguishers = '1' THEN 'Yes' WHEN tblhomestayinfo.fireextinguishers = '0' THEN 'No' ELSE Cast(tblhomestayinfo.fireextinguishers AS NVARCHAR(max)) END ) AS FireExtinguishers, CASE WHEN tblhomestayinfo.[parent names preferred] <> '' THEN tblhomestayinfo.[parent names preferred] ELSE CASE WHEN tblhomestayinfo.[his last name] <> '' AND tblhomestayinfo.[her last name] <> '' THEN CASE WHEN tblhomestayinfo.[his last name] = tblhomestayinfo.[her last name] THEN Concat('Mr. & Mrs. ', dbo.tblhomestayinfo.[his last name]) ELSE Concat(CASE WHEN tblhomestayinfo.[his last name] IS NULL THEN '' ELSE CASE WHEN dbo.tblhomestayinfo.[his name prefix] <> '' THEN dbo.tblhomestayinfo.[her name prefix] ELSE ' Mr. ' END END, dbo.tblhomestayinfo.[his last name], ' and ' , CASE WHEN tblhomestayinfo.[her last name] IS NULL THEN '' ELSE CASE WHEN dbo.tblhomestayinfo.[her name prefix] <> '' THEN dbo.tblhomestayinfo.[her name prefix] ELSE ' Ms. ' END END, dbo.tblhomestayinfo.[her last name]) END ELSE Concat(CASE WHEN tblhomestayinfo.[her last name] IS NULL THEN '' ELSE CASE WHEN dbo.tblhomestayinfo.[her name prefix] <> '' THEN dbo.tblhomestayinfo.[her name prefix] ELSE ' Ms. ' END END, dbo.tblhomestayinfo.[her last name], CASE WHEN tblhomestayinfo.[his last name] IS NULL THEN '' ELSE CASE WHEN dbo.tblhomestayinfo.[his name prefix] <> '' THEN dbo.tblhomestayinfo.[her name prefix] ELSE ' Mr. ' END END, dbo.tblhomestayinfo.[his last name]) END END AS [Formal Salutation], Concat(CASE WHEN dbo.tblhomestayinfo.[parent names preferred] <> '' THEN tblhomestayinfo.[parent names preferred] ELSE CASE WHEN tblhomestayinfo.[his last name] <> '' AND tblhomestayinfo.[her last name] <> '' THEN CASE WHEN tblhomestayinfo.[his last name] = tblhomestayinfo.[his last name] THEN Concat('Mr. & Mrs. ', tblhomestayinfo.[his first name], tblhomestayinfo.[his last name]) ELSE Concat(tblhomestayinfo.[his name prefix], tblhomestayinfo.[his first name], tblhomestayinfo.[his last name], ' and ', tblhomestayinfo.[her name prefix], tblhomestayinfo.[her first name], tblhomestayinfo.[her last name]) END ELSE Concat(tblhomestayinfo.[her name prefix], tblhomestayinfo.[her first name], ' ', tblhomestayinfo.[her last name], tblhomestayinfo.[his name prefix], tblhomestayinfo.[his first name], ' ', tblhomestayinfo.[his last name]) END END, Char(13), Char(10), dbo.tblhomestayinfo.[address street], Char(13), Char(10), dbo.tblhomestayinfo.[address city], ' ', dbo.tblhomestayinfo.[address pc]) AS [Full Mailing Address], ( CASE WHEN tblhomestayinfo.fulltime = '1' THEN 'Yes' WHEN tblhomestayinfo.fulltime = '0' THEN 'No' ELSE Cast(tblhomestayinfo.fulltime AS NVARCHAR(max)) END ) AS Fulltime, Format(dbo.tblhomestayinfo.[h/s binder given], 'dd-MMM-yyyy') AS [H/S Binder Given], Format(dbo.tblhomestayinfo.[h/s binder returned], 'dd-MMM-yyyy') AS [H/S Binder Returned], dbo.tblhomestayinfo.[h/s binder-new], dbo.tblhomestayinfo.[h/s questionnaire], dbo.tblhomestayinfo.[hs coordinator], CASE WHEN tblhomestayinfo.[his last name] IS NULL AND tblhomestayinfo.[his first name] IS NULL AND tblhomestayinfo.[her last name] IS NULL AND tblhomestayinfo.[her first name] IS NULL THEN NULL WHEN tblhomestayinfo.[his last name] IS NULL AND tblhomestayinfo.[his first name] IS NULL AND tblhomestayinfo.[her first name] IS NOT NULL AND tblhomestayinfo.[her last name] IS NOT NULL THEN Concat( tblhomestayinfo.[her last name], ', ', tblhomestayinfo.[her first name]) WHEN tblhomestayinfo.[his last name] IS NOT NULL AND tblhomestayinfo.[his first name] IS NOT NULL AND tblhomestayinfo.[her first name] IS NULL AND tblhomestayinfo.[her last name] IS NULL THEN Concat( tblhomestayinfo.[his last name], ', ', tblhomestayinfo.[his first name]) WHEN tblhomestayinfo.[his last name] IS NOT NULL AND tblhomestayinfo.[his first name] IS NOT NULL AND tblhomestayinfo.[her first name] IS NOT NULL AND tblhomestayinfo.[her last name] IS NOT NULL THEN Concat( tblhomestayinfo.[her last name], ', ', tblhomestayinfo.[her first name], ' & ', tblhomestayinfo.[his last name], ', ', tblhomestayinfo.[his first name]) END AS HSName, Cast(dbo.tblhomestayinfo.hsonlineemailapplication AS NVARCHAR(max)) AS HSOnlineEmailApplication, dbo.tblhomestayinfo.[her business address], dbo.tblhomestayinfo.[her business phone], dbo.tblhomestayinfo.[her cell phone number], Format(dbo.tblhomestayinfo.[her criminal record date], 'dd-MMM-yyyy') AS [Her Criminal Record Date], dbo.tblhomestayinfo.[her first name], CASE WHEN tblhomestayinfo.[her first name] IS NULL OR tblhomestayinfo.[her last name] IS NULL THEN '' ELSE tblhomestayinfo.[her first name] + ' ' END AS [Her First], dbo.tblhomestayinfo.[her full name with prefix], Concat(dbo.tblhomestayinfo.[her first name], ' ', dbo.tblhomestayinfo.[her last name]) AS [Her Full Name], dbo.tblhomestayinfo.[her last name], CASE WHEN tblhomestayinfo.[her last name] IS NULL THEN '' ELSE tblhomestayinfo.[her last name] END AS [Her Last], dbo.tblhomestayinfo.[her name prefix], dbo.tblhomestayinfo.[her nationality], dbo.tblhomestayinfo.[her occupation], CASE WHEN tblhomestayinfo.[her last name] IS NULL THEN '' ELSE CASE WHEN dbo.tblhomestayinfo.[her name prefix] <> '' THEN dbo.tblhomestayinfo.[her name prefix] ELSE ' Ms. ' END END AS [Her Prefix], Format(dbo.tblhomestayinfo.herbirthdate, 'dd-MMM-yyyy') AS HerBirthdate, dbo.tblhomestayinfo.heremailaddress, dbo.tblhomestayinfo.heremployer, CASE WHEN tblhomestayinfo.[her first name] IS NULL AND tblhomestayinfo.[her last name] IS NULL OR ( tblhomestayinfo.[her first name] = '' AND tblhomestayinfo.[her last name] = '' ) THEN '' WHEN tblhomestayinfo.[her first name] IS NOT NULL AND tblhomestayinfo.[her last name] IS NOT NULL THEN Concat( tblhomestayinfo.[her last name], ', ', tblhomestayinfo.[her first name]) WHEN tblhomestayinfo.[her first name] IS NOT NULL THEN tblhomestayinfo.[her first name] WHEN tblhomestayinfo.[her last name] IS NOT NULL THEN tblhomestayinfo.[her last name] END AS HerName, dbo.tblhomestayinfo.[his business address], dbo.tblhomestayinfo.[his business phone], dbo.tblhomestayinfo.[his cell phone number], Format(dbo.tblhomestayinfo.[his criminal record date], 'dd-MMM-yyyy') AS [His Criminal Record Date], dbo.tblhomestayinfo.[his first name], CASE WHEN tblhomestayinfo.[his first name] IS NULL OR tblhomestayinfo.[his last name] IS NULL THEN '' ELSE tblhomestayinfo.[his first name] + ' ' END AS [His First], dbo.tblhomestayinfo.[his full name with prefix], Concat(dbo.tblhomestayinfo.[his first name], ' ', dbo.tblhomestayinfo.[his last name]) AS [His Full Name], dbo.tblhomestayinfo.[his last name], CASE WHEN tblhomestayinfo.[his last name] IS NULL THEN '' ELSE tblhomestayinfo.[his last name] END AS [His Last], dbo.tblhomestayinfo.[his name prefix], dbo.tblhomestayinfo.[his nationality], dbo.tblhomestayinfo.[his occupation], CASE WHEN tblhomestayinfo.[his last name] IS NULL THEN '' ELSE CASE WHEN dbo.tblhomestayinfo.[his name prefix] <> '' THEN dbo.tblhomestayinfo.[his name prefix] ELSE ' Mr. ' END END AS [His Prefix], Format(dbo.tblhomestayinfo.hisbirthdate, 'dd-MMM-yyyy') AS HisBirthdate, dbo.tblhomestayinfo.hisemailaddress, dbo.tblhomestayinfo.hisemployer, CASE WHEN tblhomestayinfo.[his first name] IS NULL AND tblhomestayinfo.[his last name] IS NULL OR ( tblhomestayinfo.[his first name] = '' AND tblhomestayinfo.[his last name] = '' ) THEN '' WHEN tblhomestayinfo.[his first name] IS NOT NULL AND tblhomestayinfo.[his last name] IS NOT NULL THEN Concat( tblhomestayinfo.[his last name], ', ', tblhomestayinfo.[his first name]) WHEN tblhomestayinfo.[his first name] IS NOT NULL THEN tblhomestayinfo.[his first name] WHEN tblhomestayinfo.[his last name] IS NOT NULL THEN tblhomestayinfo.[his last name] END AS HisName, dbo.tblhomestayinfo.[home phone], Format(dbo.tblhomestayinfo.[home visit date], 'dd-MMM-yyyy') AS [Home Visit Date], ( CASE WHEN tblhomestayinfo.[home visit] = '1' THEN 'Yes' WHEN tblhomestayinfo.[home visit] = '0' THEN 'No' ELSE Cast(tblhomestayinfo.[home visit] AS NVARCHAR(max)) END ) AS [Home Visit], Cast(dbo.tblhomestayinfo.homeinfo AS NVARCHAR(max)) AS HomeInfo, dbo.tblhomestayinfo.[homestay parent], dbo.tblhomestayinfo.homestaytype, Cast(dbo.tblhomestayinfo.[hosting history] AS NVARCHAR(max)) AS [Hosting History], dbo.tblhomestayinfo.hostingtype AS [Hosting Type], CASE WHEN [date joined family] <= Getdate() AND [date left family] >= Getdate() THEN 'Here' ELSE ( CASE WHEN [date joined family] > Getdate() THEN 'Pending' END ) END AS [HS Status], Cast(dbo.tblhomestayinfo.hyperpath1 AS NVARCHAR(max)) AS HyperPath1, Cast(dbo.tblhomestayinfo.hyperpath2 AS NVARCHAR(max)) AS HyperPath2, Cast(dbo.tblhomestayinfo.hyperpath3 AS NVARCHAR(max)) AS HyperPath3, Cast(dbo.tblhomestayinfo.hyperpath4 AS NVARCHAR(max)) AS HyperPath4, Cast(dbo.tblhomestayinfo.hyperpath5 AS NVARCHAR(max)) AS HyperPath5, Cast(dbo.tblhomestayinfo.hyperpath6 AS NVARCHAR(max)) AS HyperPath6, Cast(dbo.tblhomestayinfo.hyperpath AS NVARCHAR(max)) AS HyperPath, -- dbo.tblhomestayinfo.id, dbo.tblhomestayinfo.imagecaption1, dbo.tblhomestayinfo.imagecaption2, dbo.tblhomestayinfo.imagecaption3, dbo.tblhomestayinfo.imagecaption4, dbo.tblhomestayinfo.imagecaption5, dbo.tblhomestayinfo.imagecaption6, dbo.tblhomestayinfo.imagecaption, Cast(dbo.tblhomestayinfo.[interests & hobbies] AS NVARCHAR(max)) AS [Interests & Hobbies], dbo.tblhomestayinfo.[internet?], Format(dbo.tblstudenthomestaylinking.[date joined family], 'dd-MMM-yyyy') AS [Joined Family], dbo.tblhomestayinfo.kids, Format(dbo.tblstudenthomestaylinking.[date left family], 'dd-MMM-yyyy') AS [Left Family], dbo.tblhomestayinfo.[letter contents], dbo.tblhomestayinfo.longtermhomestay, dbo.tblhomestayinfo.[mailing address], dbo.tblhomestayinfo.[mailing city], dbo.tblhomestayinfo.[mailing label], dbo.tblhomestayinfo.[mailing pc], dbo.tblhomestayinfo.[marital status], Format(dbo.tblhomestayinfo.[modification date], 'dd-MMM-yyyy') AS [Modification Date], dbo.tblhomestayinfo.nationalitypref, dbo.tblhomestayinfo.[new application received], Cast(dbo.tblhomestayinfo.notes AS NVARCHAR(max)) AS Notes, dbo.isp_cloud_homestey_additional_info.numberofrooms AS [Number of rooms], Format(dbo.tblhomestayinfo.[orientation date], 'dd-MMM-yyyy') AS [Orientation Date], dbo.tblhomestayinfo.[orientation meeting attendance], ( CASE WHEN tblhomestayinfo.orientation = '1' THEN 'Yes' WHEN tblhomestayinfo.orientation = '0' THEN 'No' ELSE Cast(tblhomestayinfo.orientation AS NVARCHAR(max)) END ) AS Orientation, Cast(dbo.tblhomestayinfo.[other amenities?] AS NVARCHAR(max)) AS [Other Amenities?], dbo.tblhomestayinfo.[other instruments], Cast(dbo.tblhomestayinfo.[other languages detail] AS NVARCHAR(max)) AS [Other Languages Detail], dbo.tblhomestayinfo.[other languages spoken?], dbo.tblhomestayinfo.[other languages spoken], ( CASE WHEN tblhomestayinfo.other = '1' THEN 'Yes' WHEN tblhomestayinfo.other = '0' THEN 'No' ELSE Cast(tblhomestayinfo.other AS NVARCHAR(max)) END ) AS Other, ( CASE WHEN tblhomestayinfo.[out of district] = '1' THEN 'Yes' WHEN tblhomestayinfo.[out of district] = '0' THEN 'No' ELSE Cast(tblhomestayinfo.[out of district] AS NVARCHAR(max)) END ) AS [Out of District], dbo.tblhomestayinfo.[parent names preferred], dbo.tblhomestayinfo.[parent salutation preferred], isnull(CAST(tblhomestayinfo.[PetsInfo] as NVARCHAR(max)),'')+ ', '+ CASE WHEN isnull(ISP_Cloud_Homestey_Additional_Info.numberOfDogs,'')='' THEN '' ELSE iif(ISP_Cloud_Homestey_Additional_Info.numberOfDogs=1, ltrim(rtrim(try_convert(char ,ISP_Cloud_Homestey_Additional_Info.numberOfDogs)))+' dog' , ltrim(rtrim(try_convert(char ,ISP_Cloud_Homestey_Additional_Info.numberOfDogs)))+' dogs' ) + CASE WHEN isnull(ISP_Cloud_Homestey_Additional_Info.dogSizeAndBreed,'')='' THEN '' ELSE ', '+ISP_Cloud_Homestey_Additional_Info.dogSizeAndBreed END END AS Dogs, CASE WHEN isnull(ISP_Cloud_Homestey_Additional_Info.numberOfCats,'')='' THEN '' ELSE iif(ISP_Cloud_Homestey_Additional_Info.numberOfCats=1, ltrim(rtrim(try_convert(char ,ISP_Cloud_Homestey_Additional_Info.numberOfCats)))+' cat' , ltrim(rtrim(try_convert(char ,ISP_Cloud_Homestey_Additional_Info.numberOfCats)))+' cats' ) + CASE WHEN isnull(ISP_Cloud_Homestey_Additional_Info.catSizeAndBreed,'')='' THEN '' ELSE ', '+ISP_Cloud_Homestey_Additional_Info.catSizeAndBreed END END AS Cats, ( CASE WHEN tblhomestayinfo.[pets?] = '1' THEN 'Yes' WHEN tblhomestayinfo.[pets?] = '0' THEN 'No' ELSE Cast(tblhomestayinfo.[pets?] AS NVARCHAR(max)) END ) AS [Pets?], Substring(Concat(( CASE WHEN [tblhomestayinfo].[dog] = '1' THEN ( CASE WHEN dbo.isp_cloud_homestey_additional_info_application.[numberofdogs] > 1 THEN Concat(', ', dbo.isp_cloud_homestey_additional_info_application.[numberofdogs], ' dogs' ) ELSE ', 1 dog' END ) ELSE '' END ), ( CASE WHEN [tblhomestayinfo].[cat] = '1' THEN ( CASE WHEN dbo.isp_cloud_homestey_additional_info_application.[numberofcats] > 1 THEN Concat(', ', dbo.isp_cloud_homestey_additional_info_application.[numberofcats], ' cats' ) ELSE ', 1 cat' END ) ELSE '' END )), 3, 20) AS Pets , ( CASE WHEN tblhomestayinfo.[piano?] = '1' THEN 'Yes' WHEN tblhomestayinfo.[piano?] = '0' THEN 'No' ELSE Cast(tblhomestayinfo.[piano?] AS NVARCHAR(max)) END ) AS [Piano?], dbo.tblhomestayinfo.[police checks complete?], ( CASE WHEN tblhomestayinfo.pool = '1' THEN 'Yes' WHEN tblhomestayinfo.pool = '0' THEN 'No' ELSE Cast(tblhomestayinfo.pool AS NVARCHAR(max)) END ) AS Pool, dbo.tblhomestayinfo.prefer, dbo.tblhomestayinfo.program, Format(dbo.tblhomestayinfo.[re-visit date], 'dd-MMM-yyyy') AS [Re-Visit Date], Format(dbo.tblhomestayinfo.recordcheckexpiry, 'dd-MMM-yyyy') AS RecordCheckExpiry, Format(dbo.tblhomestayinfo.refcheckdate, 'dd-MMM-yyyy') AS RefCheckDate, ( CASE WHEN tblhomestayinfo.reference = '1' THEN 'Yes' WHEN tblhomestayinfo.reference = '0' THEN 'No' ELSE Cast(tblhomestayinfo.reference AS NVARCHAR(max)) END ) AS Reference, ( CASE WHEN tblhomestayinfo.[rejected?] = '1' THEN 'Yes' WHEN tblhomestayinfo.[rejected?] = '0' THEN 'No' ELSE Cast(tblhomestayinfo.[rejected?] AS NVARCHAR(max)) END ) AS [Rejected?], Format(dbo.tblhomestayinfo.rejectionletterdate, 'dd-MMM-yyyy') AS RejectionLetterDate, dbo.tblhomestayinfo.rejectionreason, dbo.tblhomestayinfo.religion, Cast(dbo.tblhomestayinfo.[religious beliefs/practices detail] AS NVARCHAR (max)) AS [Religious beliefs/practices Detail], dbo.tblhomestayinfo.[religious beliefs/practices?], dbo.tblhomestayinfo.[retired?], Cast(dbo.tblhomestayinfo.roominfo AS NVARCHAR(max)) AS RoomInfo, dbo.isp_cloud_homestey_additional_info.numberofrooms - StudentQuery.studentsathomestay AS [# Rooms Offered], dbo.tblhomestayinfo.[school name short], dbo.tblhomestayinfo.schoolyear, dbo.tblhomestayinfo.[secondary catchment], ( CASE WHEN tblhomestayinfo.securitysystem = '1' THEN 'Yes' WHEN tblhomestayinfo.securitysystem = '0' THEN 'No' ELSE Cast(tblhomestayinfo.securitysystem AS NVARCHAR(max)) END ) AS SecuritySystem, dbo.tblhomestayinfo.[service schools], dbo.tblhomestayinfo.[short term only], dbo.tblhomestayinfo.[short term optional], ( CASE WHEN tblhomestayinfo.[short-term] = '1' THEN 'Yes' WHEN tblhomestayinfo.[short-term] = '0' THEN 'No' ELSE Cast(tblhomestayinfo.[short-term] AS NVARCHAR(max)) END ) AS [Short-Term], dbo.tblhomestayinfo.[smoke?], ( CASE WHEN tblhomestayinfo.smokedetectors = '1' THEN 'Yes' WHEN tblhomestayinfo.smokedetectors = '0' THEN 'No' ELSE Cast(tblhomestayinfo.smokedetectors AS NVARCHAR(max)) END ) AS SmokeDetectors, dbo.tblhomestayinfo.homestay AS SortName, Format(dbo.tblhomestayinfo.[start date], 'dd-MMM-yyyy') AS [Start Date], dbo.tblhomestayinfo.[address status] AS Status, dbo.tblhomestayinfo.[student bathroom], dbo.names.studentid AS [Student ID], dbo.names.fname AS [Student FName], dbo.names.lname AS [Student LName], dbo.tblhomestayinfo.[student phone], dbo.names.status AS [Student Status], dbo.names.program AS [Student Type], ( CASE WHEN tblhomestayinfo.[summer camp] = '1' THEN 'Yes' WHEN tblhomestayinfo.[summer camp] = '0' THEN 'No' ELSE Cast(tblhomestayinfo.[summer camp] AS NVARCHAR(max)) END ) AS [Summer Camp], dbo.tblhomestayinfo.[summer program], dbo.tblhomestayinfo.[tv?], dbo.tblhomestayinfo.transporttoschool, dbo.tblhomestayinfo.traveltime, Cast(dbo.tblhomestayinfo.[vegetarian details] AS NVARCHAR(max)) AS [Vegetarian Details], ( CASE WHEN tblhomestayinfo.vegetarian = '1' THEN 'Yes' WHEN tblhomestayinfo.vegetarian = '0' THEN 'No' ELSE Cast(tblhomestayinfo.vegetarian AS NVARCHAR(max)) END ) AS Vegetarian, dbo.tblhomestayinfo.vendornumber, dbo.tblhomestayinfo.[walking distance to school?], dbo.tblhomestayinfo.webid, dbo.tblhomestayinfo.web_status, dbo.tblhomestayinfo.web_familyid, ( CASE WHEN tblhomestayinfo.[winter camp] = '1' THEN 'Yes' WHEN tblhomestayinfo.[winter camp] = '0' THEN 'No' ELSE Cast(tblhomestayinfo.[winter camp] AS NVARCHAR(max)) END ) AS [Winter Camp], ( CASE WHEN tblhomestayinfo.workshop = '1' THEN 'Yes' WHEN tblhomestayinfo.workshop = '0' THEN 'No' ELSE Cast(tblhomestayinfo.workshop AS NVARCHAR(max)) END ) AS Workshop FROM dbo.tblhomestayinfo LEFT OUTER JOIN dbo.tblstudenthomestaylinking ON dbo.tblstudenthomestaylinking.familyid = dbo.tblhomestayinfo.familyid LEFT OUTER JOIN dbo.names ON dbo.names.studentid = dbo.tblstudenthomestaylinking.studentid LEFT OUTER JOIN dbo.agentdemographic ON dbo.names.[agent id] = dbo.agentdemographic.agid LEFT OUTER JOIN dbo.isp_cloud_tblhomestayinfo ON dbo.tblhomestayinfo.familyid = dbo.isp_cloud_tblhomestayinfo.familyid LEFT OUTER JOIN dbo.isp_cloud_homestey_additional_info_application ON dbo.isp_cloud_tblhomestayinfo.homestayapplicantuserid = dbo.isp_cloud_homestey_additional_info_application.homestayapplicantuserid LEFT OUTER JOIN dbo.isp_cloud_homestey_additional_info ON dbo.isp_cloud_tblhomestayinfo.homestayapplicantuserid = dbo.isp_cloud_homestey_additional_info.homestayapplicantuserid LEFT OUTER JOIN (SELECT familyid, Count(*) AS StudentsAtHomestay FROM dbo.tblstudenthomestaylinking WHERE ( [date joined family] <= Getdate() ) AND ( [date left family] >= Getdate() ) AND ( studentid IS NOT NULL ) GROUP BY familyid) AS StudentQuery ON dbo.isp_cloud_tblhomestayinfo.familyid = StudentQuery.familyid
Editor is loading...
Leave a Comment