Solved

Sql - Need help to display mutiple dependents on 1 row

Posted on 2014-04-22
11
353 Views
Last Modified: 2014-05-01
The following Sql does not display a single row of data for each employee, creating multiple columns for each dependent.

and if the Medical Tier Name = EE Only - there should not be a dependent displayed - should only display NULL.

However, if there are dependents then create a column(s) to display the employees dependents.

see the attached for sample data (however still not in a single row, hence the problem)

SELECT    Emp.FirstName AS EEFName
			, Emp.LastName AS EELName
			, UT.BenId, UT.OptionId AS [Medical Option ID]
			, Opt.ShortDesc AS [Medical Option]
			, UT.TierId AS [Medical TierID]
			, Tier.ShortDesc AS [Medical TierName]
			, vw_Dep.Relationship AS Spouse
			, vw_Dep.DepNum AS SpDepNum
			, vw_Dep.FirstName AS SpFName
			, vw_Dep.LastName AS SpLName
			, vw_dep1.Relationship AS Child
			, vw_dep1.FirstName AS ChFName
			, vw_dep1.LastName AS ChLName
			, vw_dep2.Relationship AS Child2
			, vw_dep2.FirstName AS Ch2FName
			, vw_dep2.LastName AS Ch2LName
			, vw_dep3.Relationship AS Child3
			, vw_dep3.FirstName AS Ch3FName
			, vw_dep3.LastName AS Ch3LName
			, vw_dep4.Relationship AS Child4
			, vw_dep4.FirstName AS Ch3FName
			, vw_dep4.LastName AS Ch3LName

FROM        dbo.vw_employee AS Emp 
             JOIN dbo.util_active_elections AS UT
				ON EMP.PersonId = UT.PersonId 
             JOIN dbo.pr_tier_def AS Tier 
				ON UT.BenId = Tier.BenId 
				  AND Tier.OptionId = UT.OptionId 
				  AND Tier.TierId = UT.TierId 
				  AND Tier.PlanYear = UT.PlanYear 
             JOIN dbo.pr_option_def AS Opt 
				ON Opt.PlanYear = Tier.PlanYear 
				  AND Opt.BenId = Tier.BenId 
				  AND Opt.OptionId = Tier.OptionId 
			Left JOIN
              dbo.vw_dependent AS vw_Dep 
			   ON Emp.PersonId = vw_Dep.PersonId 
				  AND vw_Dep.depid=1
				  AND vw_Dep.Relationship in ('SP', 'DP')
			Left JOIN
              dbo.vw_dependent AS vw_dep1 
               ON Emp.PersonId = vw_dep1.PersonId 
				  AND vw_dep1.depid=1
				  And vw_dep1.Relationship in ('CH', 'DC')
            Left JOIN
              dbo.vw_dependent AS vw_dep2 
              ON Emp.PersonId = vw_dep2.PersonId 
				  AND vw_dep2.depid=1
 				  And vw_dep2.Relationship in ('CH', 'DC')
           Left JOIN
              dbo.vw_dependent AS vw_dep3 
              ON Emp.PersonId = vw_dep3.PersonId 
				  AND vw_dep3.depid=1
				  And vw_dep3.Relationship in ('CH', 'DC')
            Left JOIN
              dbo.vw_dependent AS vw_dep4 
              ON Emp.PersonId = vw_dep4.PersonId
 				  AND vw_dep4.depid=1
				  And vw_dep4.Relationship in ('CH', 'DC')
             
WHERE     (UT.PlanYear = 2012) 
			AND (UT.BenId = 1) 
			AND (UT.Termed = 0)
			AND (emp.internaltest = 0)

Open in new window


1st highlight is and error,
2nd highlight is correct.

NOTE:
Depedent Types:
 
SP = Spouse
DP = Domestic Partner
CH = Child Dependent
DC = Domestic Child Dependent

if not EEOnly and depending on Medical Tier Name which Depedent should be displayed in its own column.

sample data
0
Comment
Question by:Karen Schaefer
11 Comments
 
LVL 5

Expert Comment

by:dannygonzalez09
ID: 40016214
Try this

