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,079 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,579 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Viorel 113.8K 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 No comments