Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT
- [addresstype].name As [Type],
- [address].city As [City], address.statecode As [State],
- [address].postalcode As [Zip],
- [address].addressid As [Id]
- FROM
- [address]
- LEFT OUTER JOIN [contact_address] ON [address].addressid = [contact_address].addressid
- LEFT OUTER JOIN [addresstype] ON [addresstype].addresstypeid = [contact_address].addresstypeid
- LEFT OUTER JOIN [clientcontact] ON dbo.contact_address.contactid = [clientcontact].contactid
- WHERE
- [contact_address].contactid = 12538
- ORDER BY
- [address].name, [address].statecode, [address].city
- SELECT
- dbo.clientcontact.clientcontactid ,
- dbo.clientcontact.clientid ,
- dbo.clientcontact.contactid
- --[addresstype].name As [Type],
- --[address].city As [City], address.statecode As [State],
- --[address].postalcode As [Zip],
- --[address].addressid As [Id]
- FROM
- [address]
- LEFT OUTER JOIN [contact_address] ON [address].addressid = [contact_address].addressid
- LEFT OUTER JOIN [addresstype] ON [addresstype].addresstypeid = [contact_address].addresstypeid
- right JOIN [clientcontact] ON dbo.contact_address.contactid = [clientcontact].contactid
- WHERE
- [contact_address].contactid = 12538
- ORDER BY
- [address].name, [address].statecode, [address].city
- SELECT
- client_addressexternal.address_table_type As [Address Record Type],
- addresstype.name As [Type],
- CASE WHEN client_addressexternal.address_table_type = 'CLIENT Address' THEN '<a href="/ClientServices/ManageClients/ClientDetails/ClientAddresses.aspx?Id=' + CONVERT(VARCHAR,client_addressexternal.addressid) + '&ClientId=' + CONVERT(VARCHAR,client_addressexternal.client_id) + '&SourceClientId=14103">' + address.name + '</a>' + '<br /><b>Client Name:</b> ' + client_addressexternal.client_full_name ELSE client_addressexternal.contact_full_name END As [Address Name],
- dbo.limssubstring(dbo.LIMSNullString(address1) + '<br />' + dbo.LIMSNullString(address2), 84) As [Address],
- address.city As [City], address.statecode As [State],
- address.postalcode As [Zip],
- CASE client.clientid WHEN 14103 THEN '' ELSE client.name END As [From Parent Client],
- address.addressid As [Id]
- FROM
- address
- JOIN (
- SELECT client_address.clientid, client_address.addressid, client_address.addresstypeid, depth, 'CLIENT Address' AS 'address_table_type', '' as 'contact_full_name', client.name as 'client_full_name', client_address.clientid as 'client_id', '' as 'contact_id'
- FROM dbo.fnClientRelatives(14103, 0, 1, 0) relatives
- inner join client_address on client_address.clientid = relatives.clientid
- LEFT OUTER JOIN client ON relatives.clientid = dbo.client.clientid
- UNION
- SELECT clientcontact.clientid, contact_address.addressid, contact_address.addresstypeid, 999 [depth], 'CONTACT Address' AS 'address_table_type', address.name + '<br /><b>Contact Name:</b> ' + LTRIM(RTRIM(ISNULL(contact.firstname, '') + ISNULL(' ' + contact.middleinitial + ' ', ' ') + ISNULL(contact.lastname, ''))), '' as 'client_full_name', clientcontact.clientid as 'client_id', clientcontact.contactid as 'contact_id'
- from clientcontact
- inner join contact_address ON contact_address.contactid=clientcontact.contactid and clientcontact.clientid=14103
- LEFT OUTER JOIN [contact] ON [clientcontact].contactid = [contact].contactid
- LEFT OUTER JOIN [address] ON contact_address.addressid = address.addressid
- ) AS client_addressexternal ON client_addressexternal.addressid = address.addressid
- JOIN client ON client.clientid = client_addressexternal.clientid
- JOIN addresstype on addresstype.addresstypeid = client_addressexternal.addresstypeid
- ORDER BY
- depth,address.statecode, address.city, address.name
- GO
- /****** Object: UserDefinedFunction [dbo].[fnClientRelatives] Script Date: 07/29/2011 12:48:24 ******/
- SET ANSI_NULLS OFF
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --your basic recursive tree searcher.
- --childrennotparents = 1 means you'll get children. = 0 means you'll get parents
- --@recursive = 1 means it finds all children, grandchildren, etc... or whatever
- -- The depth is the base level to start incrementing each level, if set to zero, the @clientid will also be part of the results
- ALTER FUNCTION [dbo].[fnClientRelatives]
- (
- @clientId INT,
- @childrenNotParents BIT,
- @recursive bit,
- @depth int
- )
- RETURNS @clientids TABLE (clientid INT primary key clustered, depth int)
- AS
- begin
- -- Add the parent client id if the depth is zero
- if @depth = 0
- begin
- INSERT INTO @clientids VALUES (@clientid, @depth)
- end
- set @depth = @depth + 1
- IF @childrenNotParents = 1
- begin
- DECLARE clientids CURSOR FOR
- SELECT clientid
- FROM client
- where parentclientid = @clientId
- END--/if childrennotparents
- ELSE--if not childrennotparents
- BEGIN
- DECLARE clientids CURSOR FOR
- SELECT parentclientid
- FROM client
- where clientid = @clientid
- END--/if not childrennotparents
- OPEN clientids
- DECLARE @nextClientID INT
- FETCH clientids INTO @nextClientID
- --@nextClientID may be null if we're loading parents, and the
- --current client has null for a parent id.
- WHILE @@FETCH_STATUS = 0 AND @nextClientID IS NOT NULL
- BEGIN
- INSERT INTO @clientids
- VALUES (@nextclientid, @depth)
- IF @recursive = 1
- BEGIN
- INSERT INTO @clientids
- SELECT * FROM dbo.fnClientRelatives(@nextclientid, @childrenNotParents, @recursive, @depth)
- END--IF @recursive = 1
- FETCH clientids INTO @nextclientid
- END--WHILE @@FETCH_STATUS = 0
- CLOSE clientids
- DEALLOCATE clientids
- RETURN
- END--/IssueRelatives
- GROUP BY [addresstype].name,
- [address].city,
- [adress].statecode,
- [address].postalcode,
- [address].addressid
- SELECT DISTINCT
- [addresstype].name As [Type],
- [address].city As [City], address.statecode As [State],
- [address].postalcode As [Zip],
- [address].addressid As [Id],
- [address].name
- FROM
- [address]
- LEFT OUTER JOIN [contact_address] ON [address].addressid = [contact_address].addressid
- LEFT OUTER JOIN [addresstype] ON [addresstype].addresstypeid = [contact_address].addresstypeid
- LEFT OUTER JOIN [clientcontact] ON dbo.contact_address.contactid = [clientcontact].contactid
- WHERE
- [contact_address].contactid = 12538
- ORDER BY
- [address].name, [address].statecode, [address].city
- GROUP BY ([addresstype].name,[address].city,
- address.statecode,[address].postalcode,[address].addressid)
- SELECT DISTINCT
- [address].name as [Address],
- [addresstype].name As [Type],
- [address].city As [City], address.statecode As [State],
- [address].postalcode As [Zip],
- [address].addressid As [Id]
- FROM
- [address]
- LEFT OUTER JOIN [contact_address] ON [address].addressid = [contact_address].addressid
- LEFT OUTER JOIN [addresstype] ON [addresstype].addresstypeid = [contact_address].addresstypeid
- LEFT OUTER JOIN [clientcontact] ON dbo.contact_address.contactid = [clientcontact].contactid
- WHERE
- [contact_address].contactid = 12538
- ORDER BY
- [address].name, [address].statecode, [address].city
Add Comment
Please, Sign In to add comment