SELECT    Emp.FirstName AS EEFName
			, Emp.LastName AS EELName
			, UT.BenId, UT.OptionId AS [Medical Option ID]
			, Opt.ShortDesc AS [Medical Option]
			, UT.TierId AS [Medical TierID]
			, Tier.ShortDesc AS [Medical TierName]
			, CASE WHEN UT.OptionId = 'EE Only' THEN '' ELSE vw_Dep.Relationship END AS Spouse
			, CASE WHEN UT.OptionId = 'EE Only' THEN '' ELSE vw_Dep.DepNum END AS SpDepNum
			, CASE WHEN UT.OptionId = 'EE Only' THEN '' ELSE vw_Dep.FirstName END AS SpFName
			, CASE WHEN UT.OptionId = 'EE Only' THEN '' ELSE vw_Dep.LastName END AS SpLName
			, vw_dep1.Relationship AS Child
			, vw_dep1.FirstName AS ChFName
			, vw_dep1.LastName AS ChLName
			, vw_dep2.Relationship AS Child2
			, vw_dep2.FirstName AS Ch2FName
			, vw_dep2.LastName AS Ch2LName
			, vw_dep3.Relationship AS Child3
			, vw_dep3.FirstName AS Ch3FName
			, vw_dep3.LastName AS Ch3LName
			, vw_dep4.Relationship AS Child4
			, vw_dep4.FirstName AS Ch3FName
			, vw_dep4.LastName AS Ch3LName

FROM        dbo.vw_employee AS Emp 
             JOIN dbo.util_active_elections AS UT
				ON EMP.PersonId = UT.PersonId 
             JOIN dbo.pr_tier_def AS Tier 
				ON UT.BenId = Tier.BenId 
				  AND Tier.OptionId = UT.OptionId 
				  AND Tier.TierId = UT.TierId 
				  AND Tier.PlanYear = UT.PlanYear 
             JOIN dbo.pr_option_def AS Opt 
				ON Opt.PlanYear = Tier.PlanYear 
				  AND Opt.BenId = Tier.BenId 
				  AND Opt.OptionId = Tier.OptionId 
			Left JOIN
              dbo.vw_dependent AS vw_Dep 
			   ON Emp.PersonId = vw_Dep.PersonId 
				  AND vw_Dep.depid=1
				  AND vw_Dep.Relationship in ('SP', 'DP')
			Left JOIN
              dbo.vw_dependent AS vw_dep1 
               ON Emp.PersonId = vw_dep1.PersonId 
				  AND vw_dep1.depid=1
				  And vw_dep1.Relationship in ('CH', 'DC')
            Left JOIN
              dbo.vw_dependent AS vw_dep2 
              ON Emp.PersonId = vw_dep2.PersonId 
				  AND vw_dep2.depid=1
 				  And vw_dep2.Relationship in ('CH', 'DC')
           Left JOIN
              dbo.vw_dependent AS vw_dep3 
              ON Emp.PersonId = vw_dep3.PersonId 
				  AND vw_dep3.depid=1
				  And vw_dep3.Relationship in ('CH', 'DC')
            Left JOIN
              dbo.vw_dependent AS vw_dep4 
              ON Emp.PersonId = vw_dep4.PersonId
 				  AND vw_dep4.depid=1
				  And vw_dep4.Relationship in ('CH', 'DC')
             
WHERE     (UT.PlanYear = 2012) 
			AND (UT.BenId = 1) 
			AND (UT.Termed = 0)
			AND (emp.internaltest = 0)
GROUP BY Emp.FirstName, Emp.LastName, UT.BenId, UT.OptionId, Opt.ShortDesc, UT.TierId, Tier.ShortDesc 
			,vw_dep1.Relationship, vw_dep1.FirstName , vw_dep1.LastName , vw_dep2.Relationship , vw_dep2.FirstName , vw_dep2.LastName
			, vw_dep3.Relationship, vw_dep3.FirstName, vw_dep3.LastName, vw_dep4.Relationship , vw_dep4.FirstName 
			, vw_dep4.LastName 		

Open in new window

0
 
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 250 total points
ID: 40016215
LEFT OUTER JOIN dbo.vw_dependent AS vw_Dep
   ON Emp.PersonId = vw_Dep.PersonId
   AND vw_Dep.depid=1
   AND vw_Dep.Relationship in ('SP', 'DP')
   AND UT.TierId > 1
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40016424
Can one person have different medical options? Or are these different employees?
see circles/ellipsesquestionsand/or, one employee with 5 wives and a "DP" also?

