April 20, 2015 at 3:54 pm
Here's the code, followed by the explanation of what I'm wanting to do:
--alter view vwStudentsAPHQualificationSummary as
WITH StudentAPH_CTE (DistrictNum, DistrictName, APHQualified, QualificationCount)
AS
(
select st.DistrictNum,
ld.DistrictName, APHQualified, 1
from vwStudentsFiltered st
left outer join lkupDistricts ld
on st.DistrictNum = ld.DistrictNum
UNION ALL
select '000' as DistrictNum, '(students unassigned to a district)' as DistrictName,
'Y' as APHQualified, 0 as QualificationCount
UNION ALL
select '000' as DistrictNum, '(students unassigned to a district)' as DistrictName,
'N' as APHQualified, 0 as QualificationCount
UNION ALL
select '000' as DistrictNum, '(students unassigned to a district)' as DistrictName,
'D' as APHQualified, 0 as QualificationCount
UNION ALL
select '000' as DistrictNum, '(students unassigned to a district)' as DistrictName,
'P' as APHQualified, 0 as QualificationCount
UNION ALL
select '000' as DistrictNum, '(students unassigned to a district)' as DistrictName,
'PS' as APHQualified, 0 as QualificationCount
UNION ALL
select '000' as DistrictNum, '(students unassigned to a district)' as DistrictName,
'S' as APHQualified, 0 as QualificationCount
)
select DistrictNum, DistrictName, APHQualified, sum(QualificationCount) as QualificationCount
from StudentAPH_CTE
group by DistrictNum, DistrictName, APHQualified
GO
So there are six APH codes: Y, N, D, P, PS, and S. This view feeds an Access crosstab query and report. The reason for the Unions is that, depending on the time of the year, not all of these values may be in use, so I'm forcing them through effectively appending zero count dummy records to the result set. And this works just fine. (I blank the zeroes in the report using format codes)
But I'm a little uneasy. I worked on this trying to join the lkupAPHCodes table using various methods with no joy, unfortunately I deleted my attempts so I can't show the approach that I was taking. I'm a firm believer in the SQL 'many ways to do things' paradigm and I'm kinda frustrated that I couldn't make it work in a single statement without using a CTE. I'm not averse to a CTE, I'm just looking for a JOIN version that does the same thing.
Any suggestions?
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
April 20, 2015 at 6:00 pm
I think this does the same thing and it looks a little easier on my eyes.
--alter view vwStudentsAPHQualificationSummary as
WITH StudentAPH_CTE (DistrictNum, DistrictName, APHQualified, QualificationCount)
AS
(
select st.DistrictNum,
ld.DistrictName, APHQualified, 1
from vwStudentsFiltered st
left outer join lkupDistricts ld
on st.DistrictNum = ld.DistrictNum
UNION ALL
SELECT '000' as DistrictNum, '(students unassigned to a district)' as DistrictName
,APHQualified, 0 as QualificationCount
FROM
(
VALUES ('Y'),('N'),('D'),('P'),('PS'),('S')
) a (APHQualified)
)
select DistrictNum, DistrictName, APHQualified, sum(QualificationCount) as QualificationCount
from StudentAPH_CTE
group by DistrictNum, DistrictName, APHQualified;
It does not address the two things you wanted to do though:
1) Remove the CTE - easy enough if you're OK to simply put that into a derived table, but that is what I'd call "artificial" removal.
2) Make sure all the APHQualified codes are forced through. In order to (possibly) do that it would be helpful to know which of your two JOINed tables that code is in. Sorry if you said it and I missed it.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 20, 2015 at 6:09 pm
Making the assumption that APHQualified comes from your vwStudentsFiltered table, maybe this is what you really wanted.
--alter view vwStudentsAPHQualificationSummary as
select DistrictNum, DistrictName, APHQualified, sum(QualificationCount) as QualificationCount
from
(
select DistrictNum=ISNULL(st.DistrictNum, '000'),
DistrictName=ISNULL(ld.DistrictName, '(students unassigned to a district)')
,a.APHQualified
,QualificationCount=CASE WHEN ld.DistrictNum IS NULL THEN 0 ELSE 1 END
from (VALUES ('Y'),('N'),('D'),('P'),('PS'),('S')) a (APHQualified)
left outer join vwStudentsFiltered st ON st.APHQualified = a.APHQualified
left outer join lkupDistricts ld
on st.DistrictNum = ld.DistrictNum
) a
group by DistrictNum, DistrictName, APHQualified;
It is still an artificial removal of the CTE though.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 21, 2015 at 9:35 am
Dwain, that first code worked perfect, the second one only has D/N/P/Y appearing for district 000, which may or may not work live. Since the first one does work the way that I need, that's the one that I'll use. I'd never used that format of a Select, most of my time has previously been spent in a DBA role. This greatly improves the fairly ugly code that I was dealing with. Much thanks!
Joe, the reason for a filtered view on the Students table is that I'm implementing row-level security. People assigned to one district are not normally allowed to view student data details of kids in another district: FERPA privacy regulations, not unlike HIPAA. The view provides that isolation quite nicely, I'd welcome a good suggestion for other ways to do it. Even though I'd rather not have views calling views, I prefer to have the security established in one view that works well than risk copying the code every time that students are referenced and potentially omitting or breaking it.
A join only between Districts and Students would not satisfy my need because not all APHCodes would be represented all of the time which would break the downstream report. Putting in a zero value record is for the downstream report to get consistent columns since it's doing a crosstab, I wanted all values represented. It has nothing to do with moving values.
As far as field names are concerned, they are based on the state-level data model which I'm following to maximize familiarity for the users using my app with other apps that I have no control over. If you have a link to an ISO guide to data element naming conventions for the education industry, I'd love to see it.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
April 21, 2015 at 5:27 pm
Wayne West (4/21/2015)
Dwain, that first code worked perfect, the second one only has D/N/P/Y appearing for district 000, which may or may not work live. Since the first one does work the way that I need, that's the one that I'll use. I'd never used that format of a Select, most of my time has previously been spent in a DBA role. This greatly improves the fairly ugly code that I was dealing with. Much thanks!
Strange that the second solution didn't work. Thought I'd nailed it. But then I didn't have access to the underlying data to be sure.
In any event, glad I provided something that could help.
Edit: I realized that it is probably the second LEFT OUTER JOIN in my second solution that is the reason it doesn't work for you. Because there's no district 000 in that table.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 22, 2015 at 8:54 am
dwain.c (4/21/2015)
Wayne West (4/21/2015)
Dwain, that first code worked perfect, the second one only has D/N/P/Y appearing for district 000, which may or may not work live. Since the first one does work the way that I need, that's the one that I'll use. I'd never used that format of a Select, most of my time has previously been spent in a DBA role. This greatly improves the fairly ugly code that I was dealing with. Much thanks!Strange that the second solution didn't work. Thought I'd nailed it. But then I didn't have access to the underlying data to be sure.
In any event, glad I provided something that could help.
Edit: I realized that it is probably the second LEFT OUTER JOIN in my second solution that is the reason it doesn't work for you. Because there's no district 000 in that table.
I went back and forth on providing tables and some sample data and ended up not doing it. It's development code, I'm still working with it because I'm getting a scan instead of a seek against the districts table, which isn't a huge deal because it's pretty static and non-growing, but just the same I'd like it to work better. The left join isn't needed as it's a not null field and related to the district table and is now removed, like I said, it's development code. The scan is only 8% of the query cost, so I'm leaning towards not worrying too much about the scan since this is one of those reports that's needed irregularly for 2-3 months at the start of the year then it won't be run until the following year.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply