Check for matches

Question

I have two tables @Cust and @Bh. I need to take the last records from the @Bh table by the "loaddate" field and if there are no matches then insert the records.

@Cust:

DECLARE @Cust TABLE(
    Custnumber INT,
    Flag NVARCHAR(10),
    data NVARCHAR(10),
    status NVARCHAR(10),
    loaddate DATETIME
)

INSERT @Cust (Custnumber
                ,Flag
                ,data
                ,status
                ,loaddate)
VALUES (123,N'Y',N'20170117',N'Test','2018-11-15 15:35:26.393')

@Bh:

DECLARE @Bh TABLE(
    Custnumber INT,
    Flag NVARCHAR(10),
    data NVARCHAR(10),
    status NVARCHAR(10),
    loaddate DATETIME
)

INSERT @Bh (Custnumber
                ,Flag
                ,data
                ,status
                ,loaddate)
VALUES (123,N'Y',N'20170117',N'','2018-11-09 15:35:26.393')
,(123,N'Y',N'20170117',N'Tests','2018-11-10 15:35:26.393')
,(123,N'Y',N'20170117',N'','2018-11-15 15:35:26.393')
,(123,N'Y',N'20170117',N'Test','2018-11-15 15:35:26.393')

Result:

INSERT INTO @Bh(Custnumber
                ,Flag
                ,data
                ,status
                ,loaddate) 
SELECT DISTINCT PC.Custnumber
                ,PC.Flag
                ,PC.data
                ,PC.status
                ,PC.loaddate
FROM @Bh  AS BH
    INNER JOIN @Cust AS PC ON PC.Custnumber = BH.Custnumber
                                        AND (ISNULL(PC.Flag, '''')  <> ISNULL(BH.Flag, '''')
                                        OR   ISNULL(PC.data, '''')  <> ISNULL(BH.data, '''')
                                        OR   ISNULL(PC.status, '''')  <> ISNULL(BH.status, '''')) 
WHERE BH.loaddate = (SELECT MAX(loaddate) FROM @Bh AS BH2 WHERE BH.[Custnumber] = BH2.[Custnumber])';

Because I have not quite the right condition, then insert the record due to the fact that row number 3 is different from the entry in the table @Cust and as a result is added to the table @Bh

Here is a solution that I came up with and it fits my cases, but maybe there is some simpler solution?

WHERE   NOT EXISTS(SELECT 1 FROM @Bh AS BH2 WHERE PC.Custnumber = BH2.Custnumber AND PC.Flag = bh2.Flag GROUP BY BH2.Custnumber HAVING CONVERT(varchar(10), max(bh2.[loaddate]), 101) = (SELECT CONVERT(varchar(10), max([loaddate]), 101) FROM @Bh AS BH3 WHERE BH3.Custnumber = BH2.Custnumber))
        OR NOT EXISTS(SELECT 1 FROM @Bh AS BH2 WHERE PC.Custnumber = BH2.Custnumber AND PC.data = bh2.data GROUP BY BH2.Custnumber HAVING CONVERT(varchar(10), max(bh2.[loaddate]), 101) = (SELECT CONVERT(varchar(10), max([loaddate]), 101) FROM @Bh AS BH3 WHERE BH3.Custnumber = BH2.Custnumber))
        OR NOT EXISTS(SELECT 1 FROM @Bh AS BH2 WHERE PC.Custnumber = BH2.Custnumber AND PC.status = bh2.status GROUP BY BH2.Custnumber HAVING CONVERT(varchar(10), max(bh2.[loaddate]), 101) = (SELECT CONVERT(varchar(10), max([loaddate]), 101) FROM @Bh AS BH3 WHERE BH3.Custnumber = BH2.Custnumber))

could you use TOP 1 and ORDER BY in your EXISTS part, if you only want to find the last record? — Cato, Nov 28 '18 at 10:09
but I need to exclude a match from the two records, not the last one. — Tibomso, Nov 28 '18 at 10:29
I'm not sure this is a duplicate, but check this SO post: https://stackoverflow.com/questions/52780121/using-a-if-condition-in-an-insert-sql-server — Zohar Peled, Nov 28 '18 at 11:22

Check for matches

0 Answers0