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)
 check if exists somre rules ina a table, help me

Author  Topic 

sebastian11c
Posting Yak Master

129 Posts

Posted - 2013-01-10 : 10:00:06
hi there i have a table

CREATE TABLE [dbo].[parts](
[idpart] [int] NOT NULL,
[part_name] [varchar](50) NOT NULL,
[condition] [varchar](50) NOT NULL
) ON [PRIMARY]

GO

lets put some records on it

INSERT INTO [dbo].[parts]
([idpart]
,[part_name]
,[condition])

select 1, 'left front door', 'very good'
union
select 2, 'left back door', 'very bad'
union
select 3, 'right front door', 'bad'
union
select 4, 'right back door', 'bad'
union
select 5, 'front window', 'bad'
union
select 6, 'back window', 'bad'


and i need your help beacuse i need to check if the data on my table match with some special rules that i have.

these are the rules,

a. i need to know if exist at least one part wich condition is "very bad"

b. i need to know if both parts (idparts =3 and idpart=4 ) are in "bad condition" (both of them in "bad condition")

c.i need to know if these 3 parts (idparts =4 and idpart=5 and idpart=6 ) are in "bad condition" (at the same time in bad condition)


how could i achieve this?

many thanks in advanced

regards

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-10 : 11:09:50
select sum(case when condition = 'very bad' then 1 else 0 end) ,
sum(case when condition like '%bad%' and idparts in (3,4)then 1 else 0 end) ,
sum(case when condition like '%bad%' and idparts in (4,5,6)then 1 else 0 end) ,
from tbl

check the values for >0, 2, 3 respectively.


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

sebastian11c
Posting Yak Master

129 Posts

Posted - 2013-01-10 : 11:37:46
thanks nigelrivett

it works
thanks a lot

any other way people??
Go to Top of Page

sebastian11c
Posting Yak Master

129 Posts

Posted - 2013-01-10 : 11:50:04
i wnat to do more dinamically, may be add other table with the rules and some kind of loop, or something like that, because avery day ill have to add more rules

thanks a lot again
Go to Top of Page
   

- Advertisement -