asked on

sql query

Hi I am not sure that my solution is correct or not as we need only one distinct field(region_name).plz provide the solution

Problem Statement 1. Populate the fields in RegionDetails table with all distinct Region_Name based on the fields CCareStageTbl.Client_Name and Client.Client_Name

My Solution 1.

INSERT INTO RegionDetails ( 
	Region_Key ,
	Region_Name ,
	Client_Key ,
	Upd_Date ,
	User_Id 
)
Select Distinct Region_Key ,Region_Name,Client_Key ,Upd_Date ,User_Id 
FROM CCareStageTbl CS INNERJOIN Client CL
ON CS.Client_Name = CL.Client_Name 

//could you plz check my joins are accurate or not ,here are also distinct Facility_Name
Problem Statement 2.	Populate the fields in FacilityDetails table with all distict Facility_Name based on the join on the fields from CCareStageTbl.Client_Name & Client.Client_Name, CCareStageTbl.Region_Name & RegionDetails.Region_Name and RegionDetails.Client_Key & Client.Client_Key and CCareStageTbl.Facility_Type_Desc & FacilityType.Facility_Type_Desc. 

My Solution 2: 
INSERT INTO FacilityDetails ( 
	Facility_Key ,
	FacilityType_Key ,
	Facility_Name ,
	Region_Key ,
	Upd_Date ,
	User_Id )
Select Distinct Facility_Key ,FacilityType_Key ,Facility_Name ,Region_Key ,Upd_Date ,User_Id 
FROM CCareStageTbl CS INNERJOIN Client CL
ON CS.Client_Name = CL.Client_Name 
LEFT Join RegionDetails RD
ON CS.Region_Name = RD.Region_Name
AND RD.Client_Key = CL.Client_Key
LEFT JOIN FacilityType FT
ON CS.Facility_Type_Desc = FT.Facility_Type_Desc

Open in new window

Dany Balian

1. use table.field in your select (of course depending on in which table the fields are found)
2. inner join should have space between inner and join
...

Select Distinct cs.Region_Key ,cs.Region_Name,cl.Client_Key ,cl.Upd_Date ,cl.User_Id
FROM CCareStageTbl CS INNER JOIN Client CL
ON CS.Client_Name = CL.Client_Name

Open in new window

same for solution 2 (you should see which field exists in which table)

Select Distinct ft.Facility_Key ,ft.FacilityType_Key ,ft.Facility_Name ,rd.Region_Key ,cl.Upd_Date ,cl.User_Id
FROM CCareStageTbl CS 
INNER JOIN Client CL ON CS.Client_Name = CL.Client_Name
LEFT Join RegionDetails RD ON CS.Region_Name = RD.Region_Name AND RD.Client_Key = CL.Client_Key
LEFT JOIN FacilityType FT ON CS.Facility_Type_Desc = FT.Facility_Type_Desc

Open in new window

nicemanish

ASKER

but here it will distinct all the fields i want to be distinct only region name

ASKER CERTIFIED SOLUTION

Dany Balian

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vitor Montalvão

This is an exam?

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nicemanish

ASKER

Hi vitlor ,
this is not a exam... dont demotivate people in experts exchange ... i made query then after i ask in experts exchage.
for better clarification i provided problem state.

i know that experts exchange having the people who are master in technology.they can provide me help.

well i respect you and all member in expert exchange.

thanks

PortletPaul

@nicemanish

thanks for clearing-up the matter of an exam.

However, what you need to do is:

Provide some sample data (for each table), private data can be changed.
(not images of data! data in a reusable format)

Then also provide the "expected results"

Form these 2 items we can advise you on the queries.

I promise you this is the most accurate way to explain your needs to us and we can then give you informed advice

Vitor Montalvão

Nicemanish, I needed that clarification because we shouldn't resolve exams but point the user how to resolve it.
Now I know how should I help you. Will try to find a solution for your problem and will be back.

Cheers

nicemanish

ASKER

Hi All, Thanks For your co-operation
plz find the correct way something as below using row number and partition

INSERT INTO Patient  ( 
	Patient_Id ,MPI ,First_Name ,Middle_Name ,Last_Name ,
	Patient_Suffix ,Age ,Birth_Date ,Gender ,SSN ,Upd_Date ,User_Id )
SELECT CCST.Patient_Id , CCST.MPI , CCST.First_Name , CCST.Middle_Name , CCST.Last_Name , CCST.Patient_Suffix , CCST.Age , CCST.Birth_Date , CCST.Gender , CCST.SSN , CCST.Upd_Date , CCST.User_Id 
FROM (
       SELECT Patient_Id ,MPI ,First_Name ,Middle_Name ,Last_Name ,
	Patient_Suffix ,Age ,Birth_Date ,Gender ,SSN ,Upd_Date ,User_Id
              ROW_NUMBER() OVER (PARTITION BY Patient_Id  ORDER BY Upd_Date DESC
                     ) rownumber
       FROM CCareStageTbl
       ) CCST
WHERE rownumber = 1

Open in new window

PortletPaul

it is a good lesson to have learned, nice job