Problem with Simple Count Query

[lrdave36]

Hey guys,

I am trying to do a query that will show employers who work in two different agencies for the same month. I'm finding this a bit tricky due to the table records.


Each employee has multiple records in my history table. A new record is created for each month so it could look like this:


SSN Employer# DATE

3234 00234 01-01-2011
3234 00234 02-01-2011



However, if they work for more than one employer:


SSN Employer# DATE

3234 00234 01-01-2011
3234 00556 01-01-2011


The employee gets more than one record for the same date since the employer# is unique.

I tried this query:

SELECT   distinct A.MBR_SSN_NBR
FROM     DSNP.PR01_T_MBR_HIST A
WHERE    A.MBR_HIST_SVC_CR_DT >= '2010-07-01' 

group by a.mbr_ssn_nbr

having count(distinct a.agty_id_cd) > 1

Problem is the query pulls in anyone that has more than one employer# after July 1. I need my query to only show more than one employer# within the SAME month.

Can anyone help?

 

[markros]

;with Employee_Info as (select Mbr_SSN_NBR,
dense_rank() over (partition by MBR_SSN_NBR, datediff(month,'19000101',MBR_HIST_SVC_CR_DT order by EmployerNbr)
as EmployeesRank
from DSN.PROJ...
where Date > '20100701')

select mbr_ssn_nbr
from cte where EmployeesRank > 1
group by mbr_SSN_nbr

I used DENSE_RANK() in order to rank employers within month per employee. If the person has rank > than 1, it means he/she has more than 1 employer in that montn. The datediff function used to correctly get records sorted within each month (I could have used convert(varchar(6),dateFld, 112) instead )

PluralSight Learning Library

 

[wbodger]

You should be able to do this with table aliases, something like this

SELECT A.MBR_SSN_NBR, A.Employer, A.MBR_HIST_SVC_CR_DT, B.MBR_SSN_NBR, B.Employer, B.MBR_HIST_SVC_CR_DT
FROM DSNP.PR01_T_MBR_HIST A JOIN DSNP.PR01_T_MBR_HIST B
	ON A. SSN = B.SSN AND A.MBR_HIST_SVC_CR_DT=B.MBR_HIST_SVC_CR_DT
WHERE A.Employer<> B.Employer
AND A.MBR_HIST_SVC_CR_DT >= '2010-07-01'

This isn't quite right, I think, but this idea should work for you. With a little more sample data I could test and figure it out more completely. There may be more elegant ways, but this should work fine.

wb

 

[PWise]

how about
SELECT A.MBR_SSN_NBR
FROM DSNP.PR01_T_MBR_HIST A
WHERE A.MBR_HIST_SVC_CR_DT >= '2010-07-01'
group by a.mbr_ssn_nbr ,MBR_HIST_SVC_CR_DT
having count( a.agty_id_cd) > 1

 

[markros]

You should also be able to do

select * from YourTable T
where exists (select 1 from YourTable T2
where T.SSN = T2.SSN and convert(varchar(6),T.Date,112) = 
convert(varchar(6),T2.Date,112) and T.Employer < T2.Employer)

This will select all employee records that have more than 1 employer per month.

PluralSight Learning Library

 

[lrdave36]

Thanks for all the help, guys! Markros, I haven't used CTE in queries yet, but I am comfortable with derived tables. Is there way to write your query using derived tables instead?

Or can you give me a link that has some good tutorials on CTE? Looks like this is a concept I need to master. Thanks a bunch!

 

[markros]

Sure, you can use CTE as a derived table in such cases as I showed.

select ... from (here goes the query that was in cte) cte ...

I can recommend this link (and it has many links inside) because I'm a bit partial to it

CTE and hierarchical queries

PluralSight Learning Library

 

[lrdave36]

Thanks for the reference link. I spent today creating a dummy table and testing the RANK functions to get a better grasp on this part of your query example.

I wrote this query:

select A.mbr_ssn_nbr, A.TEST

FROM

(SELECT   mbr_ssn_nbr, mbr_hist_svc_cr_dt,
		 dense_rank() over (partition by mbr_ssn_nbr order by  agty_id_cd) as TEST
FROM     dbo.mbr_hist) as A



group by a.mbr_ssn_nbr

having count(A.TEST) >  1

In my dummy table, I only have one SSN with two agty_id_cd so there is only one RANK over 1. I am puzzled why my query returns most of the SSNS instead of just the one. Am I missing something?

 

[markros]

Your condition is incorrect. Why you're using having count(A.Test) and not where A.Test > 1 as I specified in my original query? If one employee has several records (but all one employer), you'll have many records returned. Dense_rank is just numbering the records in according to specified condition. So, if you have several agty_id_cd per SSN, then dense_rank() will be 1, 2, 3 etc. If you have only agty_id_cd but many records per SSN, then count(A.Test) will return as many records as you have per SSN.

I used dense_rank in order to avoid count(distinct ) which doesn't really perform well.


PluralSight Learning Library

 

[lrdave36]

You're right, I don't know why I thought I still needed count. I appreciate your explanation. I have a much better understanding of RANK and DENSE RANK.