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.
Author |
Topic |
sebastian11c
Posting Yak Master
129 Posts |
Posted - 2013-01-10 : 10:00:06
|
hi there i have a tableCREATE TABLE [dbo].[parts]( [idpart] [int] NOT NULL, [part_name] [varchar](50) NOT NULL, [condition] [varchar](50) NOT NULL) ON [PRIMARY]GOlets put some records on itINSERT 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 advancedregards |
|
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 tblcheck 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. |
|
|
sebastian11c
Posting Yak Master
129 Posts |
Posted - 2013-01-10 : 11:37:46
|
thanks nigelrivettit worksthanks a lotany other way people?? |
|
|
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 rulesthanks a lot again |
|
|
|
|
|
|
|