-2

My goal is to join a sales program table to a calendar table so that there would be a joined table with the full trailing 52 weeks by day, and then the sales data would be joined to it. The idea would be that there are nulls I could COALESCE after the fact. However, my problem is that I only get results without nulls from my sales data table.

The questions I've consulted so far are:

Join to Calendar Table - 5 Business Days

Joining missing dates from calendar table Which points to

MySQL how to fill missing dates in range?

My Calendar table is all 364 days previous to today (today being day 0). And the sales data has a program field, a store field, and then a start date and an end date for the program.

Here's what I have coded:

SELECT 
    CAL.DATE,
    CAL.DAY,
    SALES.ITEM,
    SALES.PROGRAM,
    SALES.SALE_DT,
    SALES.EFF_BGN_DT,
    SALES.EFF_END_DT
  FROM 
    CALENDAR_TABLE AS CAL
  LEFT JOIN 
    SALES_TABLE AS SALES
  ON CAL.DATE = SALES.SALE_DT
  WHERE 1=1
    and SALES.ITEM = 1 or SALES.ITEM is null
  ORDER BY DATE ASC

What I expected was 365 records with dates where there were nulls and dates where there were filled in records. My query resulted in a few dates with null values but otherwise just the dates where a program exists.

   DATE   |  ITEM  | PROGRAM | SALE_DT  | PRGM_BGN  | PRGM_END  |
----------|--------|---------|----------|-----------|-----------|
8/27/2020 |        |         |          |           |           |
8/26/2020 |        |         |          |           |           |
8/25/2020 |        |         |          |           |           |
8/24/2020 |        |         |          |           |           |
6/7/2020  |    1   |    5    | 6/7/2020 | 2/13/2016 |  6/7/2020 |
6/6/2020  |    1   |    5    | 6/6/2020 | 2/13/2016 |  6/7/2020 |
6/5/2020  |    1   |    5    | 6/5/2020 | 2/13/2016 |  6/7/2020 |
6/4/2020  |    1   |    5    | 6/4/2020 | 2/13/2016 |  6/7/2020 |

Date = Calendar day.
Item = Item number being sold.
Program = Unique numeric ID of program.
Sale_Dt = Field populated if at least one item was sold under this program.
Prgm_bgn = First day when item was eligible to be sold under this program.
Prgm_end = Last day when item was eligible to be sold under this program.

What I would have expected would have been records between June 7 and August 24 which just had the DATE column populated for each day and null values as what happens in the most recent four records.

I'm trying to understand why a calendar table and what I've written are not providing the in-between dates.

EDIT: I've removed the request for feedback to shorten the question as well as an example I don't think added value. But please continue to give feedback as you see necessary.

OrangeDave
  • 27
  • 5
  • 2
    [How to create a Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) :o) – Mikhail Berlyant Aug 27 '20 at 19:09
  • Thanks! I'll look at that right now so I can try to update my question with it. – OrangeDave Aug 27 '20 at 19:11
  • 1
    What is your 1 specific researched non-duplicate question? "Any feedback" is not a valid SO question. [ask] [help] Also you can google re asking via site:meta.SO or site:meta.SE. (Or ask, but research, including what is on-topic there.) Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. – philipxy Aug 27 '20 at 19:31
  • 1
    Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. – philipxy Aug 27 '20 at 19:33
  • Is your `where` clause correct? Note the order of operations for and/or. Consider rewriting as `WHERE IFNULL(SALES.ITEM,1) = 1`. – rtenha Aug 27 '20 at 20:59
  • It didn't seem to make a difference when I removed the where clause all together or used either of the conditions or used both at the same time. So, I left them. (The 1=1 is just something that visually helps me find it and only ever have to type AND when creating conditions). However, I think I have an answer, so I'm going to paste it as an answer to my question. – OrangeDave Aug 27 '20 at 21:34
  • Please clarify via edits, not comments. – philipxy Aug 27 '20 at 21:37

1 Answers1

0

I'd be more than happy to delete this whole question or have someone else give a better answer, but after staring at the logic in some of the answers in this thread (MySQL how to fill missing dates in range?) long enough, I came up with this:

SELECT
  CAL.DATE,
  t.* EXCEPT (DATE)
FROM  
  CALENDER_TABLE AS CAL
LEFT JOIN
      (SELECT 
        CAL.DATE,
        CAL.DAY,
        SALES.ITEM,
        SALES.PROGRAM,
        SALES.SALE_DT,
        SALES.EFF_BGN_DT,
        SALES.EFF_END_DT
      FROM 
        CALENDAR_TABLE AS CAL
      LEFT JOIN 
        SALES_TABLE AS SALES
      ON CAL.DATE = SALES.SALE_DT
      WHERE 1=1
        and SALES.ITEM = 1 or SALES.ITEM is null
      ORDER BY DATE ASC) **t**
ON CAL.DATE = t.DATE

From what I can tell, it seems to be what I needed. It allows for the subquery to connect a date to all those records, then just joins on the calendar table again solely on date to allow for those nulls to be created.

OrangeDave
  • 27
  • 5