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 |
Kulwant
Starting Member
4 Posts |
Posted - 2013-03-06 : 02:35:00
|
I Have a Unit table with two fields: Unit and DescriptionUnit DescriptionA This is unit AB This is unit BC This is unit C I have another Parts table having three fields: parts UnitID Availableparts UnitID Availablepart1 A 1part2 A 1part1 B 1part2 B 0part3 B 1part1 C 0part2 C 0part3 C 0 If all the parts for a unit is available then only the unit should be available otherwise not. Also, I want to show that is a unit is not available then which which part is not availableSo, I want output with pseudo columns as :Unit Description Available part1 part2 part3A This is unit A 1 1 1 B This is unit B 0 1 0 1C This is unit C 0 0 0 0 Any help will be appreciated. thanks |
|
shaggy
Posting Yak Master
248 Posts |
Posted - 2013-03-06 : 03:42:43
|
This might help youcreate table tab1(unit char(1),Description varchar (30))create table tab2(parts varchar(5),unit char(1),available int)--truncate table tab1insert tab1select 'A','This is unit A'union select 'B','This is unit B'union select 'C','This is unit C'insert tab2select 'part1','A',1union select 'part2','A',1union select 'part1','B',1union select 'part2','B',0union select 'part3','B',1union select 'part1','C',0union select 'part2','C',0union select 'part3','C',0 select unit,Description,case when part1 = 0 or part2 = 0 or part3 = 0 then 0 else 1 end available,part1,part2,part3 from (select a.unit,Description,parts,available from tab1 a inner join tab2 b on a.unit = b.unit ) apivot (sum(available) for parts in (part1,part2,part3)) as t |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-06 : 03:44:05
|
[code]SELECT u.Unit,u.Description,CASE WHEN p.NACnt > 0 THEN 0 ELSE 1 END AS Available,part1,part2,part3FROM Unit uINNER JOIN (SELECT UnitID, MAX(CASE WHEN parts='part1' THEN Available END) AS part1, MAX(CASE WHEN parts='part2' THEN Available END) AS part2, MAX(CASE WHEN parts='part3' THEN Available END) AS part3, SUM(CASE WHEN Available=0 THEN 1 ELSE 0 END) AS NACnt FROM Parts GROUP BY unitID )pOn p.unitID = u.Unit[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Kulwant
Starting Member
4 Posts |
Posted - 2013-03-06 : 04:14:36
|
Thanks alot ... :) .... This was the very first time i ever asked a question through a forum. Thank you all. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-06 : 04:38:50
|
you're welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Kulwant
Starting Member
4 Posts |
Posted - 2013-03-14 : 09:06:50
|
The situation is bit more complex now:Unit DescriptionA This is unit AB This is unit BC This is unit CD This is unit Dparts UnitID Availablepart1 A 1part2 A 1part1 B 1part2 B 0part3 B 1part1 C 0part2 C 1part3 C 1part1 D 1part2 D 0part3 D 0The rule is different for each unit.For unit A to be available, part1 or part2 should be availableFor unit B to be available, part1 or part2 should be available and part3 should be availableFor unit C to be available part1 and part2 should be availableFor unit D to be available, part1 or part2 should be available So, I want output with pseudo columns as:Unit Description Available part1 part2 part3A This is unit A 1 1 1 B This is unit B 1 1 0 1C This is unit C 0 0 1 1D This is unit D 1 1 0 0 But I also want to know that how I will store this information in database for each unit and use it to generate the output as above. So, I am looking for database schema first to hold the information about the rules for each unit and then the query to generate the output. Thanks in advance. |
|
|
|
|
|
|
|