Group by date (1 Viewer)

ielamrani

Registered User.
Local time
Today, 01:27
Joined
May 6, 2008
Messages
51
Hi,

I am close to making this work:confused:

I have this table

attending_res_name actcase_start_datetime minutes
S_X 7/1/2017 8
S_X 7/1/2017 7
S_Y 7/7/2017 6
S_Y 7/7/2017 10


I am trying get the following result

Surgeon date total minutes
S_x 7/1/2017 15
S_Y 7/7/2017 16

Here is what I did so far


SELECT casemain.attending_res_name, SUM(DATEDIFF(n, caseintraop.pat_or_in_datetime, caseintraop.pat_or_out_datetime)) AS Minutes, casemain.actcase_start_datetime,
CAST(dateAdded AS date)
FROM casemain INNER JOIN
caseintraop ON casemain.casemain_id = caseintraop.casemain_id
WHERE (casemain.actcase_start_datetime BETWEEN CONVERT(DATETIME, '2017-02-01 00:00:00', 102) AND CONVERT(DATETIME, '2017-03-01 00:00:00', 102))
GROUP BY CAST(dateAdded AS date), casemain.attending_res_name, CAST(casemain.actcase_start_datetime AS date), casemain.actcase_start_datetime

thank you in advance
 

CJ_London

Super Moderator
Staff member
Local time
Today, 05:27
Joined
Feb 19, 2013
Messages
16,553
CAST is T-SQL not access sql.

And I would have thought a simple group by query, grouping on date and summing on minutes would do the job. Can't quite work out what your query is doing. You say you have one table but your sql uses two tables.

Suggest provide the details of the two tables so we have a clearer understanding of what you are working with. What are pat_or_in_datetime, and pat_or_out_datetime?
 

ielamrani

Registered User.
Local time
Today, 01:27
Joined
May 6, 2008
Messages
51
sorry about that. Yes it's 2 tables.


caseintraop.pat_or_in_datetime, caseintraop.pat_or_out_datetime are used to calculate the total minute the patient was in OR (Operating Room)
 

CJ_London

Super Moderator
Staff member
Local time
Today, 05:27
Joined
Feb 19, 2013
Messages
16,553
?Suggest provide the details of the two tables so we have a clearer understanding of what you are working with.

table names, field names/types, sample data

and is this for access or sql server?
 

ielamrani

Registered User.
Local time
Today, 01:27
Joined
May 6, 2008
Messages
51
I got help and it's working now.

Thank you for your help.
 

Users who are viewing this thread

Top Bottom