postgresql to mssql query conversion

Question

can any one help me to convert the following query into mssql which is working on postgresql now

query is to take the updated datetime of the report in the asc order of the date

select 
count(*) as count, 
TO_CHAR(RH.updated_datetime,'dd-mm-YYYY') as date,
SUM( 
  extract ( 
    epoch from ( 
      RH.updated_datetime - PRI.procedure_performed_datetime 
    )
  )
)/count(*) as average_reporting_tat 
from 
report R, 
report_history RH, 
study S, 
procedure_runtime_information PRI, 
priorities PP, 
patient P, 
procedure PR 

where 

RH.report_fk=R.pk and RH.pk IN ( 
  select pk from ( 
    select * from report_history where report_fk=r.pk order by revision desc limit 1 
  ) as result 
  where old_status_fk IN (21, 27)
) AND R.study_fk = S.pk 
AND S.procedure_runtime_fk = PRI.pk 
AND PRI.procedure_fk = PR.pk 
AND S.priority_fk = PP.pk 
AND PRI.patient_fk = P.pk 
AND RH.updated_datetime >= '2013-05-01' 
AND RH.updated_datetime <= '2013-05-12' 
group by date

Chris Travers · Accepted Answer · 2013-10-26 09:40:53Z

1

If I read your query properly, your problem is that you need to list everything in the group by clause that is in your column list which is not part of an aggregate. So your group by needs to be:

GROUP BY RH.updated_datetime

If this doesn't fix it, please post the error message you are getting.

answered Oct 26, 2013 at 9:40

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql to mssql query conversion

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related