Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Query - Joined 2 temporary table and align rows

Posted on 2014-02-24
4
Medium Priority
?
338 Views
Last Modified: 2014-03-05
Not sure how to explain this, but will try my best and hopefully someone can assist.  I have 2 SQL queries that query separate tables.  The 2 queries have no relations (yet tied together) and is base of a material planning sort by date.

Here's example of my raw data and final output.  The raw data shows from first query pulling (Want Date, Order#, and Qty).  The 2nd query pull fields "Required Date and Job#".

Sort and Align by Want_date and Required_date
With the above, if I sort by Want Date and Required Date, it aligns one after another.  However, I need to align the rows with "Want Date" closes to or equal to the "Required Date".

Example, line 2 of my Raw Data has a "Want Date" of 1/15/2014, and closes "Required Date" is 1/20/2014.  Since it is equal or greater than line 3, I want to align it with line 2.  See Final output.  Is this even doable?
0
Comment
Question by:holemania
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 25

Assisted Solution

by:chaau
chaau earned 400 total points
ID: 39884312
This query should do the trick:
select * from tab1 t1
left join tab2 t2 on t1.wantdate <= t2.requireddate 
  and t1.wantdate = (select max(wantdate) from tab1 where tab1.wantdate <= t2.requireddate)

Open in new window

SQL Fiddle
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39884316
I'd suggest providing the sql queries you are using right now, plus sample data from the source tables (not the temp tables)
0
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 1600 total points
ID: 39884413
CREATE TABLE #Order
(
      OrderID                  INT,
      WantDate            DATETIME,
      Qty                        INT
);

CREATE TABLE #Job
(
      JobID                  INT,
      RequiredDate      DATETIME
);

INSERT INTO #Order (OrderID, WantDate, Qty)
VALUES (29933, '1/1/2014', 2),
      (299335, '1/15/2014', 1),
      (29940, '1/25/2014', 2),
      (29930, '1/30/2014', 1),
      (29935, '2/14/2014', 2),
      (29936, '2/15/2014', 1)

INSERT INTO #Job (JobID, RequiredDate)
VALUES (29932, '1/20/2014'),
      (29933, '1/25/2014'),
      (29930, '2/27/2014')

WITH cte
AS
(
      SELECT OrderID, WantDate, Qty,
            ROW_NUMBER() OVER(ORDER BY WantDate) AS RowNumber
      FROM #Order
)
SELECT O.WantDate, O.OrderID AS [Order#], O.Qty, J.RequiredDate, J.JobID AS [Job#]
FROM cte AS O
LEFT OUTER JOIN cte AS ONext
      ON O.RowNumber = ONext.RowNumber - 1
LEFT OUTER JOIN #Job AS J
      ON J.RequiredDate >= O.WantDate
      AND (ONext.WantDate IS NULL OR J.RequiredDate < ONext.WantDate)
0
 

Author Closing Comment

by:holemania
ID: 39907081
Awesome.  That was it.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question