How to count number of distinct days from one table using two dates (for range) from another table for each row?

So I have one datatable with three columns: userid, mindate, maxdate. I have another datatable that contains all the login logs for each user.

I need to make a query so that I can sum all the distinct login days from one datatable between the mindate and maxdate from another datatable for each user.

The code I can write until:

SELECT a.*, b.logins FROM ‘usertable’ as a
LEFT JOIN (SELECT user_id, COUNT(DISTINCT(loginday)) from ‘logintable`
GROUP BY user_id) as b
on a.userid = b.user_id;

I just need help adding a ‘where’ statement that can query from b like:
WHERE loginday between a.mindate and a.maxdate.

Thank you!

SharePoint 2013 Get difference between two dates in days using JavaScript/J query

I want to get the difference in days between two dates.One date is from a List item input by User. The second day will be “Today”. get the number of days rom the date inputted by user and current day.

will this work:

var onBoardDate = value.OnBoardDate; //value obtained via REST call          var today = new Date(); // current day         var dateToString2 = today;         var dateDiff = dateToString2 - dateToString1;         console.log(dateDiff );          var date_diff_indays = function(date1,date2) {               dt1= dateToString1;                 dt2 = dateToString2;                 return Math.floor((Date.UTC(dt2.getFullYear(), dt2.getMonth(), dt2.getDate()) - Date.UTC(dt1.getFullYear(), dt1.getMonth(), dt1.getDate()) ) /(1000 * 60 * 60 * 24));               }               console.log(date_diff_indays(dt1, dt2)); 


Validating a set of dates in Microsoft Sharepoint

I am trying to validate dates in a task request form that has a column of [Date of Request] (which would be the day that they are adding the task to the main hub.

I, then, want that [Date of Request] to see the [Requested Posting Date] column and not allow it to post a date that is before the [Date of Request].

I have tried everything I can from:

=[Requested Posting Date]<=[Date of Request]  


=[Requested Posting Date]<[Date of Request]  


[Due Date] > [Created]  

and none of those work for what I am trying to do here. I am doing all of these formulas in the validation settings section if that helps. Please help me if you can everything I try fails or just doesn’t do anything, let me know how to resolve this and get it working. Just nothing will affect the date pickers whatsoever they do exactly the same thing no matter what formula I throw in, im completely lost, let me know Thanks!

Difference between two dates in hours

In SP list there are two date/time columns, column A and B.
I want to calculate difference in hours between those two dates, but only on working hours. My working hours are between 8AM (08:00) and 4PM (16:00).

If column A is 19.03.2017. 15:00 and column B is 20.03.2017. 09:00, difference should be 2 hours.

I have this column that calculates calendar time:

=IF(ROUND((([B]-[A])*24-ROUNDDOWN(([B]-[A])*24,0))*60,0)=60,(ROUNDDOWN((([B]-[A])*24),0)&": 00"),(ROUNDDOWN((([B]-[A])*24),0)&":"&ROUND((([B]-[A])*24-ROUNDDOWN(([B]-[A])*24,0))*60,0))) 

and with it I get suspected incorrect result 18 hours.

Any help is much appreciated.

Events and Holiday – Both Event and Holiday having similar dates

We have an problem in displaying the below requirement in the fullcalendar in an content editor webpart

Holiday: Start Date – 02.08.2019 End Date – 03.08.2019 Event: Start Date – 02.08.2019 End Date – 08.08.2019

Holidays are highlighted in red Events are highlighted in green

Now for in my calendar, Holidays are the first preference if they both occur in same date which i have already handled.

But what happens if the dates are same then event slot which is created (complete start to end date ) are considered as holidays which is wrong.

So i need to handle, if the event and holiday occur in the same date show holiday. But after the holiday is completed it should start showing the events.

Kindly let me know your suggestions on how to handle this. We also have full calendar plugin. Any code snippets or sample reference would also help ?


Dropdown of dates, is it acceptable to display in non-chronological order

We have a time series graph. The user can use a drop down to select their years and these years are displayed on the chart. Currently, the drop down is just a list of years for the past 30 years.

My superior is discussing ordering the drop down, not by chronological order of years, but based on a value, such as busiest year. My concern with this is that there will not be an expected order to the drop down. Years will be all over the place, 2017 might be at the bottom whilst 2019 might be at the top.

I simply can’t allow this to happen, as I think it’s very confusing to anyone trying to find a year. So I suggested we find a different way. They’re suggesting we use a checkbox to modify the drop down. Eg, default to chronological and change the drop down to busiest to least-busy year if checkbox is selected. I still have issues with this as I’m yet to see a website use boolean modifiers against a drop down, I think we’ll struggle to make it clear the link between the drop box and the checkbox.

Any suggestions?

Storing dates and their history of changes and emailing – Does my database design pose any problems?


I’m designing some tables to allow dates to be assigned to an object so progress can be tracked on that object, along with functionality to store emails sent pertaining to these contracts and their dates. For example lets say that the Object is a Contract (A contracted task to be completed).

This means we would have a contract table


Now for this contract we can have Date fields such as a obtained date, start date, review date, estimated finish date, finish date etc. Right now most contracts will contain the same columns but we don’t know in the future if more columns will be added or less will be used as requirements change quite often.

Because of this, I figured to separate the dates from the contract table. Each date in this ContractDate table would have a foreign key to a FieldHeading table that describes what aspect of the contract the date is. And finally, a AuditTrail type table called ContractDateHistory that would keep track of changes of a Date.

Contract and Date table seperate with a history of dates table.

This way we can also selectively choose what dates go to a contract. One contract may have 4 dates and another may have 6.

These contracts can have emails sent to people that inform them of how these dates are going or if a date has changed. Because multiple contracts can be contained in one email I have a ContractsForEmail table which contains the ID of the email of each contract that is contained in an email.

Contracts with emailing tables

A row would be input for each email sent to a user, needsResolution is there if the user has to take action with the email (depends on the type of email) and resolved is there if the user has taken action, a program will check if the user has taken action after a set amount of time, and if not, the program will send a reminder email (which could possibly also show up in this table)

The template can have a list of users that the emails derived from the template sends to (an email will be sent for each user assigned to the mailing list of a template, additional could be sent too but users that without fail get an email sent are kept here.

My Question

I have many questions about if my design is okay but in specific I’d like to know, is what I am doing with contracts and contractDates is okay?

Instead of having the Contract and Dates separate should I just add Date columns to the Contracts table?

If they get used then good and if they don’t for a specific contract then its just kept as NULL.

This could lead to columns in the contract table that get added and never used again (almost always NULL). Like so:

Contract table with dates inside

AddedDateInFuture is there to illustrate that a column may be added in the future, it wouldn’t literally be there.

  • What are the trade-offs between the two options?
  • Is there anything I’m missing with the emailing tables? Would this kind of design work?

Currently the system would be catering to under 200 users but I’d like it to be built a little future proof for if many more use it.

As a last note some database tables such as the usual “User” table has been left out.