Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Stumbler
Starting Member
23 Posts |
 Posted - 2007-05-11 : 09:20:20
|
I am a newbie to SQL and I neecd to solve this:We have a clocking system that just registers when people pass a badgecontroller. the table contains data like this:Date ID Direction Timestamp20070228 50102 IN 28/02/2007 7:44:0020070228 50102 OUT 28/02/2007 16:37:0020070301 50102 IN 1/03/2007 7:45:0020070301 50102 OUT 1/03/2007 16:37:00I need a table that has the following columnsIDStartdateTimespentSome caveats:Sometime you get more than one 'IN' before you get an 'OUT' (poor badging) Sometime the date in the OUT is different from the date IN. People starting to work at 22:00 PM till 06:00 AM.Is there someone who can get me on track to solve this.May gratitude will be eternal  Hans |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-05-11 : 09:36:24
|
| Just look for the first out after an in.Then you can find those which have the same in - i.e. out missed, and those in's with the same out and those with duration that isn't reasonable.select ID, InTimestamp = tin.Timestamp, OutTimestamp = tout.Timestampinto #afrom (select ID, Timestamp from tbl where Direction = 'IN) tinleft join tbl touton tout.Direction = 'OUT'and tout.Timestamp = (select min(Timestamp) from tbl t2 where t2.ID = tin.ID and t2.Timestamp > tin.Timestamp)and tou.ID = tin.ID==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
ACushen
Starting Member
29 Posts |
Posted - 2007-05-13 : 23:50:26
|
| In my understanding, it's frowned upon in database design to actually store a calculated value in a database table. Generally you would not make the calculation until Report time-- i.e., when someone runs a report to actually look at the data, that's when you calculate the time spent. I believe the idea is that you are wasting space in the database, and server resources, for a lot of values that may never be looked at, and can easily be calculated when needed.I wonder about a situation like this, though; might this be one of those times when it makes sense to break the "rule"?What does everyone think about this?-Andrew=================================================PS:nr- I seem to remember having no trouble purchasing nice cold beer in pubs and clubs in London in the late 80's...is England (and her ex-colonies) the only country that drinks it at room temperature? Maybe that explains why you also drive on the "wrong" side of the road over there... ;-) |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-05-14 : 00:07:14
|
| Usually when people say not to store a calculated value, they mean calculated results from columns in the same row or from a simple join. For example, in you have a row containing Price and Cost columns, storing GrossMargin would be a waste if all you had to do was calculate Price-Cost.In the case posted, the calculated result is from a complex join, and there may be updates or deletes needed to filter out the correct result from a complex stream of events. There may also be manual corrections required if, for example, someone forgets to clock in or clock out. In this case, storing the result will probably be required to ensure that processes that use this information have the same information.CODO ERGO SUM |
|
|
|
Stumbler
Starting Member
23 Posts |
Posted - 2007-05-14 : 08:47:50
|
| Michael,You hit the nail on top: The filled table needs to be reviewed by HRM to make sure that it contains the correct data.. Afterwards the corrected table is sent to a third party to pay subcontractors.Hans |
|
|
|
ACushen
Starting Member
29 Posts |
Posted - 2007-05-14 : 10:49:34
|
| And so it DOES make sense to break the rule here-- if the rule even applies!Thanks again, Michael.-Andrew |
|
|
|
|
|
|
|
|
Create table