Share via

How can i combine two select columns value and values should be unique and optimized ?

mehmood tekfirst 771 Reputation points
2022-11-09T13:46:49.333+00:00

Hi,

What I want that is to combine these two ar.RenterUserId,ad.MainDriverUserId columns

If RenterUserId and MainDriverUserId is same then RenterUserId would be displayed and If RenterUserId and MainDriverUserId is different then both Ids would be displayed individually/separately. 

Select ba.Id, ar.RenterUserId,ad.MainDriverUserId,ba.RentalAgreementId, ba.AgreementStatus, ba.FranchiseId  
from BookingAgreements ba with (nolock)  
left join AgreementRenters ar with (nolock) on ar.AgreementId = ba.Id and ar.FranchiseId = ba.FranchiseId  
left join AgreementDrivers ad with (nolock) on ad.AgreementId = ba.Id and ad.FranchiseId = ba.FranchiseId

thank you

See the structure of the tables below: 

 CREATE TABLE [dbo].[BookingAgreements](  
     [Id] [int] IDENTITY(1,1) NOT NULL,  
     [FranchiseId] [int] NOT NULL,     
     [ReservationId] [varchar](9) NULL,  
     [RentalAgreementId] [varchar](9) NULL,    
     [AgreementStatus] [int] NULL,     
     [StatusId] [int] NULL,    
     [OrderNumber] [nvarchar](250) NULL,  
     [ChargedDays] [decimal](18, 2) NULL,      
  CONSTRAINT [PK_BookingAgreements] PRIMARY KEY CLUSTERED   
 (  
     [Id] ASC  
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]  
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
 GO  
      
 CREATE TABLE [dbo].[AgreementDrivers](  
     [AgreementId] [bigint] NOT NULL,  
     [DriverId] [bigint] NOT NULL,         
     [FranchiseId] [bigint] NOT NULL,  
     [StatusId] [int] NULL,  
     [Id] [bigint] IDENTITY(1,1) NOT NULL,  
     [DriverPhone] [varchar](50) NULL,  
     [DriverTitle] [varchar](150) NULL,  
     [DriverFirstName] [varchar](150) NULL,  
     [DriverSurname] [varchar](150) NULL,  
     [DriverDOB] [datetime] NULL,  
     [DriverCompanyName] [varchar](150) NULL,  
     [DriverCompanyDetails] [varchar](500) NULL,  
     [DriverEmail] [varchar](150) NULL,  
     [DriverSources] [varchar](50) NULL,  
     [DriverPostCode] [varchar](50) NULL,  
     [DriverHouse] [varchar](250) NULL,  
     [DriverStreet] [varchar](250) NULL,  
     [DriverVillage] [varchar](50) NULL,  
     [DriverCountry] [int] NULL,  
     [DriverCounty] [int] NULL,  
     [DriverTown] [int] NULL,  
     [DriverDetailTownName] [nvarchar](250) NULL,  
     [DriverDetailCountyName] [nvarchar](250) NULL,  
     [DriverCountryName] [varchar](250) NULL,  
     [LicenceCopyMade] [bit] NULL,  
     [LicenceDetailCountry] [varchar](100) NULL,  
     [LicenceType] [int] NULL,  
     [LicenceTestPassDate] [datetime] NULL,  
     [LicenceExpiryDate] [datetime] NULL,  
     [RenterId] [int] NULL,  
     [MainDriverUserId] [int] NULL,  
  CONSTRAINT [PK_AgreementDrivers] PRIMARY KEY CLUSTERED   
 (  
     [Id] ASC  
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]  
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
 GO  
      
 CREATE TABLE [dbo].[AgreementRenters](  
     [Id] [int] IDENTITY(1,1) NOT NULL,  
     [AgreementId] [int] NULL,  
     [FranchiseId] [int] NULL,  
     [isRenterAgreedAccHolder] [bit] NULL,  
     [RenterTypeId] [int] NULL,  
     [isHighRiskUser] [bit] NULL,  
     [MainDriverId] [int] NULL,  
     [RenterSources] [varchar](50) NULL,  
     [RenterTitle] [varchar](150) NULL,  
     [RenterFirstName] [varchar](150) NULL,  
     [RenterSurname] [varchar](150) NULL,  
     [RenterDOB] [datetime] NULL,  
     [RenterPhone] [varchar](50) NULL,  
     [RenterEmail] [varchar](150) NULL,  
     [RenterCompanyName] [varchar](150) NULL,  
     [RenterCompanyDetails] [varchar](500) NULL,  
     [RenterCompanyAcNumber] [varchar](50) NULL,  
     [LicencePostCode] [varchar](50) NULL,  
     [LicenceHouse] [varchar](250) NULL,  
     [LicenceStreet] [varchar](250) NULL,  
     [LicenceVillage] [varchar](50) NULL,  
     [LicenceCountry] [int] NULL,  
     [LicenceCounty] [int] NULL,  
     [LicenceTown] [int] NULL,  
     [LicenceTownName] [nvarchar](250) NULL,  
     [LicenceCountyName] [nvarchar](250) NULL,  
     [LicenceCountryName] [nvarchar](250) NULL,  
     [LicenceNumber] [varchar](50) NULL,  
     [LicenceDetails] [bit] NULL,  
     [LicenceType] [int] NULL,  
     [LicenceIssuedBy] [varchar](50) NULL,  
     [LicenceIssuedCountry] [varchar](50) NULL,  
     [LicenceTestPassDate] [datetime] NULL,  
     [LicenceExpiryDate] [datetime] NULL,  
     [LicenceSeen] [bit] NULL,  
     [LicenceWebCheck] [bit] NULL,  
     [LicenceETDSeen] [bit] NULL,  
     [LicenceCopyMade] [bit] NULL,  
     [LicenceGroups] [varchar](50) NULL,  
     [RenterLicenceIsUkAddress] [bit] NOT NULL,  
     [ContactPostCode] [varchar](50) NULL,  
     [ContactAddressType] [varchar](50) NULL,  
     [ContactAddress1] [varchar](250) NULL,  
     [ContactAddress2] [varchar](250) NULL,  
     [ContactAddress3] [varchar](250) NULL,  
     [ContactCountry] [int] NULL,  
     [ContactCounty] [int] NULL,  
     [ContactTown] [int] NULL,  
     [ContactMethod] [varchar](50) NULL,  
     [RenterContactIsUkAddress] [bit] NOT NULL,  
     [ContactTownName] [nvarchar](250) NULL,  
     [ContactCountyName] [nvarchar](250) NULL,  
     [ContactCountryName] [nvarchar](250) NULL,  
     [Occupation] [varchar](50) NULL,  
     [OccEmploymentType] [varchar](150) NULL,  
     [OccName] [nvarchar](250) NULL,  
     [OccPhone] [varchar](50) NULL,  
     [OccPostcode] [nvarchar](50) NULL,  
     [OccAddress1] [varchar](250) NULL,  
     [OccAddress2] [varchar](250) NULL,  
     [OccAddress3] [varchar](250) NULL,  
     [OccCountry] [int] NULL,  
     [OccCounty] [int] NULL,  
     [OccTown] [int] NULL,  
     [OccVerified] [bit] NULL,  
     [RenterOccIsUkAddress] [bit] NOT NULL,  
     [OccTownName] [nvarchar](250) NULL,  
     [OccCountyName] [nvarchar](250) NULL,  
     [OccCountryName] [nvarchar](250) NULL,  
     [RenterIdentification] [varchar](max) NULL,  
     [CoiInsuranceCompany] [varchar](50) NULL,  
     [CoiPolicyNumber] [varchar](50) NULL,  
     [CoiContactName] [varchar](50) NULL,  
     [CoiExpiryDate] [datetime] NULL,  
     [CoiPhone] [varchar](20) NULL,  
     [CoiCertificateCopy] [bit] NULL,  
     [HighRiskUsrComment] [nvarchar](500) NULL,  
     [VatNo] [varchar](50) NULL,  
     [IptNo] [varchar](50) NULL,  
     [StatusId] [int] NULL,  
     [IsPermissionToSpeak] [bit] NULL,  
     [InsuranceProvider] [varchar](100) NULL,  
     [InsuranceCoverNote] [varchar](250) NULL,  
     [InsuranceInsuranceExpiryDate] [datetime] NULL,  
     [InsurancePhone] [varchar](50) NULL,  
     [InsuranceContract] [varchar](250) NULL,  
     [IsInsuranceSubmissionCompleted] [bit] NULL,  
     [RenterUserId] [int] NULL,  
     [Note] [nvarchar](500) NULL,  
     [CreatedBy] [int] NULL,  
     [ModifiedBy] [int] NULL,  
     [CreatedDate] [datetime] NULL,  
     [ModifiedDate] [datetime] NULL,  
     [IsGlobalUserIgnored] [int] NULL,  
  CONSTRAINT [PK_AgreementRenters] PRIMARY KEY CLUSTERED   
 (  
     [Id] ASC  
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]  
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
 GO

Ok see the plan detail at here :

https://www.brentozar.com/pastetheplan/?id=SynBgmqHj

See the sample data in these columns

258947-image.png

258958-image.png

My Desired output is to combine both column values into one column and ensure that there would not be any missing value in it. If you 'll see in the below snapshot then you 'll come to know that the UserId column has missed 10050 Id and displaying just 10045 id.

258969-image.png

The MS Sql server at our production is : Microsoft SQL Server 2017 (RTM) - Microsoft Corporation Web Edition (64-bit)

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Viorel 114.7K Reputation points
    2022-11-10T08:38:08.56+00:00

    Check another guess: 

    select ba.Id, ar.RenterUserId as UserId, ar.RenterUserId, ad.MainDriverUserId, ba.RentalAgreementId, ba.AgreementStatus, ba.FranchiseId  
from BookingAgreements ba with (nolock)  
left join AgreementRenters ar with (nolock) on ar.AgreementId = ba.Id and ar.FranchiseId = ba.FranchiseId  
left join AgreementDrivers ad with (nolock) on ad.AgreementId = ba.Id and ad.FranchiseId = ba.FranchiseId  
union  
select ba.Id, ad.MainDriverUserId as UserId, ar.RenterUserId, ad.MainDriverUserId, ba.RentalAgreementId, ba.AgreementStatus, ba.FranchiseId  
from BookingAgreements ba with (nolock)  
left join AgreementRenters ar with (nolock) on ar.AgreementId = ba.Id and ar.FranchiseId = ba.FranchiseId  
left join AgreementDrivers ad with (nolock) on ad.AgreementId = ba.Id and ad.FranchiseId = ba.FranchiseId
    1 person found this answer helpful.
  2. mehmood tekfirst 771 Reputation points
    2022-11-11T10:44:04.1+00:00

    This is my final query. If anybody can improve it. I 'll be much happy to see it performing well. 

    Declare @firstName nvarchar(250) = 'omer';	  
Declare	@surName nvarchar(250) = NULL;  
Declare	@Take int = 25;  
Declare	@Skip int = 1;  
Declare @PageNumber INT = @Skip;  
Declare @PageSize   INT = @Take;  
Declare	@renterType int = 1;  
  
;WITH CTERA  
AS (SELECT  
  ba.[Id],  
  ba.[RentalAgreementId],  
  ba.[AgreementStatus],  
  ba.[FranchiseId],  
  1 [IsRA],  
  fr.Name [FranchiseName]  
FROM BookingAgreements ba (NOLOCK)  
INNER JOIN Franchise fr (NOLOCK)  
  ON ba.FranchiseId = fr.Id  
WHERE ba.StatusId = 1  
AND fr.Status_Id = 1  
AND ba.RentalAgreementId IS NOT NULL),  
CTERES  
AS (SELECT  
  ba.[Id],  
  ba.[RentalAgreementId],  
  ba.[AgreementStatus],  
  ba.[FranchiseId],  
  1 [IsRes],  
  fr.Name [FranchiseName]  
FROM BookingAgreements ba (NOLOCK)  
INNER JOIN Franchise fr (NOLOCK)  
  ON ba.FranchiseId = fr.Id  
WHERE ba.StatusId = 1  
AND fr.Status_Id = 1  
AND ba.RentalAgreementId IS NULL),  
CTEAgRenters  
AS (SELECT  
  ar.[AgreementId],  
  ar.RenterUserId [UserId],  
  ar.[FranchiseId],  
  NULLIF(ra.FranchiseName, res.FranchiseName) [FranchiseName],  
  COUNT(ra.IsRA) OVER (PARTITION BY ar.RenterUserId) RACount,  
  COUNT(res.IsRes) OVER (PARTITION BY ar.RenterUserId) ResCount  
FROM AgreementRenters ar (NOLOCK)  
LEFT JOIN CTERA ra  
  ON ra.Id = ar.AgreementId  
  AND ra.FranchiseId = ar.FranchiseId  
LEFT JOIN CTERES res  
  ON res.Id = ar.AgreementId  
  AND res.FranchiseId = ar.FranchiseId  
UNION ALL  
SELECT  
  ad.[AgreementId],  
  ad.MainDriverUserId [UserId],  
  ad.[FranchiseId],  
  NULLIF(ra.FranchiseName, res.FranchiseName) [FranchiseName],  
  COUNT(ra.IsRA) OVER (PARTITION BY ad.MainDriverUserId) RACount,  
  COUNT(res.IsRes) OVER (PARTITION BY ad.MainDriverUserId) ResCount  
FROM AgreementDrivers ad (NOLOCK)  
LEFT JOIN CTERA ra  
  ON ra.Id = ad.AgreementId  
  AND ra.FranchiseId = ad.FranchiseId  
LEFT JOIN CTERES res  
  ON res.Id = ad.AgreementId  
  AND res.FranchiseId = ad.FranchiseId)  
SELECT  
  r.[Id],      
  r.FirstName [RenterFirstName],  
  r.Surname [RenterSurname],  
  r.PhoneNo [RenterPhone],    
  c.[RACount],  
  c.[ResCount],    
  r.[FranchiseId]  
FROM AgreementUsers r WITH (NOLOCK)  
INNER JOIN CTEAgRenters c  
  ON r.Id = c.UserId  
WHERE 1 = 1  
AND r.FirstName LIKE @firstName + '%'  
AND r.[RenterType] = @renterType  
ORDER BY r.Id DESC OFFSET @PageSize * (@PageNumber - 1) ROWS FETCH NEXT @PageSize ROWS ONLY  
OPTION (RECOMPILE);
    0 comments