How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask ATLPROG Your Own Question

ATLPROG
ATLPROG, Computer Software Engineer
Category: Programming
Satisfied Customers: 7677
Experience:  MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
44910485
Type Your Programming Question Here...
ATLPROG is online now
A new question is answered every 9 seconds

I need help with a complex SQL statement that will return

Customer Question

I need help with a complex SQL statement that will return multiple lines, each with different columns but all grouped by the same unique key (employee identifier)
Submitted: 1 year ago.
Category: Programming
Expert:  ATLPROG replied 1 year ago.

I can try to help. Please provide more info

Customer: replied 1 year ago.
OK, i will provide sample output that I need to produce, followed by my start at the SQL.
Sample output:
EEID|1446572|***-**-****|Benedict|Timothy|Cumberbatch|M|02/12/1975
OFFR|1446572|999999999|AnnualEnrollment|01/01/2015|01/01/2015|01/01/2015|12/31/2015 02:11:24.158000 PM
ELIG|1446572|999999999|BCBSSTDRD|BCBS Smart Choice|EEONLY|Employee Only|Y |01/01/2015|172.38|572.38|Y|Y|Y|Y|Y|84.00|Y|Y
ELIG|1446572|999999999|BCBSPRMR|BCBS Smart Choice Premier|EEONLY|Employee Only|Y |01/01/2015|172.38|572.38|Y|Y|Y|Y|Y|84.00|Y|Y
COVG|1446572|999999999|AnnualEnrollment|01/01/2015|BCBSSTDRD|BCBS Smart Choice Standard|EEFAM|Employee + Family|586.12|1586.12|01/01/2015||Y|Y|Y|N|||01/01/2015 02:11:24.158000 PM
DEPI|1446572|01|***-**-****|Sophie||Hunter|Spouse|Y|04/16/1978|F|01/01/2015||
EEID|3333333|***-**-****|Joe||Doe|M|12/12/1965
OFFR|3333333|000000001|AnnualEnrollment|01/01/2015|01/01/2015|01/01/2015|12/31/2015 02:11:24.158000 PM
ELIG|3333333|000000001|BCBSSTDRD|BCBS Smart Choice|EEONLY|Employee Only|Y |01/01/2015|172.38|572.38|Y|Y|Y|Y|Y|84.00|Y|Y
ELIG|3333333|000000001|BCBSPRMR|BCBS Smart Choice Premier|EEONLY|Employee Only|Y |01/01/2015|172.38|572.38|Y|Y|Y|Y|Y|84.00|Y|Y
COVG|33333332|000000001|AnnualEnrollment|01/01/2015|BCBSSTDRD|BCBS Smart Choice Standard|EEFAM|Employee + Family|586.12|1586.12|01/01/2015||Y|Y|Y|N|||01/01/2015 02:11:24.158000 PM
DEPI|3333333|01|***-**-****|Jane||Doe|Spouse|Y|08/16/1988|F|01/01/2015||
DEPI|3333333|02|***-**-****|Jack||Doe|Son|N|09/10/2013|M|01/01/2015||Initial SQL:Select (select 'EEID', emp.emp as "EmployeeID", emp.ssnsin as "SSN", emp.firstname as "FirstName", emp.middlename as "MiddleName", emp.lastname as "LastName", emp.gender as "Gender", emp.birthdt as "DOB"),
(select 'OFFR', emp.emp as "ParticipantIdentifier", EmpEnrSum.EmpEnrSumID as "OfferIdentifier", 'Initial Load' as "EventReason", dbo.NuDateGetDate(getdate()) as "EventDate", empbenelg.eligibledt as "Coverage Start date", ben.beneffdt as "PlanYearStartDate", ben.benefddt as "PlanYearEndDate", empbenelg.empbenelgcreatedt as "TransactionDate"),
(select 'ELIG', emp.emp as "ParticipantIdentifier", empbenelg.ben as "OfferIdentifier", Ben.Ben as "PlanCode", Ben.BenDesc as "PlanDescription", Case when Ben.PeopleCovered = 'EO' then 'EMP' when Ben.PeopleCovered = 'EF' then 'FAM' else NULL end as "CoverageLevelCode", Case when Ben.PeopleCovered = 'EO' then 'Employee' when Ben.PeopleCovered = 'EF' then 'Family' else NULL end as "CoverageLevelDescription", case when Ben.PeopleCovered = 'EO' then 'Y' else 'N' end as "EmployeeOnlyCoverageLevel", empbenelg.empprem as "MonthlyEmployeeCost", empbenelg.cmpprem as "MonthlyEmployerCost", 'Y' as "MinimumEssentialCoverage", 'Y' as "MinimumValuePlan", Case when Ben.PeopleCovered = 'EO' then 'N' else 'Y' end as "DependentCoverageAvailable", case when Ben.PeopleCovered = 'EO' then 'N' else 'Y' end as "SpouseCoverageAvailable", 'N' as "SelfInsured", '100.00' as "BasePlanActuarialValue", 'Y' as "WaitPeriodIndicator", NULL as "WaivedCoverage"),
(select 'COVG', emp.emp as "ParticipantIdentifier", EmpEnrSum.EmpEnrSumID as "OfferIdentifier", 'Initial Load' as "EventReason", dbo.NuDateGetDate(getdate()) as "EventDate", Ben.Ben as "PlanCode", Ben.BenDesc as "PlanDescription", Case when Ben.PeopleCovered = 'EO' then "EMP" when Ben.PeopleCovered = 'EF' then 'FAM' else NULL end as "CoverageLevelCode", Case when Ben.PeopleCovered = 'EO' then 'Employee' when Ben.PeopleCovered = 'EF' then 'Family' else NULL end as "CoverageLevelDescription", empben.empprem as "MonthlyEmployeeCost", empben.cmpprem as "MonthlyEmployerCost", empben.EmpBenEffdt as "CoverageStartDate", empben.EmpBenEfdDt as "CoverageEndDate", 'N' as "SelfInsured", 'Y' as "MinimumEssentialCoverage", 'Y' as "MinimumValuePlan", case when ben like '%wv%' then 'Y' else 'N' end as "WaivedCoverage", '06' as "WaiverReasonCode", case when ben like '%wv%' then 'Unknown' else NULL end as "ReasonforWaiverDescription", empben.empbeneffdt as "TransactionDate"),
(Select 'DEPI', emp.emp as "ParticipantIdentifier", CONVERT(varchar, EmpBenDep.Emp)+'-'+CONVERT(varchar, EmpBenDep.Dep) as "DependentIdentifier", empdep.depssnsin as "DependentSSN", empdep.depfirstname as "DependentFirstName", empdep.depmiddlename as "DependentMiddleName", empdep.deplastname as "DependentLastName", empdep.deprelation as "Relationship", case when empdep.deprelation="Spouse" then 'Y' else 'N' end as "SpouseIndicator", empdep.depbirthdt as "DependentBirthDate", empdep.depgender as "DependentGender", empbendep.empbendepeffdt as "CoverageStartDate", empbendep.empbendepefddt as "CoverageEndDate", 'A' as "Status") FROM empbenelg INNER JOIN emp ON emp.emp = empbenelg.emp INNER JOIN empben ON empben.ben = empbenelg.ben and empben.emp=empbenelg.emp INNER JOIN ben ON ben.ben = empbenelg.ben INNER JOIN empenrsum on emp.empenrsum=emp.emp and empenrsum.ben=ben.ben INNER join empdep on emp.emp=empdep.dep INNER join empbendep on empbendep.dep=empben where empbenelgeffdt>'1/1/2015'
Customer: replied 1 year ago.
the system i'm working with will add the pipe delimiter, and it won't print the field names...those are just for my benefit.
Expert:  ATLPROG replied 1 year ago.

Are you getting any error

Customer: replied 1 year ago.
i get the following: "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."
Expert:  ATLPROG replied 1 year ago.

Sorry i am out of ideas

Expert:  Ingo U replied 1 year ago.

Hello,
Sometimes pasting formatted data directly into this site doesn't render as you'd expect. It's quite difficult to read what the desired output is supposed to look like.

However, from your description and inspection of the data, I'm guessing maybe you're trying to combine sub-selects like this:

SELECT (SELECT somecolumns from sometables), (SELECT someothercolumns from someothertables)

That won't work. Since I can't really tell from the above just what your output is supposed to look like (e.g. I can't tell where the line(s) end) I'm not sure how to advise.

You may need an outer join, or multiple selects into some temp table(s), perhaps a stored procedure with a cursor.... so,

could you please upload any files (i.e. your sample output as a text file that looks correct in notepad)

to wikisend.com (or a file sharing site of your choice) so I may have a look?

It would also help if I had access to your table schema in some form

If there are multiple files, it's easiest to put them in a compressed (zipped) folder and upload just that one item.

Thanks,
Ingo U

Related Programming Questions