We help IT Professionals succeed at work.

Using "LIKE" in a CASE Statement

305 Views
Last Modified: 2010-03-20
I'm trying to pull out certifications from a table that contains different certifications as well as the latest effective and expire date for a particular coach.  I can get all of sertificates out but when the Sports Specific certification comes out the kind is listed as 'sptf', that is how they are stored in table.  But this doesn't tell me for what sport.  So another field contains additional information which is for what sport the certificate is for (it also has other info in it) ie:  Cert=bb would be for Basketball

I want to put the sport into a field called TypeCert and include that information in the query

When I try the following code I get an ADO Error : Incorrect Syntax near the keyword "LIKE"

If I take the CASE statement out I get the certificates for a person, but have no way of know that the 3 sptf certificates are for what sport

What am I doing wrong - here is my code
SELECT     TOP 100 PERCENT MAX(dbo.people.Code) AS StateID, dbo.people.Division AS Delegation, dbo.people.Name AS ParticipantName,
                      dbo.people.GamesDelegation1, dbo.people.GamesDelegation2, dbo.people.GamesDelegation3, dbo.people.GamesDelegation4,
                      dbo.people.GamesDelegation5, MAX(dbo.certifs.Kind) AS CertKind, MAX(dbo.certifs.Subkind) AS Sub, MAX(dbo.certifs.[Effective date]) AS EffDate,
                      dbo.certifs.[Expiration date] AS ExpDate, dbo.groups.Name AS DelegationName, dbo.certifs.Kind,
                      TypeCert = (CASE dbo.certifs.Misc WHEN LIKE '%Cert:sw%' THEN 'Aquatics') AND (dbo.certifs.Kind = 'sptf')
                      ELSE (CASE WHEN dbo.certifs.Misc LIKE '%Cert:at%' AND (dbo.certifs.Kind = 'sptf') THEN 'Athletics'
                      ELSE (CASE WHEN dbo.certifs.Misc LIKE '%Cert:bb%' AND (dbo.certifs.Kind = 'sptf') THEN 'Basketball'
                      ELSE (CASE WHEN dbo.certifs.Misc LIKE '%Cert:bu%' AND (dbo.certifs.Kind = 'sptf') THEN 'Bocce'
                      ELSE (CASE WHEN dbo.certifs.Misc LIKE '%Cert:bo%' AND (dbo.certifs.Kind = 'sptf') THEN 'Bowling'
                      ELSE (CASE WHEN dbo.certifs.Misc LIKE '%Cert:eq%' AND (dbo.certifs.Kind = 'sptf') THEN 'Equestrian'
                      ELSE (CASE WHEN dbo.certifs.Misc LIKE '%Cert:gf%' AND (dbo.certifs.Kind = 'sptf') THEN 'Golf'
                      ELSE (CASE WHEN dbo.certifs.Misc LIKE '%Cert:po%' AND (dbo.certifs.Kind = 'sptf') THEN 'PowerLifting'
                      ELSE (CASE WHEN dbo.certifs.Misc LIKE '%Cert:sb%' AND (dbo.certifs.Kind = 'sptf') THEN 'Softball'
                      ELSE (CASE WHEN dbo.certifs.Misc LIKE '%Cert:vb%' AND (dbo.certifs.Kind = 'sptf') THEN 'Volleyball'
                      ELSE '      '
                          END) END) END) END) END) END) END) END) END) END)
FROM         dbo.people INNER JOIN
                      dbo.certifs ON dbo.people.Code = dbo.certifs.Owner INNER JOIN
                      dbo.groups ON dbo.people.Division = dbo.groups.Code
WHERE     (dbo.certifs.Kind = 'lvl1') OR
                      (dbo.certifs.Kind = 'lvl3') OR
                      (dbo.certifs.Kind = 'med') OR
                      (dbo.certifs.Kind = 'cons') OR
                      (dbo.certifs.Kind = 'ha-c') OR
                      (dbo.certifs.Kind = 'sptf') AND (dbo.certifs.Misc LIKE '%Cert:sw%') OR
                      (dbo.certifs.Kind = 'sptf') AND (dbo.certifs.Misc LIKE '%Cert:at') OR
                      (dbo.certifs.Kind = 'sptf') AND (dbo.certifs.Misc LIKE '%Cert:bb%') OR
                      (dbo.certifs.Kind = 'sptf') AND (dbo.certifs.Misc LIKE '%Cert:bo%') OR
                      (dbo.certifs.Kind = 'sptf') AND (dbo.certifs.Misc LIKE '%Cert:bu%') OR
                      (dbo.certifs.Kind = 'sptf') AND (dbo.certifs.Misc LIKE '%Cert:eq%') OR
                      (dbo.certifs.Kind = 'sptf') AND (dbo.certifs.Misc LIKE '%Cert:gf%') OR
                      (dbo.certifs.Kind = 'sptf') AND (dbo.certifs.Misc LIKE '%Cert:po%') OR
                      (dbo.certifs.Kind = 'sptf') AND (dbo.certifs.Misc LIKE '%Cert:sb%') OR
                      (dbo.certifs.Kind = 'sptf') AND (dbo.certifs.Misc LIKE '%Cert:vb%')
GROUP BY dbo.people.Division, dbo.people.Name, dbo.people.GamesDelegation1, dbo.people.GamesDelegation2, dbo.people.GamesDelegation3,
                      dbo.people.GamesDelegation4, dbo.people.GamesDelegation5, dbo.certifs.[Expiration date], dbo.groups.Name, dbo.certifs.Kind
ORDER BY dbo.people.Name, MAX(dbo.certifs.Kind)
Comment
Watch Question

Try this

SELECT     TOP 100 PERCENT MAX(dbo.people.Code) AS StateID, dbo.people.Division AS Delegation, dbo.people.Name AS ParticipantName,
                      dbo.people.GamesDelegation1, dbo.people.GamesDelegation2, dbo.people.GamesDelegation3, dbo.people.GamesDelegation4,
                      dbo.people.GamesDelegation5, MAX(dbo.certifs.Kind) AS CertKind, MAX(dbo.certifs.Subkind) AS Sub, MAX(dbo.certifs.[Effective date]) AS EffDate,
                      dbo.certifs.[Expiration date] AS ExpDate, dbo.groups.Name AS DelegationName, dbo.certifs.Kind,
                      TypeCert =
                  CASE       WHEN dbo.certifs.Misc LIKE '%Cert:sw%' AND (dbo.certifs.Kind = 'sptf') THEN 'Aquatics'  
                                  WHEN dbo.certifs.Misc LIKE '%Cert:at%' AND (dbo.certifs.Kind = 'sptf') THEN 'Athletics'
                                  WHEN dbo.certifs.Misc LIKE '%Cert:bb%' AND (dbo.certifs.Kind = 'sptf') THEN 'Basketball'
                                  WHEN dbo.certifs.Misc LIKE '%Cert:bu%' AND (dbo.certifs.Kind = 'sptf') THEN 'Bocce'
                                  WHEN dbo.certifs.Misc LIKE '%Cert:bo%' AND (dbo.certifs.Kind = 'sptf') THEN 'Bowling'
                                  WHEN dbo.certifs.Misc LIKE '%Cert:eq%' AND (dbo.certifs.Kind = 'sptf') THEN 'Equestrian'
                                  WHEN dbo.certifs.Misc LIKE '%Cert:gf%' AND (dbo.certifs.Kind = 'sptf') THEN 'Golf'
                                  WHEN dbo.certifs.Misc LIKE '%Cert:po%' AND (dbo.certifs.Kind = 'sptf') THEN 'PowerLifting'
                                  WHEN dbo.certifs.Misc LIKE '%Cert:sb%' AND (dbo.certifs.Kind = 'sptf') THEN 'Softball'
                                  WHEN dbo.certifs.Misc LIKE '%Cert:vb%' AND (dbo.certifs.Kind = 'sptf') THEN 'Volleyball'
                            ELSE '      '
                        End
