October 2, 2007 at 8:49 am
I'm trying to create a matrix report that shows grade level on the left side and geography code across the top. The report is to show the number of students in a grade level with a particular geography code. The sql run and the data looks good:
---------------------------------------------------------
select
ss.graden as Grade,
s.geocode as Geo,
s.suniq
from
stustat as ss inner join
studemo as s on ss.suniq = s.suniq inner join
track as t on ss.trkuniq = t.trkuniq
where
t.schoolc = @School
and ss.edate <= @ddate
and (ss.xdate >= @ddate or ss.xdate is null)
group by
ss.graden,
s.geocode,
s.suniq
order by
ss.graden desc,
s.geocode desc
---------------------------------------------------------
The problem I'm having is that there are (sometimes) duplicate geography codes across the top of the report and they indicate different counts under them for the same geography code.
October 2, 2007 at 9:39 am
Opus,
Your Statement:
select
ss.graden as Grade,
s.geocode as Geo,
s.suniq
from
stustat as ss inner join
studemo as s on ss.suniq = s.suniq inner join
track as t on ss.trkuniq = t.trkuniq
where
t.schoolc = @School
and ss.edate <= @ddate
and (ss.xdate >= @ddate or ss.xdate is null)
group by
ss.graden,
s.geocode,
s.suniq
order by
ss.graden desc,
s.geocode desc
Explanation:
For your statement, you are grouping by grade, geocode, and then student id. To count number of students per geocode per grade, you will have to modify your statements to the following:
New Statement:[/u]
select
ss.graden as Grade,
s.geocode as Geo,
count(s.geoCode) as NumStudents
from
stustat as ss inner join
studemo as s on ss.suniq = s.suniq inner join
track as t on ss.trkuniq = t.trkuniq
where
t.schoolc = @School
and ss.edate <= @ddate
and (ss.xdate >= @ddate or ss.xdate is null)
group by
ss.graden,
s.geocode
order by
ss.graden desc,
s.geocode desc
Note:[/u]
Please do a syntax check prior to running the "New Statement" above, I do not have the tables so I have not executed the statement. There may be potential syntax errors. Also, the "New Statement" above will count number of student per geocode per grade; the statement will not handle "count distinct students."
I hope this sample will work for you.
Regards,
Wameng Vang
MCTS
October 2, 2007 at 10:54 am
I'm still getting two columns with the same geography code. Grades 10 and 12 are under the first geocode "P" column and 11 grade under the second geocode "P" column. I did a "select distinct geocode" from the table and there is on one code with "P".
October 2, 2007 at 1:10 pm
Opus,
For now, judging from our postings, your query will return for each grade, a geocode, and a student count. Therefore, geocode can exist more than once, because you are grouping by grade first, and then geocode.
However, if you want me to clarify this further. Please provide DDL scripts for table structure and provide some sample input data for me to test out.
You can check for me on my IM, if you need a quicker response.
Regards,
Wameng Vang
MCTS
October 2, 2007 at 2:21 pm
mengus,
Sent you a PM.
October 3, 2007 at 7:32 am
Opus,
I have replied to your PM. What I meant by IM, was my MSN contact information.
Please keep in mind to post information in the original topic posting, not PMs. 😉 You can copy the content of the private messages into the original topic posting so that others are aware that you've either have further questions or that you have found the solution you are looking for. This helps us manage our time searching topics to answer.
Regards,
Wameng Vang
MCTS
October 3, 2007 at 7:57 am
DDL
Attached has show the problem with the columns.
This is going to be long ...
CREATE TABLE [dbo].[studemo](
[suniq] [int] NOT NULL,
[ident] [varchar](10) NOT NULL,
[stuuniq] [int] NULL,
[firstname] [varchar](16) NOT NULL,
[middlename] [varchar](16) NULL,
[lastname] [varchar](20) NOT NULL,
[namesfx] [varchar](4) NULL,
[nickname] [varchar](16) NULL,
[genderc] [varchar](1) NOT NULL,
[ethnicc] [varchar](1) NOT NULL,
[birthdate] [smalldatetime] NOT NULL,
[birthplace] [varchar](32) NULL,
[regdate] [smalldatetime] NULL,
[ssn] [char](9) NULL,
[gradyear] [decimal](4, 0) NULL,
[homelangc] [varchar](2) NULL,
[primlangc] [varchar](2) NULL,
[homeaddr1] [varchar](32) NULL,
[homeaddr2] [varchar](32) NULL,
[homecity] [varchar](24) NULL,
[homestate] [varchar](4) NULL,
[homezip] [char](9) NULL,
[mailaddr1] [varchar](32) NULL,
[mailaddr2] [varchar](32) NULL,
[mailcity] [varchar](24) NULL,
[mailstate] [varchar](4) NULL,
[mailzip] [char](9) NULL,
[emailaddr] [varchar](32) NULL,
[phnnumber] [char](10) NULL,
[phntypec] [varchar](1) NULL,
[phnunlist] [decimal](1, 0) NULL,
[phnmsg] [decimal](1, 0) NULL,
[bverbasc] [varchar](2) NULL,
[bverdocnum] [varchar](32) NULL,
[citizenc] [varchar](2) NULL,
[countryc] [varchar](2) NULL,
[dUsEntry] [smalldatetime] NULL,
[geocode] [varchar](10) NULL,
[resschoolc] [varchar](5) NULL,
[resdistc] [varchar](10) NULL,
[chcschoolc] [varchar](5) NULL,
[gradreqc] [varchar](10) NULL,
[careeruniq] [int] NULL,
[hlduniq] [int] NULL,
[buspuniq] [int] NULL,
[busduniq] [int] NULL,
[maritalc] [varchar](1) NULL,
[migrantnum] [varchar](16) NULL,
[legalbind] [varchar](254) NULL,
[passwd] [varchar](11) NULL,
[retainflag] [decimal](1, 0) NULL,
[norank] [decimal](1, 0) NULL,
[inforelc] [varchar](2) NULL,
[counsfuniq] [int] NULL,
[memberc] [varchar](10) NULL,
[chgby] [varchar](5) NULL,
[chgdt] [datetime] NULL,
[stunotes] [text] NULL,
[graddate] [smalldatetime] NULL,
[compstatc] [varchar](2) NULL,
[outofstate] [tinyint] NOT NULL CONSTRAINT [DF_studemo_outofstate] DEFAULT ((0)),
[SSID] [int] NULL,
[SSIDChangeFlag] [int] NOT NULL CONSTRAINT [SSIDChangeFlag_default] DEFAULT ((0)),
------------------------------------------
CREATE TABLE [dbo].[stustat](
[stuuniq] [int] NOT NULL,
[ident] [varchar](10) NOT NULL,
[suniq] [int] NOT NULL,
[edate] [smalldatetime] NULL,
[stustatc] [varchar](1) NULL,
[graden] [decimal](2, 0) NULL,
[trkuniq] [int] NULL,
[engprofc] [varchar](2) NULL,
[servicec] [varchar](2) NULL,
[funiq] [int] NULL,
[snsortby] [varchar](24) NULL,
[xdate] [smalldatetime] NULL,
[entryc] [varchar](3) NULL,
[exitc] [varchar](3) NULL,
[chgby] [varchar](5) NULL,
[chgdt] [datetime] NULL,
[snreserve1] [decimal](1, 0) NULL,
[snreserve2] [decimal](1, 0) NULL,
---------------------------------------------
CREATE TABLE [dbo].[track](
[trkuniq] [int] NOT NULL,
[trackc] [varchar](3) NOT NULL,
[descript] [varchar](32) NOT NULL,
[schoolc] [varchar](5) NOT NULL,
[schyear] [decimal](4, 0) NOT NULL,
[ppd] [decimal](2, 0) NOT NULL,
[dpc] [decimal](2, 0) NOT NULL,
[tpy] [decimal](2, 0) NOT NULL,
[mpy] [decimal](2, 0) NULL,
[perqtr] [decimal](2, 0) NULL,
[perhalf] [decimal](2, 0) NULL,
[perfull] [decimal](2, 0) NULL,
[countabs] [decimal](1, 0) NULL,
[posatt] [decimal](1, 0) NULL,
[mpfte] [decimal](3, 0) NULL,
[usesat] [decimal](1, 0) NULL,
[belluniq] [int] NULL,
[skipdays] [decimal](1, 0) NULL,
[credyr] [decimal](6, 4) NULL,
[sched] [decimal](1, 0) NULL,
October 4, 2007 at 5:52 am
FYI,
This is the query for which the original author is now using for his reports. (Reporting Services against a SQL Server 2000 database, thus we cannot use the flexibility of pivot tables from SQL Server 2005. Therefore, I apologize for the code below. ;))
select
distinct
helper.Grade
, helper.Geo
, ISNULL(counter.NumStudents,0) as NumStudents
from
(
select
Grade
, Geo
from
(
select
distinct geoCode as Geo
from studemo
) as h
,
(
select
distinct graden as Grade
from stustat
) as h2
) as helper
left outer join
(
select
ss.graden as Grade
, s.geocode as Geo
, count(s.geoCode) as NumStudents
from
stustat as ss
inner join studemo as s
on ss.suniq = s.suniq
inner join track as t
on ss.trkuniq = t.trkuniq
where
t.schoolc = @School
and
ss.edate <= @ddate
and
(ss.xdate >= @ddate or ss.xdate is null)
group by
ss.graden
, s.geocode
) as counter
on helper.Geo = Counter.Geo
and
helper.Grade = Counter.Grade
order by
helper.Grade desc
, helper.Geo desc
Regards,
Wameng Vang
MCTS
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply