Data updation based on previous data

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Data updation based on previous data

Author

Topic

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-10 : 05:23:20

Hi guys,

Here's something that I need to do. Might be pretty simple for you guys. :)

I have a table of Employees. All the employees work in some departments. So, I have a table of Department too. Employee table consists of details like EmpID, FirstName, LastName, SAP etc.
Dept table consists of TeamID, TeamNo.
Now, I have another table called as Emp-Team. This table basically maps the employees to the department by taking EmpID and TeamID. There's one more column in this table which is date. This date is required because when some person resigns (say today) then he won't feature in the headcount for July 08 but till June 08 he was there and this is how I maintain my history. e.g All the employees in the Emp_Team table have date as 01/06/2008 for this month. So, in future if I query for the employees who worked in June I will get this list.
Now, I want to copy all this data in the same table again and want to remove any people who have resigned. Their resignation status is in the Employee table, where you have their last working date as well. So, when I add all this data with date 01/07/2008 I want to remove any employees whose last working date is before that.

Can this be done or I have to change my design? In case it can be - How?

Thanks a lot! :)

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-06-10 : 05:31:22

Just add a where clause to your query, then you can put the result set into a new table.

Incidently, what do you do when someone changes department?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 05:33:07

You could simply have a history table with EmpID,TeamID and have two dates Valid From and Valid To which indicates when a person moved to a particular team and when he moved out. Whenever you take headcount look into current table (Emp-Team) for the persons in team and also in history for those person records with ValidFrom <=date andvalidTo > Date

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-10 : 05:40:09

Oh yeah, that valid from, valid to sounds damn cool! :D
I have put the date for all the employees as 01/06/2008 right now. I have everybody's start date which will act as Valid From, how can I copy all those dates into this table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 05:43:18

quote:
Originally posted by avkuvalekar

Oh yeah, that valid from, valid to sounds damn cool! :D
I have put the date for all the employees as 01/06/2008 right now. I have everybody's start date which will act as Valid From, how can I copy all those dates into this table?

You need to put a trigger on your Emp-Team table to make sure any changes to TeamID of an Employee will be captured and trigger will put a new record to history with earier teamid and ValidFrom as startdate and currentdate as ValidTo

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-10 : 06:03:34

hey, thanks a lot.
Can you please tell me how do I change the current column? I have put the ValidFrom date as 01/06/2008 right now and I want to change that to start date of each employee which is stored in the employee table.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 06:06:32

quote:
Originally posted by avkuvalekar

hey, thanks a lot.
Can you please tell me how do I change the current column? I have put the ValidFrom date as 01/06/2008 right now and I want to change that to start date of each employee which is stored in the employee table.

Thanks!

Not sure where you will get the date when the person last moved into team he's now in. Do you have any tables that hold history data for these records now?

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-10 : 06:10:08

I have his start date which we can assume as the date when he moved in. So, the ValidFrom is his startdate which is stored as EmpStartDate in the Employee table. I want to copy all those dates and insert into the Emp-Team table because the order of employees is the same.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 06:19:37

quote:
Originally posted by avkuvalekar

I have his start date which we can assume as the date when he moved in. So, the ValidFrom is his startdate which is stored as EmpStartDate in the Employee table. I want to copy all those dates and insert into the Emp-Team table because the order of employees is the same.

Nope thats not true. startdate value will have only date when he's moved to team he's currently in. We wont be keeping current information in history table. Only when person changes a team we put the information in history. Thats why i told any time we'll take the current data as it is from Emp_team and then take retrospective data for interested period from history. so if you just want to track this from now on you just need to keep your history empty and just put a trigger on your main tabl;e. any further changes, the trigger will fire and put the history on your history table.

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-11 : 00:05:25

Hi,

Thanks a lot for this one. Now, as part of the same process, I have 2 tables called as Team_Aggregate and Team_Config.
Team_Agg table has - Date (1st of every month), Team ID, Permanent Employees, Contracted Employees, Open Positions, Team Total.
Team_Config has Date (1st of every month), Team ID ,Max Positions, Open Posns. I know that most of this Team_Agg data can be fetched using queries and this table isn't really helping me much but then now I want to continue with whatever schema that I have.
How should I go about this? What I have thought is a stored procedure that will run at the beginning of every month and calculate all the stuff and put it in. How can I pass today's date as an input to a stored procedure?

So, I want these 2 tables copy the entire data from the previous month and just change the Date.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 00:22:57

quote:
Originally posted by avkuvalekar

Hi,

Thanks a lot for this one. Now, as part of the same process, I have 2 tables called as Team_Aggregate and Team_Config.
Team_Agg table has - Date (1st of every month), Team ID, Permanent Employees, Contracted Employees, Open Positions, Team Total.
Team_Config has Date (1st of every month), Team ID ,Max Positions, Open Posns. I know that most of this Team_Agg data can be fetched using queries and this table isn't really helping me much but then now I want to continue with whatever schema that I have.
How should I go about this? What I have thought is a stored procedure that will run at the beginning of every month and calculate all the stuff and put it in. How can I pass today's date as an input to a stored procedure?
So, I want these 2 tables copy the entire data from the previous month and just change the Date.

You can have a stored procedure that picks up the records from Your EMP_Team table and history table for the previous month and calculate the required figures and populate the two new tables. You can then create a job to execute this stored procedure every month on a certain date so that it does the population. The current date can be obtained using function GETDATE() and you can pass this as a parameter to stored procedure.

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-11 : 01:23:26

Hi,

Thanks. How can I pass that getdate to a stored procedure?

Something like this - exec usp_test1 select getdate() go
doesn't work.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 01:29:27

quote:
Originally posted by avkuvalekar

Hi,

Thanks. How can I pass that getdate to a stored procedure?

Something like this - exec usp_test1 select getdate() go
doesn't work.

exec usp_test1 @Parameter=GETDATE()
ALso make sure you trim the time part from getdate() before using it to grab the records.

use DATEADD(d,DATEDIFF(d,0,GETDATE()),0) to trim time part

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-11 : 01:40:08

exec usp_test1 @t_date = getdate()

Gave an error as - Incorrect syntax near ')'.

exec usp_test1 @t_date = DATEADD(d, DATEDIFF(d,0,GETDATE()),0)

Gave an error as - Incorrect syntax near 'd'.

:(

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-11 : 01:53:17

I found out that getdate can't be directly passed to a stored proc. So, I used a workaround of declaring another stored proc which get the current date in a variable and then passes it to another stored proc.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 01:58:13

quote:
Originally posted by avkuvalekar

I found out that getdate can't be directly passed to a stored proc. So, I used a workaround of declaring another stored proc which get the current date in a variable and then passes it to another stored proc.

no need of another stored procedure. just use a variable to hold it

DECLARE @Date datetime
SET @Date=DATEADD(d, DATEDIFF(d,0,GETDATE()),0)
exec usp_test1 @t_date = @Date

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-12 : 01:21:54

Hi,

One more questions...didn't find many simple answers to this on google.
How can I conditionally use INSERT or UPDATE

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-12 : 01:23:09

to insert of the data for this month doesn't exist and update if it does exist.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-06-12 : 01:37:52

You'd have to do this as a check first in an if statement.


IF EXISTS (<your SQL statement to check if exists>)
BEGIN
INSERT INTO <Tablename>
[...]
END
ELSE
BEGIN
UPDATE <Tablename>
[...]
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources