Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2016
    Posts
    4

    Answered: Pull data from separate tables based on Date

    I've looked through the forum here and couldn't find an answer that covered what I'm looking to do so hopefully one of you can help me out.

    I'm trying to combine some actual sales results from one table with some forecasted sales results in another table while excluding the overlap between the two. The tables below show a very simplified version of what I'm trying to combine. Basically, I want to created a combined table where I'll append the actual data, and then append the Forecast data that is AFTER the latest actual date. I'm sure this is simple but hoping someone could help me out. Thanks


    Actual data:
    QTY Sales_Date
    11 7/1/2016
    11 8/1/2016

    Forecast:
    QTY Forecast_Date
    99 7/1/2016
    99 8/1/2016
    99 9/1/2016
    99 10/1/2016


    Combined Table:
    QTY Forecast_Date
    11 7/1/2016
    11 8/1/2016
    99 9/1/2016
    99 10/1/2016

  2. Best Answer
    Posted by informer

    "Hi,

    So, modify request as follows


    Code:
    SELECT sale.QTY as qty, sale.Sales_Date as dateData, 'sale table' as origin
    FROM sale
    
    UNION
    
    SELECT forecast.QTY as qty , forecast.forecast_Date as dateData , 'forecast table' as origin
    FROM forecast LEFT JOIN sale ON forecast.forecast_Date = sale.Sales_Date
    WHERE (((sale.Sales_Date) Is Null))
    
    
    ORDER BY  qty ASC, dateData
    With this request you''ll obtain all sale records and forecast records without correspondance in sale table"


  3. #2
    Join Date
    May 2016
    Posts
    81
    Provided Answers: 3
    Hi johntravis667

    See my second response


    Last edited by informer; 09-07-16 at 06:03.

  4. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I giuess it comes down to waht you want, what the data is and waht the data should be.
    if you know you will always have a row for required date in the forecast table then I'd do a LEFT JOIN, using the forecast as the LHS of the join

    Access does joins a bit wierdly but in standard SQL its going to be something like

    Code:
    select s.qty as sales_qty, f.qty as forecast_qty, f.Forecast_Date from forecast as f
    LEFT JOIN sales as S on f.Forecast_Date = s.Sales_Date 
    Order by  f.Forecast_Date
    a refinement is then to only have 1 quantity (use the sales value if present), one way to do this is to use the NZ function
    Code:
    select nz(s.qty, f.qty) as qty, f.Forecast_Date from forecast as f
    LEFT JOIN sales as S on f.Forecast_Date = s.Sales_Date 
    Order by  f.Forecast_Date
    personally I'd probably run with returning both values the sales and the forecast as somewhere someone is going to want to do a comparative between the two. you can always do the null handling in the data consumer. an advantage if you have a lot of data is that you are not using a non native SQL function inside SQL, whereas the second version uses nz to supply the alternative value if s.qty is null

    you could use an iif
    Code:
    select iif( isnull(s.qty), f.qty, s.qty) as qty, f.Forecast_Date from forecast as f
    LEFT JOIN sales as S on f.Forecast_Date = s.Sales_Date 
    Order by  f.Forecast_Date

    this will only work if there are matching dates in the sales table, so if say your sales data was recorded against 02 April 2016 as opposed to the forecast 01 April 2016 then you would not get a sales value

    a way round that is to form the join using the year and month functions.

    Code:
    ...LEFT JOIN sales as S on (year(f.Forecast_Date) = year(s.Sales_Date) and month(f.Forecast_Date) = month(s.Sales_Date))

    if there is a possibility that there may be missing forecast data then left join on sales
    Code:
    select s.qty as sales_qty, f.qty as forecast_qty, s.Forecast_Date from sales as s
    LEFT JOIN forecast as f on s.Forecast_Date = f.Sales_Date 
    Order by  s.sales_Date
    if there is a possibility that there may be missing dates in either then use a calendar table as the LHS of the join and then LEFT join to both tables

    but whatever else you do do read up on JOIN's, how to use 'em, how to form 'em, and most important of all when to use 'em
    I'd rather be riding on the Tiger 800 or the Norton

  5. #4
    Join Date
    May 2016
    Posts
    81
    Provided Answers: 3
    An other way

    • Select only sale records which are also in forecast
    • Select forecast records not in sale table
    • and merge them with union command


    The code is as follows

    Code:
    SELECT sale.QTY as qty, sale.Sales_Date as dateData
    FROM sale INNER JOIN forecast ON sale.Sales_Date = forecast.Sales_Date
    
    UNION
    
    SELECT forecast.QTY as qty , forecast.forecast_Date as dateData 
    FROM forecast LEFT JOIN sale ON forecast.forecast_Date = sale.Sales_Date
    WHERE (((sale.Sales_Date) Is Null))
    
    
    ORDER BY  qty ASC, dateData

    I use DateData because date is a reserved word

    Result obtained

    11 07/01/2016
    11 08/01/2016
    99 09/01/2016
    99 10/01/2016


    Question:

    Is there always a forecast record for a sale record?
    Last edited by informer; 09-07-16 at 06:13.

  6. #5
    Join Date
    Sep 2016
    Posts
    4
    Quote Originally Posted by informer View Post
    An other way

    Question:

    Is there always a forecast record for a sale record?
    Thanks, I'll check this out today. As for your question, no. Basically I'll have thousands of entries in the forecast table, and thousands in the actual sales table, the month would be the only thing the is unique between the two. I may forecast we'll sell 1000 of one item and in the end we sell 900 of a similar item but not the same thing, or the customer may be different than forecasted. Basically, my goal is to just have the forecasted date there, but as soon as I have official sales information for a month it automatically cuts off that month of the forecast and replaces it with actual official information.

  7. #6
    Join Date
    May 2016
    Posts
    81
    Provided Answers: 3
    Hi,

    So, modify request as follows


    Code:
    SELECT sale.QTY as qty, sale.Sales_Date as dateData, 'sale table' as origin
    FROM sale
    
    UNION
    
    SELECT forecast.QTY as qty , forecast.forecast_Date as dateData , 'forecast table' as origin
    FROM forecast LEFT JOIN sale ON forecast.forecast_Date = sale.Sales_Date
    WHERE (((sale.Sales_Date) Is Null))
    
    
    ORDER BY  qty ASC, dateData
    With this request you''ll obtain all sale records and forecast records without correspondance in sale table
    Last edited by informer; 09-07-16 at 09:38.

  8. #7
    Join Date
    Sep 2016
    Posts
    4
    Thanks, I'll try it out today and reply. Also have to try the other version posted above.

  9. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why are you trying to create another table based on information already in your db. one of the cardinal rules in relationl db's is that you don't store derived information UNLESS there is a very good reason to do so.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #9
    Join Date
    Sep 2016
    Posts
    4
    Quote Originally Posted by healdem View Post
    why are you trying to create another table based on information already in your db. one of the cardinal rules in relationl db's is that you don't store derived information UNLESS there is a very good reason to do so.
    So, the actual problem I'm dealing with is combining data from multiple sources into one functional table.

    I have the following databases:

    Actual Sales - This is a historical database of all actual sales for the company and is updated monthly.

    Production Backlog - This is a collection of orders entered for production with a projected invoicing month (there is overlap with actual sales on the first month of this file and most recent sales month and they never exactly match so I want to replace that info with the actual sales data). *This was what I used in the example as projection.

    Projected Sales
    - This is our running sales projection. Some of the information in the backlog is in here but it goes further out and we always keep this in frozen versions so that we can make comparisons to actual sales and previous forecasts to gauge our accuracy. I have already linked this without any problem.

    All of these databases come from different parts of the company and have different purposes requiring their continued existence outside of what I'm putting together.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •