Instances And History In One Table
Jan 11, 2008
I have a database design question. There're lots of ways to rome they say, and I want to hear what you think of this way.
Government supplies wheel chairs (and thinks alike) to people who need them. They stay in possesion of the (local) government and are distrubuted by a company X.
So we have Tools (Wheelchairs) and Users (of wheel chairs). The life-cycle of a wheel chair is that more than one user while use it over time.
I want to keep track of which users used a instance of a wheelchair.
No there's a developer who likes to put this in one table. (the chair and it's user) in a way like this
UID, WheelChairId, UserId, OwnerId, SerialNumber, BeginDateTime, EndDateTime, SomeOtherColumns
The UID is unique, the WheelChairId is a GUID which is Unique per wheelchair, but can have mutliple records in the table with no overlap.
If one of the values of the columns is changed a new record is made with the same wheelchair and a new begin date (the closed record gets an Enddate). So history is made automaticaly. By using the right query's I can see what users uses the chair in what period of time. But also changed ownerships and other changes in Someother columns overtime.
Is this a good or a common practice? Why use it, or stay away from it?
Henri
~~~~
There's no place like 127.0.0.1
View 1 Replies
ADVERTISEMENT
Sep 28, 2007
When I am in Visual Studio 2005, and I try to add an SQL database, I get the following error "generating user instances in sql server is disabled. use sp_configure user instances enabled to generate user instances." I am currently using SQL server 2005 Express. What do I need to do, to create an SQL database? Thanks in advance.
View 4 Replies
View Related
Jan 22, 2008
Hi there!
I'm working on an application designed like this:
There's a table "DailyTransations" (DT) containing daily transactions...
Then there's an archive table "TransationsArchive" (TA) with the exact same structure.
When a record is inserted in DT, it is also in TA (via a trigger) and the reporting is done against TA.
Now for performance issues, we delete from DT the records older than 2 days since they are not needed for processing.
First, what do you think of that implementation?
We thought about using partitions based on the transaction date and completely eliminate TA, but it seems once a record is assigned to a partition, it is not moved automatically...
What is the common solution for this need?
Thanks
Frantz
View 4 Replies
View Related
Nov 15, 2007
Hi all,
this is more of a design issue for a History table.
Suppose if i have a transaction table and then based on the transactions i want to keep a history of those do i need to define Primary Key and Foreign Key for history table.
Regards,
General Problem
View 1 Replies
View Related
Feb 8, 2007
I am running a website of crossword puzzle and Sudoku games. The website is designed to be:
There are 20-30 games onlines each day.
Every registered user could play and submit the game to win scores.
For each game, every registered user could get the score for ONLY one time. i.e., No score will be calculated if the user had finished the game before.
To avoid wasting time on a game finished before, user will be notified with hint message in the page when enter a already finished game.
The current solution is:
3 tables are designed for the functions mentioned above.
Table A: UserTable --storing usering information, userid
Table B: GameList --storing all the game information.
Related fields:
GameID primary key
FinshiedTimes recording how many times the game has been finished
Table C: FinishHistory --storing who and when finished the game
Related fields:
GameID ID of the game
UserID ID of the user
FinishedDate the time when the game was finshied
PS: Fields listed above are only related ones, not the complete structure.
Each time when user enters the game, the program will read Table B(GameList), listing all the available game and the times games have been finished. User could then choose a desired game to play.
When user clicks the link and enter a page showing the detail content of the game, the program will read Table C(FinishHistory) to check whether user has finished this game before. If yes, hint message will be shown in the page.
When user finishes the game and submit, the program will again read Table C(FinishHistory) to check whether user has finished this game before. If yes, hint message will be shown in the page. If no, user will get the score.
Existing Problems:
With the increase of game and users, the capacity of Table C(FinishHistory) grows rapidly. And each time when a game is loaded, the Table C will be loaded to check, and when a game is submitted, the Table C will be loaded to check again. So it is only a time question to find out Table C to become a bottleneck.
Does any one here have any good suggestions to change / re-invent a new structure or design to avoid this bottleneck?
View 5 Replies
View Related
Jun 28, 2014
I have a table history of Employee data.
id | EmpNo | EmpName | MobileNo | Email | EmpSSS | UpdateDate | UpdateUser
I have to make a stored procedure that will show the history and changes made to a given EmpNo, with the UpdateDate, UpdateUser and indicate which field is modified. Ex. Employee Mobile number was changed from '134151235' to '23523657'.
Result must be:
EmpNo | UpdateDate | UpdateUser | Field changed | Change from | change to
View 4 Replies
View Related
Mar 25, 2015
how to get products on which SalesPerson changed. Here is table with data.
View 9 Replies
View Related
Nov 3, 2015
I have order history table that tracks who worked on a order
ID. ColA. ColB. ColC
1. Process 1234
2. Work. 7666
3. Return. 6789
4. Work. Null Role1
5. Return. 6538
I want a query to return recent colB or ColC where colA or colB are not null. In this case row 4
I have order history table that tracks who worked on a order
Another example
ID. ColA. ColB. ColC
1. Work. 1234
2. Process. 7666
3. Return. 6789
I want a query to return most recent colB or ColC where colB or ColC are not null. In this case row 2
View 1 Replies
View Related
May 21, 2008
I work for a college and have recently been working on our enquiries and applications process (getting it onto our big enrollment db rather than standalone). It has all been going well but now they have asked for a report of students where it has taken more than x days or weeks to progress to the next stage code.
For stage codes they basically follow something like application, guidance interview, programme area interview, conditional/ unconditional offer... Although they could skip a stage code.
Any ideas how to do this bearing in mind I can't guarantee them to go to every stage so really I need to look in the history table and find records more than x days apart where one is the next progression date of the other. Hope I explained that ok.
history table is similar to:
StudentID CourseCode StageDate InputDate Stage
View 1 Replies
View Related
May 23, 2008
Hello,
I am using SQL Server 2005 and am having trouble with making a history table like mentioned in my earlier thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102811
This is the table "People" I have created:
|PersonId (PK)|DateFrom (PK)|DateTo|PersonName|Other Attributes....
Each change to a person's attributes results in a new row formed with the same PersonId as in the row with old attributes and the Date these new attributes are valid (DateFrom). So as shown above the Primary Key is a combination of the PersonId and DateFrom as a change to a person's attributes should never happen at the same time twice.
My problem is when I want to create a new person, how do I get a new unique id? Ideally I want the a new incremented id, so that all peoples' ids are in a sequential order.
As always, thanks for the help!
View 4 Replies
View Related
May 6, 2008
I've recently finished an application for a small company with perhaps two hundred employees. Each employee was set up in a Users table in the database, against which application logins were processed.
For just about every other table in the database, other than pure lookup tables, we created columns to indicate the user who created the entry, and the user who last modified the entry. This was done using FK references back to the Users table. Each table contains two references back to the Users table, and there are over 150 tables now that follow this scheme. At first I was not concerned, other than the fact that it makes a visual picture of the data model look very confusing (almost every table has a pair of links back to the Users table), until I encountered an issue where I could no longer delete from the Users table. Upon surpassing 253 FK references to Users, I can no longer delete users, as the Query Optimizer can't complete the query.
Now, all of that so far is really not a big deal. Deleting users was never my intent anyway. The only real question I have is whether this is the standard way of maintaining history for table records. Have others used this method? Is there a better way?
View 1 Replies
View Related
Mar 25, 2008
Hi guys,
acutally i have setup a Disaster Recovery plan for my database.. i m taking a full back once in a week,. i dont' know when i right click on the job and trying to check the view history option to check when was last backup has been taken, it's showing nothing..but when i check on acutall location the backup was taken there.. i don't know y it's not writing any info in view history table.. or is it clear once in a week and i cann't see that...
Can any one tell's me about this...
View 3 Replies
View Related
Oct 22, 2015
i have a table and i would like to know what are the queries that were executed referring to that table in the past 5 days. is it possible?
View 4 Replies
View Related
May 7, 2014
I have a table of users including: UserName, Password (comuted col), FirstName, LastName, Address and other details....
I have to keep 10 Recent passwords , so I created another table "ut_Password " (Table2)
This table contains the following columns : Username, Password , and Password_Date.
I searched a lot but could not find something similar in my opinion need SP for it.
- 10 row Max for Password History in table 2
- when user change password it's need to be uniqe and it should not appear last 10 passwords
- Each user can have a maximum of 10 lines containing history password table
- Most old password deleted and replaced with a new password will enter the correct date (FIFO method first in first out).
View 9 Replies
View Related
Jan 26, 2015
Project has 2 tables process(parent) and processchild(child).
Project workflow recorddsany changes to these tables as a history.
I want to find out all the process that are in status = saved(1) where processchild is at status = started(1).
Here is example.
Process table
PK, processid, status , other data
1, 1, 1,...
2, 1, 2,...
3, 2, 1,...
4, 3, 1,...
ProcessChild table
PK, processid, processchildid status, other data
1, 1, 1, 1,..
2, 1, 1, 2,..
3, 1, 2, 1,...
4, 1, 2, 2,...
5, 2, 1, 1,..
6, 2, 1, 2,...
7, 2, 2, 1,...
8, 3, 1, 1,..
I want to find out all the processes where processchildid=2 and processchild.status =1
View 3 Replies
View Related
Apr 6, 2015
I have a history table with the following values
CREATE TABLE History (SnapShotDate DATETIME, UID VARCHAR(10), DUEDATE DATETIME)
INSERT INTO History VALUES ('03-23-2015','PT-01','2015-04-22')
INSERT INTO History VALUES ('03-30-2015','PT-01','2015-04-20')
INSERT INTO History VALUES ('04-06-2015','PT-01','2015-06-30')
[Code] ....
I need an output in the below format. I need the most recent changed value for any given UID. Need to get the below result
OUTPUT
UID PreviousDueDate CurrentDueDate
----------------------------------------
PT-01 2015-04-20 2015-06-30
PT-02 2015-04-22 2015-04-22
PT-03 2015-04-18 2015-04-22
PT-042015-04-222015-04-18
View 4 Replies
View Related
Jun 27, 2014
I have a table history of Employee data.
id | EmpNo | EmpName | MobileNo | Email | EmpSSS | UpdateDate | UpdateUser
I have to make a stored procedure that will show the history and changes made to a given EmpNo, with the UpdateDate, UpdateUser and indicate which field is modified. Ex. Employee Mobile number was changed from '134151235' to '23523657'.
Result must be:
EmpNo | UpdateDate | UpdateUser | Field changed | Change from | change to
View 4 Replies
View Related
Jun 7, 2006
Hi All,
I have a table that hold status history records for cases. In this table is a status field with values, opened, assigned, or complete. Each case can be assigned a number of times before it is complete, and can be reassigned. I have the need to run a query that will get each case that is still assigned, and not yet complete. I wrote a stored procedure that contains a cursor containing each case, and get the last status history record for each case and puts it into a temp table to return to the user, but is hurting performance as there are .5 million records here. Does anyone know of a better way of doing this?
Thanks in advance : )
View 1 Replies
View Related
Feb 25, 2005
Hi everyone,
I have a big table which contains approx. 31,524,044 rows. The structure of the table look like this:
date ID A B C D
1/1/65 X Null Null Null Null
1/4/65 X 1 2 3 4
...
2/25/05 X 2 3 4 5
1/1/65 Y Null Null Null Null
1/4/65 Y Null Null Null Null
...
2/25/05 Y 2 3 4 5
...
The number of distinct(ID) is approx 3200 and each one has daily historical A, B, C, and D back for 40 years. For going forward I need to update daily information for 3200 ids. Currently, I am runing query against to this table is ok. I am thinking by the time pass by the table will be hudge since "stored historical information". It probably takes "long long long" time to run the query against this table. Any suggestion or comments... what is the best/better solution? Or it is not problem at all?
Thank you everyone for the help.
shiparsons
View 8 Replies
View Related
Mar 5, 2008
Given the following data how do I make a SQL query that returns only 1 row per product?
The returned rows need consist of only currently active products (that is WHERE (DateEffective <= { fn NOW() }).
The twist: Sometimes a product will have duplicate DateEffective records. In that case, only return the record created latest because that's the most current data that exists for a product. RowTimeStap is when the record was created.
Example Data:
HistoryID ProductID Name Color DateEffective RowTimeStamp
(auto-number PK)
1 1 Wheel Red 2/1/2008 2/1/2008
2 1 Wheel Blue 3/5/2008 3/1/2008
3 1 Wheel Orange 3/5/2008 3/2/2008
4 1 Wheel Black 1/1/2010 3/3/2008
5 2 Knob Blue 3/2/2008 3/2/2008
6 2 Knob Green 3/3/2008 3/3/2008
Query should return:
3 1 Wheel Orange 3/5/2008 3/2/2008
5 2 Knob Green 3/3/2008 3/3/2008
The query I've created fails on the twist part. I have to allow duplicate DateEffective to keep a history of changes.
Can anyone help?
View 15 Replies
View Related
Nov 19, 2007
Let's say you have a Users table, which simply contains a collection of people. Each person has attributes like their password, first and last name, username, etc. It would be useful to track changes to that table over time, so that if an entry is changed, you can simply look at a history table and see which changes occured, and when.
An INSERT/UPDATE trigger works well for this sort of thing, where the trigger inserts all of the 'INSERTED' values into a history table that has basically the same table definition as the Users table. But it gets tricky when considering Deletes.
If my History table entries reference back to the User in the Users table, this means that if I ever want to delete the user, I need to delete all their History first. This means I can't keep records of user deletions, which is significant. The other approach is not to have a foreign key reference in the History table, so that if a user is deleted, I can still keep my History about that user. Including deletes.
I have been timid about doing it this way, since I felt it broke the idea of a well structured database. Is there a better way to approach this? Is there a standard way to set up triggered history to track changes, including deletions, from a table?
Thanks,
-Dan
View 1 Replies
View Related
Jul 18, 2001
I had a server with SQL Server 7.0
I installed a named instance of SQL Server 2000 and then i passed all my DB
of the 7.0 instance to the 2000 instance.
Then i removed the 7.0 instance, that was the default instance.
So at the moment there is only the 2000 version, but it isn't the default
instance
Can the 2000 instance become the default instance? (So that clients can
connect to it simply through computer name, and not creating an alias)
thanks
Fede
View 1 Replies
View Related
Nov 10, 2005
For each blog entry viewed in my web app, a (IPNumber, Date, BlogID) row is inserted into table "BlogViews"
How can I fetch the BlogID which has been most often visited, that is has the most number of rows?
View 1 Replies
View Related
Jun 30, 2015
How can I easily identify who dropped a table?
View 8 Replies
View Related
Nov 5, 2007
Hi
Please help! I'm really stuck. I have a table (AssessorAllocations) that stores the data of students who have submitted assessments for marking by an Assessor. Each time an assessment is submitted by a student my program must determine which Assessor has the smallest workload and then assign the current assessment to that Assessor. My problem is how do I determine which Assessor has the smallest number of assessments assigned to him/her.
The table columns are:
AssessorRegistrationNo
LearnerRegistrationNo
QualficationName
SubjectName
AssessmentName
DateAssigned
Marked
DateMarked
Price
So I'm looking for something like this:
Select AssessorRegistrationNo from AssessorAllocations where {Select the Assessor who has the smallest number of AssessmentNames assigned to him/her where Marked='0' and DateAssigned<>''}
Thanks
Neil
View 4 Replies
View Related
Oct 21, 2015
I have to count the number of instances of each word in the Comments column throughout the table using SQL Server. Eg:
Row# Comments
1 I like working on SQL
2 I enjoy sleeping and like watching TV
So the output should be :
Word Count
I 2
like 2
working 1
on 1
SQL 1
.......
Any way to get this count using MS SQL?
View 5 Replies
View Related
Feb 9, 2007
Hi,
My scenario:
I have a master securities table which has 7 fields. As a part of the daily process I am uploading flat files into database tables. The flat files contains the master(static) security data as well as the analytics(transaction) data. I need to
1) separate the master (static) data from the flat files,
2) check whether that data is present in the master table, if not then insert that data into the master table
3) If data present then move that existing record to an history table and then update the main master table.
All the 7 fields need to be checked to uniquely identify a single record in the master table.
How can this be done? Whether we can us a combination of data flow items or write a sql procedure to do all this.
Thanks in advance for your help.
Regards,
$wapnil
View 4 Replies
View Related
Jun 10, 2015
I have a problem where I have 2 compare 2 records from the same table. This part looks easy but the problem is for a User there can be multiple records and I have 2 compare each record with its previous instance based on the timestamp. Not only I have to compare I have to perform some analysis. Below is the Table script and sample output.
Givens: All SQL Server 2008 or 2012 tools at your disposal.
Production database contains the following tables (simplified for example: constraints ignored, etc.) associated with a racing video game’s server.
-- A player of our game
-- Table greater than 10 million rows
CREATE
TABLE [dbo].[User]
(
[UserId]
[bigint] NOT
NULL
,[country]
[int] NULL
-- User’s home country
,[name]
[nvarchar](15)
NULL -- User’s displayable name (‘John’, ‘Bill’)
,[subscriptionTier]
[int] NULL
)
-- 0 == free, 1 == paid, for instance
Assume that rows get written into the event tables at a rate of 1,000 a minute,are never updated once written and currently are only read on a replica/reporting server.
Question Background: Write up a single query that would return the following: List of users and whose “TotalMoneyEarned” value ever grew (between logon events) at a rate of more than 1,000 per minute (we’d consider these suspicious and flag them for later investigation).
For instance, if the sample data were:
-- example of [Events.UserLogon] data -- not the query output we want
EventId UserId TotalMoneyEarned LogonDate
----------- -------------------- ---------------- -----------------------
1 1 1000 2010-10-16 00:19:56.460
2 1 1500 2010-10-16 00:20:56.460
3 1 3000 2010-10-16 00:21:56.460
4 1 10000 2010-10-16 00:29:56.460
Event 1 is okay because there’s nothing to compare it against
Event 2 is okay because the TotalMoneyEarned only grew 500 in a minute
Event 3 should be flagged, as the value grew 1500 in a minute
Event 4 is okay, as it grew 7,000 in 8 minutes (< 1000 per minute)
Query Output (your query should return data in a format like this):
User Flagged Logon Time Rate Since Last Logon (money/minute)
John 2010-10-16 00:21:56 1500
Dave 2010-10-16 00:30:50 3200
Bill 2010-10-16 00:35:23 1000
It is likely that you will need to create sample data for both the User and [Events.Logon] tables. We are looking for a single query that returns data like what is represented in Query Output.
View 3 Replies
View Related
Jul 24, 2001
The 'view job history' on Enterprise Manager is showing me only 4 or 5 run histories. I want to see more history on each of the jobs.Even I changed the limit to 10000 lones from 1000 lines on the server properties in the job system option, I am still seeing only the last 4 or 5 histories for each job(there are a total of 70 jobs on the server).Any ideas??Thanks.
Reddy.
View 1 Replies
View Related
May 15, 2000
Anyone tell me why some of my SQL-agent jobs have no history?
Some jobs are reporting history fine, others say "No history"
View 4 Replies
View Related
Jul 6, 2000
I have set up a job as sa.After that I changed the sa password.Now I am not able to view the job history since then.Do i need to delete the job and reschedule it?
View 1 Replies
View Related
Aug 18, 2004
I have a question about the sql job history. I have a sql 2000 server that was recording all the job history on my sql jobs just fine until the other day. Today I checked the history and the jobs didnt show the past history anymore. Just today's history. So if the job didnt run today there is no history for that particular job. This isn't true, the job was run a few days ago. I know that no one went in and cleard the history on the jobs. What causes this issue, is there a way to not make this happen in the future? Is there a way I can recover the past history for my jobs.
Thanks for your time!! TIA
View 11 Replies
View Related
May 16, 2012
i have my job running every 5 min. I want to view the history log. In job history it shows only few hundreds of records.. But i want to see from beginning. where to view whole log of job history?
View 8 Replies
View Related
