I’m a huge fan of Bob’s Burgers. So today’s SQL tip will be featuring the Burger of the Day. We want to show burger records even on days when Bob hasn’t been grilling.
Let’s setup our use case. Our goal is to capture a record per day in the month of January for every Bob’s Burger of the Day ordered. (Note that this example uses SQL Server syntax.)
For the SQL part…we are going to create a temporary table and insert some burger records. I think I’m going to have to eat a burger at some point today…this data is making me hungry!
--This handy dandy 'delete if exists' is something I routinely put in for managing temp tables DROP TABLE if exists #tmp_BobsBurgers; --Create a temporary table to store records of Bob's Burgers of the Day CREATE TABLE #tmp_BobsBurgers ( Burger varchar(100) ,BurgerServedDate date ,BurgerOrderQuantity int ) ; --Inserting some burgers of the day data...YUM! INSERT INTO #tmp_BobsBurgers ( Burger ,BurgerServedDate ,BurgerOrderQuantity ) SELECT 'Foot Feta-ish Burger' as Burger ,'2018-01-02' as BurgerServedDate ,5 as BurgerOrderQuantity UNION ALL SELECT 'Tunami' as Burger ,'2018-01-05' as BurgerServedDate ,8 as BurgerOrderQuantity UNION ALL SELECT 'Mission A-Corn-Plished Burger - Comes with Corn Salsa' as Burger ,'2018-01-08' as BurgerServedDate ,3 as BurgerOrderQuantity UNION ALL SELECT 'Pepper Don''t Preach Burger' as Burger ,'2018-01-09' as BurgerServedDate ,11 as BurgerOrderQuantity UNION ALL SELECT 'Rest in Peas Burger' as Burger ,'2018-01-10' as BurgerServedDate ,4 as BurgerOrderQuantity UNION ALL SELECT 'Fig-eta Bout It Burger' as Burger ,'2018-01-12' as BurgerServedDate ,12 as BurgerOrderQuantity UNION ALL SELECT 'The Roquefort Files Burger' as Burger ,'2018-01-15' as BurgerServedDate ,5 as BurgerOrderQuantity UNION ALL SELECT 'Olive And Let Die Burger' as Burger ,'2018-01-16' as BurgerServedDate ,7 as BurgerOrderQuantity UNION ALL SELECT 'Krauted House Burger' as Burger ,'2018-01-25' as BurgerServedDate ,14 as BurgerOrderQuantity UNION ALL SELECT 'Hit Me With Your Best Shallot Burger' as Burger ,'2018-01-26' as BurgerServedDate ,10 as BurgerOrderQuantity UNION ALL SELECT 'Chorizo Your Own Adventure Burger' as Burger ,'2018-01-30' as BurgerServedDate ,3 as BurgerOrderQuantity ; --Now let's see our burgers! SELECT * FROM #tmp_BobsBurgers;

Problem is, Bob had to close shop during portions of January. (Apparently Hugo, the Health Inspector, was giving Bob some grief.) For those of you Tableau users, you are well aware how hard it is to report on days where there is no activity. How can we “fake” a January 1st burger record?
Luckily, there is a solution! We simply need to create a date dimension. The following date dimension is extremely minimal. You can add all sorts of date attributes like a weekend or holiday flag to your date dimension.
--Create a temporary table to hold our date values --Note this is just a simple example. You could add lots of other fields to your date dimension like first/last of month, fiscal stuff, etc. DROP TABLE if exists #tmp_DimDate; CREATE TABLE #tmp_DimDate ( DateKey Integer NOT NULL ,Date date ) ; --Adding a primary key to our new date dimension ALTER TABLE #tmp_DimDate ADD CONSTRAINT key_DimDate PRIMARY KEY (DateKey); --Let's declare some variables to store our begin and end dates as well as hold our looping variable DECLARE @StartDate date = '2018-01-01' DECLARE @EndDate date = '2018-01-31' DECLARE @DateLoop date --Insert just a month's worth of dates for January 2018 SET @DateLoop = @StartDate --Time to loop WHILE @DateLoop <= @EndDate BEGIN --Let's add the current loop date record to our temporary Date Dimension INSERT INTO #tmp_DimDate ( DateKey ,Date ) VALUES ( CONVERT(VARCHAR(10), @DateLoop, 112) --DateKey as YYYYMMDD ,@DateLoop --Date ) --Time to move/add the next date record so we need to add a day to our loop SET @DateLoop = DATEADD(d, 1, @DateLoop) END ;
Now, if we do a left join from our new date dimension to our burger data, we’ll be able to “fake” records.
SELECT dt.Date as BurgerServedDate ,ISNULL(bb.Burger,'No Burger of the Day') as Burger ,ISNULL(bb.BurgerOrderQuantity,0) as BurgerOrderQuantity FROM #tmp_DimDate dt left join #tmp_BobsBurgers bb on dt.Date = bb.BurgerServedDate
And voila! We now have one record per day for the entire month of January!
What is your favorite Bob’s Burger of the Day?