Database

Krishna Aditya2-Jul-09 22:41

Krishna Aditya

2-Jul-09 22:41

My SP reads as

ALTER PROCEDURE [dbo].[tblRoleSummary_sel_all_sp]

SELECT txtRoleName, txtRoleDescription, txtRoleTypeDescription, txtUserName FROM tblRoleSummary_View

Do I need to change the SP and display the data as per my requiremnt or view itself can be changed for this/?

Thank YOu
Ramm

David Skelly3-Jul-09 2:26

David Skelly

3-Jul-09 2:26

Do a self-join on the table, something like:

select p.rolename, p.roledesc, p.roleuser, b.roleuser
from RoleTable p, RoleTable b
where p.roletype = 'Primary'
and p.rolename = b.rolename
and b.roletype = 'Backup'

I think that's right, off the top of my head.

This assumes that every Primary has a Backup. If not, you will need an outer join.

Krishna Aditya3-Jul-09 2:58

Krishna Aditya

3-Jul-09 2:58

Hi,

Its not compulsory, for every Primary, a backup will be available,

My query is

Hi, This is my SQL Query

SELECT dbo.tblRoleDescription.txtRoleName, dbo.tblRoleDescription.txtRoleDescription, dbo.tblRoleType.txtRoleTypeDescription, dbo.tblUserInfo.txtUserName, dbo.tblRoleAssignment.idSubProject, dbo.tblRoleAssignment.idLaunch FROM dbo.tblLaunchInfo INNER JOIN dbo.tblRoleAssignment ON dbo.tblLaunchInfo.idLaunch = dbo.tblRoleAssignment.idLaunch INNER JOIN dbo.tblProjectUsers ON dbo.tblRoleAssignment.idSubProject = dbo.tblProjectUsers.idSubProject AND dbo.tblRoleAssignment.idUser = dbo.tblProjectUsers.idUser INNER JOIN dbo.tblUserInfo ON dbo.tblProjectUsers.idUser = dbo.tblUserInfo.idUser INNER JOIN dbo.tblRoleDescription ON dbo.tblRoleAssignment.idRole = dbo.tblRoleDescription.idRole INNER JOIN dbo.tblRoleType ON dbo.tblRoleAssignment.idRoleType = dbo.tblRoleType.idRoleType

tblroleassignment -> idSubProject , idUser , idRoleType, idRole , idLaunch tblroledescription -> idRole(PK), txtRoleName, txtRoleDescription tblRoleType -> idRoleType (PK) , txtRoleTypeDescription tbluserinfo -> iduser, txtusername tbllaunchinfo -> idlaunch tblprojusers -> idsubproject, iduser

the sql query and the tables are given above, Please help me

Thank you,
Ramm

Full Text Search in SQL Server 2005

David Skelly3-Jul-09 5:57

David Skelly

3-Jul-09 5:57

To be completely honest, I don't have time to write your query for you. I have my own work to do.

I've given you an idea of how to do it: a self-join on the table to link the Primary user to the associated Backup user will give you what you want.

If you want to simplify things for yourself you could create a view that only includes Primary users. Then create another view that only includes Backup users. Then join them together on role id. That would make the query easier to work with.

Isaac Gordon2-Jul-09 18:21

Isaac Gordon

2-Jul-09 18:21

Hi Experts
I am using sql server 2005 express edition
i am not able to run the full text search.
when i create the catalog or any query realted to Full Text Search
it give me message
Laugh | :laugh:

Full-Text Search is not installed, or a full-text component cannot be loaded.

plz help me how the full text search is runing
Thank u
Dinesh

Re: Full Text Search in SQL Server 2005

Niladri_Biswas2-Jul-09 18:53

Complicated Summation Query Help

2-Jul-09 18:53

Even when I started learning Full-Text Search, I also fall into the same problem. However, I visited these site

http://www.devnewsgroups.net/group/microsoft.public.sqlserver.tools/topic25572.aspx[^]

http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/2b44f674-0cf2-4c79-89b3-4d9d8b641d53[^]

