Double Checking Logic

[Gixonita]

Hello,

I'm just looking for a second pair of eyes to double check the logic in simplifying a query:

FROM:

INSERT INTO	tblCFARS_Scores_0506 	(
						 DSITPAY,
						 PROVID,
						 SSN,
						 CFARDATEMAX, 
						 PURPOSEMAX,
						 SCOREMAX
						)

	SELECT	C1.DISTPAY,
		C1.PROVID,
		C1.SSN,
		MAX (C1.CFARDATE),
		MAX (C1.PURPOSE),
		C2.MAXSCORE

	FROM	samh.dbo.tblCFAR_SAMH AS C1,
	(
	select DISTPAY, PROVID, SSN, CFARDATE, PURPOSE,
	(cast(isnull(ADLFUNCT, 1) as int) + 
	cast(isnull(ANXIETY, 1) as int) +
	cast(isnull(BEHAVIOR, 1) as int) +
	cast(isnull(COGNITIV, 1) as int) +
	cast(isnull(DANGOTH, 1) as int) +
	cast(isnull(DANGSELF, 1) as int) +
	cast(isnull(DEPRESS, 1) as int) +
	cast(isnull(HYPERACT, 1) as int) +
	cast(isnull(RELATION, 1) as int) +
	cast(isnull(MEDICAL, 1) as int) +
	cast(isnull(SCHOOL, 1) as int) +
	cast(isnull(SECURITY, 1) as int) +
	cast(isnull(SOCLEGAL, 1) as int) +
	cast(isnull(SUBSTANC, 1) as int) +
	cast(isnull(THOUGHT, 1) as int) +
	cast(isnull(TRAUMATI, 1) as int) +
	cast(isnull(DAYSWORK, 1) as int)) as MaxScore
	from samh.dbo.tblCFAR_SAMH
	where purpose in ('2', '3') and SSN is not null
	) as c2
where c1.DISTPAY = c2.DISTPAY
	and c1.PROVID = c2.PROVID
	and c1.SSN = c2.SSN
	and c1.purpose in ('2', '3') and c1.SSN is not null
group by c1.DISTPAY, c1.PROVID, c1.SSN, c2.MaxScore
having max(c1.CFARDATE) between @BeginDate and @EndDate
	and max(c1.CFARDATE) = max(c2.CFARDATE)
	and max(c1.purpose) = max(c2.PURPOSE)
order by c1.DISTPAY, c1.PROVID, c1.SSN

TO:

	INSERT INTO	tblCFARS_Scores_0506 	(
						 DSITPAY,
						 PROVID,
						 SSN,
						 CFARDATEMAX, 
						 PURPOSEMAX,
						 SCOREMAX
						)

	SELECT	C1.DISTPAY,
		C1.PROVID,
		C1.SSN,
		MAX (C1.CFARDATE),
		MAX (C1.PURPOSE),
		CAST 	(ISNULL (C1.ADLFUNCT, 1) + 
			 ISNULL (C1.ANXIETY,  1) +
			 ISNULL (C1.BEHAVIOR, 1) +
			 ISNULL (C1.COGNITIV, 1) +
			 ISNULL (C1.DANGOTH,  1) +
			 ISNULL (C1.DANGSELF, 1) +
			 ISNULL (C1.DEPRESS,  1) +
			 ISNULL (C1.HYPERACT, 1) +
			 ISNULL (C1.RELATION, 1) +
			 ISNULL (C1.MEDICAL,  1) +
			 ISNULL (C1.SCHOOL,   1) +
			 ISNULL (C1.SECURITY, 1) +
			 ISNULL (C1.SOCLEGAL, 1) +
			 ISNULL (C1.SUBSTANC, 1) +
			 ISNULL (C1.THOUGHT,  1) +
			 ISNULL (C1.TRAUMATI, 1) +
			 ISNULL (C1.DAYSWORK, 1) AS INT) AS SCOREMAX

	FROM	samh.dbo.tblCFAR_SAMH AS C1,

	WHERE	C1.PURPOSE IN ('2', '3') 	AND
		C1.SSN IS NOT NULL

	GROUP BY	C1.DISTPAY,
			C1.PROVID,
			C1.SSN

	HAVING	MAX (C1.CFARDATE) BETWEEN @BeginDate AND @EndDate

	ORDER BY	C1.DISTPAY,
			C1.PROVID,
			C1.SSN

Thank you in advance

 

[vongrunt]

I'm not sure 1st query works under T-SQL rules - becase SCOREMAX is used in SELECT but not in GROUP BY...

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[vongrunt]

Actually... I was talking about 2nd query...

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[Gixonita]

Hi vongrunt,

checked the syntax in QA and gave me no error messages.

 

[SQLDenis]

run both selects only

SELECT    C1.DISTPAY,
        C1.PROVID,
        C1.SSN,
        MAX (C1.CFARDATE),
        MAX (C1.PURPOSE),
        CAST     (ISNULL (C1.ADLFUNCT, 1) + 
             ISNULL (C1.ANXIETY,  1) +
             ISNULL (C1.BEHAVIOR, 1) +
             ISNULL (C1.COGNITIV, 1) +
             ISNULL (C1.DANGOTH,  1) +
             ISNULL (C1.DANGSELF, 1) +
             ISNULL (C1.DEPRESS,  1) +
             ISNULL (C1.HYPERACT, 1) +
             ISNULL (C1.RELATION, 1) +
             ISNULL (C1.MEDICAL,  1) +
             ISNULL (C1.SCHOOL,   1) +
             ISNULL (C1.SECURITY, 1) +
             ISNULL (C1.SOCLEGAL, 1) +
             ISNULL (C1.SUBSTANC, 1) +
             ISNULL (C1.THOUGHT,  1) +
             ISNULL (C1.TRAUMATI, 1) +
             ISNULL (C1.DAYSWORK, 1) AS INT) AS SCOREMAX

    FROM    samh.dbo.tblCFAR_SAMH AS C1,

    WHERE    C1.PURPOSE IN ('2', '3')     AND
        C1.SSN IS NOT NULL

    GROUP BY    C1.DISTPAY,
            C1.PROVID,
            C1.SSN

    HAVING    MAX (C1.CFARDATE) BETWEEN @BeginDate AND @EndDate

    ORDER BY    C1.DISTPAY,
            C1.PROVID,
            C1.SSN

are the results the same?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Gixonita]

Denis,

Can't run the selects yet, let me explain why... I work on a non profit organization that sends data to the state every month, then that data can be analyzed through their dashboard. We have been having a lot of problems with their results so they provided us with their table definitions and the sqls they use to generate and display results. At the moment I'm just going through the huge file "cleaning", "formatting" and when I can "reducing or simplifying" their code (which is really messy and with redundant queries).

As soon as I'm done with this and I have an understanding of what is going on then I'm going to replicate their process (obvioulsy we have all the data I need) and I'm going to create an internal dashboard of our own.

I just got to this query where I wasn't really sure if I was recoding correctly and I just wanted to see if any of you could point out flaws in the logic.

Thanks for your help

Luis Torres