could I suggest you include EMP.PersonId in the group by?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40016448
any chance of getting some sample data for each of the tables referenced, and the expected result for that data?

seems to me you are joining the same dependents multiple times which suggests the joins are incorrect and grouping won't solve thatjoins?
0
 

Author Comment

by:Karen Schaefer
ID: 40018703
I was told that case statemetn was not the way to go, so I tried with the Left Outer join.

This method got me closer, however, it does not display the separate child dependents in the proper columns - instead repeats the same child dependent.

Please note that the data being use is really a mess since it is development data.

I need to find a way to separate the child dependents.

I tried the "and vw_dep1.personid <> vw_dep2.personid" within the outer join and then in the Where statement  bothe without success.  The addition to the Where statement did not return any records.

what do I need to add to separate the child dependents into separate columns?pic
SELECT    Emp.EMPLOYEEID
		, Emp.FirstName AS EEFName
		, Emp.LastName AS EELName
		, UT.BenId
		, UT.OptionId AS [Medical Option ID]
		, Opt.ShortDesc AS [Medical Option]
		, UT.TierId AS [Medical TierID]
		, Tier.ShortDesc AS [Medical TierName]
		, vw_Dep.Relationship AS Spouse
		, vw_Dep.DepNum AS SpDepNum
		, vw_Dep.FirstName AS SpFName
		, vw_Dep.LastName AS SpLName
		, vw_dep1.Relationship AS Child1
		, vw_Dep1.DepNum AS Child1DepNum
		, vw_dep1.FirstName AS Ch1FName
		, vw_dep1.LastName AS Ch1LName
		, vw_dep2.Relationship AS Child2
		, vw_Dep2.DepNum AS Ch2DepNum
		, vw_dep2.FirstName AS Ch2FName
		, vw_dep2.LastName AS Ch2LName
		--, vw_dep3.Relationship AS Child3
		--, vw_Dep3.DepNum AS Ch3DepNum
		--, vw_dep3.FirstName AS Ch3FName
		--, vw_dep3.LastName AS Ch3LName
		--, vw_dep4.Relationship AS Child4
		--, vw_Dep4.DepNum AS Ch4DepNum
		--, vw_dep4.FirstName AS Ch3FName
		--, vw_dep4.LastName AS Ch3LName

FROM   dbo.vw_employee AS Emp 
		JOIN dbo.util_active_elections AS UT
			ON EMP.PersonId = UT.PersonId 
		JOIN dbo.pr_tier_def AS Tier 
			ON UT.BenId = Tier.BenId 
			  AND Tier.OptionId = UT.OptionId 
			  AND Tier.TierId = UT.TierId 
			  AND Tier.PlanYear = UT.PlanYear 
		JOIN dbo.pr_option_def AS Opt 
			ON Opt.PlanYear = Tier.PlanYear 
			  AND Opt.BenId = Tier.BenId 
			  AND Opt.OptionId = Tier.OptionId 
		Left OUTER JOIN dbo.vw_dependent AS vw_Dep 
		   ON Emp.PersonId = vw_Dep.PersonId 
			   AND vw_Dep.depid=1
			   AND vw_Dep.Relationship in ('SP', 'DP')
			   AND UT.TierId NOT in (0, 1, 11)
		Left OUTER JOIN dbo.vw_dependent AS vw_Dep1
		   ON Emp.PersonId = vw_Dep1.PersonId 
			   AND vw_Dep1.depid = 1
			   --AND vw_Dep.PersonId  <> vw_Dep1.PersonId 
			   AND vw_Dep1.Relationship in ('CH', 'DC')
			   AND UT.TierId NOT IN (0, 1, 11)

		Left OUTER JOIN dbo.vw_dependent AS vw_Dep2
		   ON Emp.PersonId = vw_Dep2.PersonId 
		   AND vw_Dep2.depid=1
		   AND vw_Dep2.Relationship in ('CH', 'DC')
		   AND UT.TierId NOT in (0, 1, 11)
		   --and vw_dep1.personid <> vw_dep2.personid
		--Left OUTER JOIN dbo.vw_dependent AS vw_Dep3
		--   ON Emp.PersonId = vw_Dep3.PersonId 
		--   AND vw_Dep3.depid=1
		--   AND vw_Dep3.Relationship in ('CH', 'DC')
		--   AND UT.TierId NOT in (0, 1, 11)
		--   --and vw_dep2.personid <> vw_dep3.personid
		--Left OUTER JOIN dbo.vw_dependent AS vw_Dep4
		--   ON Emp.PersonId = vw_Dep4.PersonId 
		--   AND vw_Dep4.depid=1
		--   AND vw_Dep4.Relationship in ('CH', 'DC')
		--   AND UT.TierId NOT in (0, 1, 11)
		   --and vw_dep3.personid <> vw_dep4.personid

WHERE     (UT.PlanYear = 2012) 
			AND (UT.BenId = 1) 
			AND (UT.Termed = 0)
			AND (emp.internaltest = 0)
		  -- AND UT.TierId NOT in (0, 1, 11)
		and UT.Tierid >1
			   --AND vw_Dep.PersonId  <> vw_Dep1.PersonId 
			   --AND vw_Dep1.PersonId  <> vw_Dep2.PersonId 
			   ----AND vw_Dep2.PersonId  <> vw_Dep3.PersonId 
			   --AND vw_Dep3.PersonId  <> vw_Dep4.PersonId 

GROUP BY Emp.EMPLOYEEID
		, Emp.FirstName
		, Emp.LastName
		, UT.BenId
		, UT.OptionId
		, Opt.ShortDesc
		, UT.TierId
		, Tier.ShortDesc
		, vw_Dep.Relationship
		, vw_Dep.DepNum
		, vw_Dep.FirstName
		, vw_Dep.LastName
		, vw_Dep1.Relationship
		, vw_Dep1.DepNum
		, vw_Dep1.FirstName
		, vw_Dep1.LastName
		, vw_Dep2.Relationship
		, vw_Dep2.DepNum
		, vw_Dep2.FirstName
		, vw_Dep2.LastName
		--, vw_Dep3.Relationship
		--, vw_Dep3.DepNum
		--, vw_Dep3.FirstName
		--, vw_Dep3.LastName
		--, vw_Dep4.Relationship
		--, vw_Dep4.DepNum
		--, vw_Dep4.FirstName
		--, vw_Dep4.LastName

Open in new window

0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 48

Expert Comment

by:PortletPaul
ID: 40018878
unless you share at least some data from that dependents table we are blind

you have the privilege of that data, but we don't

and. please, not the query output, just some raw data from that table

Regarding the multiple left joins, yes that quite possibly is the way to go; BUT!!
aside from the alias, those joins are exactly the same, so it's not surprising you get the same data multiple times. This is why I would like to understand that table and its data.

(not after private data like names of course)
0
 

Author Comment

by:Karen Schaefer
ID: 40018912
attached is a xls with separate tab for each table in question.  with sample data.

Thanks for taking the time to look at this issue.

K
DataModelLab8-DataSamples.xlsx
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 40018952
OK, here is a snippet that concentrates on the children, notice how [Depid] changes
**MS SQL Server 2008 Schema Setup**:

    
    
    CREATE TABLE vw_employee
    	([PersonId] int, [TaxIdNumber] int, [InternalTest] int, [EmployeeId] int, [FirstName] varchar(5), [MiddleName] varchar(1), [LastName] varchar(7))
    ;
    	
    INSERT INTO vw_employee
    	([PersonId], [TaxIdNumber], [InternalTest], [EmployeeId], [FirstName], [MiddleName], [LastName])
    VALUES
    	(54818, 777770087, 0, 777770087, 'KELLY', 'R', 'PAULINA'),
    	(54868, 777770099, 0, 777770099, 'CARLY', 'A', 'NHTEST'),
    	(54912, 777520001, 0, 520001, 'SAM1', 'A', 'HEALTH1'),
    	(54919, 777520008, 0, 520008, 'SAM2', 'A', 'HEALTH2'),
    	(54969, 777530003, 0, 530003, 'SAM3', 'A', 'HEALTH3')
    ;
    
    CREATE TABLE vw_dependent
    	([PersonId] int, [DepNum] int, [BenNum] int, [DepId] varchar(4), [Relationship] varchar(2), [FirstName] varchar(9), [LastName] varchar(13), [Gender] varchar(1))
    ;
    	
    INSERT INTO vw_dependent
    	([PersonId], [DepNum], [BenNum], [DepId], [Relationship], [FirstName], [LastName], [Gender])
    VALUES
    	(54818, 54850, 0, NULL, 'CH', 'JIMMY', 'PAULINA', 'F'),
    	(54868, 54870, 0, NULL, 'SP', 'TESTER', 'SHMESTER', 'M'),
    	(54919, 54964, 0, '1', 'CH', 'TEST', 'TEST', 'F'),
    	(54919, 54965, 0, '2', 'CH', 'TEST', 'ADD2008', 'F'),
    	(54919, 54966, 0, '3', 'CH', 'ADDNEWDEP', 'IN2007', 'F'),
    	(54919, 54967, 0, '4', 'CH', 'NEWDEP', '2007', 'F'),
    	(54969, 54974, 0, NULL, 'CH', 'TEST', '2007BEGINYEAR', 'F'),
    	(54912, 54980, 0, NULL, 'SP', 'TEST', 'SPOUSE', 'F'),
    	(54912, 54982, 0, NULL, 'CH', 'TEST', 'CHILD2', 'F')
    ;

**Query 1**:

    select
          emp.PersonId, emp.FirstName, emp.LastName
        , ch1.FirstName as fnch1
        , ch1.LastName  as lnch1
        , ch2.FirstName as fnch2
        , ch2.LastName  as lnch2
        , ch3.FirstName as fnch3
        , ch3.LastName  as lnch3
        , ch4.FirstName as fnch4
        , ch4.LastName  as lnch4
    from dbo.vw_employee as emp
    left join dbo.vw_dependent as ch1 on emp.PersonId = ch1.PersonId
                                     and ch1.Relationship = 'CH'
                                     and ( ch1.DepId is null or ch1.DepId = 1 )
    
    left join dbo.vw_dependent as ch2 on emp.PersonId = ch2.PersonId
                                     and ch2.Relationship = 'CH'
                                     and ( ch2.DepId = 2 )
    
    left join dbo.vw_dependent as ch3 on emp.PersonId = ch3.PersonId
                                     and ch3.Relationship = 'CH'
                                     and ( ch3.DepId = 3 )
    
    left join dbo.vw_dependent as ch4 on emp.PersonId = ch4.PersonId
                                     and ch4.Relationship = 'CH'
                                     and ( ch4.DepId = 4 )
    
    order by emp.PersonId, ch1.DepId
    

**[Results][2]**:
    
    | PERSONID | FIRSTNAME | LASTNAME |  FNCH1 |         LNCH1 |  FNCH2 |   LNCH2 |     FNCH3 |  LNCH3 |  FNCH4 |  LNCH4 |
    |----------|-----------|----------|--------|---------------|--------|---------|-----------|--------|--------|--------|
    |    54818 |     KELLY |  PAULINA |  JIMMY |       PAULINA | (null) |  (null) |    (null) | (null) | (null) | (null) |
    |    54868 |     CARLY |   NHTEST | (null) |        (null) | (null) |  (null) |    (null) | (null) | (null) | (null) |
    |    54912 |      SAM1 |  HEALTH1 |   TEST |        CHILD2 | (null) |  (null) |    (null) | (null) | (null) | (null) |
    |    54919 |      SAM2 |  HEALTH2 |   TEST |          TEST |   TEST | ADD2008 | ADDNEWDEP | IN2007 | NEWDEP |   2007 |
    |    54969 |      SAM3 |  HEALTH3 |   TEST | 2007BEGINYEAR | (null) |  (null) |    (null) | (null) | (null) | (null) |



  [1]: http://sqlfiddle.com/#!3/025809/1

  [2]: http://sqlfiddle.com/#!3/025809/1/0

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40019040
Here is a tentative query to work with, it introduces a left join to [util_active_elections] but that may not be what you are seeking so it may be changed back to an inner join (that would apply to [pr_tier_def] also). You will notice I have introduced some tests n the coverage description, I'm not entirely happy doing this and maybe there's a better way but for now I think this will help you move forward.
SELECT
      emp.PersonId
    , emp.FirstName
    , emp.LastName

    , isnull(tier.ShortDesc, 'No Coverage') AS tier_desc

    , p.FirstName   AS fnp
    , p.LastName    AS lnp
    , ch1.FirstName AS fnch1
    , ch1.LastName  AS lnch1
    , ch2.FirstName AS fnch2
    , ch2.LastName  AS lnch2
    , ch3.FirstName AS fnch3
    , ch3.LastName  AS lnch3
    , ch4.FirstName AS fnch4
    , ch4.LastName  AS lnch4

