Select from two tables

I have the following two tables and I need to select a group of records based on the data returned from one table.
Table1
BomCapacity
Fields
CapacityFlag, WorkCentre, CapacityYear, CapacityMonth, CapacityAvail1, CapacityAvail2, CapacityAvail3, CapacityAvail4, CapacityAvail5, CapacityAvail6,
                      CapacityAvail7, CapacityAvail8, CapacityAvail9, CapacityAvail10, CapacityAvail11, CapacityAvail12, CapacityAvail13, CapacityAvail14, CapacityAvail15,
                      CapacityAvail16, CapacityAvail17, CapacityAvail18, CapacityAvail19, CapacityAvail20, CapacityAvail21, CapacityAvail22, CapacityAvail23, CapacityAvail24,
                      CapacityAvail25, CapacityAvail26, CapacityAvail27, CapacityAvail28, CapacityAvail29, CapacityAvail30, CapacityAvail31

The first table has a list of WorkCentres with a row of data for each which shows the year, month and capacity by each day
WorkCentre      CapacityYear      CapacityMonth      CapacityAvail1      CapacityAvail2      CapacityAvail3      CapacityAvail4  etc....
C102A                            14                  1                                  0                                459                        270                         0
C102A                            14                  2                                  0                                    0                        459                    459
C102A                            14                  3                                  0                                    0                        459                    459
C201                            14                  2                               20                                568                        459                    459
C201                            14                  3                             785                                568                        459                    459
 
The fields CapacityAvail1 ....   are the available Capacity by day, 1 to 31 days


Table2
BSP_VMrpCapcityDYN

Fields
WorkCentre, Year, Month, Day, Planned
 
The second table has a list of WorkCentres with the planned usage by day
 WorkCentre      Year      Month      Day         Planned
C201               14                   4               10               46.8
C200               14                   5               12                 8.88
C201               14               5               12          1216
C201               14                  6                9            145.5
C200               14                  6                9                1.11

I need to link the two tables to show the planned and available capacity for each workcentre for the next 14 days

Can this be done ?
LVL 1
RickCooperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>The fields CapacityAvail1 ....   are the available Capacity by day, 1 to 31 days
which is a bad table design. yes, nice for the quick select, but not simple at all for the regular/reporting queries

so, either you change that table (and eventually present a view that does the crosstab/pivot as needed), or you have to "unpivot" the data (which is a much heavier operation):
http://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RickCooperAuthor Commented:
Hi,

 I know its a bad table design but I cannot change the table as its part of our ERP software.
I can create a view on that table if that helps

I have looked at the link you have provided and its above my level of SQL
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
UNPIVOT will still be the most efficient solution here...
it shows a concrete and simple example of how to UNPIVOT
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dale FyeCommented:
To normalize (unpivot) your 1st table the syntax would look something like:

SELECT WorkCentre, CapacityYear, CapacityMonth, 1 as CapacityDay, CapacityAvail1 as CapacityAvail 
FROM BOMCapacity WHERE CapacityAvail1 IS NOT NULL
UNION ALL
SELECT WorkCentre, CapacityYear, CapacityMonth, 2 as CapacityDay, CapacityAvail2 as CapacityAvail 
FROM BOMCapacity WHERE CapacityAvail2 IS NOT NULL
UNION ALL
SELECT WorkCentre, CapacityYear, CapacityMonth, 3 as CapacityDay, CapacityAvail3 as CapacityAvail 
FROM BOMCapacity WHERE CapacityAvail3 IS NOT NULL
UNION ALL
...
SELECT WorkCentre, CapacityYear, CapacityMonth, 31 as CapacityDay, CapacityAvail31 as CapacityAvail 
FROM BOMCapacity WHERE CapacityAvail31 IS NOT NULL

Open in new window

This is not difficult, but it is tedious.  This will result in a view that looks like:

