In both quarters - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

In both quarters

Author

Topic

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2012-04-02 : 12:00:44

Hi -

Can anyone please help? I am trying to count those that were included in Quarter 2 that are still present in Quarter 3. This is what I have:

select q3.Plan_Name, q3.Region, q3.Commcareplan, max(q3.mbr_id) as mbr_id, q3.ratecell, q3.sum_of_count, q3.age_sex, q3.geo_score,
q3.Benefit_Score, q3.risk_score, q3.normalization_factor, q3.normed_risk, q3.final_risk_score as q3FSR,
q2.final_risk_score as q2FSR
from dbo.jb_Q2_detail Q2 inner join dbo.jb_Q3_detail Q3
on q2.mbr_id = Q3.mbr_id
group by q3.Plan_Name, q3.Region, q3.Commcareplan, q3.ratecell, q3.sum_of_count, q3.age_sex, q3.geo_score,
q3.Benefit_Score, q3.risk_score, q3.normalization_factor, q3.normed_risk, q3.final_risk_score,
q2.final_risk_score

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-02 : 12:24:37

Well, since you are doing an inner join between Q2 table and Q3 table, you will only get a result for those mbr_id that does exist in both quarter.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-02 : 12:30:52

Perhaps this is what you want?

SELECT		q3.Plan_Name,
		q3.Region,
		q3.Commcareplan,
		q3.mbr_id,
		q3.ratecell,
		q3.sum_of_count,
		q3.age_sex,
		q3.geo_score,
		q3.Benefit_Score,
		q3.risk_score,
		q3.normalization_factor,
		q3.normed_risk,
		q3.q3FSR,
		q2.q2FSR
FROM		(
			SELECT	Plan_Name,
				Region,
				Commcareplan,
				mbr_id,
				ratecell,
				sum_of_count,
				age_sex,
				geo_score,
				Benefit_Score,
				risk_score,
				normalization_factor,
				normed_risk,
				final_risk_score AS q2FSR,
				ROW_NUMBER() OVER (PARTITION BY Plan_Name, Region, Commcareplan, ratecell, sum_of_count, age_sex, geo_score, Benefit_Score, risk_score, normalization_factor, normed_risk, final_risk_score ORDER BY mbr_id DESC) AS SeqID
			FROM	dbo.jb_Q2_detail
		) AS Q2
INNER JOIN	(
			SELECT	mbr_id,
				final_risk_score AS q3FSR,
				ROW_NUMBER() OVER (PARTITION BY Plan_Name, Region, Commcareplan, ratecell, sum_of_count, age_sex, geo_score, Benefit_Score, risk_score, normalization_factor, normed_risk, final_risk_score ORDER BY mbr_id DESC) AS SeqID
			FROM	dbo.jb_Q3_detail
		) AS Q3 ON q2.mbr_id = Q3.mbr_id
WHERE		Q2.SeqID = 1
		AND Q3.SeqID = 1

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-02 : 15:56:52

why do you need separate tables for each quarter like jb_Q3_detail? isnt it matter of having an extra datefield inside table to indicate timeperiod. you can derive all other things from it like month,year,quarter etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources