Hello SQLTeam.This is my first post, so I apologize in advance.I've been asked to do an SQL query. Part of the query is to find 3 related rows in a table that occur within 24 hours of each other. The person I'm doing this for has allowed me to lax this restriction to 3 related rows occurring on the same day (so if one occurs 23-aug-2010 21:00 and another occurs 24-aug-2010 1:00, it is ok for these to not be related).Essentially my thoughts was to do a partition by the date, where I would strip the time from the date, however this requires afaik that I cast the date to a float, and then floor the value, however I've been told that doing this makes the index useless, thereby ruining the performance of such a query. Is there a better way? Here is what I have with the names of the tables changed. select tickets.TicketKey,StoreCode,TransactionDateTime,ShiftCode, CashierCode ,club_cards.ClubType , count(1) over (partition by StoreCode,floor(cast(TransactionDateTime as float)),ShiftCode, CashierCode) as EmployeeDiscountTicketsCount from [T_Ticks] tickets inner join [T_Clubs_Relations] clubs on tickets.TicketKey=clubs.RelationOwnerCode and clubs.RelationOwnerType = 1 inner join [T_Clubs] club_cards on club_cards.ClubDiscountCode=clubs.ClubDiscountCode where club_cards.ClubType in ('1','2')I know it's confusing without the structure of the tables, however I'm only interested in optimizing the cast if possible. If you guys have a better idea then partitioning or anything like that, I'm all ears.Please let me know if that's possible.
Indexing and Casting
Posted - 2010-09-01 : 17:26:02