FROM         dbo.people INNER JOIN
                      dbo.certifs ON dbo.people.Code = dbo.certifs.Owner INNER JOIN
                      dbo.groups ON dbo.people.Division = dbo.groups.Code
WHERE     (dbo.certifs.Kind = 'lvl1') OR
                      (dbo.certifs.Kind = 'lvl3') OR
                      (dbo.certifs.Kind = 'med') OR
                      (dbo.certifs.Kind = 'cons') OR
                      (dbo.certifs.Kind = 'ha-c') OR
                      (dbo.certifs.Kind = 'sptf') AND (dbo.certifs.Misc LIKE '%Cert:sw%') OR
                      (dbo.certifs.Kind = 'sptf') AND (dbo.certifs.Misc LIKE '%Cert:at') OR
                      (dbo.certifs.Kind = 'sptf') AND (dbo.certifs.Misc LIKE '%Cert:bb%') OR
                      (dbo.certifs.Kind = 'sptf') AND (dbo.certifs.Misc LIKE '%Cert:bo%') OR
                      (dbo.certifs.Kind = 'sptf') AND (dbo.certifs.Misc LIKE '%Cert:bu%') OR
                      (dbo.certifs.Kind = 'sptf') AND (dbo.certifs.Misc LIKE '%Cert:eq%') OR
                      (dbo.certifs.Kind = 'sptf') AND (dbo.certifs.Misc LIKE '%Cert:gf%') OR
                      (dbo.certifs.Kind = 'sptf') AND (dbo.certifs.Misc LIKE '%Cert:po%') OR
                      (dbo.certifs.Kind = 'sptf') AND (dbo.certifs.Misc LIKE '%Cert:sb%') OR
                      (dbo.certifs.Kind = 'sptf') AND (dbo.certifs.Misc LIKE '%Cert:vb%')
GROUP BY dbo.people.Division, dbo.people.Name, dbo.people.GamesDelegation1, dbo.people.GamesDelegation2, dbo.people.GamesDelegation3,
                      dbo.people.GamesDelegation4, dbo.people.GamesDelegation5, dbo.certifs.[Expiration date], dbo.groups.Name, dbo.certifs.Kind
ORDER BY dbo.people.Name, MAX(dbo.certifs.Kind)

Commented:
It looks as you were trying to assign to things at once

TypeCert = (CASE dbo.certifs.Misc WHEN LIKE '%Cert:sw%' THEN 'Aquatics') AND (dbo.certifs.Kind = 'sptf')

I did the same thing asvforce did, move the statement from the CASE to the WHEN  and get rid of all of the other CASE statements.

CASE  WHEN dbo.certifs.Misc LIKE '%Cert:sw%' THEN 'Aquatics'
                       WHEN dbo.certifs.Misc  LIKE '%Cert:at%' AND dbo.certifs.Kind = 'sptf' THEN 'Athletics'
                       WHEN dbo.certifs.Misc LIKE '%Cert:bb%' AND dbo.certifs.Kind = 'sptf' THEN 'Basketball'
                       WHEN dbo.certifs.Misc LIKE '%Cert:bu%' AND dbo.certifs.Kind = 'sptf' THEN 'Bocce'
                       WHEN dbo.certifs.Misc LIKE '%Cert:bo%' AND dbo.certifs.Kind = 'sptf' THEN 'Bowling'
                       WHEN dbo.certifs.Misc LIKE '%Cert:eq%' AND dbo.certifs.Kind = 'sptf' THEN 'Equestrian'
                       WHEN dbo.certifs.Misc LIKE '%Cert:gf%' AND dbo.certifs.Kind = 'sptf' THEN 'Golf'
                       WHEN dbo.certifs.Misc LIKE '%Cert:po%' AND dbo.certifs.Kind = 'sptf' THEN 'PowerLifting'
                       WHEN dbo.certifs.Misc LIKE '%Cert:sb%' AND dbo.certifs.Kind = 'sptf' THEN 'Softball'
                       WHEN dbo.certifs.Misc LIKE '%Cert:vb%' AND dbo.certifs.Kind = 'sptf' THEN 'Volleyball'
                      ELSE ''
                           END as typeCert

Author

Commented:
When I use that I get 10 errors saying Column 'dbo.certifs.Misc' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Author

Commented:
If I put dbo.certifs.Misc in the GROUP BY I get  ADO error: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator

Commented:
IN the GROUP BY

CAST(dbo.certifs.Misc as varchar(8000))

Author

Commented:
I get the same error when I use CAST(dbo.certifs.Misc as varchar(8000))

Commented:
That is odd. You have the GROUP BY like this:

GROUP BY dbo.people.Division, dbo.people.Name, dbo.people.GamesDelegation1, dbo.people.GamesDelegation2, dbo.people.GamesDelegation3,
                      dbo.people.GamesDelegation4, dbo.people.GamesDelegation5, dbo.certifs.[Expiration date], dbo.groups.Name, dbo.certifs.Kind, CAST(dbo.certifs.Misc as varchar(8000))

***
I ask because you have explicitly cast the Misc column as a varchar column.

Author

Commented:
I tried putting it last like you have and also first and got the same error
Top Expert 2007

Commented:

