advice on best design...

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » advice on best design...

advice on best design...

Rate Topic

Display Mode

Topic Options

Author

Message

sdhanpaul

Posted Thursday, December 20, 2012 8:21 AM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, May 11, 2013 7:02 AM
Points: 43, Visits: 562

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.

Post #1399001

Sean Lange

Posted Thursday, December 20, 2012 8:38 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 8,620, Visits: 8,261

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1399014

MyDoggieJessie

Posted Thursday, December 20, 2012 10:13 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:19 PM
Points: 2,037, Visits: 3,761

Based off what you've offered for information it sounds like something you could do in SSIS?

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" Wink

Post #1399046

sdhanpaul

Posted Thursday, December 20, 2012 5:08 PM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, May 11, 2013 7:02 AM
Points: 43, Visits: 562

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!

Post #1399136

sdhanpaul

Posted Friday, January 04, 2013 4:33 PM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, May 11, 2013 7:02 AM
Points: 43, Visits: 562

can i just delete this topic? i mean no one responding to u...and i would rather it deleted.

thanks.

Post #1403157

« Prev Topic | Next Topic »

Permissions