Home > database >  How to find a missing element in a sql table
How to find a missing element in a sql table

Time:11-29

I have a SQL database with some data that are added daily by a third person, each person has to add 10 lines of data to one of my tables trough a form , but sometimes someone forget to add one of those lines, each line represent a different result, what i wanna do is display what line wasnt inserted in a specific day and shift, here is an example of the table

data_id user date shift machine
3227 100 28/11/2021 1 TG01
3228 103 28/11/2021 1 TG02
3229 103 28/11/2021 1 TG03
3230 100 28/11/2021 1 TG04
3231 105 28/11/2021 1 TG05
3232 100 28/11/2021 1 TG06
3233 107 28/11/2021 1 TG07
3234 100 28/11/2021 1 TG08
3235 108 28/11/2021 1 TG09
3236 100 28/11/2021 1 TG010
3237 101 28/11/2021 2 TG01
3238 101 28/11/2021 2 TG04
3239 101 28/11/2021 2 TG05
3240 109 28/11/2021 2 TG06
3241 106 28/11/2021 2 TG07
3242 101 28/11/2021 2 TG08

i must have TG01, TG02, TG03 ....TG10 for every shift, how can i display in a case like in shift 2 which ones are missing? i would like to use an sql query to display it in powerbi to not have to manually check it every day

CodePudding user response:

What I would do for this is create a simple heap table with one column going from TG01 to TG10 and do a LEFT JOIN with it checking NULL's.

The table would look like this:

CREATE TABLE HeapTableWithMachines
(
    [machine] CHAR(4)
)

Then do the 10 inserts for the machines.

INSERT INTO HeapTableWithMachines VALUES ('TG01')
INSERT INTO HeapTableWithMachines VALUES ('TG02')
INSERT INTO HeapTableWithMachines VALUES ('TG03')
INSERT INTO HeapTableWithMachines VALUES ('TG04')
INSERT INTO HeapTableWithMachines VALUES ('TG05')
INSERT INTO HeapTableWithMachines VALUES ('TG06')
INSERT INTO HeapTableWithMachines VALUES ('TG07')
INSERT INTO HeapTableWithMachines VALUES ('TG08')
INSERT INTO HeapTableWithMachines VALUES ('TG09')
INSERT INTO HeapTableWithMachines VALUES ('TG10')

Then use a subselect to check a shift already inserted but missing some machines by doing a cartesian product aka CROSS JOIN and after that check against the whole table.

 SELECT 
           S.[user]
          ,S.[date]
          ,S.[shift]
          ,S.[machine]
      FROM (SELECT M.[machine], T0.[user], T0.[date], T0.[shift] 
                FROM [HeapTableWithMachines] M,  [TableWithLinesInsertedByThirdPerson] T0 GROUP BY M.[machine], T0.[user], T0.[date], T0.[shift]) S
      LEFT JOIN [TableWithLinesInsertedByThirdPerson] T1 ON S.[date] = T1.[date] AND S.[machine] = T1.[machine] AND S.[shift] = T1.[shift] AND S.[user] = T1.[user] WHERE T1.[user] IS NULL

PS: I agree with Yitzhak Khabinsky that there should more information to work with your problem.

CodePudding user response:

One would assume that your database is properly normalized, therefore you would have a Users table, as well as a Shift table. You would also have a Machine table containing the machines TG01-TG10.

You also need a calendar table called Dates, containing a row for each day. You can generate this on the fly, but it's easier with a proper table

You then cross-join them all, and remove rows where there is a match from your table

SELECT *
FROM Users u
CROSS JOIN Shift s
CROSS JOIN Machine m
JOIN Dates d ON d.Date BETWEEN '20211101' AND CAST(GETDATE() AS date)
WHERE NOT EXISTS (SELECT 1
    FROM YourTable sd
    WHERE sd.[User] = u.Id
      AND sd.Shift = s.Id
      AND sd.Machine = m.Id);
  • Related