Need help to retrive data populate zeros in a row

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 2000 Forums

SQL Server Development (2000)

Need help to retrive data populate zeros in a row

Author

Topic

RaghaSM
Yak Posting Veteran

52 Posts

Posted - 2008-04-22 : 09:34:28

Hi All,

I have 2 tables :

Levels Training
------ ----------
Levelid integer Companyid integer
LevelName varchar Levelid integer
A integer
B integer
C integer
ModifiedDate DateTime

Suppose i have data like this in Training table:

COmpanyid LevelName A B C ModifiedDate
---------------------------------------------------------
1 Level1 10 2 8 12/3/2008
1 Level3 11 4 7 12/4/2008
1 Level4 15 5 3 21/2/2008
1 level5 12 6 6 14/2/2008

2 Level1 10 4 6 2/3/2008
2 Level2 11 8 3 30/4/2008
2 Level3 5 5 0 25/2/2008
2 level4 12 9 3 23/2/2008

I have 2 questions with this data :

Question 1:
-----------

In the above data I have level 2 missing with respect to companyid 1 and level5 missing with respect to companyid 2,

But I want to show my data as following :

COmpanyid LevelName A B C ModifiedDate
---------------------------------------------------------
1 Level1 10 2 8 12/3/2008
1 Level2 0 0 0 Null or empty
1 Level3 11 4 7 12/4/2008
1 Level4 15 5 3 21/2/2008
1 level5 12 6 6 14/2/2008

2 Level1 10 4 6 2/3/2008
2 Level2 11 8 3 30/4/2008
2 Level3 5 5 0 25/2/2008
2 level4 12 9 3 23/2/2008
2 Level5 0 0 0 Null or empty

Ho wcan i fill all zeros for A B C columns with respect to the leveliname missing for a particular companyid.

Question2:
----------

suppose my data has duplicate entries for a combination of companyid and levelid . SOmethis like this

COmpanyid LevelName A B C ModifiedDate
---------------------------------------------------------
1 Level1 10 2 8 12/3/2008
1 Level1 45 10 35 15/3/2008
1 Level2 11 4 7 12/4/2008
1 Level2 15 5 3 21/2/2008
1 level3 12 6 6 14/2/2008
1 Level3 10 4 6 2/3/2008
1 Level2 11 8 3 30/4/2008
1 Level3 5 5 0 25/2/2008

How to retrive all the training details for a particular company where with levelid and A B C columns for the latest modified date.

I mean to say i want to retrive the data in this manner for the above data: one levelid fior each companyid with the data with latest modified date

COmpanyid LevelName A B C ModifiedDate
---------------------------------------------------------
1 Level1 45 10 35 15/3/2008
1 Level2 11 8 3 30/4/2008
1 Level3 10 4 6 2/3/2008

Please Help.............Needs the solution ASAP

Your help will be verymuch appreciated

Thanks
Ragha

RaghaSM
Yak Posting Veteran

52 Posts

Posted - 2008-04-22 : 09:58:18

I am sorry the data is not clear .Will post it again

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-22 : 10:04:00

Before you do, please read and follow the steps in this blog post
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources