Wind can be an ally or an enemy of the fisherman. Both in terms of comfort and in changing the mood and location of the fish, wind is something that can’t be ignored. As it relates to the fish, wind can often turn fish on. The term “muskie chop” refers to medium sized waves that can help create good conditions for fishing. The wind does a couple things: it restricts the light by creating waves that break up the sun, and it also creates a current that can move fish to specific locations that can be targeted. The other factor to consider related to wind if fisherman comfort. I love fishing the colder months, but you’d better make sure you’re dressed for the weather. There is no indoors in a fishing boat, so if it’s going to be windy and cold, bundle up. At the same time on those hot, sunny, humid July days, you may not want to even be out unless there is some wind to cool you down. Keeping all these factors in mind, it’s important to remember that wind is strongest when it has a large open space to build up it force. If you want to avoid the wind, head to the upwind side of the lake. If you want to embrace the wind, head to the downwind side.
In SQL Server, a recursive common table expression (CTE) could be compared to wind building up power as it moves over the lake. A recursive CTE will call itself, and in doing so use the previous results to build to a final results set.
I recently had a perfect use case for this concept. I had to take dollars given to me on a monthly level and distribute it to each day within the month. Using a recursive CTE, I told SQL Server to give me the monthly total divided by the days in the month for each day in the month. Below is an example of how I set it up:
CREATE TABLE #SalesTotalsByMonth ( FirstOfMonth DATE , Channel VARCHAR(10) , SalesTotal DECIMAL(10 , 2) ); INSERT INTO #SalesTotalsByMonth ( FirstOfMonth , Channel , SalesTotal ) VALUES ( '2016-01-01' , 'Web' , 165473.99 ), ( '2016-01-01' , 'In-store' , 56998.45 ), ( '2016-01-01' , 'Mail' , 4645.85 ) , ( '2016-02-01' , 'Web' , 27463.56 ), ( '2016-02-01' , 'In-store' , 61423.78 ), ( '2016-02-01' , 'Mail' , 5341.56 ) , ( '2016-03-01' , 'Web' , 487356.67 ), ( '2016-03-01' , 'In-store' , 15734.56 ), ( '2016-03-01' , 'Mail' , 3104.85 ) , ( '2016-04-01' , 'Web' , 478236.78 ), ( '2016-04-01' , 'In-store' , 24675.67 ), ( '2016-04-01' , 'Mail' , 1024.56 ) , ( '2016-05-01' , 'Web' , 167524.89 ), ( '2016-05-01' , 'In-store' , 31672.78 ), ( '2016-05-01' , 'Mail' , 1798.67 ) , ( '2016-06-01' , 'Web' , 347652.19 ), ( '2016-06-01' , 'In-store' , 41675.19 ), ( '2016-06-01' , 'Mail' , 801.78 ) , ( '2016-07-01' , 'Web' , 247653.02 ), ( '2016-07-01' , 'In-store' , 59713.02 ), ( '2016-07-01' , 'Mail' , 2097.19 ) , ( '2016-08-01' , 'Web' , 891642.23 ), ( '2016-08-01' , 'In-store' , 67134.23 ), ( '2016-08-01' , 'Mail' , 3752.02 ) , ( '2016-09-01' , 'Web' , 342591.24 ), ( '2016-09-01' , 'In-store' , 77123.24 ), ( '2016-09-01' , 'Mail' , 2406.23 ) , ( '2016-10-01' , 'Web' , 246758.25 ), ( '2016-10-01' , 'In-store' , 81214.24 ), ( '2016-10-01' , 'Mail' , 3012.24 ) , ( '2016-11-01' , 'Web' , 267423.26 ), ( '2016-11-01' , 'In-store' , 91023.26 ), ( '2016-11-01' , 'Mail' , 2034.24 ) , ( '2016-12-01' , 'Web' , 265219.56 ), ( '2016-12-01' , 'In-store' , 34167.02 ), ( '2016-12-01' , 'Mail' , 1010.26 ); WITH recurse AS ( SELECT stbm.Channel , stbm.SalesTotal / DATEDIFF(DAY , stbm.FirstOfMonth , DATEADD(MONTH , 1 , stbm.FirstOfMonth)) AS Revenue , DATEDIFF(DAY , stbm.FirstOfMonth , DATEADD(MONTH , 1 , stbm.FirstOfMonth)) AS daysleft , stbm.FirstOfMonth AS [Sales Day] FROM #SalesTotalsByMonth stbm UNION ALL SELECT recurse.Channel , recurse.Revenue , recurse.daysleft - 1 , DATEADD(DAY , 1 , recurse.[Sales Day]) FROM recurse WHERE recurse.daysleft > 1 ) SELECT recurse.[Sales Day] , recurse.Channel , SUM(recurse.Revenue) AS Revenue FROM recurse GROUP BY recurse.Channel , recurse.[Sales Day]; DROP TABLE #SalesTotalsByMonth;
The important thing to note here is the general pattern for a recursive CTE – the initial expression with a UNION ALL that calls the CTE. Be sure to put the upper limit in the WHERE clause of the bottom half to avoid infinite recursion.
My final results gave me the total per day.