I need help with my SQL query PLEASE

mysqlquestion · November 1, 2018, 3:02pm

This is my current sql and and a small sample of the output:

SELECT BNBEN.EMPLOYEE, BNBEN.PLAN_TYPE, BNBEN.PLAN_CODE, BNBEN.COVER_OPT, BNCOVOPT.COV_DESC, DEPBEN.DEPENDENT, EMDEPEND.REL_CODE, MPLOYEE.PROCESS_LEVEL
FROM BNBEN INNER JOIN
DEPBEN ON BNBEN.COMPANY = DEPBEN.COMPANY AND BNBEN.EMPLOYEE = DEPBEN.EMPLOYEE AND BNBEN.PLAN_TYPE = DEPBEN.PLAN_TYPE AND BNBEN.PLAN_CODE = DEPBEN.PLAN_CODE INNER JOIN
BNCOVOPT ON BNBEN.COMPANY = BNCOVOPT.COMPANY AND BNBEN.PLAN_TYPE = BNCOVOPT.PLAN_TYPE AND BNBEN.PLAN_CODE = BNCOVOPT.PLAN_CODE AND
BNBEN.COVER_OPT = BNCOVOPT.COVERAGE_OPT INNER JOIN
EMDEPEND ON DEPBEN.COMPANY = EMDEPEND.COMPANY AND DEPBEN.EMPLOYEE = EMDEPEND.EMPLOYEE AND DEPBEN.DEPENDENT = EMDEPEND.SEQ_NBR INNER JOIN
EMPLOYEE ON EMDEPEND.COMPANY = EMPLOYEE.COMPANY AND EMDEPEND.EMPLOYEE = EMPLOYEE.EMPLOYEE
WHERE (BNBEN.START_DATE = CONVERT(DATETIME, '2019-01-01 00:00:00', 102))
GROUP BY BNBEN.EMPLOYEE, BNBEN.PLAN_TYPE, BNBEN.PLAN_CODE, BNBEN.COVER_OPT, BNCOVOPT.COV_DESC, DEPBEN.DEPENDENT, EMDEPEND.REL_CODE, EMPLOYEE.PROCESS_LEVEL
HAVING (EMPLOYEE.PROCESS_LEVEL = 'HR')

Employee PlanType PlanCode CoverageOption CoverageDesc DependentSequence DependDescrip
7010 DN DN01 3 Employee + Family 1 SPOUSE
7010 DN DN01 3 Employee + Family 2 SON
7010 DN DN01 3 Employee + Family 3 DAUGHTER

I need to see the data displayed as:
Employee PlanType PlanCode CoverageOption CoverageDesc Spouse NbrDependents
7010 DN DN01 3 Employee 1 2
+Family
Thanks.

harishgg1 · November 1, 2018, 3:44pm

hi

Hope this helps

Drop Create Data Script

use  tempdb 
go 


drop table #data 
go 


create table #data 
(
Employee  int ,
PlanType  varchar(100) ,
PlanCode  varchar(100) ,
CoverageOption	 int ,
CoverageDesc  varchar(100) ,
DependentSequence  int ,
DependDescrip  varchar(100) 
)
go 


insert into #data select 7010,'DN','DN01',3,'Employee + Family',1,'SPOUSE'
insert into #data select 7010,'DN','DN01',3,'Employee + Family',2,'SON'
insert into #data select 7010,'DN','DN01',3,'Employee + Family',3,'DAUGHTER'
go

SQL

SELECT employee, 
       plantype, 
       plancode, 
       coverageoption, 
       coveragedesc, 
       Sum(CASE 
             WHEN dependdescrip = 'SPOUSE' THEN 1 
             ELSE 0 
           END) AS Spouse, 
       Sum(CASE 
             WHEN dependdescrip IN ( 'SON', 'DAUGHTER' ) THEN 1 
             ELSE 0 
           END) AS NbrDependents 
FROM   #data 
GROUP  BY employee, 
          plantype, 
          plancode, 
          coverageoption, 
          coveragedesc 

go

Results

mysqlquestion · November 2, 2018, 1:36pm

Hey Harishgg1,

I don't have permission to create temp table or view from my database. So is there another way to create subquery to get it works instead create temp table. I know this is very complicate in one query, but please help me out with this query because I only have ready permission from my SQL database.

harishgg1 · November 2, 2018, 2:19pm

hi

no need to create any table

you can just use the logic .. in your query ,..

please post your query ... lot of people watching this also
either me or they will help

yosiasz · November 2, 2018, 3:25pm

what @harishgg1 gave you should work. the temp table he provided you is just sampling of the data you did not provide
Implementing what @harishgg1 did into your query goes something like this.

