{"id":478,"date":"2016-06-08T16:59:16","date_gmt":"2016-06-08T08:59:16","guid":{"rendered":"https:\/\/eligiblestore.com\/blog\/?p=478"},"modified":"2020-06-30T21:35:14","modified_gmt":"2020-06-30T13:35:14","slug":"actual-nights","status":"publish","type":"post","link":"https:\/\/eligiblestore.com\/blog\/2016\/06\/08\/actual-nights\/","title":{"rendered":"Actual Nights"},"content":{"rendered":"<p>So real case scenario, I was asked to calculate real nights in a month. Below are the sample scenarios:<\/p>\n<ul>\n<li>If guest checked <strong>in<\/strong> at <strong>2016-04-23<\/strong>, checked <strong>out<\/strong> at <strong>2016-05-01<\/strong>, actual nights for May 2016 = <strong>0<\/strong> night<\/li>\n<li>If guest checked <strong>in<\/strong> at <strong>2016-04-23<\/strong>, checked <strong>out<\/strong> at <strong>2016-05-05<\/strong>, actual nights for May 2016 = <strong>4<\/strong> nights<\/li>\n<li>If guest checked <strong>in<\/strong> at <strong>2016-05-23<\/strong>, checked <strong>out<\/strong> at <strong>2016-05-27<\/strong>, actual nights for May 2016 = <strong>4<\/strong> nights<\/li>\n<li>If guest checked <strong>in<\/strong> at <strong>2016-05-27<\/strong>, checked <strong>out<\/strong> at <strong>2016-06-07<\/strong>, actual nights for May 2016 = <strong>5<\/strong> nights<\/li>\n<li>If guest checked <strong>in<\/strong> at <strong>2016-04-27<\/strong>, checked <strong>out<\/strong> at <strong>2016-06-07<\/strong>, actual nights for May 2016 = <strong>31<\/strong> nights<\/li>\n<\/ul>\n<p>Here&#8217;s how I do it:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\n\r\n-- =============================================\r\n-- Author:\t\tDavid B\r\n-- Created date: 2016-04-07\r\n-- Description:\tCalculating how many nights for a period of stay in a month\r\n-- How to use:  select * from &#x5B;dbo].&#x5B;Fn_nightsmonth]('2015-01-25','2015-02-05',1 , 2015)\r\n-- =============================================\r\nCREATE FUNCTION &#x5B;dbo].&#x5B;Fn_nightsmonth](@StartDate datetime, @EndDate datetime, @for_month int, @for_year int)\r\nRETURNS TABLE\r\nAS\r\nRETURN\r\nSelect\r\nCASE\r\n\tWHEN (DATEPART(MONTH, @StartDate) = @for_month and DATEPART(MONTH, @EndDate) = @for_month) and ((DATEPART(YEAR, @StartDate) = @for_year or DATEPART(YEAR, @EndDate) = @for_year)) THEN \r\n\t\tDATEDIFF(DAY, @StartDate,@EndDate) \t\r\n\tWHEN (@StartDate &lt; cast(CONVERT(varchar(4), @for_year) + '-' + CONVERT(varchar(2), @for_month) + '-01' as date)) and (@EndDate between (cast(CONVERT(varchar(4), @for_year) + '-' + CONVERT(varchar(2), @for_month) + '-01' as date)) and (cast(DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, cast( CONVERT(varchar(4), @for_year) + '-' + CONVERT(varchar(2), @for_month) + '-01' as date)) + 1, 0)) as date))) THEN\r\n\t\tDATEDIFF(DAY, DATEADD(MONTH, DATEDIFF(MONTH, -1, @EndDate)-1, 0),@EndDate)\r\n\tWHEN (@EndDate &gt; cast(DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, cast( CONVERT(varchar(4), @for_year) + '-' + CONVERT(varchar(2), @for_month) + '-01' as date)) + 1, 0)) as date)) and (@StartDate between (cast(CONVERT(varchar(4), @for_year) + '-' + CONVERT(varchar(2), @for_month) + '-01' as date)) and (cast(DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, cast( CONVERT(varchar(4), @for_year) + '-' + CONVERT(varchar(2), @for_month) + '-01' as date)) + 1, 0)) as date))) THEN\r\n\t\tDATEDIFF(DAY, @StartDate,DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @StartDate) + 1, 0))) + 1 \r\n\tWHEN ((DATEDIFF(DAY, @StartDate, cast(DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, cast( CONVERT(varchar(4), @for_year) + '-' + CONVERT(varchar(2), @for_month) + '-01' as date)) + 1, 0)) as date)) &gt;= 0) and (DATEDIFF(DAY, cast(CONVERT(varchar(4), @for_year) + '-' + CONVERT(varchar(2), @for_month) + '-01' as date), @EndDate) &gt;= 0)) THEN\r\n\t\tDATEDIFF(DAY, cast( CONVERT(varchar(4), @for_year) + '-' + CONVERT(varchar(2), @for_month) + '-01' as datetime), DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, cast( CONVERT(varchar(4), @for_year) + '-' + CONVERT(varchar(2), @for_month) + '-01' as datetime)) + 1, 0))) + 1\r\n\tELSE\r\n\t\t0\r\nEND as &#x5B;DD]\t\t\t\r\n<\/pre>\n<p>And what if you need between a range but not in a month let say weekly, then you can use below query:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\n\r\n-- =============================================\r\n-- Author:\t\tDavid B\r\n-- Created date: 2016-06-07\r\n-- Description:\tCalculating how many nights overlap between 2 date ranges\r\n-- How to use:  select * from &#x5B;dbo].&#x5B;Fn_dayoverlap]('2000-01-25','2000-02-05','2000-01-01','2000-01-31')\r\n-- =============================================\r\nCREATE FUNCTION &#x5B;dbo].&#x5B;Fn_dayoverlap](@StartDate1 datetime, @EndDate1 datetime, @StartDate2 datetime, @EndDate2 datetime)\r\nRETURNS TABLE \r\nAS\r\nRETURN\r\nSELECT CASE \r\n\tWHEN (NOT((@EndDate2 &lt; @StartDate1) or (@StartDate2 &gt; @EndDate1))) THEN \r\n\t\tDATEDIFF(DAY, \r\n\t\tCASE WHEN @StartDate1 &gt; @StartDate2 THEN @StartDate1 ELSE @StartDate2 END,\r\n\t\tCASE WHEN @EndDate1 &lt; @EndDate2 AND NOT(@EndDate1 &gt;= @StartDate2) and (@StartDate2 &gt; @StartDate1) THEN DATEADD(day,1,@EndDate1) \r\n\t\t\tWHEN (@EndDate1 &lt;= @EndDate2 AND (@EndDate1 &gt;= @StartDate2)) or (@StartDate1 &gt; @StartDate2 and @EndDate1 &lt; @EndDate2) THEN @EndDate1 \r\n\t\t\tWHEN (@EndDate2 &lt; @EndDate1 and (@StartDate1 &gt; @StartDate2)) or (@StartDate2 &gt; @StartDate1 and @EndDate1 &gt; @EndDate2) or (@EndDate2 = @StartDate1) THEN DATEADD(day,1,@EndDate2) \r\n\t\t\tWHEN @EndDate2 &lt; @EndDate1 AND not (@EndDate2 = @StartDate1) THEN @EndDate2 END)\r\n\tELSE\r\n\t\t0\r\nEND AS &#x5B;DAY]\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>So real case scenario, I was asked to calculate real nights in a month. Below are the sample scenarios: If guest checked in at 2016-04-23, checked out at 2016-05-01, actual nights for May 2016 = 0 night If guest checked in at 2016-04-23, checked out at 2016-05-05, actual nights for May 2016 = 4 nights [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":156,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[9],"tags":[26,16],"class_list":["post-478","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-programming-database","tag-nights","tag-sql"],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p8pg5H-7I","jetpack-related-posts":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/eligiblestore.com\/blog\/wp-content\/uploads\/2016\/03\/logo-sql.png?fit=810%2C441&ssl=1","_links":{"self":[{"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/posts\/478","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/comments?post=478"}],"version-history":[{"count":1,"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/posts\/478\/revisions"}],"predecessor-version":[{"id":1264,"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/posts\/478\/revisions\/1264"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/media\/156"}],"wp:attachment":[{"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/media?parent=478"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/categories?post=478"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/tags?post=478"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}