FROM dbo.vw_employee AS emp
LEFT JOIN dbo.util_active_elections AS UT ON EMP.PersonId = UT.PersonId
                                    AND UT.PlanYear = 2012
                                    AND UT.BenId = 1
                                    AND UT.Termed = 0

LEFT JOIN dbo.pr_tier_def AS Tier ON UT.BenId = Tier.BenId
                                 AND Tier.OptionId = UT.OptionId
                                 AND Tier.TierId = UT.TierId
                                 AND Tier.PlanYear = UT.PlanYear

LEFT JOIN dbo.vw_dependent AS p ON emp.PersonId = p.PersonId
                                 AND p.Relationship IN ('SP','DP')
                                 AND ( charindex('DP',tier.ShortDesc,1) > 0
                                      OR
                                        charindex('spouse',tier.ShortDesc,1) > 0
                                      OR
                                        charindex('family',tier.ShortDesc,1) > 0
                                      )

LEFT JOIN dbo.vw_dependent AS ch1 ON emp.PersonId = ch1.PersonId
                                 AND ch1.Relationship = 'CH'
                                 AND ( ch1.DepId IS NULL OR ch1.DepId = 1 )
                                 AND ( charindex('child',tier.ShortDesc,1) > 0
                                      OR
                                        charindex('family',tier.ShortDesc,1) > 0
                                      )

LEFT JOIN dbo.vw_dependent AS ch2 ON emp.PersonId = ch2.PersonId
                                 AND ch2.Relationship = 'CH'
                                 AND ( ch2.DepId = 2 )
                                 AND ( charindex('child',tier.ShortDesc,1) > 0
                                      OR
                                        charindex('family',tier.ShortDesc,1) > 0
                                      )

LEFT JOIN dbo.vw_dependent AS ch3 ON emp.PersonId = ch3.PersonId
                                 AND ch3.Relationship = 'CH'
                                 AND ( ch3.DepId = 3 )
                                 AND ( charindex('child',tier.ShortDesc,1) > 0
                                      OR
                                        charindex('family',tier.ShortDesc,1) > 0
                                      )

LEFT JOIN dbo.vw_dependent AS ch4 ON emp.PersonId = ch4.PersonId
                                 AND ch4.Relationship = 'CH'
                                 AND ( ch4.DepId = 4 )
                                 AND ( charindex('child',tier.ShortDesc,1) > 0
                                      OR
                                        charindex('family',tier.ShortDesc,1) > 0
                                      )

ORDER BY emp.PersonId, ch1.DepId
;

Open in new window

note as there is no coverage, or EE Only, no spouses/partners or children are displayed in that output based on the sample provided.
| PERSONID | FIRSTNAME | LASTNAME |   TIER_DESC |    FNP |    LNP |  FNCH1 |  LNCH1 |  FNCH2 |  LNCH2 |  FNCH3 |  LNCH3 |  FNCH4 |  LNCH4 |
|----------|-----------|----------|-------------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|
|    54818 |     KELLY |  PAULINA | No Coverage | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
|    54868 |     CARLY |   NHTEST | No Coverage | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
|    54912 |      SAM1 |  HEALTH1 |     EE Only | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
|    54919 |      SAM2 |  HEALTH2 |     EE Only | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
|    54969 |      SAM3 |  HEALTH3 | No Coverage | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
	

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40019044
oh, there is an inherent assumption in all these queries so far;

that there is a maximum of one active election for an employee.

If that is not true then you would get multiple rows for an employee.
0
 

Author Closing Comment

by:Karen Schaefer
ID: 40035689
Thanks for you input
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now