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.
| Author |
Topic |
|
inquisitive123
Starting Member
11 Posts |
 Posted - 2010-10-20 : 06:44:36
|
| My requirement is to get same Inscode used by same cdbid for three consecutive days and for CountryCode = 'IN', this query is working, but is taking 12 mins, any ideas how I can improve performance on this. Thanks SELECT t.DayCode, t.ClId, t.ExecutionDatetime, t.ClPrice, o.ClId, o.ClQty, i.SecCode, i.ExcCode, i.CountryCode, s.SecurityName, s.EntityId,td.TrDate, t.cdbidFROM t WITH (nolock) INNER JOIN td ON t.DayCode = td.TrDate AND t.InsCode = td.seccode AND t.ExcId = td.ExcCode INNER JOIN o ON t.ClId = o.ClId INNER JOIN i ON t.InsCode = i.SecCode AND t.ExcId = i.ExcCode INNER JOIN s ON i.SecurityId = s.EntityIdWHERE (i.CountryCode = 'IN') AND t.DayCode IN (DATEADD(D, - 2, 20100107)), DATEADD(D, - 1,20100107), 20100107AND (t.InsCode IN (SELECT DISTINCT t1.InsCodeFROM t1 INNER JOIN i1 ON t1.InsCode = i1.SecCode AND t1.ExId = i1.ExCodeWHERE (i.CountryCode = 'IN') AND t.DayCode IN (DATEADD(D, - 2, 20100107)), DATEADD(D, - 1,20100107), 20100107GROUP BY t1.InsCode, t1.cdbidHAVING (COUNT(DISTINCT t1.DayCode) >= 3))) |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-10-20 : 07:45:58
|
| code of this generic type should work.select a1.* from mytable a1inner join mytable a2 on a2.key = a1.key and a2.date = a1.date + 1 (or a2.date-a1.date = 1 this might perform better) inner join mytable a3 on a3.key = a1.key and a3.date = a2.date + 1 (or a3.date-a2.date = 1 this might perform better) adapt to your local situation. |
 |
|
|
inquisitive123
Starting Member
11 Posts |
Posted - 2010-10-21 : 18:22:12
|
| That did not help improve performance:( Any other suggestions please... |
|
|
|
|
|
|
|
|
Query for three consecutive days