How to improve this query performance by indexing properly? I have following select query, and I need to improve the performance of this query. Can anyone suggest me how to convert the clustered index scan to index seek?

DECLARE @now DateTime;        
DECLARE @currentweekstart DateTime;        
DECLARE @currentweekend DateTime;     
SET @now = getDate();        
SET @currentweekstart = DATEADD(        
                 DD,        
                 -(DATEPART(DW, @now - 7) - 1),        
                 @now - 7);         
SET @currentweekend = DATEADD(        
               DD,        
               7 - (DATEPART(DW, @now - 7)),        
               @now - 7);     

  SELECT  CHINFO.CHILDID,COUNT(*) AS CURRENTWEEKPOTTYBREAKS        
          FROM  BKA.CHILDINFORMATION CHINFO         
  JOIN  BKA.CHILDEVENTS CHE         
         ON CHE.CHILDID = CHINFO.CHILDID          
  WHERE TYPE ='POTTYBREAK'        
          AND CHE.ADDDATE BETWEEN @currentweekstart AND @currentweekend         
           GROUP BY CHINFO.CHILDID


         

Primary Keys:


  TableName --> ColumnName
  
  CHILDINFORMATION-->ChildId
  
  CHILDEVENTS-->EventId


Foreign Keys:


  CHILDEVENTS-->Here ChildId is the foreign key reference of
  CHILDINFORMATION table


NonClustered Indexes:


  CHILDINFORMATION-->No nonclustered indexes
  
  ChildEvents-->IX_ChildEvents_Type-->Type include EventId
  
  ChildEvents-->IX_ChildEvents_Type_AddDate-->Type,AddDate
  
  ChildEvents-->IX_ChildEvents_Adddate_Type-->Adddate,Type include
  ChildId,EventId
Since you have a foreign key from CHILDEVENTS to CHILDINFORMATION and you don't select any column from the child information table, you can skip that table. One less join will certainly not hurt:

SELECT CHE.CHILDID, COUNT(*) AS CURRENTWEEKPOTTYBREAKS        
FROM  BKA.CHILDEVENTS AS CHE           
WHERE CHE.TYPE ='POTTYBREAK'        
  AND CHE.ADDDATE BETWEEN @currentweekstart AND @currentweekend         
GROUP BY CHE.CHILDID ;


As for indexing, the existing index on (Adddate,Type) INCLUDE (ChildId, EventId) does not look very good for this query. I would try replacing it (in fact removing both those AddDate-Type indexes) with an index on:

(Type, AddDate) INCLUDE (ChildId)