Avatar of Richard Cooper
Richard Cooper
Flag for United Kingdom of Great Britain and Northern Ireland asked on

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 ?
Microsoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Guy Hengel [angelIII / a3]

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Richard Cooper

ASKER
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
Guy Hengel [angelIII / a3]

UNPIVOT will still be the most efficient solution here...
it shows a concrete and simple example of how to UNPIVOT
Dale Fye

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Richard Cooper

ASKER
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
SOLUTION
Dale Fye

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Richard Cooper

ASKER
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
Dale Fye

@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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Richard Cooper

ASKER
@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.
Richard Cooper

ASKER
Thanks to both for your input
Dale Fye

@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

Your help has saved me hundreds of hours of internet surfing.
fblack61