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