0

I have the below test data. There are 3 tables, sales table, sales delivery table and sales delivery months table.

I need to join all the tables together, so that the blue marked rows are connected to the blue marked rows and the red marked rows are connected to the red marked rows.

The join should use the From and To columns that exist in every table, I guess.

Update: I have tried the following:

SELECT *
  FROM Sales co 
  LEFT JOIN SalesDelivery cd
    ON co.SalesID = cd.SalesID 
       AND cd.From BETWEEN co.From AND co.To 
       AND cd.To BETWEEN co.From AND co.To
  LEFT JOIN SalesDeliveryMonth cdp 
    ON cd.SalesDeliveryID = cdp.SalesDeliveryID 
    AND cdp.From BETWEEN cd.From AND cd.To 
    AND cdp.To BETWEEN cd.From AND cd.To

Sales table:

 SalesID    Name    Revenue     From        To          Current row
    100     New CRM 250000.00   1800-01-01  2018-10-03  0
    100     New CRM 500000.00   2018-10-03  9999-12-31  1

SalesDelivery table:

 SalesID SalesDeliveryID SalesDeliveryName      Revenue   SalesStart    From  To    Current row
    100  AB100           New CRM                250000.00 2018-07-01    1800-01-01  2018-10-03  0
    100  AB100           New CRM                500000.00 2018-07-01    2018-10-03  9999-12-31  1
    100  ABM100          New CRM - maintenance  0.00      2018-07-01    2018-10-03  9999-12-31  1

SalesDeliveryMonths table:

RevenueMonth    Month   SalesDeliveryID SalesID From    To  Current row
833333.3333 2018-07-01  AB100   100 1800-01-01  2018-10-04  0
166666.6667 2018-07-01  AB100   100 2018-10-04  9999-12-31  1
833333.3333 2018-08-01  AB100   100 1800-01-01  2018-10-04  0
166666.6667 2018-08-01  AB100   100 2018-10-04  9999-12-31  1
833333.3333 2018-09-01  AB100   100 1800-01-01  2018-10-04  0
166666.6667 2018-09-01  AB100   100 2018-10-04  9999-12-31  1
uba2012
  • 373
  • 1
  • 3
  • 14
  • 3
    what have you tried so far? This is no code writing service! And do not post screens of data but copy the data in and format it! – veritaS Oct 04 '18 at 08:25
  • I don't know how to insert the data without making a total mess of it. – uba2012 Oct 04 '18 at 08:29
  • why is the salesid not unique? Also the salesdeliveryID is not unique! Don´t you have something like a positionID, because I dont see anything where you might be able to distinguish these datasets from each other other then revenue and from/to date. – veritaS Oct 04 '18 at 08:38
  • I get the data from an datawarehouse archive table. If a user in the sales system changes the value of the revenue a new row is added to the table and the old row is the NOT current anymore – uba2012 Oct 04 '18 at 08:41
  • One sale can have many saledeliveries and one saledelivery can have multiple saledelivery months – uba2012 Oct 04 '18 at 08:42
  • you could add the current row to your ON statement "and cd.currentrow = co.currentorw" try first with one join. Also you should maybe fix the years of the FROM/TO where it sais 9999 because otherwise you will have a hard time adding them. Try with one join first until it is like you expect, then add the other one – veritaS Oct 04 '18 at 08:54
  • Please read & act on [mcve]. That includes a clear specification of how output is a function of input. This post is not clear about that. Your table images should have been text but now that they are it is not clear what you are referring to by red & blue. But that was never clear anyway. Tell us for a row (salesID, name, ...) exactly the condition for it to be in the result--either in terms of base tables or the business situation. If the latter, tell us the same thing for each base table. – philipxy Oct 04 '18 at 09:33
  • @veritaS Constraints are not needed to query. – philipxy Oct 04 '18 at 09:35
  • hey @philipxy , I dont understand your comment. Of course constraints are not needed to query data. But when you want to join you would need at least a few columns to be at least together somewhat unique to be able to join them in a way to be usefull. can you explain what you mean? – veritaS Oct 04 '18 at 10:08
  • ok the last table was wrong just updated it – uba2012 Oct 04 '18 at 12:17
  • Posters get notice of comments but when there is more than one commenter other than oneself one must write @username for one non-poster commenter to be notified. PS Code questions must have a [mcve]. That includes cut & paste & runnable code with input & expected output & a clear specification. Show that your program does what you expect it to as it goes through (sub)expressions by saying what that is & showing that it does it via incremental output. Part of justifying a MCVE is finding a working example for maximal partial functionality. Ask about the (small) difference between the examples. – philipxy Oct 04 '18 at 20:44
  • @veritaS You are saying constraints affect querying. "to join" is to query. "you would need at least a few columns to be ..." No. Meanings of base & result tables are necessary & sufficient, and constraints (CKs, PKs, FKs, UNIQUE, cardinalities, etc) are neither. Base table meanings & situations/states that can arise determine the constraints. See [How do I find relations between tables that are long-distance related? MySQL](https://stackoverflow.com/a/44124759/3404097)& its links or [similar answers by me](https://stackoverflow.com/search?q=user%3A3404097+constraints+not+needed+query). – philipxy Oct 04 '18 at 20:55

0 Answers0