SQL Script to display usage based on date & time

We have a Ms Sql table with 3 columns: DateTime, Tools and Technician,

We are trying to create script for where it present how each tools has been used simultaneously (same Date and time or close to it)

For example:

Tools that has been used in same date/time
Tools   DateTime          Technician
------- ----------------- ----------
Dryer   2017-1217 5:00pm  Joe
Dryer   2017-1217 5:00pm  Don
Dryer   2017-1217 5:00pm  Sandy
Hammer  2017-1218 6:00pm  Ray
Hammer  2017-1218 6:00pm  Steve
Spark   2017-1219 4:00pm  Joe
Spark   2017-1219 4:00pm  Ray

Open in new window

rayluvsAsked:
Who is Participating?
 
Dustin SaundersConnect With a Mentor Director of OperationsCommented:
The first script does the following:

ROW_NUMBER() OVER (PARTITION BY tools, datetime ORDER BY DateTime) AS "RN"

Open in new window


This adds a row number ("RN") to each item grouped by matching tools & datetime columns.  This gives you the number of occurrences.  The row numbers reset each time the group changes.  The other items are just standard column names.

However, what you might do is round to the nearest 15 minutes and you can get 'blocks'.  For example:

SELECT DATEADD(MINUTE, ROUND(DATEDIFF(MINUTE,0,datetime) / 15.0,0) * 15, 0) AS datetime2, datetime
  FROM [Scratch].[dbo].[ee29076364]

Open in new window


Gives me the nearest 15 minutes to these datetimes:
datetime.png
So, now even if the time is off by minutes and seconds, I can still group into 15 minute blocks based off it's closest quarter hour.
SELECT ROW_NUMBER() OVER (PARTITION BY tools,DATEADD(MINUTE, ROUND(DATEDIFF(MINUTE,0,datetime) / 15.0,0) * 15, 0) ORDER BY datetime) AS "RN",DATEADD(MINUTE, ROUND(DATEDIFF(MINUTE,0,datetime) / 15.0,0) * 15, 0) AS "nearesttime",datetime,tools,technician
FROM [Scratch].[dbo].[ee29076364]
ORDER BY datetime

Open in new window


And we get:
datetime2.png
You can see, even though the minutes are different I have grouped them by the closest 15 minutes and can get the count.
0
 
Dustin SaundersDirector of OperationsCommented:
How are you trying to present the data?  What does 'close to it' mean?

You could do this a few ways.  If you want to show a timeline including the non-simultaneous uses, just order your results by datetime.

If you want to show that data and then how many are simultaneously using it, something like this:
SELECT ROW_NUMBER() OVER (PARTITION BY tools, datetime ORDER BY DateTime) AS "RN", tools,datetime,technician
FROM [Scratch].[dbo].[ee29076364]

Open in new window


If you want to show the total count and only simultaneously used items, something like this:
SELECT COUNT(*) AS "Simultaneous",tools,datetime
FROM [Scratch].[dbo].[ee29076364]
GROUP BY tools,datetime
HAVING COUNT(*) > 1

Open in new window


Etc
0
 
rayluvsAuthor Commented:
Hi,

By 'close to it', I meant grouping the options that has been used within 15 minute, as follows:

Tools   DateTime          Technician
------- ----------------- ----------
Dryer   2017-1217 5:00pm  Joe
Dryer   2017-1217 5:08pm  Don
Dryer   2017-1217 5:15pm  Sandy
Hammer  2017-1218 6:20pm  Ray
Hammer  2017-1218 6:29pm  Steve
Spark   2017-1219 4:10pm  Joe
Spark   2017-1219 4:12pm  Ray

Open in new window


Noticed that it groups all that falls beings used within 15 minutes.


Will try  you script.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Dustin SaundersDirector of OperationsCommented:
Within 15 minutes of the last usage?

i.e. if you also had Dryer used at 5:20 pm, would that be grouped as well since it was within 15 minutes of the last usage at 5:15 pm by Sandy?
0
 
rayluvsAuthor Commented:
Good observation.  No.  If it was so, so then all will be under same 1 group since each use follows each-other, thus the "15 min" expand, thus, will have no range.

Thank you, Just made us think of the desired query - we have to place a control somehow?

But first, tried your script and seems to work.  Can you explain the first script please:

SELECT ROW_NUMBER() OVER (PARTITION BY tools, datetime ORDER BY DateTime) AS "RN", tools,datetime,technician
FROM [Scratch].[dbo].[ee29076364]
0
 
rayluvsAuthor Commented:
Thank you very much!  You have helped a lot!
0
 
Dustin SaundersDirector of OperationsCommented:
No problem, happy to help.
0
 
rayluvsAuthor Commented:
Hi,

Just noticed that the column 'DateTime' is actually 2 columns, zDate and zTime - so I modified your script:

SELECT ROW_NUMBER() OVER (PARTITION BY tools,DATEADD(MINUTE, ROUND(DATEDIFF(MINUTE,0,zDate+zTime) / 15.0,0) * 15, 0) ORDER BY zDate+zTime) AS "RN",DATEADD(MINUTE, ROUND(DATEDIFF(MINUTE,0,zDate+zTime) / 15.0,0) * 15, 0) AS "nearesttime",zDate+zTime,tools,technician
FROM [Scratch].[dbo].[ee29076364]
ORDER BY zDate+zTime

It runs with no errors, but wanted to check with u if any additional consideration by the change, am I ok or should I change something for proper results?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.