static data - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

static data

Author

Topic

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-06-09 : 07:07:10

I want result like this:

slno. date status months remarks
1 06-05-2009 trainee 12 ok
2 06-05-2010 confirm -

in above table the 1st record is coming from one table, that was entered while the employee is joining in the organization.

the 2nd record is coming from another table(stauschange) after some period the employee status is updated

After i updated staus of the employee in statuschange table, the months should automatically update to 1strecord(12 month) and for 2nd record it should be null

if 3rd record is there means,the view should be like below:

slno. date status months remarks
1 06-05-2009 trainee 7 ok
2 06-01-2010 probation 5
3 06-05-2010 confirmed -

how can i write stored procedure for this in sqlserver 2008?

pls help me as soon as possible, its urgent

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-06-09 : 08:28:33

Could you perhaps copy the question from your textbook so that we have all the needed table structures, logic and sample data?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-09 : 11:39:08

Your question is not very clear. Can you post sample data and expected ouptut? Here is a link that will help you to post the required DDL and DML:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-06-10 : 00:06:21

I have these columns from one table(EmpInfo):

effectivefrom statusid
2009-05-05 1

effectivefrom is nothing but the joiningdate of the employee and the statusid is coming from Master Table:Status
which has

statusid status
1 trainee
2 probation
3 confirmation

like that

i have another table Statuschange which has:

status_id empid effectivefrom statuschageid months remarks
1 1 2009-10-05 2 5 ok
2 1 2010-05-05 3 7 gud

like that,

here the Months colum should be updated automatically by finidnthe difference between latest statuschange to present change
i.e

in the status_id =1 row, the months 5 is the difference between the effectivefrom (empinfo table) and effectivefrom (statuschage table)
and status_id=2 months 7 is the difference from status_id=1 record's effectivefrom ann status_id=2 record's effectivefrom columns

Now i wnat the result like below:

slno effectivefrom statuschange months remarks action
1 2009-05-05 trainee 5 view
2 2009-10-05 probation 7 view
3 2010-05-05 confirmed - edit view

the first 2 records are only viewable, and the latest record(3) should only editable and viewable
how i can write the stored procedure for the above situtaion in sqlserver 2008?

pls help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources