Click here to Skip to main content
14,207,050 members
   

Database

 
GeneralRe: User Access Database - Best practise Pin
Eddy Vluggen9-Nov-18 5:49
mveEddy Vluggen9-Nov-18 5:49 
AnswerRe: User Access Database - Best practise Pin
Jörgen Andersson9-Nov-18 0:47
protectorJörgen Andersson9-Nov-18 0:47 
GeneralRe: User Access Database - Best practise Pin
Simon_Whale9-Nov-18 1:38
professionalSimon_Whale9-Nov-18 1:38 
GeneralRe: User Access Database - Best practise Pin
Jörgen Andersson9-Nov-18 1:46
protectorJörgen Andersson9-Nov-18 1:46 
GeneralRe: User Access Database - Best practise Pin
Simon_Whale9-Nov-18 1:50
professionalSimon_Whale9-Nov-18 1:50 
QuestionConditional statements in ON clause of joins Pin
simpledeveloper7-Nov-18 12:49
membersimpledeveloper7-Nov-18 12:49 
AnswerRe: Conditional statements in ON clause of joins Pin
Mycroft Holmes7-Nov-18 20:25
memberMycroft Holmes7-Nov-18 20:25 
GeneralRe: Conditional statements in ON clause of joins Pin
simpledeveloper9-Nov-18 13:00
membersimpledeveloper9-Nov-18 13:00 
Yeah I did it using table valued functions - thanks for giving me the idea, here is my script for it.
create FUNCTION udf_GetLegalEntityAddress(@leId int)  
	returns @T table(PKAddressId int,
	StreetAddress1 varchar(75),
	StreetAddress2 varchar(75),
	City varchar(75),
	FK_County int,
	FK_State int,
	Zip1 int,
	Zip2 int,
	CreatedDate datetime,
	CreatedBy varchar(500),
	ModifiedDate datetime,
	ModifiedBy varchar(500),
	MigrationNotes varchar(50),
	FKProgramTypeLKPId int,
	OldSystemId int,
	ValidFlag bit)  
AS
begin
	DECLARE @AdminAddressTypeId int=0, @AddressId int=0, @effectivedate datetime, @PKLegalEntityAddressId int
	SET @AdminAddressTypeId = (select top 1 AddType.PKAddressTypeLKPId from AddressTypeLKP AddType where AddType.Description = 'Administrative')

	SET @AddressId = 
	(select top 1 FKAddressId from LegalEntityAddress where FKAddressTypeLKPId= @AdminAddressTypeId and FKLegalEntityId=@leId and IsValid = 1)

	if ((@AddressId is null) or (@AddressId=0))
	begin
		SET @AddressId = 
		(select top 1 FKAddressId from LegalEntityAddress where (EffectiveDateTo is null) and FKLegalEntityId=@leId and IsValid = 1)		
	end

	if ((@AddressId is null) or (@AddressId=0))
	begin
		set @effectivedate = (select max(EffectiveDateTo) from LegalEntityAddress where FKLegalEntityId=@leId and (IsValid = 1))
		if ((@effectivedate is null) or (@effectivedate<='1900-01-01'))
		begin
		 set @PKLegalEntityAddressId = (select max(PKLegalEntityAddressId) from LegalEntityAddress where (FKLegalEntityId=@leId) and (IsValid = 1))
		 SET @AddressId = 
		 (select top 1 FKAddressId from LegalEntityAddress where PKLegalEntityAddressId=@PKLegalEntityAddressId) 
		end
		else
		begin
		 SET @AddressId = 
		 (select top 1 FKAddressId from LegalEntityAddress where FKLegalEntityId=@leId and EffectiveDateTo=@effectivedate) 		 
		end
	end
	if ((@AddressId is null) or (@AddressId=0))
	begin
		SET @AddressId = 
		(select top 1 FKAddressId from LegalEntityAddress where FKLegalEntityId=@leId and IsValid = 1)
	end

		insert into @T (
		PKAddressId,
		StreetAddress1,
		StreetAddress2,
		City,
		FK_County,
		FK_State,
		Zip1,
		Zip2,
		CreatedDate,
		CreatedBy,
		ModifiedDate,
		ModifiedBy,
		MigrationNotes,
		FKProgramTypeLKPId,
		OldSystemId,
		ValidFlag)
		select top 1 
		PKAddressId,
		StreetAddress1,
		StreetAddress2,
		City,
		FK_County,
		FK_State,
		Zip1,
		Zip2,
		CreatedDate,
		CreatedBy,
		ModifiedDate,
		ModifiedBy,
		MigrationNotes,
		FKProgramTypeLKPId,
		OldSystemId,
		ValidFlag from [Address] where PKAddressId=@AddressId

		RETURN
