Can someone help me on this TSQL Query

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)

Can someone help me on this TSQL Query

Author

Topic

raghav99k
Starting Member

32 Posts

Posted - 2011-11-10 : 02:17:19

Hi.

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2011-11-10 : 02:58:42

This may help


UPDATE El
   SET El.EndDate = En.max_end_date
  FROM Elig AS El
       OUTER APPLY
       (SELECT CONVERT(CHAR(8), MAX(End_Dt), 112) AS max_end_date
          FROM Enrollment AS En
         WHERE ID = Mbr_ID
           AND CovType = 'DD'
           AND EffDate BETWEEN Row_Eff_Dt AND Row_End_Dt
           AND EndDate > Row_End_Dt) AS En
 WHERE En.max_end_date IS NOT NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-10 : 03:01:34

sorry this doesnt make sense. are you trying to update all rows EndDate value in ELig table with same maximum value from Enrollment table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2011-11-10 : 05:24:18

quote:
Originally posted by visakh16

sorry this doesnt make sense. are you trying to update all rows EndDate value in ELig table with same maximum value from Enrollment table?

It does make sense. In fact my update will affects the same rows that would be affected by the original update.

raghav99k
Starting Member

32 Posts

Posted - 2011-11-10 : 06:33:18

actually what need be happen is for the inner query if a value is retrived ie is true as in where exists condition for the same condition outer query has to update. is it make sense.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-10 : 06:37:16

quote:
Originally posted by malpashaa

quote:
Originally posted by visakh16

sorry this doesnt make sense. are you trying to update all rows EndDate value in ELig table with same maximum value from Enrollment table?

It does make sense. In fact my update will affects the same rows that would be affected by the original update.

sorry that was not for you
i was asking about OPs original suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

raghav99k
Starting Member

32 Posts

Posted - 2011-11-10 : 07:33:27

Hi Malpashaa...Thanks for ur post..

With ur query i ran on 10Lak records..it took 2:59 sec for updating, but with my actual query it took 3:04 sec..it shows 0:05sec bt great helping on this query... Can we do any thing more, if any ideas pls suggest, Very thankful..

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2011-11-10 : 07:35:27

quote:
Originally posted by visakh16

quote:
Originally posted by malpashaa

quote:
Originally posted by visakh16

sorry this doesnt make sense. are you trying to update all rows EndDate value in ELig table with same maximum value from Enrollment table?

It does make sense. In fact my update will affects the same rows that would be affected by the original update.

sorry that was not for you
i was asking about OPs original suggestion

No problem. My mistake.

raghav99k
Starting Member

32 Posts

Posted - 2011-11-10 : 07:42:57

i wil be very thankful to someone who helps me in the query to run in lesthan 2 mins...

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2011-11-10 : 07:57:53

quote:
Originally posted by raghav99k

i wil be very thankful to someone who helps me in the query to run in lesthan 2 mins...

If you need more help, then give us more information regrading execution plan, statistics (IO and time), and indexes; so we can understand where is the issue?

raghav99k
Starting Member

32 Posts

Posted - 2011-11-10 : 08:24:57

now one wierd thing happen on 1C records my query took 0:19sec, modified query took 0:42sec, bt this is on stage.

raghav99k
Starting Member

32 Posts

Posted - 2011-11-10 : 09:09:59

hi.

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2011-11-10 : 10:01:23

How many rows are there in Elig and Enrollment tables, and to which table CovType column belongs, and how many rows out of that table rows meets the condition CovType = 'DD'. By the way you did not provide the other information I asked you for. Things would be a lot easier if you can provide us with as much as possible information; so we can guess better the appropriate way to write the update query.

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-11 : 06:16:53

quote:
Originally posted by raghav99k

Hi.

Why did you delete your original question?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

raghav99k
Starting Member

32 Posts

Posted - 2011-11-11 : 07:17:54

Hi, sry..actually i was trying to edit to state that 'Covtype' column is related to Enrollment table.Pls do needful.

quote:
Originally posted by visakh16

quote:
Originally posted by raghav99k

Hi.

Why did you delete your original question?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

raghav99k
Starting Member

32 Posts

Posted - 2011-11-11 : 08:04:47

actualy we have 7 crore records on each table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources