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
 pseudo columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kulwant
Starting Member

Australia
4 Posts

Posted - 03/06/2013 :  02:35:00  Show Profile  Reply with Quote
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
245 Posts

Posted - 03/06/2013 :  03:42:43  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/06/2013 :  03:44:05  Show Profile  Reply with Quote

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/

Go to Top of Page

Kulwant
Starting Member

Australia
4 Posts

Posted - 03/06/2013 :  04:14:36  Show Profile  Reply with Quote
Thanks alot ... :) .... This was the very first time i ever asked a question through a forum.

Thank you all.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/06/2013 :  04:38:50  Show Profile  Reply with Quote
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Kulwant
Starting Member

Australia
4 Posts

Posted - 03/14/2013 :  09:06:50  Show Profile  Reply with Quote
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.
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.36 seconds. Powered By: Snitz Forums 2000