Group by date

[ielamrani]

Hi,

I am close to making this work

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]

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]

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]

?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]

I got help and it's working now.

Thank you for your help.