Work Center    CapacityYear   CapacityMonth    CapacityDay   CapacityAvail
C102A                            14                  1                                  1                   0
C102A                            14                  1                                  1               459
C102A                            14                  1                                  1               270
0
RickCooperAuthor Commented:
Hi
I have managed to get the information in rows but I cannot work out how to get the day number in the row.
SELECT     CapacityFlag, WorkCentre, Cap, CapacityYear, CapacityMonth
FROM         (SELECT     CapacityFlag, WorkCentre, CapacityYear, CapacityMonth, CapacityAvail1, CapacityAvail2, CapacityAvail3
                       FROM          BomCapacity) P UNPIVOT (Cap FOR WorkC IN (CapacityAvail1, CapacityAvail2, CapacityAvail3)) AS unpvt
WHERE     CapacityFlag = 'W' AND CapacityYear = '14'

Open in new window


I get rows
CapacityFlag   WorkCentre  Cap  CapacityYear CapacityMonth  (I need  a column for daynumber adding)
W                           C102           30         14                     1                    
W                           C102           20         14                     1          
W                           C102           43         14                     1          
W                           C102           76         14                     1
0
Dale FyeCommented:
I've never used the UnPivot statement, but I think it might look something like the following in your case, I have not repeated all of the columns, but if this works, you can expand the two column listings
SELECT WorkCentre, CapacityYear, CapacityMonth, CapacityDay, Available
FROM 
   (SELECT WorkCentre, CapacityYear, CapacityMonth, CapacityAvail1,
        CapacityAvail2, CapacityAvail3, CapacityAvail4, CapacityAvail5
   FROM BOMCapacity) p
UNPIVOT
   (Available FOR CapacityDay IN 
      (CapacityAvail1, CapacityAvail2, CapacityAvail3, 
       CapacityAvail4, CapacityAvail5)
) AS unpvt;

Open in new window

0
RickCooperAuthor Commented:
Hi fyed
The UnPivot statement you posted works with the extra column but it shows CapacityAvail1, CapacityAvail2,CapacityAvail3 etc

Is there any way to change this to 1,2,3,4 etc
0
Dale FyeCommented:
@Guy,

Thanks for that reference.  I've been using the longer syntax for years because the last time I used SQL Server extensively was back in 2000-2002 and that feature was not available.
0
RickCooperAuthor Commented:
@fyed,

 i changed CapacityAvail1 to CapacityAvail1 AS [1] etc  in the select and changed Available FOR CapacityDay IN ([1], [2], etc and it works as I require.

I will split the points between the two experts.
0
RickCooperAuthor Commented:
Thanks to both for your input
0
Dale FyeCommented:
@Rick, I failed to pickup on that in the link that Guy posted.

You might try:

SELECT WorkCentre, CapacityYear, CapacityMonth
, Replace(CapacityDay, 'CapacityAvail', '') as CapDay, Available
FROM 
   (SELECT WorkCentre, CapacityYear, CapacityMonth, CapacityAvail1,
        CapacityAvail2, CapacityAvail3, CapacityAvail4, CapacityAvail5
   FROM BOMCapacity) p
UNPIVOT
   (Available FOR CapacityDay IN 
      (CapacityAvail1, CapacityAvail2, CapacityAvail3, 
       CapacityAvail4, CapacityAvail5)
) AS unpvt;

Open in new window

I assume that your [Day] column in the 2nd table is numeric, so you may also need to cast or convert the value that is the result of that Replace as an integer.
SELECT WorkCentre, CapacityYear, CapacityMonth
, Cast(Replace(CapacityDay, 'CapacityAvail', '') as smallint) as CapDay
, Available
FROM 
   (SELECT WorkCentre, CapacityYear, CapacityMonth, CapacityAvail1,
        CapacityAvail2, CapacityAvail3, CapacityAvail4, CapacityAvail5
   FROM BOMCapacity) p
UNPIVOT
   (Available FOR CapacityDay IN 
      (CapacityAvail1, CapacityAvail2, CapacityAvail3, 
       CapacityAvail4, CapacityAvail5)
) AS unpvt;

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.