Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

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

0
lrbrister
Asked:
lrbrister
1 Solution
 
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
 
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
 
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
 
lrbristerAuthor Commented:
That did it.  Thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now