SELECT BNBEN.EMPLOYEE, BNBEN.PLAN_TYPE, BNBEN.PLAN_CODE, BNBEN.COVER_OPT, BNCOVOPT.COV_DESC, 
       Sum(CASE 
             WHEN DEPENDENT = 'SPOUSE' THEN 1 
             ELSE 0 
           END) AS Spouse, 
       Sum(CASE 
             WHEN DEPENDENT IN ( 'SON', 'DAUGHTER' ) THEN 1 
             ELSE 0 
           END) AS NbrDependents 
 FROM BNBEN 
 INNER JOIN DEPBEN ON BNBEN.COMPANY = DEPBEN.COMPANY AND BNBEN.EMPLOYEE = DEPBEN.EMPLOYEE AND BNBEN.PLAN_TYPE = DEPBEN.PLAN_TYPE AND BNBEN.PLAN_CODE = DEPBEN.PLAN_CODE 
 INNER JOIN BNCOVOPT ON BNBEN.COMPANY = BNCOVOPT.COMPANY AND BNBEN.PLAN_TYPE = BNCOVOPT.PLAN_TYPE AND BNBEN.PLAN_CODE = BNCOVOPT.PLAN_CODE AND BNBEN.COVER_OPT = BNCOVOPT.COVERAGE_OPT 
 INNER JOIN EMDEPEND ON DEPBEN.COMPANY = EMDEPEND.COMPANY AND DEPBEN.EMPLOYEE = EMDEPEND.EMPLOYEE AND DEPBEN.DEPENDENT = EMDEPEND.SEQ_NBR 
 INNER JOIN EMPLOYEE ON EMDEPEND.COMPANY = EMPLOYEE.COMPANY AND EMDEPEND.EMPLOYEE = EMPLOYEE.EMPLOYEE
 WHERE (BNBEN.START_DATE = CONVERT(DATETIME, '2019-01-01 00:00:00', 102))
GROUP BY BNBEN.EMPLOYEE, BNBEN.PLAN_TYPE, BNBEN.PLAN_CODE, BNBEN.COVER_OPT, BNCOVOPT.COV_DESC
HAVING (EMPLOYEE.PROCESS_LEVEL = 'HR')

mysqlquestion · November 2, 2018, 6:18pm

Hey Yosiasz,

I got an error when try to run your query.. Please help... thank you very much
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'SPOUSE' to data type smallint.

yosiasz · November 2, 2018, 6:45pm

change DEPENDENT here

Sum(CASE 
             WHEN DEPENDENT = 'SPOUSE' THEN 1 
             ELSE 0 
           END) AS Spouse,

to the column name that has SPOUSE , SON etc

mysqlquestion · November 5, 2018, 7:51pm

I still get the same error message. thanks

mysqlquestion · November 5, 2018, 8:17pm

Hey Yosiasz,

When I changed to Dependent as you suggested,
Sum(CASE
WHEN DEPENDENT = DEPENDENT THEN 1
ELSE 0
END) AS Spouse,
Sum(CASE
WHEN DEPENDENT IN ( DEPENDENT ) THEN 1
ELSE 0
END) AS NbrDependents

I got the count for spouse and NbrDependents are the same. Here's the output
EMPLOYEE PLAN_TYPE PLAN_CODE COVER_OPT COV_DESC Spouse NbrDependents
7010 DN DN01 3 Employee + Family 3 3
7010 HL CI01 3 Employee+Children 2 2
7010 HL GAP1 3 Employee+Children 2 2
7010 HL MED4 4 EE + Family 3 3
7010 HL VS02 3 Employee + Family 3 3

yosiasz · November 5, 2018, 11:20pm

what is the table name and column name that has values of SPOUSE , SON

mysqlquestion · November 6, 2018, 4:49pm

the table name is EMDEPEND and column name for spouse and son are REL_CODE.
I join EmployeeID from DEPEND table to EMDEPEND
and Dependent# from DENPEND table to SEQ_NBR from EMDEPEND table

thank you very much for your help

yosiasz · November 6, 2018, 9:46pm

SELECT BNBEN.EMPLOYEE, BNBEN.PLAN_TYPE, BNBEN.PLAN_CODE, BNBEN.COVER_OPT, BNCOVOPT.COV_DESC, 
       Sum(CASE 
             WHEN REL_CODE= 'SPOUSE' THEN 1 
             ELSE 0 
           END) AS Spouse, 
       Sum(CASE 
             WHEN REL_CODE IN ( 'SON', 'DAUGHTER' ) THEN 1 
             ELSE 0 
           END) AS NbrDependents 
 FROM BNBEN 
 INNER JOIN DEPBEN ON BNBEN.COMPANY = DEPBEN.COMPANY AND BNBEN.EMPLOYEE = DEPBEN.EMPLOYEE AND BNBEN.PLAN_TYPE = DEPBEN.PLAN_TYPE AND BNBEN.PLAN_CODE = DEPBEN.PLAN_CODE 
 INNER JOIN BNCOVOPT ON BNBEN.COMPANY = BNCOVOPT.COMPANY AND BNBEN.PLAN_TYPE = BNCOVOPT.PLAN_TYPE AND BNBEN.PLAN_CODE = BNCOVOPT.PLAN_CODE AND BNBEN.COVER_OPT = BNCOVOPT.COVERAGE_OPT 
 INNER JOIN EMDEPEND ON DEPBEN.COMPANY = EMDEPEND.COMPANY AND DEPBEN.EMPLOYEE = EMDEPEND.EMPLOYEE AND DEPBEN.DEPENDENT = EMDEPEND.SEQ_NBR 
 INNER JOIN EMPLOYEE ON EMDEPEND.COMPANY = EMPLOYEE.COMPANY AND EMDEPEND.EMPLOYEE = EMPLOYEE.EMPLOYEE
 WHERE (BNBEN.START_DATE = CONVERT(DATETIME, '2019-01-01 00:00:00', 102))
GROUP BY BNBEN.EMPLOYEE, BNBEN.PLAN_TYPE, BNBEN.PLAN_CODE, BNBEN.COVER_OPT, BNCOVOPT.COV_DESC
HAVING (EMPLOYEE.PROCESS_LEVEL = 'HR')