troubleshooting Question

Select from two tables

Avatar of Richard Cooper
Richard CooperFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL ServerMicrosoft SQL Server 2005
11 Comments2 Solutions270 ViewsLast Modified:
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 ?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros