Adding a Y/N column based on criteria

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

Adding a Y/N column based on criteria

Author

Topic

meth73
Starting Member

10 Posts

Posted - 2005-09-21 : 22:20:04

Currently I'm trying to add a column based on certain criteria based on the following data:

CallID GroupName CustomerPending
------ ----------- ------------
00500588 Followup N
00500588 Followup N
00500588 Server N
00500588 Service Desk N
00500588 Service Desk N

Basically I'm trying to add an extra column, so that whenever the GroupName is "Followup", then a 'Y' will appear in the CustomerPending column for all instances of that CallID. I tried with the following, but it only provides a 'Y' in the rows (not the CallId's) where "Followup" is found.

--------------------------------------
UPDATE dbo.Asgnmnt
SET CustomerPending = 'Y'
FROM dbo.Asgnmnt
WHERE dbo.Asgnmnt.GroupName IN ('SD Followup')

ALTER Table Asgnmnt
ALTER column CustomerPending varchar(1)

UPDATE dbo.Asgnmnt
SET CustomerPending = 'N'
FROM dbo.Asgnmnt
WHERE dbo.Asgnmnt.GroupName NOT IN ('SD Followup')

ALTER Table Asgnmnt
ALTER column CustomerPending varchar(1)
---------------------------------------

Any assistance appreciated.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-09-21 : 23:15:59

[code]DECLARE @table TABLE(CallID CHAR(8), GroupName VARCHAR(55), CustomerPending CHAR(1))

INSERT @table(CallID, GroupName, CustomerPending)
SELECT '00500588','Followup','N' UNION ALL
SELECT '00500588','Followup','Y' UNION ALL
SELECT '00500588','Server','N' UNION ALL
SELECT '00500588','Service Desk','N' UNION ALL
SELECT '00500588','SD Followup','Y' UNION ALL
SELECT '00500588','SD Followup','N'

SELECT CallID, GroupName, CustomerPending FROM @table

UPDATE @table
SET CustomerPending =
CASE
WHEN GroupName = 'SD Followup' THEN 'Y'
ELSE 'N'
END

SELECT CallID, GroupName, CustomerPending FROM @table
[/code]

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

meth73
Starting Member

10 Posts

Posted - 2005-09-22 : 00:15:22

Hi Derrick,

The output of what you've provided gave me:

CallID GroupName CustomerPending
------ ----------- ------------
00500588 Followup N
00500588 Followup N
00500588 Server N
00500588 Service Desk N
00500588 SD Followup Y
00500588 SD Followup Y

I probably wasn't clear enough on what I was trying to produce, so I'll produce an example..

I want to start with something like this:

CallID GroupName
------ -----------
00500588 Desktop
00500588 Followup
00500600 Server
00500600 Service Desk
00500777 Network
00500777 Followup

And end up with something like this:

CallID GroupName CustomerPending
------ --------- ---------------
00500588 Desktop Y
00500588 Followup Y
00500600 Server N
00500600 Service Desk N
00500777 Followup Y
00500777 Network Y

In other words, whenever a particular CallID has the GroupName ='Followup', a 'Y' appears on all rows for that particular CallID, no matter what row of the CallID that the GroupName 'Followup' appears. The alternative is put the added 'CustomerPending' column into another table where CallID is also found and where there is only 1 row for each CallID.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-22 : 00:42:01

Here is modified code of Derrick



DECLARE @table TABLE(CallID CHAR(8), GroupName VARCHAR(55), CustomerPending CHAR(1))

INSERT @table(CallID, GroupName, CustomerPending)
	SELECT '00500588','Desktop',Null UNION ALL
	SELECT '00500588','Followup',Null UNION ALL
	SELECT '00500600','Server',Null UNION ALL
	SELECT '00500600','Service Desk',Null UNION ALL
	SELECT '00500777','Followup',Null UNION ALL
	SELECT '00500777','Network',Null

SELECT CallID, GroupName, CustomerPending FROM @table

UPDATE @table
SET CustomerPending = 'Y' where CallId in (select CallId from @table where GroupName like 'Followup%') 

SELECT CallID, GroupName, CustomerPending FROM @table

Madhivanan

Failing to plan is Planning to fail

meth73
Starting Member

10 Posts

Posted - 2005-09-22 : 00:56:29

Thanks for your help guys. Much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources