Extracting time availability from my calendar table

Question

I have a calendar in a table and I should take out the equipment availability per month until today's date. The table is as follows:

create table period
(GCP_START date, GCP_END date, 
 GCP_MONSTART int, GCP_MONEND int, 
 GCP_TUESSTART int, GCP_TUESEND int,
 GCP_WEDSTART int, GCP_WEDEND int, 
 GCP_THURSSTART int, GCP_THURSEND int,
 GCP_FRISTART int, GCP_FRIEND int,
 GCP_SATSTART int, GCP_SATEND int,
 GCP_SUNSTART int, GCP_SUNEND int);
 
 insert into period values
 ('2020-10-25', '2020-10-31', 360, 1320, 360, 1320, 360, 1320, 360, 1320, 360, 1320, NULL, NULL, NULL, NULL), 
('2020-11-01', '2020-11-30', 360, 1320, 360, 1320, 360, 1320, 360, 1320, 360, 1320, NULL, NULL, NULL, NULL), 
('2020-12-01', '2020-12-23', 360, 1320, 360, 1320, 360, 1320, 360, 1320, 360, 1320, NULL, NULL, NULL, NULL), 
('2020-12-24', '2020-12-24', 360, 720,  360,  720, 360,  720, 360,  720, 360,  720, NULL, NULL, NULL, NULL), 
('2020-12-27', '2020-12-31', 480, 1020, 480, 1020, 480, 1020, 480, 1020, 480, 1020, NULL, NULL, 360, 720);

Every numeric value corresponds to a start or end in minutes (i.e. 360 -> 6:00 AM, 1320 -> 10:00 PM; the difference between 1320 and 360 is 960 minutes and is the effective availability of equipment on the day in example). Every record in the "period" table doesn't cross 2 or more months: is only a portion of 1 month. Given the above dataset, I'd like to get the following results:

(Period, minutes_of availability)

    ('2020-10', 4800), <---- 1 Monday = 960 minutes + 1 Tuesday = 960 minuntes + 1 Wednesday = 960 minutes .....
    ('2020-11', 20160), <---- 5 Monday = 4800 minutes + 4 Tuesday = 3840 minutes + 4 Wednesday = 3840 minutes .....
    ('2020-12', 17220), <---- 3 Monday (until 23 December and before today) = 2880 minutes + 4 (until 23 December and before today) ..... 24 December ha a different availability, after 27 too

Moreover the calculation should stop with the "current day" so I don't want to calculate for the whole calendar. Can someone help me to solve this problem? My database engine is SQL Server.

Over

Love to help, what have you researched, tried, and got stuck on? — Dale K, Dec 27 '20 at 00:45
Post DDL, not a textual description of your table. And rather than force everyone to guess how you came up with "1 Monday = 960 minutes + ..." write the actual formula you use and the source of the values used. To you it may seem obvious, but it might not to others. And you seem to assume that the dates for a row do NOT cross a month boundary. Is that a safe assumption? Does the table have constraints to guarantee it? If not, do you STILL think it is a safe assumption? — SMor, Dec 27 '20 at 03:06
By "sysdate" do you mean [`SysDateTime`](https://learn.microsoft.com/en-us/sql/t-sql/functions/sysdatetime-transact-sql?view=sql-server-ver15)? Does that mean that you want the calculation to run up to the current time-of-day, not just the date? — HABO, Dec 27 '20 at 04:16
@over thats where you find yourself a SQL tutorial and learn the basics... SO is not a tutorial site. You know how to calculate the amounts manually, so just translate that into a query. Unless I'm mistaken its basic math per row, then maybe a group by/sum - pretty straight forward. — Dale K, Dec 27 '20 at 08:56
@SMor, what do you mean for "post DDL" ? ...I suppose the "create table" statement, is it right ? The formula is (i.e. for Monday) "GCP_MONEND - GCP_MONSTART" , but I didn't want to write poems to not annoying to much people, I'm sorry for such missing info. A record in my table don't have to cross two or more months, my table doesn't have constraints to guarantee it but I can create something similar as end user. Thanks for your answer. — over, Dec 27 '20 at 08:59
Since you know the formula, why not just put that into a query? — Dale K, Dec 27 '20 at 09:01
@HABO, I wrote "sysdate" (maybe wrong keyword); what I mean is that the "select" shouldn't calculate availability "in the future", only in the past until today. Thanks — over, Dec 27 '20 at 09:37

Prasanna · Accepted Answer · 2020-12-27T14:48:40.957

You can try the below query,

SELECT convert(varchar(7), gcp_start, 126) as month,
sum(
(datediff(day, -7, gcp_end)/7 - datediff(day, -6, gcp_start)/7) *  (isnull(gcp_monend, 0) - isnull(gcp_monstart, 0))
+ (datediff(day, -6, gcp_end)/7 - datediff(day, -5, gcp_start)/7) * (isnull(gcp_tuesend, 0) - isnull(gcp_tuesstart, 0))
+ (datediff(day, -5, gcp_end)/7 - datediff(day, -4, gcp_start)/7 )*  (isnull(gcp_wedend, 0) - isnull(gcp_wedstart, 0))
+ (datediff(day, -4, gcp_end)/7 - datediff(day, -3, gcp_start)/7) *  (isnull(gcp_thursend, 0) - isnull(gcp_thursstart, 0))
+ (datediff(day, -3, gcp_end)/7 - datediff(day, -2, gcp_start)/7) *  (isnull(gcp_friend, 0) - isnull(gcp_fristart, 0))
+ (datediff(day, -2, gcp_end)/7 - datediff(day, -1, gcp_start)/7) *   (isnull(gcp_satend, 0) - isnull(gcp_satstart, 0))
+ (datediff(day, -1, gcp_end)/7 - datediff(day, 0, gcp_start)/7) *  (isnull(gcp_sunend, 0) - isnull(gcp_sunstart, 0))
) as minutes_of_availability
from period
where gcp_end < getdate()
group by convert(varchar(7), gcp_start, 126);

SQL Fiddle Link: http://sqlfiddle.com/#!18/e6c541/30

Note:

To fetch the month and year part, used the approach mentioned in this post.
To find the number of weekdays between start and end date, refer this.
Assumed, gcp_end will fall in the same month as gcp_start

Update: Modified the sql to also include records between the date range.

SELECT convert(varchar(7), gcp_start, 126) as month,
sum(
(  datediff(day, -7, iif(gcp_end < getdate(), gcp_end, getdate()))/7 - datediff(day, -6, gcp_start)/7) * (isnull(gcp_monend, 0) - isnull(gcp_monstart, 0))
+ (datediff(day, -6, iif(gcp_end < getdate(), gcp_end, getdate()))/7 - datediff(day, -5, gcp_start)/7) * (isnull(gcp_tuesend, 0) - isnull(gcp_tuesstart, 0))
+ (datediff(day, -5, iif(gcp_end < getdate(), gcp_end, getdate()))/7 - datediff(day, -4, gcp_start)/7 )* (isnull(gcp_wedend, 0) - isnull(gcp_wedstart, 0))
+ (datediff(day, -4, iif(gcp_end < getdate(), gcp_end, getdate()))/7 - datediff(day, -3, gcp_start)/7) *  (isnull(gcp_thursend, 0) - isnull(gcp_thursstart, 0))
+ (datediff(day, -3, iif(gcp_end < getdate(), gcp_end, getdate()))/7 - datediff(day, -2, gcp_start)/7) *  (isnull(gcp_friend, 0) - isnull(gcp_fristart, 0))
+ (datediff(day, -2, iif(gcp_end < getdate(), gcp_end, getdate()))/7 - datediff(day, -1, gcp_start)/7) *   (isnull(gcp_satend, 0) - isnull(gcp_satstart, 0))
+ (datediff(day, -1, iif(gcp_end < getdate(), gcp_end, getdate()))/7 - datediff(day, 0, gcp_start)/7) *  (isnull(gcp_sunend, 0) - isnull(gcp_sunstart, 0))
) as minutes_of_availability
from period
where gcp_start <= getdate()
group by convert(varchar(7), gcp_start, 126);

SQL Fiddle Link: http://sqlfiddle.com/#!18/e6c541/30

Unless I am missing something, it can be a lot simpler? i.e. `GCP_MONEND - GCP_MONSTART` ... — Dale K, Dec 27 '20 at 04:49
@Dale K, the correct formula is not so simple, I need to test it, but what Prasanna suggested me is very near to my needs. Thanks. — over, Dec 27 '20 at 09:32
@Prasanna, Thanks, it works as I need but at 90% :-) ...It seems that a record is not considered until getdate() is greater or equal than gcp_end, so, today is 27 December, is sunday, so there is a null in my table, but if I place some valid values for sunday they are not considered until 30 December (gcp_end) and is not what I need. I'm trying to analyze your code to improve it (for what I can) since I need day by day (even between my record date range) of my equipment availability. Thanks again. — over, Dec 27 '20 at 13:59
@over, please check if this http://sqlfiddle.com/#!18/afc810/2 is what you need? I will do check for corner cases and update the answer. Thanks — Prasanna, Dec 27 '20 at 14:17
@Over if "the correct formula is not so simple" then please update your question with what the formula is and explain how its not just the different between end minutes and start minutes? — Dale K, Dec 27 '20 at 19:51
@Prasanna, sorry for the delay, the solution you adjusted is what I exactly need. Thanks again. I'm going to document better this post (I'll copy something from your statements in sqlfiddle) to let it available for the future. Bye. — over, Jan 01 '21 at 10:05

Extracting time availability from my calendar table

1 Answers1