| Author |
 Topic  |
|
|
Kulwant
Starting Member
Australia
4 Posts |
 Posted - 03/06/2013 : 02:35:00
|
I Have a Unit table with two fields: Unit and Description
Unit Description
A This is unit A
B This is unit B
C This is unit C
I have another Parts table having three fields: parts UnitID Available
parts UnitID Available
part1 A 1
part2 A 1
part1 B 1
part2 B 0
part3 B 1
part1 C 0
part2 C 0
part3 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 available
So, I want output with pseudo columns as :
Unit Description Available part1 part2 part3
A This is unit A 1 1 1
B This is unit B 0 1 0 1
C This is unit C 0 0 0 0
Any help will be appreciated. thanks |
Edited by - Kulwant on 03/06/2013 02:35:41
|
|
|
shaggy
Posting Yak Master
India
228 Posts |
Posted - 03/06/2013 : 03:42:43
|
This might help you
create table tab1
(
unit char(1),
Description varchar (30)
)
create table tab2
(
parts varchar(5),
unit char(1),
available int
)
--truncate table tab1
insert tab1
select 'A','This is unit A'
union select 'B','This is unit B'
union select 'C','This is unit C'
insert tab2
select 'part1','A',1
union select 'part2','A',1
union select 'part1','B',1
union select 'part2','B',0
union select 'part3','B',1
union select 'part1','C',0
union select 'part2','C',0
union 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 ) a
pivot (sum(available) for parts in (part1,part2,part3)) as t |
Edited by - shaggy on 03/06/2013 03:46:09 |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 03/06/2013 : 03:44:05
|
SELECT u.Unit,u.Description,CASE WHEN p.NACnt > 0 THEN 0 ELSE 1 END AS Available,part1,part2,part3
FROM Unit u
INNER 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
)p
On p.unitID = u.Unit
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Kulwant
Starting Member
Australia
4 Posts |
Posted - 03/06/2013 : 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
India
47069 Posts |
Posted - 03/06/2013 : 04:38:50
|
you're welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Kulwant
Starting Member
Australia
4 Posts |
Posted - 03/14/2013 : 09:06:50
|
The situation is bit more complex now:
Unit Description
A This is unit A
B This is unit B
C This is unit C
D This is unit D
parts UnitID Available
part1 A 1
part2 A 1
part1 B 1
part2 B 0
part3 B 1
part1 C 0
part2 C 1
part3 C 1
part1 D 1
part2 D 0
part3 D 0
The rule is different for each unit.
For unit A to be available, part1 or part2 should be available
For unit B to be available, part1 or part2 should be available and part3 should be available
For unit C to be available part1 and part2 should be available
For unit D to be available, part1 or part2 should be available
So, I want output with pseudo columns as:
Unit Description Available part1 part2 part3
A This is unit A 1 1 1
B This is unit B 1 1 0 1
C This is unit C 0 0 1 1
D 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.
|
|
|
| |
Topic |
|
|
|
pseudo columns