end
go


Then using it in the stored Procedure using Outer Apply
SELECT LE.LegalEntityName
         ,LE.PKLegalEntityId
         ,LE.FederalTaxId
         ,LE.LegalEntityNbr
         ,PRG.Description as ProgramType
         ,LE.FKProgramTypeLKPId
         ,C.PK_Geographic_Location_Code + '-' + C.County_Name as CountyName
         ,LE.FKCountyLKPId
         ,AA.StreetAddress1
         , AA.City
         ,Admin_S.State_Name
         ,AA.Zip1
  FROM [dbo].[LegalEntity] LE
  inner join  [dbo].[ProgramTypeLKP] PRG on LE.FKProgramTypeLKPId = PRG.PKProgramTypeLKPId and PRG.Description = 'Mental Health'
  left outer Join [dbo].County C on LE.FKCountyLKPId = C.PKCountyId
  outer apply dbo.udf_GetLegalEntityAddress(LE.PKLegalEntityId) AS LEA
  left Outer JOIN  Address AA ON  LEA.PKAddressId = AA.PKAddressId
  Left outer JOIN County Admin_CY on Admin_CY.PKCountyId = AA.FK_County
  Left outer join State Admin_S on Admin_S.PKStateId = AA.FK_State
  where LE.IsCompleted = 1

GeneralRe: Conditional statements in ON clause of joins Pin
Mycroft Holmes9-Nov-18 17:02
memberMycroft Holmes9-Nov-18 17:02 
GeneralRe: Conditional statements in ON clause of joins Pin
simpledeveloper11-Nov-18 17:28
membersimpledeveloper11-Nov-18 17:28 
QuestionLooking for a lightweight database to use for a vb win app Pin
jkirkerx7-Nov-18 12:01
professionaljkirkerx7-Nov-18 12:01 
AnswerRe: Looking for a lightweight database to use for a vb win app Pin
Nathan Minier8-Nov-18 1:08
professionalNathan Minier8-Nov-18 1:08 
GeneralRe: Looking for a lightweight database to use for a vb win app Pin
jkirkerx8-Nov-18 10:25
professionaljkirkerx8-Nov-18 10:25 
AnswerRe: Looking for a lightweight database to use for a vb win app Pin
--ZiM-- .11-Nov-18 8:36
member--ZiM-- .11-Nov-18 8:36 
QuestionBackup file is not restoring into Database even after giving message as restored successfully Pin
simpledeveloper7-Nov-18 7:40
membersimpledeveloper7-Nov-18 7:40 
QuestionIndex on nvarchar column doesnot affect performance (sql server) Pin
Heba Kamel23-Oct-18 1:29
memberHeba Kamel23-Oct-18 1:29 
AnswerRe: Index on nvarchar column doesnot affect performance (sql server) Pin
Richard Deeming23-Oct-18 1:58
mveRichard Deeming23-Oct-18 1:58 
GeneralRe: Index on nvarchar column doesnot affect performance (sql server) Pin
Heba Kamel23-Oct-18 2:03
memberHeba Kamel23-Oct-18 2:03 
GeneralRe: Index on nvarchar column doesnot affect performance (sql server) Pin
Richard Deeming23-Oct-18 2:04
mveRichard Deeming23-Oct-18 2:04 
Questionhow do i scan a image from a flat bed scanner using python and Microsoft access Pin
Member 1401815918-Oct-18 3:19
memberMember 1401815918-Oct-18 3:19 
Rant[REPOST] how do i scan a image from a flat bed scanner using python and Microsoft access Pin
Richard Deeming18-Oct-18 3:28
mveRichard Deeming18-Oct-18 3:28 
AnswerRe: how do i scan a image from a flat bed scanner using python and Microsoft access Pin
CHill6018-Oct-18 4:50
protectorCHill6018-Oct-18 4:50 
Generalthank you for your reply how you suggest i do it? Pin
Member 1401815918-Oct-18 20:52
memberMember 1401815918-Oct-18 20:52 
GeneralRe: thank you for your reply how you suggest i do it? Pin
CHill6021-Oct-18 2:20
protectorCHill6021-Oct-18 2:20 
QuestionACCESS DATABASE OR PYTHON - SCANNING FROM A FLAT BED SCANNER Pin
Member 1401815918-Oct-18 0:41
memberMember 1401815918-Oct-18 0:41 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.


Advertise | Privacy | Cookies | Terms of Service
Web03 | 2.8.190612.1 | Last Updated 10 Jun 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid