Select all from 1 file and top from another

Question

Select all from 1 file and top from another

Viewing 3 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

The reason I did the query with an inline sub-query instead of a defined UDF is because there's a performance cost to using a UDF just because it's an external object to the query. That may not matter in your case, but I recommend against using them unless the code will be re-used repeatedly in many different queries.

Again, that may not matter in your case. Personally, I prefer to get as much performance as possible out of my queries, so long as it doesn't jeapardize accuracy of results.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

stephen.staple SSC Rookie Points: 33 More actions · Answer 2

OK. I have taken your comments on board, I simplified the SQL I was using to make it more readable. The real procedures look like this:

ALTER FUNCTION [dbo].[fn_GetTopMatch](@LoginID AS int)

RETURNS TABLE

AS

RETURN

--

SELECT

TOP (1)

a.MatchID, a.VacancyID, b.PlacementPeriodID, c.orgID AS tblOrganisationOrgID, c.orgName, c.orgAddress1, c.orgAddress2, c.orgTown, c.orgPostcode,

c.orgWebSite, c.orgContactName, c.ContactLastName, c.orgContactTel, c.orgContactEmail, d.TFGFileName, d.EVS, isnull(h.County,'Missing') as County, isnull(g.cntName,'Missing') AS orgCountry,

isnull(f.JobTitle,'Missing') as JobTitle, isnull(e.SupervisorName,'Missing') as SupervisorName, e.SupervisorTel, e.SupervisorMobile, e.SupervisorEmail, e.SupervisorSkype, e.Supervisor2Name, e.Supervisor2Tel,

e.Supervisor2Mobile, e.Supervisor2Skype, e.Supervisor2Email, e.Supervisor3Name, e.Supervisor3Tel, e.Supervisor3Mobile, e.Supervisor3Skype,

e.Supervisor3Email

FROM Match AS a INNER JOIN

Vacancy AS b ON a.VacancyID = b.VacancyID INNER JOIN

tblOrganisation AS c ON b.OrgID = c.orgID INNER JOIN

OrganisationAdmin AS d ON c.orgID = d.OrgID INNER JOIN

tblPlacement AS e ON c.orgID = e.plaOrgID INNER JOIN

PlacementRole AS f ON c.orgID = f.OrgID LEFT OUTER JOIN

County AS h ON c.orgCountyID = h.CountyID LEFT OUTER JOIN

tblLookupCountry AS g ON c.orgCountyID = g.cntID

WHERE (a.LoginID = @LoginID)

order by MatchID desc

and

USE [TimeForGod]

GO

/****** Object: StoredProcedure [dbo].[Report_volunteer_ultimate] Script Date: 11/23/2011 15:00:30 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[Report_volunteer_ultimate]

@PlacementPeriodID int,

@VolunteerStatusID int

AS

IF @PlacementPeriodID = -1 SET @PlacementPeriodID = NULL

IF @VolunteerStatusID = -1 SET @VolunteerStatusID = NULL

BEGIN

IF @PlacementPeriodID IS NULL and @VolunteerStatusID IS NULL

BEGIN

/* both parameters null*/

SELECT a.perForename, a.perSurname, a.perPreferredName, a.perSex, a.perCountry, a.perEmail, a.perStreet, a.perTown, a.perCounty, a.PerPostcode,

a.perPhone, a.perMobile, CONVERT(varchar(10), a.perDateOfBirth, 103) AS DOB, FLOOR(DATEDIFF(day, a.perDateOfBirth, GETDATE()) / 365.242199) AS Age,

a.perSkype, b.sexName, c.ConferenceName, CONVERT(varchar(10), c.ConferenceDate, 103) AS ConferenceDate, c.Location, c.ConferenceID, d.AdminID,

CONVERT(VARCHAR(10), d.ArrivalDate, 103) AS StartDate, CONVERT(VARCHAR(10), d.EndOfServiceDate, 103) AS EndDate,

e.FirstName AS FieldOfficerFirstName, e.LastName AS FieldOfficerLastName, f.Status, g.AddressLine1, g.AddressLine2, g.CountyOther, g.Postcode,

g.Phone, g.CountryID, g.CountyID, g.Town AS ServAdTown, ISNULL(h.County, 'Missing') AS County, i.medDietaryNeeds, ISNULL(j.DietType, 'Missing')

AS DietType, k.ApplicationType, k.ApplicationTypeID, l.cntName, ISNULL(m.AgencyName, 'Missing') AS AgencyName, m.EDYN, n.emeName,

n.emeRelationship, n.emeStreet, n.emeTown, n.emeCounty, n.emePostcode, n.emeCountry, n.emePhone, n.emeMobile, n.emeEmail, n.emeSkype,

o.cntName AS EmergencyContactCountry, p.Year, p.Month, q.wayID, r.Denomination,

matchTable.MatchID,

matchTable.VacancyID,

matchTable.PlacementPeriodID,

matchTable.tblOrganisationOrgID,

matchTable.TFGFileName,

matchTable.EVS,

matchTable.SupervisorName,

matchTable.SupervisorTel,

matchTable.SupervisorMobile,

matchTable.SupervisorEmail,

matchTable.orgName,

matchTable.orgAddress1,

matchTable.orgAddress2,

matchTable.orgTown,

matchTable.orgPostcode,

matchTable.County AS orgCounty,

matchTable.orgCountry,

matchTable.orgWebSite,

matchTable.orgContactName,

matchTable.ContactLastName,

matchTable.orgContactTel,

matchTable.orgContactEmail,

matchTable.JobTitle,

matchTable.SupervisorSkype,

matchTable.Supervisor2Name,

matchTable.Supervisor2Tel,

matchTable.Supervisor2Mobile,

matchTable.Supervisor2Skype,

matchTable.Supervisor2Email,

matchTable.Supervisor3Name,

matchTable.Supervisor3Tel,

matchTable.Supervisor3Mobile,

matchTable.Supervisor3Skype,

matchTable.Supervisor3Email

FROM tblPersonalData AS a LEFT OUTER JOIN

tblLookupSex AS b ON a.perSex = b.sexID LEFT OUTER JOIN

Conference AS c ON a.ConferenceID = c.ConferenceID INNER JOIN

CheckList AS d ON a.perLoginID = d.LoginID LEFT OUTER JOIN

AdminLogin AS e ON e.AdminID = d.AdminID INNER JOIN

VolunteerStatus AS f ON a.VolunteerStatusID = f.VolunteerStatusID INNER JOIN

VolServiceAddress AS g ON a.perLoginID = g.LoginID LEFT OUTER JOIN

County AS h ON g.CountyID = h.CountyID INNER JOIN

tblMedical AS i ON a.perLoginID = i.medLoginID LEFT OUTER JOIN

DietType AS j ON i.DietTypeID = j.DietTypeID INNER JOIN

ApplicationType AS k ON a.ApplicationTypeID = k.ApplicationTypeID INNER JOIN

tblLookupCountry AS l ON a.perCountry = l.cntID LEFT OUTER JOIN

Agency AS m ON a.PartnerOrganisationID = m.AgencyID INNER JOIN

tblEmergencyContact AS n ON a.perLoginID = n.emeLoginID INNER JOIN

tblLookupCountry AS o ON l.cntID = o.cntID INNER JOIN

PlacementPeriod AS p ON a.PlacementPeriodID = p.PlacementPeriodID INNER JOIN

tblWhoAreYou AS q ON a.perLoginID = q.wayLoginID INNER JOIN

Denomination AS r ON q.DenominationID = r.DenominationID

cross apply

dbo.fn_GetTopMatch(perLoginID) as matchTable

where a.archived = 0

ORDER BY a.perSurname

END

ELSE IF @VolunteerStatusID IS NULL

/* volunteer status is null */

/* placement period is selected */