Untitled

 avatar
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