Building distinct Column from a common column from two tables

casedistinct

Good afternoon team,

hope all is well

I am hoping you can help me.

I have following two tables "GODS.DQ_TOBE" & "GODS.DQ_CURRENT". Within each table there is a common column called "Account_code__C". What i would like to do is build a column called Account_code__C which houses the distinct account codes from both of these tables. i would then like to produce two other columns to tell me what table the Account_code__C was found in.

Desired outcome would look something like this

SELECT Account_code__C FROM GODS.DQ_TOBE
Select Account_code__C from GODS.DQ_CURRENT

Best Answer

The following code should get you the result for MS SQL or MySQL:

select distinct f.Account_code__C,
case when a.Account_code__C<>'' then 'Y' else 'N' end AS DQ_TOBE, 
case when b.Account_code__C<>'' then 'Y' else 'N' end AS DQ_CURRENT
from (
    SELECT Account_code__C FROM GODS.DQ_TOBE
    union
    SELECT Account_code__C FROM GODS.DQ_CURRENT
) f
left join GODS.DQ_TOBE a on a.Account_code__C=f.Account_code__C
left join GODS.DQ_CURRENT b on b.Account_code__C=f.Account_code__C

Related Solutions

Mysql – Distinct Combination of Two Columns

You can count distinct elements by running:

select count(distinct policy_id, client_id) from policy_client;

Another option would be to group by and count that:

select count(*) from (select policy_id, client_id from policy_client group by 1,2) a;

Run both version and see which one performs better on your dataset.

A very quick way but not totally accurate if you have a key on (policy_id and client_id) you can also check the cardinality of that index but that's an approximate not exact number.

Sql-server – Need to Add Update to Query

Here is something you can play with - I did minimal testing in this example and I could have missed something or misunderstood your question or intentions.

--Demo setup
set nocount on
IF OBJECT_ID('Parts') IS NOT NULL
    DROP TABLE Parts;
IF OBJECT_ID('Vehicles') IS NOT NULL
    DROP TABLE Vehicles;
IF OBJECT_ID('Workorders') IS NOT NULL
    DROP TABLE Workorders;
IF OBJECT_ID('WoPart') IS NOT NULL
    DROP TABLE WoPart;
CREATE TABLE Parts (PartNumber VARCHAR(25), Notes VARCHAR(MAX))
CREATE TABLE Vehicles (RecordId INT,VIN VARCHAR(25))
CREATE TABLE Workorders (RecordId INT,VehKey VARCHAR(25))
CREATE TABLE WoPart (RecordId INT,PartNumber VARCHAR(25))

INSERT INTO Parts (PartNumber,Notes) VALUES('Part1','')
INSERT INTO Parts (PartNumber,Notes) VALUES('Part2','Begin note - COMPATIBLE WITH xyz')
INSERT INTO Vehicles (RecordId,VIN) VALUES(1,'123456789')
INSERT INTO Vehicles (RecordId,VIN) VALUES(2,'987654321')
INSERT INTO Workorders (RecordId,VehKey) VALUES(1,'123456789')
INSERT INTO Workorders (RecordId,VehKey) VALUES(2,'987654321')
INSERT INTO WoPart (RecordId,PartNumber) VALUES(1,'Part1')
INSERT INTO WoPart (RecordId,PartNumber) VALUES(2,'Part1')
INSERT INTO WoPart (RecordId,PartNumber) VALUES(2,'Part2')
go

--To keep the concatenation simple, I created a TVF to return the 
--concatenated strings of VIN's for the part
--The function concatenates with commas for easier visualization, but could
--be changed to use line feeds
IF EXISTS 
(
SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[ConcatenateVinsForPart]')
AND type IN (N'FN',N'IF',N'TF',N'FS',N'FT')
)
    DROP FUNCTION [dbo].[ConcatenateVinsForPart]
GO

CREATE FUNCTION ConcatenateVinsForPart (@PartNumber VARCHAR(25))
RETURNS @temptable TABLE (ConcatString VARCHAR(Max))

BEGIN
    DECLARE @ConcatString VARCHAR(max)
    ;
    WITH DistinctVins AS (
            SELECT DISTINCT VIN
            FROM Vehicles veh
            JOIN Workorders wo ON wo.VehKey = veh.VIN
            JOIN WoPart wop ON wop.RecordId = wo.RecordId
            WHERE wop.PartNumber = @PartNumber
            )

    SELECT @ConcatString = isnull(@ConcatString + ',', '') + VIN
    FROM DistinctVins
    INSERT INTO @temptable(ConcatString) values(@ConcatString)

    RETURN
END
GO

--If you want to see the current notes and the NewNotes BEFORE
--actually running this update, you can use this SELECT statement
SELECT Notes,NewNotes = 
CASE 
    --Checking for note that's never had COMPATIBLE WITH string
    WHEN 0 = PATINDEX('%COMPATIBLE WITH%', NOTES) 
        THEN notes + 'COMPATIBLE WITH ' + VinsForPart.ConcatString 
        + '-Last Compatibility Report: ' + convert(varchar(20),GETDATE())
    --add 15 to get past COMPATIBLE WITH string
    ELSE substring(notes, 1, PATINDEX('%COMPATIBLE WITH%', NOTES) + 15) 
    + ' '
    + VinsForPart.ConcatString
    + '-Last Compatibility Report: ' + convert(varchar(20),GETDATE())
    END
FROM Parts p
CROSS APPLY (
    SELECT ConcatString
    FROM ConcatenateVinsForPart(p.PartNumber)
    ) VinsForPart       


--Update the part table
UPDATE P
SET Notes = CASE 
        --Checking for note that's never had COMPATIBLE WITH string
        WHEN 0 = PATINDEX('%COMPATIBLE WITH%', NOTES) 
            THEN notes + 'COMPATIBLE WITH ' + VinsForPart.ConcatString 
            + '-Last Compatibility Report: ' + convert(varchar(20),GETDATE())
        --add 15 to get past COMPATIBLE WITH string
        ELSE substring(notes, 1, PATINDEX('%COMPATIBLE WITH%', NOTES) + 15) 
        + ' '
        + VinsForPart.ConcatString
        + '-Last Compatibility Report: ' + convert(varchar(20),GETDATE())
        END
FROM Parts p
CROSS APPLY (
    SELECT ConcatString
    FROM ConcatenateVinsForPart(p.PartNumber)
    ) VinsForPart       

--Check to see if parts was updated correctly
select * from Parts

Best Answer

Related Solutions

Mysql – Distinct Combination of Two Columns

Sql-server – Need to Add Update to Query

Related Question