Employee Data - Show Changes From And To In History Table

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


ADVERTISEMENT

T-SQL (SS2K8) :: Show Changes From And To In History Table

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

Can I Show History In Reports Via Previous Snapshots Of The Same Report?

May 2, 2008

I have a report that runs monthly and they want to see the previous 2 months of data. This data is stored no place other than the snapshot of the same report for the previous 2 months. Is there any way I can display the last 2 snapshots/history withing my report? Thanks.

View 1 Replies View Related

T-SQL (SS2K8) :: Get Data Changes From History Table?

Mar 25, 2015

how to get products on which SalesPerson changed. Here is table with data.

View 9 Replies View Related

Transact SQL :: Table Row Data Show In Column Data

Oct 22, 2015

I have a simple table data i want want to show row data in to column data.
      
SELECT clblcode,mlblmsg
FROM warninglabels

My expected result will be 

0001                 0002  0003                      0004
-------                ------- --------                    --------
May Cause....       Important.....  Take madi...........         Do Not Take.......

View 16 Replies View Related

Insert / Update In Master Table And Also Save A History Of Changed Records : Using Data Flow/simple Sql Queries

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

Transact SQL :: Joining A Calendar Table And Employee Table?

Apr 20, 2015

I run into a problem when asking to show a query of employee vacation days.

table 1:
column1  is dates
e.g.
2015-01-01
2015-01-02
2015-01-03 
.
.
.
2015-12-31

table2:
employeeID
vacation_date
Tom
2015-01-03
Tom
2015-01-04
David
2015-01-04
John
2015-01-08
Mary
2015-01-012

My query output need to be:

2015-01-01
2015-01-02
2015-1-03
Tom
2015-01-04
Tom
2015-01-04
David
2015-01-05
2015-01-06
2015-01-07
2015-01-08
John
2015-01-09
2015-01-10
2015-01-11
2015-11-12
Mary

... etc... all the way to 2015-12-31

when i use left outer join, i only record one employee per date.

View 4 Replies View Related

Can Not Show Table Data From The SQL Server Comact Edition

Apr 28, 2008

I'm getting the error message "The parameter is incorrect" when I'm trying to perform "Show Table Data" from the SQL Server Compact Edition" within VS 2008. Any ideas? Thank you in advance,
.

View 6 Replies View Related

Linked Table From Server To Access Doesn't Show Data?

Oct 25, 2013

I linked an SQL server table into Access, but when I open the table in Access, no data shows. Do I need to set some permissions on the sql table?

View 1 Replies View Related

How To Get Top Three Salary Getters From Table Employee

Mar 1, 2007

Dear All,i want to know how to get top three salary getters from the employee(eid , ename, salary) table
i tried this select  top 3  salary from employee order by salary desc       
but it gives me top three salary record say there is salary 1000,1200,1300,1300,1500then my query return me 1500,1300,1200 whereas i want to 1500,1300,1300,1200
how can i do it
please help
thanks 
  

View 1 Replies View Related

Using Employee/Boss Self Referencing Table

Feb 28, 2008

I have an Employee table that has
EmployeeID (PK)
SupervisorID (which is really just another EmployeeID)
..random junk...


Now that part makes sense, everyone gets one and only one boss.

Their boss can change, and therefore the SupervisorID would be updated.

Now I have an EmployeeEvals table that has quarterly evaluation data.

I want to relate these two tables.

Eval table has
EvalID (PK)
ReviewedEmployeeID (the one being evaluated)
SupervisorID (the one doing the evaluation)

Now I need to link this back to the employee table (at least I think I do).

So I would want to relate it by the ReviewedEmployeeID going back to EmployeeID in the employee table and I also want the SupervisorID to do the same...

But of course that won't work because that would seem to indicate that a single record on the Employees table (say EmployeeID 55) should have a matching (or could) record in the Eval table that would look like
EvalID: 12345
ReviewedEmployeeID: 55
SupervisorID: 55

which of course wouldn't happen as an employee wouldn't evaluate themself.

How do I handle the relationships for this properly?

Do I just not link the SupervisorID back to anything?

View 2 Replies View Related

Updating Datetime Data Types In A Table To Show Just Day, Month, Year

Apr 2, 2008

I have a table with a datetime field 'TheDate'. Currently dates are stored as 'mm-dd-yyyy 00:00:00'. Is there a way to get just the month, day and year parts, '01/01/2008' into the field without changing the field data type to varchar? I'm asking because when I do this:


declare @MyDate as datetime

set @MyDate = '04/02/2008 18:00:00'

select substring(convert(varchar,@MyDate,101),1,10)

I get '04/02/2008', but when I do this:

update TheTable
set TheDate = substring(convert(varchar,TheDate,101),1,10)

I'm still getting a date in the format 'mm-dd-yyyy 00:00:00' stored in the table. I'd like to be able to lose the time portion, but I'd like to be able to keep the datetime datatype for date math purposes. Can it be done?

View 4 Replies View Related

Deciphering Employee Shift Data

