SELECT  SCH.Scheme_Id As SchID,SCH.Scheme_Name As SchName,
MAX(CP.Port_Date) As [Date],CM.Company_Name As CompName,CN.Nature_Name As NatureName,
SM.Sector_Name As SectName,I.Instrument_Name As InstName,R.Rating_Name As RatName,
Sum(CP2.Mkt_Value) As MktVal,Sum(CP2.Mkt_Value) AS NetAsset,
Sum(ISNULL(CP2.No_Of_Shares,0)) As SH  
FROM T_COM_POT CP 
INNER JOIN T_COM_POT_DTLS1 CP1 ON CP.Port_ID = CP1.Port_ID 
INNER JOIN T_FUND_MASTER FM ON CP.Fund_ID = FM.Fund_Id 
INNER JOIN T_COMPANY_MASTER CM ON CP1.Company_ID = CM.Company_Id
INNER JOIN T_MF_MASTER MF ON FM.Mutualfund_Id = MF.MutualFund_ID 
INNER JOIN T_COMPANY_NATURE CN ON CP1.Com_Nature_ID = CN.Com_Nature_ID
LEFT JOIN T_INSTRUMENT I ON CP1.Instrument_ID = I.Instrument_ID
LEFT JOIN T_RATING R ON CP1.Rating_ID = R.Rating_ID
LEFT JOIN T_SECTOR_MASTER SM ON CM.Sector_Id = SM.Sector_ID
INNER JOIN T_SCHEMES_MASTER SCH ON SCH.Fund_Id = FM.Fund_Id
INNER JOIN T_COM_POT_DTLS2 CP2 ON CP1.Com_Pot_N_DTLS1_ID = CP2.Com_Pot_N_DTLS1_ID
GROUP BY SCH.Scheme_ID,SCH.Scheme_Name,CM.Company_Name,CN.Nature_Name,SM.Sector_Name,I.Instrument_Name,R.Rating_Name

also i am giving the  record count for tables

T_COM_POT:	        45853
T_FUND_MASTER 	        3219
T_COMPANY_MASTER 	5549
T_MF_MASTER	        55
T_COMPANY_NATURE	6
T_INSTRUMENT	        64
T_RATING	        78
T_SECTOR_MASTER	        56
T_SCHEMES_MASTER	10264
T_COM_POT_DTLS2	        1287949
T_COM_POT_DTLS1	        1287949

and each column used in where clause are primary keys in their respective tables.
 

please suggest me solution. this query is used in while loop this query returns output for many times. 

Where Month(CP.Port_Date) = Month('02-01-2009') AND Year(CP.Port_Date) = Year('02-01-2009') 
										AND SCH.Parent_Scheme_Flag = 1 And SCH.Scheme_Id IN ('1','34','654','345','359','223') GROUP BY SCH.Scheme_ID,SCH.Scheme_Name,I.Instrument_Name  ORDER BY 3 Desc,1 Asc

quote:

---

Originally posted by tosscrosby

First, I don't see a where clause so it is processing each row in T_COM_POT that meets all the "ON" criteria. Second, why would you be using this in a while loop, for what purpose? You're not passing any variables so it returns more or less the same rowset each time it's called - just seems kind of pointless. Lastly, please provide DDL for the tables, sample input and expected output. Row counts are kind of useless as none are large by any means.

Terry

-- Procrastinate now!

---

Where		CP.Port_Date >= '20090201'-- Feb 1 2009
		AND CP.Port_Date < '20090301'-- March 1 2009 
		AND SCH.Parent_Scheme_Flag = 1
		And SCH.Scheme_Id IN ('1','34','654','345','359','223')
GROUP BY 	SCH.Scheme_ID,
		SCH.Scheme_Name,
		I.Instrument_Name
ORDER BY 	3 Desc,
		1 Asc