[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

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 ?
0
RickCooper
Asked:
RickCooper
  • 5
  • 4
  • 2
2 Solutions
 
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
 
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now