Sir
I am showing the crystal Report between Date. and Date can be entered By User so If User enters Date having 2 years Gap then records will be more Than 2 00 000 .
After that i have optimise query removing unwanted fields,unwanted left outer joins and query
is in simple form as below
SELECT DISTINCT bt_memid,bt_level,bt_pvcode,bt_date ,BT_Type,ISNULL(SUM(BT_LeftCnt),0) AS BT_LeftCnt,ISNULL(SUM(BT_RightCnt),0) AS BT_RightCnt,ISNULL(SUM(BT_ConfLeftCnt),0) AS BT_ConfLeftCnt, ISNULL(SUM(BT_ConfRightCnt),0) AS BT_ConfRightCnt,ISNULL(SUM(BT_ownbv),0) AS BT_ownbv,ISNULL(SUM(BT_Confownbv),0) AS BT_Confownbv,ISNULL(SUM(BT_ConfLeftBV),0) AS BT_ConfLeftBV,ISNULL(SUM(BT_confrightbv),0) AS BT_confrightbv,tm_memno,tm_name FROM Binary_Dtls INNER JOIN TempMember_Mst ON TempMember_Mst.TM_MemId=Binary_Dtls.BT_Memid WHERE BT_Date between CONVERT(DATETIME,'20/11/2010',103) AND CONVERT(DATETIME,'24/11/2010',103) GROUP BY bt_memid,bt_level,bt_pvcode,bt_date ,BT_Type,tm_memno,tm_name ORDER BY BT_Memid ASC, BT_Date ASC
tables are as