min and max query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

min and max query

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-05-24 : 03:15:07

hi this is my query


SELECT ISNULL(PATIENT_ID,0) AS PATIENT_ID,ISNULL(CREATED_DATE,0) AS  CREATED_DATE FROM EMRAllergies
UNION
SELECT ISNULL(PATIENT_ID,0) AS PATIENT_ID,ISNULL(CREATED_DATE,0) AS  CREATED_DATE FROM EMRAppDetailsSuppHistory
UNION
SELECT ISNULL(PATIENT_ID,0) AS PATIENT_ID,ISNULL(CREATED_DATE,0) AS  CREATED_DATE FROM EMRAppointmentDetails

now please help me in finding using above query min and max for the created_date group by patient_id and created_date
column with group by

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-24 : 04:34:00

Is this you are looking for :

Select PATIENT_ID, Min(CREATED_DATE), Max(CREATED_DATE) from
(
SELECT ISNULL(PATIENT_ID,0) AS PATIENT_ID,ISNULL(CREATED_DATE,0) AS CREATED_DATE FROM EMRAllergies
UNION
SELECT ISNULL(PATIENT_ID,0) AS PATIENT_ID,ISNULL(CREATED_DATE,0) AS CREATED_DATE FROM EMRAppDetailsSuppHistory
UNION
SELECT ISNULL(PATIENT_ID,0) AS PATIENT_ID,ISNULL(CREATED_DATE,0) AS CREATED_DATE FROM EMRAppointmentDetails
) as SubTab
Group by PATIENT_ID

Regards,
Bohra

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-05-25 : 01:50:31

hi thanks for your reply and i want one more help i.e
i have created a new table like

Create Table EMRMigartion
(
[Patient_id] [Varchar](50),
[First_Access_Date] [Datetime],
[Last_Access_Date] [Datetime]
)
GO

using the query

Select PATIENT_ID, Min(CREATED_DATE), Max(CREATED_DATE) from
(
SELECT ISNULL(PATIENT_ID,0) AS PATIENT_ID,ISNULL(CREATED_DATE,0) AS CREATED_DATE FROM EMRAllergies
UNION
SELECT ISNULL(PATIENT_ID,0) AS PATIENT_ID,ISNULL(CREATED_DATE,0) AS CREATED_DATE FROM EMRAppDetailsSuppHistory
UNION
SELECT ISNULL(PATIENT_ID,0) AS PATIENT_ID,ISNULL(CREATED_DATE,0) AS CREATED_DATE FROM EMRAppointmentDetails
) as SubTab
Group by PATIENT_ID

populate the newly created table with patient_id, min-> first_access_date and max -> last_access_date.
how to do so?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-25 : 01:52:50

insert EMRMigartion
<here comes the query>

No, you're never too old to Yak'n'Roll if you're too young to die.

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-05-25 : 01:59:35

thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources