Forum Discussion

mike_asplin's avatar
mike_asplin
Helper V
1 year ago
Solved

Trying to use SQL to calculate a max excluding certain lines

Way beyond my SQL abilties

 

I tried this code

 

SELECT 
  StudyCode , 
 MAX(  Date_ScheduledPulldate) as 'Trial End Date'
 
FROM 
  Custom_PullPointDate
  WHERE
  Recordstatus=1
  AND NOT CONTAINS(TestGroup,'SET UP')
  AND NOT CONTAINS(TestGroup,'SET DOWN')
  AND NOT CONTAINS(TestGroup,'COLLECTION')
  
  GROUP BY StudyCode

 as need to exclude al lthe lines where theTestGroup field contains any of these phrases before calc the MAX

 

Get an error which is Chinese to me.  I read CONTAINS was more efficient than LIKE, but cant work out syntax or will that have same error?  I could do it long way round and pull everything in and calcuate the MAX within Power Query. 

 

Appreciate any advice. 

 

---------- Message ----------
Microsoft SQL: Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Custom_PullPointDate' because it is not full-text indexed.
 
 
 

 

Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    mike_asplin , Try using

    SELECT
    StudyCode,
    MAX(Date_ScheduledPulldate) AS 'Trial End Date'
    FROM
    Custom_PullPointDate
    WHERE
    Recordstatus = 1
    AND TestGroup NOT LIKE '%SET UP%'
    AND TestGroup NOT LIKE '%SET DOWN%'
    AND TestGroup NOT LIKE '%COLLECTION%'
    GROUP BY
    StudyCode

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    mike_asplin , Try using

    SELECT
    StudyCode,
    MAX(Date_ScheduledPulldate) AS 'Trial End Date'
    FROM
    Custom_PullPointDate
    WHERE
    Recordstatus = 1
    AND TestGroup NOT LIKE '%SET UP%'
    AND TestGroup NOT LIKE '%SET DOWN%'
    AND TestGroup NOT LIKE '%COLLECTION%'
    GROUP BY
    StudyCode