SELECT *,
CASE Kind WHEN 'sptf' THEN
CASE
WHEN Misc LIKE '%Cert:sw%' THEN 'Aquatics'  
WHEN Misc LIKE '%Cert:at%' THEN 'Athletics'
WHEN Misc LIKE '%Cert:bb%' THEN 'Basketball'
WHEN Misc LIKE '%Cert:bu%' THEN 'Bocce'
WHEN Misc LIKE '%Cert:bo%' THEN 'Bowling'
WHEN Misc LIKE '%Cert:eq%' THEN 'Equestrian'
WHEN Misc LIKE '%Cert:gf%' THEN 'Golf'
WHEN Misc LIKE '%Cert:po%' THEN 'PowerLifting'
WHEN Misc LIKE '%Cert:sb%' THEN 'Softball'
WHEN Misc LIKE '%Cert:vb%' THEN 'Volleyball'
ELSE '      '
END
ELSE '      '
End as Misc
FROM
(
SELECT 
TOP 100 PERCENT MAX(dbo.people.Code) AS StateID, dbo.people.Division AS Delegation, dbo.people.Name AS ParticipantName,
dbo.people.GamesDelegation1, dbo.people.GamesDelegation2, dbo.people.GamesDelegation3, dbo.people.GamesDelegation4,
dbo.people.GamesDelegation5, MAX(dbo.certifs.Kind) AS CertKind, MAX(dbo.certifs.Subkind) AS Sub, MAX(dbo.certifs.[Effective date]) AS EffDate,
dbo.certifs.[Expiration date] AS ExpDate, dbo.groups.Name AS DelegationName, dbo.certifs.Kind,
Convert(Varchar(400), dbo.certifs.Misc) as Misc
FROM         dbo.people
INNER JOIN
dbo.certifs ON dbo.people.Code = dbo.certifs.Owner INNER JOIN
dbo.groups ON dbo.people.Division = dbo.groups.Code
WHERE     (dbo.certifs.Kind = 'lvl1') OR
(dbo.certifs.Kind = 'lvl3') OR
(dbo.certifs.Kind = 'med') OR
(dbo.certifs.Kind = 'cons') OR
(dbo.certifs.Kind = 'ha-c') OR
(dbo.certifs.Kind = 'sptf') AND (Convert(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:sw%') OR
(dbo.certifs.Kind = 'sptf') AND (Convert(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:at') OR
(dbo.certifs.Kind = 'sptf') AND (Convert(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:bb%') OR
(dbo.certifs.Kind = 'sptf') AND (Convert(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:bo%') OR
(dbo.certifs.Kind = 'sptf') AND (Convert(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:bu%') OR
(dbo.certifs.Kind = 'sptf') AND (Convert(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:eq%') OR
(dbo.certifs.Kind = 'sptf') AND (Convert(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:gf%') OR
(dbo.certifs.Kind = 'sptf') AND (Convert(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:po%') OR
(dbo.certifs.Kind = 'sptf') AND (Convert(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:sb%') OR
(dbo.certifs.Kind = 'sptf') AND (Convert(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:vb%')
GROUP BY dbo.people.Division, dbo.people.Name, dbo.people.GamesDelegation1, dbo.people.GamesDelegation2, dbo.people.GamesDelegation3,
dbo.people.GamesDelegation4, dbo.people.GamesDelegation5, dbo.certifs.[Expiration date], dbo.groups.Name, dbo.certifs.Kind,
Convert(Varchar(400), dbo.certifs.Misc)
ORDER BY dbo.people.Name, MAX(dbo.certifs.Kind)
) Q

Open in new window

Top Expert 2007

Commented:
Please replace the top select * with the actual column names, otherwise you get MISC twice.

Author

Commented:
when I use
SELECT     MAX(dbo.people.Code) AS StateID, dbo.people.Division AS Delegation, dbo.people.Name AS ParticipantName, dbo.people.GamesDelegation1,
                      dbo.people.GamesDelegation2, dbo.people.GamesDelegation3, dbo.people.GamesDelegation4, dbo.people.GamesDelegation5, MAX(dbo.certifs.Kind)
                      AS CertKind, MAX(dbo.certifs.Subkind) AS Sub, MAX(dbo.certifs.[Effective date]) AS EffDate, dbo.certifs.[Expiration date] AS ExpDate,
                      dbo.groups.Name AS DelegationName, dbo.certifs.Kind, CONVERT(Varchar(400), dbo.certifs.Misc) AS Misc,
                      CASE Kind WHEN 'sptf' THEN CASE WHEN Misc LIKE '%Cert:sw%' THEN 'Aquatics' WHEN Misc LIKE '%Cert:at%' THEN 'Athletics' WHEN Misc LIKE '%Cert:bb%'
                       THEN 'Basketball' WHEN Misc LIKE '%Cert:bu%' THEN 'Bocce' WHEN Misc LIKE '%Cert:bo%' THEN 'Bowling' WHEN Misc LIKE '%Cert:eq%' THEN 'Equestrian'
                       WHEN Misc LIKE '%Cert:gf%' THEN 'Golf' WHEN Misc LIKE '%Cert:po%' THEN 'PowerLifting' WHEN Misc LIKE '%Cert:sb%' THEN 'Softball' WHEN Misc LIKE
                       '%Cert:vb%' THEN 'Volleyball' ELSE '      ' END ELSE '      ' END AS Misc
FROM         (SELECT     TOP 100 PERCENT MAX(dbo.people.Code) AS StateID, dbo.people.Division AS Delegation, dbo.people.Name AS ParticipantName,
                                              dbo.people.GamesDelegation1, dbo.people.GamesDelegation2, dbo.people.GamesDelegation3, dbo.people.GamesDelegation4,
                                              dbo.people.GamesDelegation5, MAX(dbo.certifs.Kind) AS CertKind, MAX(dbo.certifs.Subkind) AS Sub, MAX(dbo.certifs.[Effective date])
                                              AS EffDate, dbo.certifs.[Expiration date] AS ExpDate, dbo.groups.Name AS DelegationName, dbo.certifs.Kind, CONVERT(Varchar(400),
                                              dbo.certifs.Misc) AS Misc
                       FROM          dbo.people INNER JOIN
                                              dbo.certifs ON dbo.people.Code = dbo.certifs.Owner INNER JOIN
                                              dbo.groups ON dbo.people.Division = dbo.groups.Code
                       WHERE      (dbo.certifs.Kind = 'lvl1') OR
                                              (dbo.certifs.Kind = 'lvl3') OR
                                              (dbo.certifs.Kind = 'med') OR
                                              (dbo.certifs.Kind = 'cons') OR
                                              (dbo.certifs.Kind = 'ha-c') OR
                                              (dbo.certifs.Kind = 'sptf') AND (CONVERT(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:sw%') OR
                                              (dbo.certifs.Kind = 'sptf') AND (CONVERT(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:at') OR
                                              (dbo.certifs.Kind = 'sptf') AND (CONVERT(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:bb%') OR
                                              (dbo.certifs.Kind = 'sptf') AND (CONVERT(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:bo%') OR
                                              (dbo.certifs.Kind = 'sptf') AND (CONVERT(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:bu%') OR
                                              (dbo.certifs.Kind = 'sptf') AND (CONVERT(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:eq%') OR
                                              (dbo.certifs.Kind = 'sptf') AND (CONVERT(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:gf%') OR
                                              (dbo.certifs.Kind = 'sptf') AND (CONVERT(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:po%') OR
                                              (dbo.certifs.Kind = 'sptf') AND (CONVERT(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:sb%') OR
                                              (dbo.certifs.Kind = 'sptf') AND (CONVERT(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:vb%')
                       GROUP BY dbo.people.Division, dbo.people.Name, dbo.people.GamesDelegation1, dbo.people.GamesDelegation2, dbo.people.GamesDelegation3,
                                              dbo.people.GamesDelegation4, dbo.people.GamesDelegation5, dbo.certifs.[Expiration date], dbo.groups.Name, dbo.certifs.Kind,
                                              CONVERT(Varchar(400), dbo.certifs.Misc)
                       ORDER BY dbo.people.Name, MAX(dbo.certifs.Kind))
I get an ADO Error: Line 37: Incorrect syntax near ")"
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I had to change

INNER JOIN dbo.groups g ON p.Division = g.Code

INNER JOIN dbo.groups g ON p.Delegation = g.Code

and it works great - thank you all for your help - I guess ScottPletcher: gets the points

Author

Commented:
or do I give part to him and part to imitchie: since he started the idea?
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
You can do as you wish with the points, but:

He didn't start my idea -- I worked from the original q and took a different approach, reworking each query individually, not just pushing your full original query down a level.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
I would expect the reworked version to also run considerably faster :-) .

Author

Commented:
I spoke to soon,  the query is suppose to return the latest / current record.  It finds everything ok - but it shows all of their medicals and consents that expire(a person can have multiple entries of the certificate - I just want the last one entered).  I thought that using MAX function on Kind, SubKind, Effective date and expire date was suppose to bring back just the latest one - what needs to change to get that correct
Top Expert 2007

Commented:
MikeGinMn:
If you need to reopen the question, please post a question in the Community Support zone with the this Question number.

In the meantime, I fixed my query:

SELECT     MAX(dbo.people.Code) AS StateID, dbo.people.Division AS Delegation, dbo.people.Name AS ParticipantName, dbo.people.GamesDelegation1,
                      dbo.people.GamesDelegation2, dbo.people.GamesDelegation3, dbo.people.GamesDelegation4, dbo.people.GamesDelegation5, MAX(dbo.certifs.Kind)
                      AS CertKind, MAX(dbo.certifs.Subkind) AS Sub, MAX(dbo.certifs.[Effective date]) AS EffDate, dbo.certifs.[Expiration date] AS ExpDate,
                      dbo.groups.Name AS DelegationName, dbo.certifs.Kind, CONVERT(Varchar(400), dbo.certifs.Misc) AS Misc,
                      CASE Kind WHEN 'sptf' THEN CASE WHEN Misc LIKE '%Cert:sw%' THEN 'Aquatics' WHEN Misc LIKE '%Cert:at%' THEN 'Athletics' WHEN Misc LIKE '%Cert:bb%'
                       THEN 'Basketball' WHEN Misc LIKE '%Cert:bu%' THEN 'Bocce' WHEN Misc LIKE '%Cert:bo%' THEN 'Bowling' WHEN Misc LIKE '%Cert:eq%' THEN 'Equestrian'
                       WHEN Misc LIKE '%Cert:gf%' THEN 'Golf' WHEN Misc LIKE '%Cert:po%' THEN 'PowerLifting' WHEN Misc LIKE '%Cert:sb%' THEN 'Softball' WHEN Misc LIKE
                       '%Cert:vb%' THEN 'Volleyball' ELSE '      ' END ELSE '      ' END AS Misc
FROM         (SELECT     TOP 100 PERCENT MAX(dbo.people.Code) AS StateID, dbo.people.Division AS Delegation, dbo.people.Name AS ParticipantName,
                                              dbo.people.GamesDelegation1, dbo.people.GamesDelegation2, dbo.people.GamesDelegation3, dbo.people.GamesDelegation4,
                                              dbo.people.GamesDelegation5, MAX(dbo.certifs.Kind) AS CertKind, MAX(dbo.certifs.Subkind) AS Sub, MAX(dbo.certifs.[Effective date])
                                              AS EffDate, dbo.certifs.[Expiration date] AS ExpDate, dbo.groups.Name AS DelegationName, dbo.certifs.Kind, CONVERT(Varchar(400),
                                              dbo.certifs.Misc) AS Misc
                       FROM          dbo.people INNER JOIN
                                              dbo.certifs ON dbo.people.Code = dbo.certifs.Owner INNER JOIN
                                              dbo.groups ON dbo.people.Division = dbo.groups.Code
                       WHERE      (dbo.certifs.Kind = 'lvl1') OR
                                              (dbo.certifs.Kind = 'lvl3') OR
                                              (dbo.certifs.Kind = 'med') OR
                                              (dbo.certifs.Kind = 'cons') OR
                                              (dbo.certifs.Kind = 'ha-c') OR
                                              (dbo.certifs.Kind = 'sptf') AND (CONVERT(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:sw%') OR
                                              (dbo.certifs.Kind = 'sptf') AND (CONVERT(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:at') OR
                                              (dbo.certifs.Kind = 'sptf') AND (CONVERT(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:bb%') OR
                                              (dbo.certifs.Kind = 'sptf') AND (CONVERT(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:bo%') OR
                                              (dbo.certifs.Kind = 'sptf') AND (CONVERT(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:bu%') OR
                                              (dbo.certifs.Kind = 'sptf') AND (CONVERT(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:eq%') OR
                                              (dbo.certifs.Kind = 'sptf') AND (CONVERT(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:gf%') OR
                                              (dbo.certifs.Kind = 'sptf') AND (CONVERT(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:po%') OR
                                              (dbo.certifs.Kind = 'sptf') AND (CONVERT(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:sb%') OR
                                              (dbo.certifs.Kind = 'sptf') AND (CONVERT(Varchar(400), dbo.certifs.Misc) LIKE '%Cert:vb%')
                       GROUP BY dbo.people.Division, dbo.people.Name, dbo.people.GamesDelegation1, dbo.people.GamesDelegation2, dbo.people.GamesDelegation3,
                                              dbo.people.GamesDelegation4, dbo.people.GamesDelegation5, dbo.certifs.[Expiration date], dbo.groups.Name, dbo.certifs.Kind,
                                              CONVERT(Varchar(400), dbo.certifs.Misc)
                       ORDER BY dbo.people.Name, MAX(dbo.certifs.Kind)) X
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Yes, it was a complete re-structuring of the query, so it's definitely possible that something is different.  I tried very hard to keep the same criteria.  Without sample data w/ desired results, it's very difficult to know what adjustments to make to correct it.


>> a person can have multiple entries of the certificate - I just want the last one entered <<

The last one for each kind or just one per person, period?
Top Expert 2007

Commented:
[Yes, it was a complete re-structuring of the query, so it's definitely possible that something is different.]
Maybe it'd help Mike to understand why you felt you had to restructure the query and what each part is doing.

Author

Commented:
Here is more info for you then

Here is what the current query returns

SOMAGICPER014624,SOMAGICDEL009LEP,Abraham, Jeffrey,SOMAGICDEL009LEP,cons,10/10/1998, ,MN.09.LEP: LEEP-Mankato,cons           
SOMAGICPER014624,SOMAGICDEL009LEP,Abraham, Jeffrey,SOMAGICDEL009LEP,cons,7/22/2003, ,MN.09.LEP: LEEP-Mankato,cons           
SOMAGICPER014624,SOMAGICDEL009LEP,Abraham, Jeffrey,SOMAGICDEL009LEP,cons,9/13/2006, ,MN.09.LEP: LEEP-Mankato,cons           
SOMAGICPER014624,SOMAGICDEL009LEP,Abraham, Jeffrey,SOMAGICDEL009LEP,med,10/10/1998,10/31/2004,MN.09.LEP: LEEP-Mankato,med           
SOMAGICPER014624,SOMAGICDEL009LEP,Abraham, Jeffrey,SOMAGICDEL009LEP,med,7/3/2003,7/2/2006,MN.09.LEP: LEEP-Mankato,med           
SOMAGICPER014624,SOMAGICDEL009LEP,Abraham, Jeffrey,SOMAGICDEL009LEP,med,6/20/2006,6/19/2009,MN.09.LEP: LEEP-Mankato,med           


Here is what I need

SOMAGICPER014624,SOMAGICDEL009LEP,Abraham, Jeffrey,SOMAGICDEL009LEP,cons,9/13/2006, ,MN.09.LEP: LEEP-Mankato,cons
SOMAGICPER014624,SOMAGICDEL009LEP,Abraham, Jeffrey,SOMAGICDEL009LEP,med,6/20/2006,6/19/2009,MN.09.LEP: LEEP-Mankato,med           

the records in certifs contain a record with the following

      Owner                  varchar            16      1      
      Kind                  varchar            4      1      
      Subkind                  varchar            4      1      
      Code                  varchar            16      0      
      [Effective date]      datetime      8      1      
      [Expiration date]      datetime      8      1      
      [Reference event]      varchar            16      1      
      Misc                  ntext            16      1      

Jeff has a records like this

SOMAGICPER014624,cons,7/22/2003
SOMAGICPER014624,cons,9/13/2006
SOMAGICPER014624,cons,10/10/1998
SOMAGICPER014624,med,10/10/1998,10/31/2004
SOMAGICPER014624,med,7/3/2003,7/2/2006
SOMAGICPER014624,med,6/20/2006,6/19/2009

a certificate may or may not have a expire date(if older than 18 - consent is good for life unless major life event/change happens), medicals are due every three years
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:

imitchie:

You mean the GROUP BY doesn't convince you that the query is not in the best form??

GROUP BY dbo.people.Division, dbo.people.Name, dbo.people.GamesDelegation1, bbo.people.GamesDelegation2, dbo.people.GamesDelegation3, dbo.people.GamesDelegation4, dbo.people.GamesDelegation5, dbo.certifs.[Expiration date], dbo.groups.Name, dbo.certifs.Kind, CONVERT(Varchar(400), dbo.certifs.Misc)

Yikes!


Btw, if you intend to keep that grouping, you should at least make the first column(s) of the grouping the same as the order by, so it's less overhead.  So, given:

ORDER BY dbo.people.Name, MAX(dbo.certifs.Kind)) X

the grouping should start with dbo.people.Name, i.e.:

GROUP BY dbo.people.Name, dbo.people.Division, dbo.people.GamesDelegation1,
...
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:

Thanks Mike!

So it looks like the last [Effective Date] for each Kind of certif, but you still need to see the expiration date ... grr, that's annoying, more work for SQL ... now I see why you just joined it directly in the original query instead of using a derived table :-) .

The "clean" way to do it is to use the first derived table to get the last [eff date], then join back to the original table to get the other needed column(s) (in this case [exp date]).  I'll use that method first.  If it runs too slowly for you, we can try a little "trick" to use just the derived table and see if the overhead of the trick is less than having to re-join to the original table.
SELECT p.StateID, p.Delegation, p.ParticipantName, p.GamesDelegation1, 
    p.GamesDelegation2, p.GamesDelegation3, p.GamesDelegation4, p.GamesDelegation5,  
    c.CertKind, c.Sub, c.EffDate, c2.[Expiration date], 
    g.Name AS DelegationName, c.Kind, c.TypeCert
FROM       (
    SELECT   MAX(dbo.people.Code) AS StateID, dbo.people.Division AS Delegation, 
        dbo.people.Name AS ParticipantName, dbo.people.GamesDelegation1, 
        dbo.people.GamesDelegation2, dbo.people.GamesDelegation3, 
        dbo.people.GamesDelegation4, dbo.people.GamesDelegation5
    FROM     dbo.people 
    GROUP BY dbo.people.Division, dbo.people.Name, dbo.people.GamesDelegation1, dbo.people.GamesDelegation2,
                      dbo.people.GamesDelegation3, dbo.people.GamesDelegation4, dbo.people.GamesDelegation5
) AS p
INNER JOIN (
    SELECT   MAX(dbo.certifs.Kind) AS CertKind, MAX(dbo.certifs.Subkind) AS Sub, 
             MAX(dbo.certifs.[Effective date]) AS EffDate, 
             dbo.certifs.Owner, dbo.certifs.Kind, 
             TypeCert = 
             CASE WHEN dbo.certifs.Kind = 'sptf' THEN
                 CASE WHEN dbo.certifs.Misc LIKE '%Cert:sw%' THEN 'Aquatics'
                      WHEN dbo.certifs.Misc LIKE '%Cert:at%' THEN 'Athletics'
                      WHEN dbo.certifs.Misc LIKE '%Cert:bb%' THEN 'Basketball' 
                      WHEN dbo.certifs.Misc LIKE '%Cert:bu%' THEN 'Bocce' 
                      WHEN dbo.certifs.Misc LIKE '%Cert:bo%' THEN 'Bowling'
                      WHEN dbo.certifs.Misc LIKE '%Cert:eq%' THEN 'Equestrian' 
                      WHEN dbo.certifs.Misc LIKE '%Cert:gf%' THEN 'Golf'
                      WHEN dbo.certifs.Misc LIKE '%Cert:po%' THEN 'PowerLifting' 
                      WHEN dbo.certifs.Misc LIKE '%Cert:sb%' THEN 'Softball' 
                      WHEN dbo.certifs.Misc LIKE '%Cert:vb%' THEN 'Volleyball' 
                  ELSE '      ' END
              ELSE '      ' END
    FROM dbo.certifs
    WHERE     (dbo.certifs.Kind = 'lvl1') OR
              (dbo.certifs.Kind = 'lvl3') OR
              (dbo.certifs.Kind = 'med') OR
              (dbo.certifs.Kind = 'cons') OR
              (dbo.certifs.Kind = 'ha-c') OR
              ((dbo.certifs.Kind = 'sptf') AND 
               (dbo.certifs.Misc LIKE '%Cert:sw%' OR
                dbo.certifs.Misc LIKE '%Cert:at' OR
                dbo.certifs.Misc LIKE '%Cert:bb%' OR
                dbo.certifs.Misc LIKE '%Cert:bo%' OR
                dbo.certifs.Misc LIKE '%Cert:bu%' OR
                dbo.certifs.Misc LIKE '%Cert:eq%' OR
                dbo.certifs.Misc LIKE '%Cert:gf%' OR
                dbo.certifs.Misc LIKE '%Cert:po%' OR
                dbo.certifs.Misc LIKE '%Cert:sb%' OR
                dbo.certifs.Misc LIKE '%Cert:vb%'))
    GROUP BY dbo.certifs.Owner, dbo.certifs.Kind,
             CASE WHEN dbo.certifs.Kind = 'sptf' THEN
                 CASE WHEN dbo.certifs.Misc LIKE '%Cert:sw%' THEN 'Aquatics'
                      WHEN dbo.certifs.Misc LIKE '%Cert:at%' THEN 'Athletics'
                      WHEN dbo.certifs.Misc LIKE '%Cert:bb%' THEN 'Basketball' 
                      WHEN dbo.certifs.Misc LIKE '%Cert:bu%' THEN 'Bocce' 
                      WHEN dbo.certifs.Misc LIKE '%Cert:bo%' THEN 'Bowling'
                      WHEN dbo.certifs.Misc LIKE '%Cert:eq%' THEN 'Equestrian' 
                      WHEN dbo.certifs.Misc LIKE '%Cert:gf%' THEN 'Golf'
                      WHEN dbo.certifs.Misc LIKE '%Cert:po%' THEN 'PowerLifting' 
                      WHEN dbo.certifs.Misc LIKE '%Cert:sb%' THEN 'Softball' 
                      WHEN dbo.certifs.Misc LIKE '%Cert:vb%' THEN 'Volleyball' 
                  ELSE '      ' END
              ELSE '      ' END
) AS c ON p.StateID = c.Owner
INNER JOIN dbo.certifs c2 ON c.Owner = c2.Owner AND c.Kind = c2.Kind AND
    c.EffDate = c2.[Effective date]
INNER JOIN dbo.groups g ON p.Division = g.Code
ORDER BY p.ParticipantName, c.Kind

Open in new window

Author

Commented:
OK I tried that I had to add a closing ")" just before ORDER and that got rid of one error.  But I keep getting an incorrect syntax near keyword "AS".  I went thru and don't see any of them that appear wrong - what should I be looking for

Author

Commented:
Sorry wrote down the wrong error message.  I was getting an error saying I could use ORDER with out a TOP - so after trial and error I added a Top 100 Percent to each select and I think that has it - am going to check a few more names to make sure .  Thank you so very much for your help.  I just wish I had a little bigger fraction of the knowledge you have.  Can you recommend any good books to learn more about this?

Author

Commented:
Almost there - heres what happening, for the sptf that have the same effective and expire date I'm getting multiple records return.  Here is Alice's
info in certifs



Name      Code      Kind      Subkind      Effective date      Expiration date      Misc      Expr1
Braam, Alice E.      SOMAGICPER001233      djob            1/30/2001            "created:38120.6467628819
delegation:SOMAGICDEL009AMT
lastChange:38120.6467628819
role:col
"      4MGIDN2J1EUM8CET
Braam, Alice E.      SOMAGICPER001233      djob            1/2/1997            "created:38120.6467637847
delegation:SOMAGICDEL009STP
lastChange:38120.6467637847
role:hod
"      S3NC9LLI7W19DO38
Braam, Alice E.      SOMAGICPER001233      lvl1            4/16/2001            "created:38113.7226434491
gensession:Y
lastChange:38113.7226434491
protective:N
"      9690D0HUST2BKJBU
Braam, Alice E.      SOMAGICPER001233      lvl1            3/8/2004            "created:38294.6664622569
lastChange:38294.6665705787
protective:Y
sigdate:2
"      F85AJV5QITSWGQ9K
Braam, Alice E.      SOMAGICPER001233      lvl1            2/2/2006            "created:38750.4739820486
gensession:Y
lastChange:38750.4739994097
protective:Y
"      N2N2JQ7PATALHQS4
Braam, Alice E.      SOMAGICPER001233      lvl3            3/31/2004      3/30/2008      "cert:gm
created:38749.6024009722
lastChange:38749.6028227431
rddate:38390
rdsite:Delivered by Kim Gelperin
ri:ds
"      1RW014LKTF072725
Braam, Alice E.      SOMAGICPER001233      lvl3            10/7/2000      10/6/2004      "cert:oth
created:38642.5208827315
lastChange:38642.5209415046
"      AEJ0K5TDQAVD26TA
Braam, Alice E.      SOMAGICPER001233      sptf            9/19/2005      9/18/2009      "Cert:bu
created:38749.5791204977
expdate:2
lastChange:38749.5792897917
rddate:38668
rdsite:Fall Bowling Classic
ri:bc
"      DUVTPYL3QJN9KOG3
Braam, Alice E.      SOMAGICPER001233      sptf            4/2/2004      4/1/2008      "Cert:vb,vt
created:38755.6212818287
lastChange:38755.6214642014
ri:bc
"      ELZ3G8T910M0C40Z
Braam, Alice E.      SOMAGICPER001233      sptf            3/31/2004      3/30/2008      "Cert:at
created:38749.6140514352
expdate:2
lastChange:38749.6151130787
protective:N
recert:Y
sigdate:2
"      SOMAGICC03303008
Braam, Alice E.      SOMAGICPER001233      sptf            3/31/2004      3/30/2008      "Cert:bo
created:38749.6140514352
expdate:2
lastChange:38749.6151955556
protective:N
recert:Y
sigdate:2
"      SOMAGICC03744322
Braam, Alice E.      SOMAGICPER001233      sptf            10/3/2002      3/30/2008      "Cert:gf
created:38755.6166341667
expdate:2
lastChange:38755.6208034259
protective:N
rddate:37722
recert:Y
ri:po
sigdate:2
"      SOMAGICC03895018
Braam, Alice E.      SOMAGICPER001233      sptf            3/31/2004      3/30/2008      "Cert:sb,st
created:38749.6140514352
expdate:2
lastChange:38749.6153387963
protective:N
recert:Y
sigdate:2
"      SOMAGICC05171924
Braam, Alice E.      SOMAGICPER001233      sptf            5/10/1999      3/30/2008      "Cert:sw
created:38755.6166341667
expdate:2
lastChange:38755.6182618634
protective:N
recert:Y
sigdate:2
"      SOMAGICC08605892
Braam, Alice E.      SOMAGICPER001233      sptf            3/2/1999      1/26/2009      "Cert:bb,bt
created:38755.6212818287
expdate:2
lastChange:38755.6220421875
notes:has two basketball certification forms (1999 an 2003)
protective:N
recert:Y
ri:po
"      SOMAGICC08684536

Here is what I get

StateID                       Delegation            ParticipantName      GamesDelegation1 GD2  GD3  GD4  GD5                    CertKind  Sub   EffDate      Expiration date      DelegationName      Kind      TypeCert
SOMAGICPER001233      SOMAGICDEL009STP      Braam, Alice E.      SOMAGICDEL009STP                              lvl1            2/2/2006            MN.09.STP: MN Valley Special Olympics      lvl1           
SOMAGICPER001233      SOMAGICDEL009STP      Braam, Alice E.      SOMAGICDEL009STP                              lvl3            3/31/2004      3/30/2008      MN.09.STP: MN Valley Special Olympics      lvl3           
SOMAGICPER001233      SOMAGICDEL009STP      Braam, Alice E.      SOMAGICDEL009STP                              sptf            5/10/1999      3/30/2008      MN.09.STP: MN Valley Special Olympics      sptf      Aquatics
SOMAGICPER001233      SOMAGICDEL009STP      Braam, Alice E.      SOMAGICDEL009STP                              sptf            3/31/2004      3/30/2008      MN.09.STP: MN Valley Special Olympics      sptf      Athletics
SOMAGICPER001233      SOMAGICDEL009STP      Braam, Alice E.      SOMAGICDEL009STP                              sptf            3/31/2004      3/30/2008      MN.09.STP: MN Valley Special Olympics      sptf      Athletics
SOMAGICPER001233      SOMAGICDEL009STP      Braam, Alice E.      SOMAGICDEL009STP                              sptf            3/31/2004      3/30/2008      MN.09.STP: MN Valley Special Olympics      sptf      Athletics
SOMAGICPER001233      SOMAGICDEL009STP      Braam, Alice E.      SOMAGICDEL009STP                              sptf            3/2/1999      1/26/2009      MN.09.STP: MN Valley Special Olympics      sptf      Basketball
SOMAGICPER001233      SOMAGICDEL009STP      Braam, Alice E.      SOMAGICDEL009STP                              sptf            9/19/2005      9/18/2009      MN.09.STP: MN Valley Special Olympics      sptf      Bocce
SOMAGICPER001233      SOMAGICDEL009STP      Braam, Alice E.      SOMAGICDEL009STP                              sptf            3/31/2004      3/30/2008      MN.09.STP: MN Valley Special Olympics      sptf      Bowling
SOMAGICPER001233      SOMAGICDEL009STP      Braam, Alice E.      SOMAGICDEL009STP                              sptf            3/31/2004      3/30/2008      MN.09.STP: MN Valley Special Olympics      sptf      Bowling
SOMAGICPER001233      SOMAGICDEL009STP      Braam, Alice E.      SOMAGICDEL009STP                              sptf            3/31/2004      3/30/2008      MN.09.STP: MN Valley Special Olympics      sptf      Bowling
SOMAGICPER001233      SOMAGICDEL009STP      Braam, Alice E.      SOMAGICDEL009STP                              sptf            10/3/2002      3/30/2008      MN.09.STP: MN Valley Special Olympics      sptf      Golf
SOMAGICPER001233      SOMAGICDEL009STP      Braam, Alice E.      SOMAGICDEL009STP                              sptf            3/31/2004      3/30/2008      MN.09.STP: MN Valley Special Olympics      sptf      Softball
SOMAGICPER001233      SOMAGICDEL009STP      Braam, Alice E.      SOMAGICDEL009STP                              sptf            3/31/2004      3/30/2008      MN.09.STP: MN Valley Special Olympics      sptf      Softball
SOMAGICPER001233      SOMAGICDEL009STP      Braam, Alice E.      SOMAGICDEL009STP                              sptf            3/31/2004      3/30/2008      MN.09.STP: MN Valley Special Olympics      sptf      Softball
SOMAGICPER001233      SOMAGICDEL009STP      Braam, Alice E.      SOMAGICDEL009STP                              sptf            4/2/2004      4/1/2008      MN.09.STP: MN Valley Special Olympics      sptf      Volleyball

As you can see she is getting 3 listed for Athletics, Bowling & Softball - I sure it is because of the fact that her effective & expire dates are the same for 3 different certificates - because
if I change the date of one - the number of duplicates goes down by one for each date I change.

But changing the for all date isn't an option so what next
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
>>  I sure it is because of the fact that her effective & expire dates are the same for 3 different certificates <<

Yep, quite right, you got it.

Hmm, is there a unique identifier/value on the rows (such as an IDENTITY column)?

If not, or just as another test, please try this:

SELECT p.StateID, p.Delegation, p.ParticipantName, p.GamesDelegation1, 
    p.GamesDelegation2, p.GamesDelegation3, p.GamesDelegation4, p.GamesDelegation5,  
    c.CertKind, c.Sub, 
    CONVERT(CHAR(10), CAST(LEFT(c.EffDateAndExpDate, 8) AS DATETIME), 101) AS EffDate,
    CONVERT(CHAR(10), CAST(SUBSTRING(c.EffDateAndExpDate, 9, 8) AS DATETIME), 101) AS ExpDate,    
    g.Name AS DelegationName, c.Kind, c.TypeCert
FROM       (
    SELECT   MAX(dbo.people.Code) AS StateID, dbo.people.Division AS Delegation, 
        dbo.people.Name AS ParticipantName, dbo.people.GamesDelegation1, 
        dbo.people.GamesDelegation2, dbo.people.GamesDelegation3, 
        dbo.people.GamesDelegation4, dbo.people.GamesDelegation5
    FROM     dbo.people 
    GROUP BY dbo.people.Division, dbo.people.Name, dbo.people.GamesDelegation1, dbo.people.GamesDelegation2,
                      dbo.people.GamesDelegation3, dbo.people.GamesDelegation4, dbo.people.GamesDelegation5
) AS p
INNER JOIN (
    SELECT   MAX(dbo.certifs.Kind) AS CertKind, MAX(dbo.certifs.Subkind) AS Sub, 
             MAX(CONVERT(CHAR(8), dbo.certifs.[Effective date], 112) +
                 CONVERT(CHAR(8), dbo.certifs.[Expiration date], 112))
                 AS EffDateAndExpDate, 
             dbo.certifs.Owner, dbo.certifs.Kind, 
             TypeCert = 
             CASE WHEN dbo.certifs.Kind = 'sptf' THEN
                 CASE WHEN dbo.certifs.Misc LIKE '%Cert:sw%' THEN 'Aquatics'
                      WHEN dbo.certifs.Misc LIKE '%Cert:at%' THEN 'Athletics'
                      WHEN dbo.certifs.Misc LIKE '%Cert:bb%' THEN 'Basketball' 
                      WHEN dbo.certifs.Misc LIKE '%Cert:bu%' THEN 'Bocce' 
                      WHEN dbo.certifs.Misc LIKE '%Cert:bo%' THEN 'Bowling'
                      WHEN dbo.certifs.Misc LIKE '%Cert:eq%' THEN 'Equestrian' 
                      WHEN dbo.certifs.Misc LIKE '%Cert:gf%' THEN 'Golf'
                      WHEN dbo.certifs.Misc LIKE '%Cert:po%' THEN 'PowerLifting' 
                      WHEN dbo.certifs.Misc LIKE '%Cert:sb%' THEN 'Softball' 
                      WHEN dbo.certifs.Misc LIKE '%Cert:vb%' THEN 'Volleyball' 
                  ELSE '      ' END
              ELSE '      ' END
    FROM dbo.certifs
    WHERE     (dbo.certifs.Kind = 'lvl1') OR
              (dbo.certifs.Kind = 'lvl3') OR
              (dbo.certifs.Kind = 'med') OR
              (dbo.certifs.Kind = 'cons') OR
              (dbo.certifs.Kind = 'ha-c') OR
              ((dbo.certifs.Kind = 'sptf') AND 
               (dbo.certifs.Misc LIKE '%Cert:sw%' OR
                dbo.certifs.Misc LIKE '%Cert:at' OR
                dbo.certifs.Misc LIKE '%Cert:bb%' OR
                dbo.certifs.Misc LIKE '%Cert:bo%' OR
                dbo.certifs.Misc LIKE '%Cert:bu%' OR
                dbo.certifs.Misc LIKE '%Cert:eq%' OR
                dbo.certifs.Misc LIKE '%Cert:gf%' OR
                dbo.certifs.Misc LIKE '%Cert:po%' OR
                dbo.certifs.Misc LIKE '%Cert:sb%' OR
                dbo.certifs.Misc LIKE '%Cert:vb%'))
    GROUP BY dbo.certifs.Owner, dbo.certifs.Kind,
             CASE WHEN dbo.certifs.Kind = 'sptf' THEN
                 CASE WHEN dbo.certifs.Misc LIKE '%Cert:sw%' THEN 'Aquatics'
                      WHEN dbo.certifs.Misc LIKE '%Cert:at%' THEN 'Athletics'
                      WHEN dbo.certifs.Misc LIKE '%Cert:bb%' THEN 'Basketball' 
                      WHEN dbo.certifs.Misc LIKE '%Cert:bu%' THEN 'Bocce' 
                      WHEN dbo.certifs.Misc LIKE '%Cert:bo%' THEN 'Bowling'
                      WHEN dbo.certifs.Misc LIKE '%Cert:eq%' THEN 'Equestrian' 
                      WHEN dbo.certifs.Misc LIKE '%Cert:gf%' THEN 'Golf'
                      WHEN dbo.certifs.Misc LIKE '%Cert:po%' THEN 'PowerLifting' 
                      WHEN dbo.certifs.Misc LIKE '%Cert:sb%' THEN 'Softball' 
                      WHEN dbo.certifs.Misc LIKE '%Cert:vb%' THEN 'Volleyball' 
                  ELSE '      ' END
              ELSE '      ' END
) AS c ON p.StateID = c.Owner
INNER JOIN dbo.groups g ON p.Division = g.Code
ORDER BY p.ParticipantName, c.Kind 

Open in new window

Author

Commented:
OK I did that but if anything doesn't have a expiration date it leaves both effective and expired blank.  So I checked expiration to see if null/empty and if it is I put a '8888/12/31' date in so that when it seperate for effective and expire something gets into those fields.  How do I get the '8888/12/31' out of expired date

SELECT     TOP 100 PERCENT p.StateID, p.Delegation, p.ParticipantName, p.GamesDelegation1, p.GamesDelegation2, p.GamesDelegation3,
                      p.GamesDelegation4, p.GamesDelegation5, c.CertKind, c.Sub, CONVERT(CHAR(10), CAST(LEFT(c.EffDateAndExpDate, 8) AS DATETIME), 101)
                      AS EffDate, CONVERT(CHAR(10), CAST(SUBSTRING(c.EffDateAndExpDate, 9, 8) AS DATETIME), 101) AS ExpDate, g.Name AS DelegationName, c.Kind,
                      c.TypeCert
FROM         (SELECT     MAX(dbo.people.Code) AS StateID, dbo.people.Division AS Delegation, dbo.people.Name AS ParticipantName,
                                              dbo.people.GamesDelegation1, dbo.people.GamesDelegation2, dbo.people.GamesDelegation3, dbo.people.GamesDelegation4,
                                              dbo.people.GamesDelegation5
                       FROM          dbo.people
                       GROUP BY dbo.people.Division, dbo.people.Name, dbo.people.GamesDelegation1, dbo.people.GamesDelegation2, dbo.people.GamesDelegation3,
                                              dbo.people.GamesDelegation4, dbo.people.GamesDelegation5) AS p INNER JOIN
                          (SELECT     MAX(dbo.certifs.Kind) AS CertKind, MAX(dbo.certifs.Subkind) AS Sub, MAX(CONVERT(CHAR(8), dbo.certifs.[Effective date], 112)
                                                   + CONVERT(CHAR(8), isnull(dbo.certifs.[Expiration date], '8888/12/31'), 112)) AS EffDateAndExpDate, dbo.certifs.Owner, dbo.certifs.Kind,
                                                   TypeCert = CASE WHEN dbo.certifs.Kind = 'sptf' THEN CASE WHEN dbo.certifs.Misc LIKE '%Cert:sw%' THEN 'Aquatics' WHEN dbo.certifs.Misc
                                                    LIKE '%Cert:at%' THEN 'Athletics' WHEN dbo.certifs.Misc LIKE '%Cert:bb%' THEN 'Basketball' WHEN dbo.certifs.Misc LIKE '%Cert:bu%' THEN
                                                    'Bocce' WHEN dbo.certifs.Misc LIKE '%Cert:bo%' THEN 'Bowling' WHEN dbo.certifs.Misc LIKE '%Cert:eq%' THEN 'Equestrian' WHEN dbo.certifs.Misc
                                                    LIKE '%Cert:gf%' THEN 'Golf' WHEN dbo.certifs.Misc LIKE '%Cert:po%' THEN 'PowerLifting' WHEN dbo.certifs.Misc LIKE '%Cert:sb%' THEN
                                                    'Softball' WHEN dbo.certifs.Misc LIKE '%Cert:vb%' THEN 'Volleyball' ELSE '      ' END ELSE '      ' END
                            FROM          dbo.certifs
                            WHERE      (dbo.certifs.Kind = 'lvl1') OR
                                                   (dbo.certifs.Kind = 'lvl3') OR
                                                   (dbo.certifs.Kind = 'med') OR
                                                   (dbo.certifs.Kind = 'cons') OR
                                                   (dbo.certifs.Kind = 'ha-c') OR
                                                   ((dbo.certifs.Kind = 'sptf') AND (dbo.certifs.Misc LIKE '%Cert:sw%' OR
                                                   dbo.certifs.Misc LIKE '%Cert:at' OR
                                                   dbo.certifs.Misc LIKE '%Cert:bb%' OR
                                                   dbo.certifs.Misc LIKE '%Cert:bo%' OR
                                                   dbo.certifs.Misc LIKE '%Cert:bu%' OR
                                                   dbo.certifs.Misc LIKE '%Cert:eq%' OR
                                                   dbo.certifs.Misc LIKE '%Cert:gf%' OR
                                                   dbo.certifs.Misc LIKE '%Cert:po%' OR
                                                   dbo.certifs.Misc LIKE '%Cert:sb%' OR
                                                   dbo.certifs.Misc LIKE '%Cert:vb%'))
                            GROUP BY dbo.certifs.Owner, dbo.certifs.Kind,
                                                   CASE WHEN dbo.certifs.Kind = 'sptf' THEN CASE WHEN dbo.certifs.Misc LIKE '%Cert:sw%' THEN 'Aquatics' WHEN dbo.certifs.Misc LIKE '%Cert:at%'
                                                    THEN 'Athletics' WHEN dbo.certifs.Misc LIKE '%Cert:bb%' THEN 'Basketball' WHEN dbo.certifs.Misc LIKE '%Cert:bu%' THEN 'Bocce' WHEN
                                                    dbo.certifs.Misc LIKE '%Cert:bo%' THEN 'Bowling' WHEN dbo.certifs.Misc LIKE '%Cert:eq%' THEN 'Equestrian' WHEN dbo.certifs.Misc LIKE
                                                    '%Cert:gf%' THEN 'Golf' WHEN dbo.certifs.Misc LIKE '%Cert:po%' THEN 'PowerLifting' WHEN dbo.certifs.Misc LIKE '%Cert:sb%' THEN 'Softball'
                                                    WHEN dbo.certifs.Misc LIKE '%Cert:vb%' THEN 'Volleyball' ELSE '      ' END ELSE '      ' END) AS c ON p.StateID = c.Owner INNER JOIN
                      dbo.groups g ON p.Delegation = g.Code
ORDER BY p.ParticipantName, c.Kind, TypeCert
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
...
+ ISNULL(CONVERT(CHAR(8), dbo.certifs.[Expiration date], 112), '88881231') AS ...

Author

Commented:
I don't think I made the statement correctly - when you build your effandexpdate I check Expiration Date to see if it is null - if it is I make the expire date to be 8888/12/31 and then you combine it with the efffective date (ie:  Effective Date - 11/23/2005 and no expiration date would be 11/23/2005/8888/12/31  then you split the date into its 2 parts into effdate and expdate which from my example would be 11/23/2005 and 12/31/8888.  How do I get rid of that 12/31/8888
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Ah, OK, now I understand.

Keep in mind that the inner query needs to use a MAX() on the (first) date.  Because of that, and for the minor performance benefit of having fewer characters, the format in the derived table is:
yyyymmddyyyymmdd
with, as you know, the first date being the eff date, the second being the exp date.

For the main (outermost) query, we will need to check for that bogus date and replace it with whatever value you want, for example:
...,
CASE WHEN SUBSTRING(c.EffDateAndExpDate, 9, 4) = '8888' THEN ''  --<<-- chg this to whatever you want to indicate a "missing" date
ELSE CONVERT(CHAR(10), CAST(SUBSTRING(c.EffDateAndExpDate, 9, 8) AS DATETIME), 101) END AS ExpDate
,...

Author

Commented:
I got it - I used a case statement to check the value and if it equaled 12/31/8888 I NULLed it out other wise I used your convert statement

Author

Commented:
THANK YOU THANK YOU THANK YOU - so much for helping I'm doing a project for Special Olympics and want it to be useful tool for the coaches and head of teams.  This will enable them to see who is current, expired and will expire on the web pages I am making.  With your permission I would Like to give you credit for helping on the pages I am making
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Absolutely.  Thank you very much, I appreciate the recognition!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.