and got the solution.

Hope this helps.

Smile | :)

Niladri Biswas

JohnQuar12-Jul-09 7:44

JohnQuar1

2-Jul-09 7:44

Hello,

I have two columns, a category and a value. Below is the data:

Category		Total
Devices		38111
Plant		6063
Improvements		2266

I want to add two more columns: Zero and Sum

So the columns would be Category, Zero, Total, Sum

Is there any way to take the addition of Zero and Total and have the result in Sum, THEN take the result of Sum, and make it the answer in the next row of Zero, then repeat by adding zero and Total?

The reason why I want to do this is b/c I am trying to create a waterfall graph and this is the only way I can think to do it with the data that I have. Thanks for your time.

JM

I would be forever indebt to you if you could help out. Thanks!

Re: Complicated Summation Query Help

David Mujica2-Jul-09 8:37

David Mujica

2-Jul-09 8:37

After thinking about this for a bit, I think I would do this by creating a TEMP table and cursor to loop through the main table.

The TEMP table would be something like this:

Category      Zero  Total    Sum
Devices          0  38111   38111
Plan         38111   6063   44147
Improvements 44174   2266    4640

Use the following code snipet to get you going with the logic ...

DECLARE  myCURSOR CURSOR FOR 
	  SELECT CATEGORY, TOTAL
	  FROM myTABLE

OPEN myCURSOR
FETCH NEXT FROM myCURSOR INTO @XCATEGORY, @XTOTAL

SET @PBAL = 0.0
	
WHILE(@@FETCH_STATUS=0)	BEGIN

  SET @GTOTAL = @PBAL + @XTOTAL

  SET @MYSQL = 'INSERT INTO #MYTEMP'
  SET @MYSQL = @MYSQL + ' VALUES(' + CHAR(39) + @XCATEGORY+ CHAR(39) + ',' + @PBAL, + ',' + @XTOTAL + ','
  SET @MYSQL = @MYSQL + @GTOTAL + ')'

  PRINT @MYSQL

  EXEC(@MYSQL)

  FETCH NEXT FROM myCURSOR INTO @XCATEGORY, @XTOTAL

END

CLOSE myCURSOR
DEALLOCATE myCURSOR

GO

Hope this helps you ! Thumbs Up | :thumbsup:

Re: Complicated Summation Query Help

JohnQuar12-Jul-09 8:44

JohnQuar1

2-Jul-09 8:44

I will give that a shot, thank you for your help!

Sequential updates

Gymnast1-Jul-09 18:42

Gymnast

1-Jul-09 18:42

I am trying to update records in a table in sequence by setting up a Cursor and using Fetch like so..

DECLARE VPres_Cursor CURSOR FOR

select dbo.Person.PersonID, dbo.Person.LastName from dbo.Person inner join dbo.VicePresident
ON dbo.Person.PersonID = dbo.VicePresident.PersonID

OPEN VPres_Cursor;

FETCH NEXT FROM VPres_Cursor

WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM VPres_Cursor;
update dbo.VicePresident
set dbo.VicePresident.LastName = LastName
END;

CLOSE VPres_Cursor;
DEALLOCATE VPres_Cursor;

While I am able to see the records in the Results window in SQL Management Studio, I am not able to see the actual update in the table.
47 Burr
48 Clinton
...
..
81 Humphrey

Can anyone help??
Thanks in advance..

Gymnast.

Re: Sequential updates [modified]

Niladri_Biswas1-Jul-09 18:58

1-Jul-09 18:58

Do like this

DECLARE @LASTNAME VARCHAR(50)

DECLARE VPres_Cursor CURSOR FOR

select dbo.Person.PersonID, dbo.Person.LastName from dbo.Person inner join dbo.VicePresident
ON dbo.Person.PersonID = dbo.VicePresident.PersonID

OPEN VPres_Cursor;

FETCH VPres_Cursor INTO @LASTNAME

WHILE @@FETCH_STATUS = 0
BEGIN

