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.familyidEditor is loading...
Leave a Comment