Identify tools used in a the day's time range

We have large table containing all the tools of a specific department used; with date-used, time taken out, time return and tech name. We would are trying to write a ms sql script that would present the following:

Tooles  Date       9:00am thru 9:59am  10:00am thru 10:59am 11:00am thru 11:59am
------  --------   ------------------- -------------------- --------------------
Desk    2017-0103      2                   1                   2
Hammer  2017-0104      0                   1                   1

Open in new window


the data looks something like this:
tools    Technician  DateI	TimeI	    TimeR
------	 ---------   ----------	--------    --------
Desk     Joe         2017-0103    9:00:00   10:30:00
Desk     Shmow       2017-0103    9:15:00   9:35:00
Hammer   Jenny       2017-0104    10:40:00  11:40:00
Desk     Lenny       2017-0103    10:45:00  10:59:00
Desk     Ralph       2017-0103    11:00:00  11:50:00
Desk     Zules       2017-0103    11:15:00  11:45:00
Hammer   Ray         2017-0104    11:48:00  11:59:00

Open in new window


What is the optimum way to go about it?
rayluvsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
How is the data stored.
Can you post an example of how the data is store.

Deals with whether the data is in a single row, or has to be aggregated (group by, partition, or used with cte to create and then pivot.......
0
rayluvsAuthor Commented:
The data is stored as it is displayed in the question (below virtual example of my table, data and data type):
DECLARE @ItemTable TABLE(Tools VARCHAR(255), Technician VARCHAR(255), DateI datetime, TimeIn datetime, TimeReturn datetime)
INSERT INTO @ItemTable  VALUES ('Desk','Joe','20170103', '9:00:00','10:30:00')
INSERT INTO @ItemTable  VALUES ('Desk','Shmow','20170103', '9:15:00','9:35:00')
INSERT INTO @ItemTable  VALUES ('Hammer','Jenny','20170104', '10:40:00','11:40:00')
INSERT INTO @ItemTable  VALUES ('Desk','Lenny','20170103', '10:45:00','10:59:00')
INSERT INTO @ItemTable  VALUES ('Desk','Ralph','20170103', '11:00:00','11:50:00')
INSERT INTO @ItemTable  VALUES ('Desk','Zules','20170103', '11:15:00','11:45:00')
INSERT INTO @ItemTable  VALUES ('Hammer','Ray','20170104', '11:48:00','11:59:00')
SELECT tools, dateI, TimeIn, TimeReturn FROM @ItemTable

Open in new window


This is what I have so far (I need to isolate per tools/date/time sum per column/time):
SELECT ParentItemTable.Tools, ParentItemTable.datei
,SUM((CASE WHEN ParentItemTable.timein BETWEEN '09:00:00' and '09:59:00'THEN 1 ELSE 0 END)) '9:00am thru 9:59am'
,SUM((CASE WHEN ParentItemTable.timein BETWEEN '10:00:00' and '10:59:00'THEN 1 ELSE 0 END)) '10:00am thru 10:59am'
,SUM((CASE WHEN ParentItemTable.timein BETWEEN '11:00:00' and '11:59:00'THEN 1 ELSE 0 END)) '11:00am thru 11:59am'
FROM @ItemTable AS ParentItemTable
INNER JOIN @ItemTable AS ChildItemTable ON ParentItemTable.Tools = ChildItemTable.Tools
and ParentItemTable.datei = ChildItemTable.datei
GROUP BY ParentItemTable.tools, ParentItemTable.DateI

Open in new window


when run, it displays,
Tools	datei               	9:00am thru 9:59am	10:00am thru 10:59am	11:00am thru 11:59am
Desk 	2017-01-03 00:00:00.000	10			5			10
Hammer	2017-01-04 00:00:00.000	0			2			2

Open in new window



And I am going for this output result:
Tooles    Date       9:00am thru 9:59am  10:00am thru 10:59am 11:00am thru 11:59am
------  --------   ------------------- -------------------- --------------------
Desk    2017-0103      2                   1                   2
Hammer  2017-0104      0                   1                   1

Open in new window

0
arnoldCommented:
Seems you have everything, but you want the date,
Use convert with style 112 to get yyyymmdd
Alternatively, you could use substring to grab the first 11 or 8 or part 4,2,2 and separate them with dashes,you may have to use cast or convert , to change the datetime to varchar.
Another option could be to rtrim to cut 12.

Guide to use cast and convert
https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

rayluvsAuthor Commented:
But it is not counting correctly (i want to display the tools, per day and a count of the 3 times-ranges each date - the first 2 I thinks its done)...  can u take look at the script and show me where I flaw for counting the time-ranges per tool/date?

Thanx you.

(regsrding the date format ion my previous entry, I don't want to format it. In previous entry of date "2017-0103" is only for showing what we want.  It's ok if it is displayed as SQL does "2017-01-03 00:00:00.000"
0
Scott PletcherSenior DBACommented:
I'm not sure what the minimum part of an hour you want to count is, so these totals are higher, but if you'd explain the controlling logic, I'll adjust the code:

SELECT Tools, DateI AS Date,
    SUM(CASE WHEN  9 BETWEEN InHour AND ReturnHour THEN 1 ELSE 0 END) AS '9:00am thru 9:59am',
    SUM(CASE WHEN 10 BETWEEN InHour AND ReturnHour THEN 1 ELSE 0 END) AS '10:00am thru 10:59am',
    SUM(CASE WHEN 11 BETWEEN InHour AND ReturnHour THEN 1 ELSE 0 END) AS '11:00am thru 11:59am' --,...
FROM @ItemTable
CROSS APPLY (
    SELECT DATEPART(HOUR, TimeIn) AS InHour, DATEPART(HOUR, DATEADD(MINUTE, -1, TimeReturn)) AS ReturnHour
) AS ca1
GROUP BY Tools, DateI
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
arnoldCommented:
Please post create table where the date/times stored.
Your issue might be that the comparison does not account for correct time ranges.

Converting strings to a range using dateadd to set the range..........
0
arnoldCommented:
Oh, you also need to calculate timein, timeout to define when the tool was out...
0
arnoldCommented:
your query the tool should be counted in use, while out?
0
rayluvsAuthor Commented:
Will try Scott.

Arnold the table is supposed as virtual Table ar id 42442305
0
arnoldCommented:
The issue a tool is used from the tine it is taken out and until it is returned.
Your query seems to look at when the tool is released.
The point being, you are grouping by tool, date taken

Look at the data without the group to see how many rows are returned before you start counting....
0
arnoldCommented:
Datepart(hour,dated column) this extracts the hour from in, then you compare it to
0
rayluvsAuthor Commented:
Thanx!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.