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?