Select Statement - Difficult

My following select works - kinda...
It returns 3 rows of data.

Everything up to the CASE statements is repeating data.

I'm not the DBA so I HAVE to use the existing FROM statements in the select

How do I get everything on one line?


SELECT DISTINCT
     v.[Assignment Number] ,
     v.[Start Date] ,
     v.[STE End Date] ,
     v.[Employee Last Name] ,
     v.[Employee First Name] ,
     a.[RN license],
     a.[License Verify],
     e.[Employment Application],
     a.[Skills Checklist],
     e.[HIPAA Compliance],
     v.[Requirement status],
     CASE v.[Doc Display] WHEN 'Core Competencies' THEN v.[Expiration Date] END [Core Competencies],
     CASE v.[Doc Display] WHEN 'CPR' THEN v.[Expiration Date] END [CPR],
     CASE v.[Doc Display] WHEN 'PPD Statement' THEN v.[Expiration Date] END [PPD]
FROM dbo.[vw_Document Requirements by Assignment] v
INNER JOIN	Assignment a ON v.[Assignment Number] = a.[Assignment Number]
INNER JOIN	Employee e
	ON	a.[Employee Number] = e.[Employee Number]
WHERE v.[Assignment Number] = 10864
AND Doc IN ('Core Competencies','CPR','PPD Statement')

Open in new window

Larry Bristersr. DeveloperAsked:
Who is Participating?
 
lwadwellCommented:
Seem like you want a MAX() around the CASE statements and a GROUP BY, e.g.
SELECT v.[Assignment Number] ,
     v.[Start Date] ,
     v.[STE End Date] ,
     v.[Employee Last Name] ,
     v.[Employee First Name] ,
     a.[RN license],
     a.[License Verify],
     e.[Employment Application],
     a.[Skills Checklist],
     e.[HIPAA Compliance],
     v.[Requirement status],
     MAX(CASE v.[Doc Display] WHEN 'Core Competencies' THEN v.[Expiration Date] END) [Core Competencies],
     MAX(CASE v.[Doc Display] WHEN 'CPR' THEN v.[Expiration Date] END) [CPR],
     MAX(CASE v.[Doc Display] WHEN 'PPD Statement' THEN v.[Expiration Date] END) [PPD]
FROM dbo.[vw_Document Requirements by Assignment] v
INNER JOIN	Assignment a ON v.[Assignment Number] = a.[Assignment Number]
INNER JOIN	Employee e
	ON	a.[Employee Number] = e.[Employee Number]
WHERE v.[Assignment Number] = 10864
AND Doc IN ('Core Competencies','CPR','PPD Statement')
GROUP BY v.[Assignment Number] ,
     v.[Start Date] ,
     v.[STE End Date] ,
     v.[Employee Last Name] ,
     v.[Employee First Name] ,
     a.[RN license],
     a.[License Verify],
     e.[Employment Application],
     a.[Skills Checklist],
     e.[HIPAA Compliance],
     v.[Requirement status]

Open in new window

The DISTINCT was redundant and removed.
0
 
Scott PletcherSenior DBACommented:
Which column(s) is(are) not the same for all the docs?

I'm guessing maybe just v.[Requirement status], but can't know w/o your input or sample data.
0
 
Pratima PharandeCommented:
I think you need this ... only one value at a time
I have one more question in where cluase you have selected Doc IN ('Core Competencies','CPR','PPD Statement')  and in case you are checking v.[Doc Display] , are both columns have same vlaues?

try this


SELECT DISTINCT
     v.[Assignment Number] ,
     v.[Start Date] ,
     v.[STE End Date] ,
     v.[Employee Last Name] ,
     v.[Employee First Name] ,
     a.[RN license],
     a.[License Verify],
     e.[Employment Application],
     a.[Skills Checklist],
     e.[HIPAA Compliance],
     v.[Requirement status],
     CASE v.[Doc Display]
      WHEN 'Core Competencies' THEN v.[Expiration Date]
      WHEN 'CPR' THEN v.[Expiration Date]
      WHEN 'PPD Statement' THEN v.[Expiration Date]
    END [Doc Display]
FROM dbo.[vw_Document Requirements by Assignment] v
INNER JOIN      Assignment a ON v.[Assignment Number] = a.[Assignment Number]
INNER JOIN      Employee e
      ON      a.[Employee Number] = e.[Employee Number]
WHERE v.[Assignment Number] = 10864
AND Doc IN ('Core Competencies','CPR','PPD Statement')
0
 
Larry Bristersr. DeveloperAuthor Commented:
That did it.  Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.