{"id":443,"date":"2013-07-29T16:53:21","date_gmt":"2013-07-29T20:53:21","guid":{"rendered":"http:\/\/happytechnologist.com\/?p=379"},"modified":"2024-06-24T16:47:29","modified_gmt":"2024-06-24T20:47:29","slug":"adding-a-quick-lunar-calendar-to-the-data-mart","status":"publish","type":"post","link":"https:\/\/www.chiplynch.com\/wordpress\/?p=443","title":{"rendered":"Adding a quick lunar calendar to the data mart"},"content":{"rendered":"<p>I ran across an article at Popular Science citing research that correlated <a href=\"http:\/\/www.popsci.com\/science\/article\/2013-07\/thinking-having-heart-surgery-save-it-full-moon\">Heart Surgery outcomes to the Lunar Calendar<\/a>. That&#8217;s right&#8230; the claim is that you are more likely to survive and to spend less time in the hospital recovering from heart surgery if you get it during a waning full moon. \u00c2\u00a0We know the moon can affect us in ways that make sense&#8230; for example an article popped up this week about <a href=\"http:\/\/www.bbc.co.uk\/news\/health-23405941\">the full moon disturbing sleep<\/a>\u00c2\u00a0&#8212; it&#8217;s brighter, so it&#8217;s harder to sleep. \u00c2\u00a0Ok. \u00c2\u00a0And <a href=\"http:\/\/www.livescience.com\/1617-strange-happen-full-moon.html\">crime increases during full moons<\/a>\u00c2\u00a0could make some sense too due to increased night visibility or such. \u00c2\u00a0But medical outcomes?<\/p>\n<p>Well, this gives us something new to play with, so let&#8217;s find out! \u00c2\u00a0First, I&#8217;m working on the data warehouse I&#8217;ve been working on for the last few months (wow, almost a year). \u00c2\u00a0It has a well behaved date dimension table, so we&#8217;re going to add a phase-of-the-moon calculation. \u00c2\u00a0The basic logic for doing that is:<\/p>\n<ul>\n<li>Lunar month is 29.530588853 days<\/li>\n<li>Jan 1, 1900 at 13:52 PM GMT was a full moon<\/li>\n<li>There are 9 indicators of the Lunar cycle, but it basically has 4 phases:\n<ul>\n<li>Waxing Crescent when Lunar month is in the first quarter (29.530588853\/4)<\/li>\n<li>Waxing Gibbous when Lunar month is in the second quarter<\/li>\n<li>Waning Gibbous when Lunar month is in the third quarter<\/li>\n<li>Waning Crescent when Lunar month is in the last quarter ( &gt; 29.530588853*3\/4)<\/li>\n<\/ul>\n<\/li>\n<li>The other five are specific indicators, which we will round to the nearest day:\n<ul>\n<li>New Moon is day 1<\/li>\n<li>Dark Moon is day &gt; 28.53<\/li>\n<li>First Quarter is day between 6.88 and 7.88 (29.530588853 \/ 4 +- 0.5)<\/li>\n<li>Full Moon is day between 14.265 and 15.265 (29.530588853\/2 +- 0.5)<\/li>\n<li>Last Quarter is day between 21.648 and 22.648 (29.530588853 *3 \/ 4 +- 0.5)<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p><!--more-->There were other variations available, and really this calculation could be done on-the-fly, but this was fun enough. \u00c2\u00a0Here&#8217;s some SQL&#8230; note the 13:52 HH:MM is probably unnecessary but, why not?:<\/p>\n<pre><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\n\nupdate DD set\n MOON_PHASE =\n CASE\n WHEN DATEDIFF(DAY,'1900-01-01 13:52:00.000',PK_DATE) % 29.530588853 &amp;amp;lt;= 1 THEN 'New Moon'\n WHEN DATEDIFF(DAY,'1900-01-01 13:52:00.000',PK_DATE) % 29.530588853 BETWEEN 1 and 6.88 THEN 'Waxing Crescent'\n WHEN DATEDIFF(DAY,'1900-01-01 13:52:00.000',PK_DATE) % 29.530588853 BETWEEN 6.88 and 7.88 THEN 'First Quarter'\n WHEN DATEDIFF(DAY,'1900-01-01 13:52:00.000',PK_DATE) % 29.530588853 BETWEEN 7.88 and 14.265 THEN 'Waxing Gibbous'\n WHEN DATEDIFF(DAY,'1900-01-01 13:52:00.000',PK_DATE) % 29.530588853 BETWEEN 14.265 and 15.265 THEN 'Full Moon'\n WHEN DATEDIFF(DAY,'1900-01-01 13:52:00.000',PK_DATE) % 29.530588853 BETWEEN 15.265 and 21.648 THEN 'Waning Gibbous'\n WHEN DATEDIFF(DAY,'1900-01-01 13:52:00.000',PK_DATE) % 29.530588853 BETWEEN 21.648 and 22.648 THEN 'Last Quarter'\n WHEN DATEDIFF(DAY,'1900-01-01 13:52:00.000',PK_DATE) % 29.530588853 BETWEEN 22.648 and 28.53 THEN 'Waning Crescent'\n WHEN DATEDIFF(DAY,'1900-01-01 13:52:00.000',PK_DATE) % 29.530588853 &amp;amp;gt;= 28.53 THEN 'Dark Moon'\n ELSE 'Apocalypse' END\n from Dimensions.D_DATE DD\n\n<\/pre><\/pre>\n<p>So now everything that we have tied to a specific date (date of surgery, birth date, etc.) can now be tied to a lunar cycle! \u00c2\u00a0Great! \u00c2\u00a0So now what?<\/p>\n<p>The Data Warehouse this is on top of has several million claims over the years of Medical, Dental, Vision, Mental Health, and other categories. \u00c2\u00a0Since I can&#8217;t publish any real information due to privacy and proprietary restrictions, I&#8217;ve had to dumb this down substantially, but, at least for my very-unscientific data-set, I can absolutely verify a spike in mental and behavioral health claims after a full moon:<\/p>\n<p>\u00a0<\/p>\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"792\" height=\"227\" src=\"https:\/\/www.chiplynch.com\/wordpress\/wp-content\/uploads\/2024\/06\/Lunar-Cost-by-Claim-Type-1.jpg\" alt=\"Heatmap of expenditures relative to baseline (average) by lunar phase.\" class=\"wp-image-1415\" srcset=\"https:\/\/www.chiplynch.com\/wordpress\/wp-content\/uploads\/2024\/06\/Lunar-Cost-by-Claim-Type-1.jpg 792w, https:\/\/www.chiplynch.com\/wordpress\/wp-content\/uploads\/2024\/06\/Lunar-Cost-by-Claim-Type-1-300x86.jpg 300w, https:\/\/www.chiplynch.com\/wordpress\/wp-content\/uploads\/2024\/06\/Lunar-Cost-by-Claim-Type-1-150x43.jpg 150w, https:\/\/www.chiplynch.com\/wordpress\/wp-content\/uploads\/2024\/06\/Lunar-Cost-by-Claim-Type-1-768x220.jpg 768w\" sizes=\"auto, (max-width: 792px) 100vw, 792px\" \/><\/figure>\n\n\n<p>The chart shows the relative percent of payments\u00c2\u00a0<em>per claim<\/em>, to adjust for the relative length of the phases (since the New Moon, First Quarter, Last Quarter, and Dark Moon are one day long each and the rest are roughly 6 days each). \u00c2\u00a0Immediately after the full moon, the average price per claim for Behavioral Health spikes to 151% of average. \u00c2\u00a0Again, this is wildly unscientific&#8230; any number of rationale could exist other than a true impact on human behavior, but we certainly can&#8217;t rule that OUT in this data.<\/p>\n<p>I was NOT able to replicate the results of the\u00c2\u00a0cardiothoracic surgery study, but this was a really fun exercise that didn&#8217;t take long at all.<\/p>\n<p>Just for more fun, I did something similar with Zodiac Sign&#8230; I&#8217;ll leave the actual analysis up to you, but here&#8217;s some code that will help generate the zodiac signs in your date dimension (Transact SQL syntax, but that&#8217;s easily repaired&#8230; thanks to <a href=\"http:\/\/www.midnightdba.com\/Jen\/2011\/07\/meme-monday-sqlstrology-table-valued-zodiac-function\/\">http:\/\/www.midnightdba.com\/Jen\/2011\/07\/meme-monday-sqlstrology-table-valued-zodiac-function\/<\/a>):<\/p>\n<pre><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nDECLARE @SQLStrology TABLE (\nZodiacSign VARCHAR(20),\nSTARTMMDD VARCHAR(20),\nENDMMDD VARCHAR(20),\nQuickCharacteristics NVARCHAR(400));\n\nINSERT INTO @SQLStrology (ZodiacSign, STARTMMDD, ENDMMDD, QuickCharacteristics)\nVALUES ('Aries', '03\/21', '04\/19', 'Active, Demanding, Determined, Effective, Ambitious'),\n('Taurus', '04\/20', '05\/20', 'Security, Subtle strength, Appreciation, Instruction, Patience'),\n('Gemini', '05\/21', '06\/20', 'Communication, Indecision, Inquisitive, Intelligent, Changeable'),\n('Cancer', '06\/21', '07\/22', 'Emotion, Diplomatic, Intensity, Impulsive, Selective'),\n('Leo', '07\/23', '08\/22', 'Ruling, Warmth, Generosity, Faithful, Initiative'),\n('Virgo', '08\/23', '09\/22', 'Analyzing, Practical, Reflective, Observation, Thoughtful'),\n('Libra', '09\/23', '10\/22', 'Balance, Justice, Truth, Beauty, Perfection'),\n('Scorpio', '10\/23', '11\/21', 'Transient, Self-Willed, Purposeful, Unyielding'),\n('Sagittarius', '11\/22', '12\/21', 'Philosophical, Motion, Experimentation, Optimism'),\n('Capricorn', '12\/22', '12\/31', 'Determination, Dominance, Perservering, Practical, Willful'),\n('Capricorn', '01\/01', '01\/19', 'Determination, Dominance, Perservering, Practical, Willful'), -- Split Capricorn to manage year boundaries\n('Aquarius', '01\/20', '02\/18', 'Knowledge, Humanitarian, Serious, Insightful, Duplicitous'),\n('Pisces', '02\/19', '03\/20', 'Fluctuation, Depth, Imagination, Reactive, Indecisive');\n\nUPDATE DD SET\nZodiacSign = SS.ZodiacSign\nFROM\nDimensions.D_DATE DD\ninner join @SQLStrology SS on\nright(replicate('0',2)+CAST(MONTH(PK_DATE) AS varchar(2)),2) + '\/' + right(replicate('0',2)+CAST(DAY(PK_DATE) AS VARCHAR(2)),2)\nBETWEEN SS.STARTMMDD AND SS.ENDMMDD\n\n<\/pre><\/pre>","protected":false},"excerpt":{"rendered":"<p>I ran across an article at Popular Science citing research that correlated Heart Surgery outcomes to the Lunar Calendar. That&#8217;s right&#8230; the claim is that you are more likely to survive and to spend less time in the&#8230; <a class=\"read-more\" href=\"https:\/\/www.chiplynch.com\/wordpress\/?p=443\">Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[29,30],"tags":[],"class_list":["post-443","post","type-post","status-publish","format-standard","hentry","category-data","category-healthcare"],"_links":{"self":[{"href":"https:\/\/www.chiplynch.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/443","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.chiplynch.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.chiplynch.com\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.chiplynch.com\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.chiplynch.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=443"}],"version-history":[{"count":1,"href":"https:\/\/www.chiplynch.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/443\/revisions"}],"predecessor-version":[{"id":1416,"href":"https:\/\/www.chiplynch.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/443\/revisions\/1416"}],"wp:attachment":[{"href":"https:\/\/www.chiplynch.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=443"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.chiplynch.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=443"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.chiplynch.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=443"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}