Feb 11, 2008

Here is my current challenge that I realize I could do a few different ways but nothing efficient or flexible comes to mind. Hoping one of you guys can help me out with an elequent efficient T-SQL script.

Employee workstate information in our system is stored by segment. Segment 1 cooresponds to the entire shift while segments greater than 1 coorespond to subsequent breaks during the shift (Segment 1). i.e.

SegmentNumber SegmentStart SegmentEnd

1 10:00am 06:00pm
2 12:00pm 13:00pm
3 03:15pm 04:00pm


What I would like to do is generate continuous nonoverlapping records. i.e.

SegmentNumber SegmentStart SegmentEnd

1 10:00am 12:00pm
2 12:00pm 13:00pm
3 13:00pm 03:15pm
4 03:15pm 04:00pm
5 04:00pm 06:00pm


*Notice End has changed to match start of first break and so on. Also records have been added to fill gaps between breaks during the shift.

What adds to the challenge is that the segment number for a given employee/report day can be 1 (meaning no breaks) to any number (lots of breaks). The segment start and end times can be any increment. In addition Breaks can be divided into paid time and unpaid time but I imagine given a solution to the above I could apply it to another level down on my own.

Thank you in advance for your help!

View 3 Replies View Related

Transact SQL :: Select Last Inserted Value Row ID In Employee Table?

Nov 2, 2015

<g class="gr_ gr_54 gr-alert gr_gramm Grammar multiReplace" data-gr-id="54" id="54">I want</g> to get row number of last inserted into employee table?

How can i get it @@identity function returns null.

is there any way to do it?

View 9 Replies View Related

How To Get Details Of Employee Excluding Junc Data

Mar 11, 2015

I have table like :

emp

eno ename deptnno sal
1 a 10 50000
2 b 20 100000
3 c 20 150000
4 d 30 200000
5 e 30 2500000

Here how can I get all employee details excluding junc datas?

View 1 Replies View Related

Query To Find Manager Name From Employee Table Without Joins

Oct 25, 2012

Table structure is very simple as below and I know there are solutions with joins (Left outer joins), need to know if it is possible to get o/p without using joins

Note:- also need records who doesn't have manager (null)

table structure
eid------ename------mgrid
1------Nancy------2
2------Andrew------null
3------Janet ------2
4------Margaret------2
5------Steven------4
6------Michael ------5

o/p
Employee------Manager
Nancy------Andrew
Andrew------Null
Janet ------Andrew
.
.

View 9 Replies View Related

I'm Trying To Get The Last Activity Date From W/in A Table Where There Are Multiple Rows Per Employee

Apr 22, 2008

Hi, I have been struggling trying to design a query that will alow be to select the most recent date in a table
and I'm obviously not having much luck

This is basically the table layout, note each employee can have multiple rows with different dates






Employee_ID

Last_Name

First_Name

Evaluation_Date

Evaluation_Score


1

Jones

Tom

01/04/07

40


1

Jones

Tom

01/.12/07

50


1

Jones

Tom

04/01/08

60


2

Smith

Ed

02/14/05

70


2

Smith

Ed

03/18/06

80


3

Brown

John

06/23/04

80


3

Brown

John

12/23/04

79


3

Brown

John

01/07/06

50


3

Brown

John

10/22/08

69


What I'd like to do would be to write some thing that would return the following, just the last date of the evaluation & whatever relevant data is in the table






Employee_ID

Last_Name

First_Name

Evaluation_Date

Evaluation_Score


1

Jones

Tom

04/01/08

60


2

Smith

Ed

03/18/06

80


3

Brown

John

10/22/08

69


I've looked at select distinct and the date operatives with out any success.

Thanks Much
Vince

View 3 Replies View Related

Transact SQL :: Show (0) Amount For A Month If No Data Exists In The Table For That Month?

Nov 9, 2015

I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.

I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount

My Desired output

My current query

SELECT
RG.Id AS Id,
RG.Name AS Name,
ISNULL(SUM(AC.Amount), 0) AS Amount,
RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]

[Code] ....

View 6 Replies View Related

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 View Related

Common Need: History Table

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

History Table Design Issue

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

How To Design The History Table To Be More Efficient?

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

SQL 2012 :: Query For History Table?

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

History Table &> X Days Between Progression

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

New Unique Number In History Table

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

Referencing A Users Table For History

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

Nothing Written In View History Table

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

DB Engine :: Table Transaction History

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

Data Replication History Detail

Oct 3, 2007

We are using a SQL Server 2000 Replication.
I'm using the Merge Agent History Screen to retrive Informacion about
replication sessions, is there any other screen to know exaclty which datawas replicated on each session?Or at least to know the script generated on each session?

Thanks

View 5 Replies View Related

SQL Server 2012 :: Password History Table

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

SQL 2012 :: Select Statement From History Table?

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

T-SQL (SS2K8) :: Get Recent Changed Value From History Table

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

Complex Stored Procedure On History Table

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







Copyrights 2005-15 www.BigResource.com, All rights reserved