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

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);