SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 case query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masterdineen
Aged Yak Warrior

United Kingdom
548 Posts

Posted - 04/10/2013 :  11:08:47  Show Profile  Reply with Quote
I have three columns in my data below.

ref, status, year.

I have managed to set year to 0 when status is 'canceled',
but I also want to set other years to 0 of the same ref.

see below example data.

declare @sample table (
ref int,
status nvarchar(12),
Year int)

insert @sample

select '33', 'member', '1998' union all
select '33', 'Canceled', '1998' union all
select '33', 'member', '1998' union all
select '33', 'Canceled', '1998' union all
select '33', 'Canceled', '1998' union all
select '33', 'member', '1998' union all
select '33', 'Canceled', '1998' union all
select '40', 'member', '1998' union all
select '40', 'Canceled', '1998' union all
select '40', 'Canceled', '1998' union all
select '40', 'member', '1998' union all
select '40', 'Canceled', '1998' union all
select '40', 'Canceled', '1998' union all
select '40', 'member', '1998' union all
select '40', 'Canceled', '1998' union all
select '40', 'member', '2004' union all
select '33', 'Canceled', '2004' union all
select '33', 'member', '2004' union all
select '33', 'Canceled', '2004' union all
select '33', 'member', '2009' union all
select '33', 'Canceled', '2009' union all
select '33', 'member', '2005' union all
select '33', 'member', '2005' union all
select '33', 'member', '2005' union all
select '33', 'member', '2005' union all
select '33', 'member', '2005' union all
select '33', 'member', '1998' union all
select '33', 'member', '1998' union all
select '33', 'Canceled', '1998' union all
select '33', 'Canceled', '1998' union all
select '33', 'member', '2005' union all
select '33', 'Canceled', '2005' union all
select '33', 'member', '1998' union all
select '33', 'member', '1998' union all
select '33', 'Canceled', '1999' union all
select '33', 'member', '1998' union all
select '33', 'Canceled', '1998' union all
select '33', 'Canceled', '1998' union all
select '33', 'member', '1999' union all
select '33', 'Canceled', '1999' union all
select '33', 'member', '1999' union all
select '33', 'Canceled', '1999' union all
select '33', 'member', '1998' union all
select '33', 'Canceled', '1999' union all
select '33', 'member', '1999' union all
select '33', 'Canceled', '1999' union all
select '33', 'member', '1999' union all
select '33', 'Canceled', '1999' union all
select '33', 'Canceled', '1999' union all
select '33', 'member', '1999' union all
select '33', 'member', '1999' union all
select '33', 'Canceled', '1999' union all
select '33', 'member', '1999' union all
select '33', 'member', '1999' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'Canceled', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2001' union all
select '33', 'member', '2001' union all
select '33', 'member', '2001' union all
select '33', 'member', '2001' union all
select '33', 'member', '2001' union all
select '33', 'member', '2001' union all
select '33', 'member', '2001' union all
select '33', 'member', '2001' union all
select '33', 'member', '2001' union all
select '33', 'member', '2001' union all
select '33', 'member', '2001' union all
select '33', 'member', '2001' union all
select '33', 'member', '2001' union all
select '33', 'member', '2001' union all
select '33', 'member', '2001' union all
select '33', 'member', '2001' union all
select '33', 'member', '2001' union all
select '33', 'member', '2001' union all
select '33', 'member', '2001' union all
select '33', 'member', '2001' union all
select '33', 'member', '2001' union all
select '33', 'member', '2001' union all
select '33', 'member', '2001' union all
select '33', 'member', '2002' union all
select '33', 'member', '2002' union all
select '33', 'member', '2002' union all
select '33', 'member', '2002' union all
select '33', 'member', '2002' union all
select '33', 'member', '2002' union all
select '33', 'member', '2002' union all
select '33', 'member', '2002' union all
select '33', 'member', '2002' union all
select '33', 'member', '2002' union all
select '33', 'member', '2002' union all
select '33', 'member', '2002' union all
select '33', 'member', '2002' union all
select '33', 'member', '2002' union all
select '33', 'member', '2002' union all
select '33', 'member', '2002' union all
select '33', 'member', '2002' union all
select '33', 'member', '2002' union all
select '33', 'member', '2002' union all
select '33', 'member', '2002' union all
select '33', 'member', '2002' union all
select '33', 'member', '2002' union all
select '33', 'member', '2002' union all
select '33', 'member', '2002' union all
select '33', 'member', '2002' union all
select '33', 'member', '2002' union all
select '33', 'member', '2002' union all
select '33', 'member', '2002' union all
select '33', 'member', '2003' union all
select '33', 'member', '2003' union all
select '33', 'member', '2003' union all
select '33', 'member', '2004' union all
select '33', 'member', '2004' union all
select '33', 'member', '2004' union all
select '33', 'member', '2004' union all
select '33', 'member', '2005' union all
select '33', 'member', '2005' union all
select '33', 'member', '2005' union all
select '33', 'member', '2005' union all
select '33', 'member', '2005' union all
select '33', 'member', '2005' union all
select '33', 'member', '2005' union all
select '33', 'member', '2005' union all
select '33', 'member', '2005' union all
select '33', 'member', '2006' union all
select '33', 'member', '2006' union all
select '33', 'member', '2006' union all
select '33', 'member', '2006' union all
select '33', 'member', '2006' union all
select '33', 'member', '2006' union all
select '33', 'member', '2006' union all
select '33', 'member', '2006' union all
select '33', 'member', '2006' union all
select '33', 'member', '2006' union all
select '33', 'member', '2006' union all
select '33', 'member', '2006' union all
select '33', 'member', '1999' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2000' union all
select '33', 'member', '2001' union all
select '33', 'member', '1999' union all
select '33', 'member', '1999' union all
select '33', 'member', '1999' union all
select '33', 'member', '2000' union all
select '33', 'member', '2001' union all
select '33', 'member', '2001' union all
select '33', 'member', '2001' union all
select '33', 'member', '2001' union all
select '33', 'member', '2001' union all
select '33', 'member', '2001' union all
select '33', 'member', '1999' union all
select '33', 'member', '1999' union all
select '33', 'member', '1999' union all
select '33', 'member', '1999' union all
select '33', 'member', '1999' union all
select '33', 'member', '1999' union all
select '33', 'member', '1999' union all
select '33', 'Canceled', '1999' union all
select '33', 'member', '1999'




select ref,
status,
[year]
,case when [status] = 'Canceled' then '0' else year end as 'cancel',
'0' as 'Desired Result'



from @sample
where [YEAR] = 1999
order by YEAR

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1646 Posts

Posted - 04/10/2013 :  14:27:59  Show Profile  Reply with Quote
Not clear on the requirements here. Are you trying to set Year to '0' if any Status = 'canceled' within the same Ref? Using your data, every Ref = '33' should have its Year set to '0' since the second row of your sample data is marked as "clanceled'. Is that the task at hand?
BTW: Some sample expected output would clarify your need.

=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 04/10/2013 :  14:37:36  Show Profile  Reply with Quote
--> "but I also want to set other years to 0 of the same ref."

remove the where condition,
where [YEAR] = 1999


Cheers
MIK
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/11/2013 :  02:20:10  Show Profile  Reply with Quote

select ref, 
status,
[year]
,case when sum(case when [status] = 'Canceled' then 1 else 0 end)over(partition by ref) >0 then  '0' else year end as 'cancel'
from @sample
order by YEAR


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000