December 20, 2012 at 8:21 am
All,
I have a database which contains a table for Raw data. Now the data in this table is mainly time. The other fields are linked to other tables...
The problem is, this raw data needs to be reorganized before it can be used. Then from reorganizing it goes to a different table where the data is matched and can further be used.
So, essentially, Raw data is captured by an input device and stored in the raw data table.
This data is moved to a second table where it is sorted.
It is moved to a third table where it is grouped and matched...three phase process if something changes in the raw data table then it automatically changes in the other two tables by use of queries, stored procedures ect.
Question: Is there a better way of doing this rather than having the data in all these different tables. I mean, its already the same data but I just cant seem to figure out how to do all in one go.
Maybe the information here is too little but if you need additional information then I would provide.
December 20, 2012 at 8:39 am
With the extremely limited amount of details nobody can do much here but speculate. It seems that having 3 or more tables all with the same basic data is overkill. You mentioned that you have a table just to sort it. I would be willing and able to help but you need to provide a LOT more detail first.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 20, 2012 at 10:13 am
Based off what you've offered for information it sounds like something you could do in SSIS?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 20, 2012 at 5:08 pm
Okay, thanks for the reply. The system is running well for the time. I am 90% satisfied with its performance.
This is a Time and Attendance / Payroll system. It collects the data from biometric devices (fingerprint devices). It has loads and loads of features that companies won't get from an off the shelf software and I customize it for each Client I sell to.
Okay. The time data moves from the device into a table...
Table 1
Record ID ! EmpPin ! TrxDateTime
Those are the basic fields. Now there are two ways I go about matching time in and out. for the purpose of this post I will use only one, that is: I split the date time field and add another field for TrxDate in a stored procedure. that same stored procedure I use to add the data to the second table:
Table 2
RecordID EmpPin TrxDate TrxTime RunningTotal
these are the basic fields in the second table. The concept is, the data from the first is sorted by EmpPin, then TrxDate, then TrxTime when they are moved from table 1 to table 2. Running total is reset by employee pin for each day. so punch 1 is in corresponding punch 2 is out, punch 3 is in and corresponding punch 4 is out. If the running total is an odd number then there is a missing punch which shows up in the missing punch report.
this data is matched by the punches (1, 3 ,5 ...) are transferred to TableIn and even numbers go to table out.
these are further transferred to final table where in punch is matched to out punch...so this final table has:
record id emppin trxdate trxtimeIn trxtimeOut
So I'm wondering if there is a way I can do all of this in one go without having all these tables...
Actually, while typing this I think I solved my problem.
Originally when I created the system it was slow so i never deleted the information in the tables between the first and the last. But now that I made it faster (all this runs in under 5 seconds on about 10,000 rows):
solution: delete the data in all the tables between the first and the last making them temp tables.
But if anyone else has an idea on how to make this better, I am willing to listen!
January 4, 2013 at 4:33 pm
can i just delete this topic? i mean no one responding to u...and i would rather it deleted.
thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply