MS SQL - Combine information from two tables query

I am sure once I see the answer it will be an ah-ha moment..

I have one table with Preventive Maintenance records for a piece of equipment. And I have another table with Work Order records..

I want to bring them together in date order... so the query would be

PM Date 1
PM Date 2
WO Date 3
WO Date 4
PM Date 5
PM Date 6

etc. So I have a chronological order of events by date to see if a machine is ok or having issues do to alot of work orders between pm's.

I appreciate any help.
LVL 24
DMTechGrooupAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I just ordered the columns from both tables in same order and same data type (guessed from their names). For those one I couldn't get any relation between two tables I added a NULL value so won't return error. Here is the result:
SELECT [ID]
      ,[EQUIPMENT_ID]
      ,[COMPONENT_NUMBER]
      ,NULL
      ,[NAME]
      ,[COMPLETION_DATE]
      ,[NEXT_DATE]
      ,[EMPLOYEE_ID]
      ,[START_DATE]
      ,[END_DATE]
      ,[TIME_ACTUAL]
      ,[TYPE]
      ,NULL
FROM [IQM].[dbo].[EQUIPMENT_REPORT_PM]
UNION ALL
SELECT [ID]
      ,[EQUIPMENT_ID]
      ,NULL
      ,[REPORTED_BY]
      ,[ASSIGNED_BY]
      ,[COMPLETION_DATE]
      ,NULL
      ,[EMPLOYEE_ID]
      ,[RECEIVED_DATE]
      ,[DUE_DATE]
      ,NULL
      ,[TYPE]
      ,[BREAKDOWN_CAUSE]      
FROM [IQM].[dbo].[EQUIPMENT_REPORT_WO]
ORDER BY [COMPLETION_DATE]

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
For starters, 'bring them together' implies that they have the same columns and column names, which I'm guessing is not the case, so assuming that both have column name and dt...

SELECT type, name, dt
FROM (
   SELECT 'PM' as type, name, dt
   FROM PreventiveMaintenance
   UNION ALL
   SELECT 'WO', name, dt
   FROM WorkOrder) a
ORDER BY dt

Open in new window


Since there was no description of how/if these tables were related, none was provided in my answer.  If these tables are related and there are other criteria involved in how this is displayed, you'll have to tell us.
0
 
Leo TorresSQL DeveloperCommented:
Select Col1, Col2
from ATable a
       Join BTable b
           on a.Key = b.Key
Order by Col

Open in new window

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:
You need a union query, something like:

Select  "PM", id, [maint date]
From [preventative maintenance]
Union all
Select "WO", id, [WO date]
From [work orders]
Order by [maint date]
0
 
DMTechGrooupAuthor Commented:
SELECT TOP 1000 [PREVENTIVE]
      ,[EQUIPMENT_ID]
      ,[COMPONENT_NUMBER]
      ,[NAME]
      ,[LAST_DATE]
      ,[NEXT_DATE]
      ,[EMPLOYEE_ID]
      ,[START_DATE]
      ,[END_DATE]
      ,[TIME_ACTUAL]
      ,[TYPE]
  FROM [IQM].[dbo].[EQUIPMENT_REPORT_PM]

Open in new window


SELECT TOP 1000 [REACTIVE_ID]
      ,[EQUIPMENT_ID]
      ,[REPORTED_BY]
      ,[ASSIGNED_BY]
      ,[DUE_DATE]
      ,[RECEIVED_DATE]
      ,[CLOSED_DATE]
      ,[BREAKDOWN_CAUSE]
      ,[EMPLOYEE_ID]
      ,[TYPE]
  FROM [IQM].[dbo].[EQUIPMENT_REPORT_WO]

Open in new window



Sorry I should have put the two table detentions.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
If the only criteria is 'chronological by date' then this question has already been answered by me (and later Dale) based on how it was originally asked.   If there is any other criteria, such as joining the two tables, you need to spell it out.

Mind readers we ain't, and experts here aren't that wild on having to ask multiple clarifying questions to get at all the necessary requirements of an asker's question.
0
 
DMTechGrooupAuthor Commented:
Wow.. Uh Thanks.. seems when I ask a question with lots of detail I get no response..

http://www.experts-exchange.com/questions/28661788/SSRS-Report-Multiple-Tables.html

But thanks for your time and response.
0
 
DMTechGrooupAuthor Commented:
So as listed in my post with the table definitions.. the columns are not the same to both tables.. and the only constant between the tables is the Equipment ID..  So my apologies for not being more clarifying, but I need all listed columns from each table.  I get the following error.

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

So would I need to create views so both tables have the exact same column names?

SELECT [ID]
      ,[EQUIPMENT_ID]
      ,[COMPONENT_NUMBER]
      ,[NAME]
      ,[COMPLETION_DATE]
      ,[NEXT_DATE]
      ,[EMPLOYEE_ID]
      ,[START_DATE]
      ,[END_DATE]
      ,[TIME_ACTUAL]
      ,[TYPE]
  FROM [IQM].[dbo].[EQUIPMENT_REPORT_PM]
  UNION
  SELECT [ID]
      ,[EQUIPMENT_ID]
      ,[REPORTED_BY]
      ,[ASSIGNED_BY]
      ,[DUE_DATE]
      ,[RECEIVED_DATE]
      ,[COMPLETION_DATE]
      ,[BREAKDOWN_CAUSE]
      ,[EMPLOYEE_ID]
      ,[TYPE]
  FROM [IQM].[dbo].[EQUIPMENT_REPORT_WO]

Open in new window

0
 
senadCommented:
your table design is flawed...
0
 
DMTechGrooupAuthor Commented:
That is from a view, but this unfortunately wasn't something we designed, its from a big ERP company.  Suggestions to work around it?  Thanks.
0
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.

All Courses

From novice to tech pro — start learning today.