update dbo.VicePresident
        set dbo.VicePresident.LastName = @LASTNAME 

        FETCH VPres_Cursor INTO @LASTNAME

END;

CLOSE VPres_Cursor;
DEALLOCATE VPres_Cursor;

I have only highlighted those lines which I think to be changed.

And it is working fine in my system.

Hope this helps.

Let me know in case of any concern
Smile | :)

Niladri Biswas

modified on Thursday, July 2, 2009 2:45 AM

Mycroft Holmes1-Jul-09 21:37

1-Jul-09 21:37

Using the update in a cursor I thought you needed Where Current of cursorname otherwise you set all the names to @Lastname

Never underestimate the power of human stupidity
RAH

Niladri_Biswas1-Jul-09 22:17

1-Jul-09 22:17

Hello Mycroft,

Actually I was not able to fathom your statement.

Did you mean that, the update will be based on some condition!

But actually, in the original post, the author of the code didn't specify any such thing by which I can use the Where Current of cursorname.

So I thought that I should do that in the way I did!.

You are always welcome to give a better solution so that I can also improve myself.
Smile | :)

Niladri Biswas

Mycroft Holmes1-Jul-09 22:42

1-Jul-09 22:42

When you do an update on a table if you do not apply a filter you will update every name to the variable. This updates the current record being accessed by the cursor.

Declare csrPV Cursor For
	SELECT ColName_0 
	FROM stg.IRsCap
	WHERE ID > @HeadRow
OPEN csrPV 
FETCH NEXT FROM csrPV INTO @Value

WHILE @@FETCH_STATUS = 0
BEGIN	
	IF @Value != ''
		SET @LastValue = @Value
	
	IF @Value = ''
	BEGIN
		UPDATE STG.IRsCap SET ColName_0 = @LastValue
		WHERE CURRENT OF csrPV
	END	
	FETCH NEXT FROM csrPV INTO @Value
END
CLOSE csrPV
DEALLOCATE csrPV

Never underestimate the power of human stupidity
RAH

Niladri_Biswas1-Jul-09 23:02

1-Jul-09 23:02

Niladri Biswas

Help necessary in making Query

Gymnast2-Jul-09 16:27

Gymnast

2-Jul-09 16:27

Thanks Mycroft! But I am yet to understand portions of the code you suggested:

SELECT ColName_0 	
FROM stg.IRsCap

I have a resultSet from a join that I would liek to use.. I assume I can still use that.

WHERE ID > @HeadRow

I assume that @HEADERROW is the value of the first IDin the table I am updating (which is 33 in my case).

IF @Value != ''		
SET @LastValue = @Value

Here I assume that @Value is declared to hold the Value of the LastName

Your input is appreciated.

Thanks,

Rosh

Saiyed Alam1-Jul-09 16:28

Saiyed Alam

1-Jul-09 16:28

Hello friends I have ha table like below

Amount GLID
2000 6523
4000 2356
4500 5623

I need to write a query that will show this table like below

6523 2356 5623
2000 4000 4500

How can I do this? Any help would be helpful.

Thanking in advance
Johnny

Not Active1-Jul-09 18:02

Not Active

1-Jul-09 18:02

I believe what you need is PIVOT[^]

only two letters away from being an asset

[Message Deleted]

Niladri_Biswas1-Jul-09 18:36

1-Jul-09 18:36

[Message Deleted]

Mycroft Holmes1-Jul-09 21:39

1-Jul-09 21:39

WRONG - you can use MAX or MIN as the aggregate when you want to pivot on 1 value. It is still using an aggregate but it allws you to achieve a solution.

Never underestimate the power of human stupidity
RAH

[Message Deleted]

Niladri_Biswas1-Jul-09 22:19

1-Jul-09 22:19

[Message Deleted]

Mycroft Holmes1-Jul-09 22:48

1-Jul-09 22:48

I was not arguing with your solution, just your statement that a pivot would not do the job because of the aggregate requirement. You can see it in this article[^]

Never underestimate the power of human stupidity
RAH

Niladri_Biswas1-Jul-09 20:39