Finding discrepancies in data with a query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mikeashfield
Forum Contributor
Posts: 159
Joined: Sat Oct 22, 2011 10:50 am

Finding discrepancies in data with a query

Post by mikeashfield »

Hello,

I'm not sure if I can explain this as clearly as I would like, but my database is basically a rota system, let's say for example, that I have a table named tbl_shifts and a table named tbl_weeks for each of the 7 day fields in tbl_weeks (week_day1, week_day2, ... ) every shift must be covered from day 2 to day 6.

I have now got all my data in the DB and now just want to validate it for errors in the data entry (it was all manual) so I would like to construct some SQL queries that find weeks in the weeks table that aren't valid. I would also like to run a query to identify shifts that have a seconds value in the start or finish time. The time fields are formatted as H:i:m.

I built the first query I need, but it returns thousands of results (which definitely isn't the case).

Could anybody help?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Finding discrepancies in data with a query

Post by califdon »

Names of tables and fields don't tell much about what you intend them to represent. From the sparse information you have furnished, it doesn't look to me like your schema design is correct. I suggest that you describe what it is that you are trying to do, then probably we could recommend something for you to try. What is it that you want the database to do?
mikeashfield
Forum Contributor
Posts: 159
Joined: Sat Oct 22, 2011 10:50 am

Re: Finding discrepancies in data with a query

Post by mikeashfield »

Hello there,

Sorry about being so vague, I've attached my schema design as produced by MySQL Wokbench reverse engineering tool.
I basically want to be able to identify any week numbers where on days 2-6 all of the duty numbers are not covered. Hope this makes more sense.


Mike
Attachments
Schema Design
Schema Design
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Finding discrepancies in data with a query

Post by califdon »

Your schema diagram confirms that your underlying relational model is unlikely to yield useful results for anything I can think of. Whenever I see something being forced into a structure like your tbl_weeks, it is a bright red warning flag.

The only way I know to help you is to have you tell me in words just what this database is going to be used for. It needs to be something generally along the lines of this: "The database will record the descriptions of many duties, as well as on what shifts these duties must be performed. It will then permit the user to assign individuals to perform these duties during the shifts and display wherever nobody is assigned to a duty during a particular shift. ..." Your explanation should be perhaps 5 or 10 times that length and of course it must accurately describe what the database is intended to do--I'm only guessing. Then we can talk about what entities must be represented and what attributes each entity must have. That's the only way you can design a database of this kind.
Post Reply