help required to implement the logic

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

SQL Server 2005 Forums

Transact-SQL (2005)

help required to implement the logic

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2009-08-18 : 15:26:07

Hi Pals,

Need urgent help.

I have below source table from Excl fle.

drop table test
create table test
(dist varchar(10),
school varchar(10),
history varchar(100),
science varchar(100),
commerce varchar(100)
)
DELETE from test
GO
insert test(dist,school,history,science,commerce) values('D1','S1','100','400',null)
insert test(dist,school,history,science,commerce) values('D1','S1','300','504','305')
insert test(dist,school,history,science,commerce) values('D1','S1','358','650',null)
GO
update statistics test
GO

select * from test

dist school history science commerce
D1 S1 100 400
D1 S1 300 504 305
D1 S1 358 650

clearly from the above output it is clear the 3 students have written "History" exam
3 students written science exam but only 1 student has written commerce exam.

Note : Looking at the source data, i can say total 3 students have written the exams
and if iMart_Graduation take the averages of each subject i will get the below results.

After pivoting also i need to able to get back the total no of students.
and if iMart_Graduation calculate the averages then i should get back the original values.

dist school history science commerce
D1 S1 100 400
D1 S1 300 504 305
D1 S1 358 650
---- --- ---
252.666 518 305
----- --- ----

-- After unpivoting on 3 subjects, am storing data into table "stg"
Hi Pals,

Need help!

create table stg
(dist varchar(10),
school varchar(10),
subjct varchar(50),
student_cnt int,
score varchar(10)
)

insert into stg
select dist,
school,
subjct,
0 as student_cnt,
score
from (select dist,school,case when history IS null then 0 else history end as history ,case when science IS null then 0 else science end as science, case when commerce is null then 0 else commerce end as commerce from test) p
unpivot
(score for subjct in (history,science,commerce)
) AS Unpvt

select * from stg

dist school subjct student_cnt score
D1 S1 history 0 100
D1 S1 science 0 400
D1 S1 commerce 0 0

D1 S1 history 0 300
D1 S1 science 0 504
D1 S1 commerce 0 305

D1 S1 history 0 358
D1 S1 science 0 650
D1 S1 commerce 0 0

now from the staging am pulling data to dataware house as follows.

drop table trg

create table trg
(dist varchar(10),
school varchar(10),
subjct varchar(20),
student_cnt int,
avg_score float
)
truncate table trg

insert into trg
select dist,
school,
subjct,
COUNT(*) as student_cnt,
AVG(convert(decimal(9,2),score)) avg_score
from stg
group by dist,school,subjct

select * from trg

dist school subjct student_cnt avg_score
D1 S1 commerce 3 101.666666666667
D1 S1 history 3 252.666666666667
D1 S1 science 3 518

Original Values from Source
dist school history science commerce
D1 S1 100 400
D1 S1 300 504 305
D1 S1 358 650
---- --- ---
252.666 518 305
----- --- ----

It is very much clear above 2 result sets that , avg for history , science is correct but the average of commerce is wrong.It has to be 305/1 which is 305. instead it is performing 305/3 =>101.6666

But using this data i can get back by no of students taken the exams
i.e
select SUM(student_cnt)/3 from trg
--3
but my average is wrong!!!!!!!!!!

For this purpose, what i have done is, am ignoring the students not given the respective exam so that my averages will always be corrct.
This is what is have done

truncate table stg

insert into stg
select * from
(
select dist,
school,
subjct,
0 as student_cnt,
score
from (select dist,school,history,science,commerce from test) p
unpivot
(score for subjct in (history,science,commerce)
) AS Unpvt
) a where len(a.score)>0

-- 9 recs
truncate table trg

select * from stg
/*
dist school subjct student_cnt score
D1 S1 history 0 100
D1 S1 science 0 400
D1 S1 history 0 300
D1 S1 science 0 504
D1 S1 commerce 0 305
D1 S1 history 0 358
D1 S1 science 0 650
*/
-- only 7 recs

insert into trg
select dist,
school,
subjct,
COUNT(*) as student_cnt,
AVG(convert(decimal(9,2),score)) avg_score
from stg
group by dist,school,subjct

select * from trg

dist school subjct student_cnt avg_score
D1 S1 commerce 1 305
D1 S1 history 3 252.666666
D1 S1 science 3 518

This time am getting the exact values

Original Values from Source
dist school history science commerce
D1 S1 100 400
D1 S1 300 504 305
D1 S1 358 650
---- --- ---
252.666 518 305
----- --- ----

But now again problem, using this logic am able to get the Averages correctly but now
suppose i want to display the no of students attended for the Exam i.e. i need to get "3" as total.
How to get it???????????

Please suggest me the correct way so that i can get no of students from trg as well as averages correct.

It is urgent.

Thanks in advance!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-18 : 15:38:55

sorry you lost me. Could you just post what you are starting with (DDL/DML) and what your desired result output based on the sample DML?

Be One with the Optimizer
TG

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2009-08-18 : 21:22:11

This is my source data.

Now my ddl / dml will be as follows

DROP TABLE #test

create table #test
( yr int,
dist varchar(10),
school varchar(10),
history int,
science int,
commerce int
)
GO
insert #test(yr,dist,school,history,science,commerce) values(1991,'D1','S1','100','400',null)
insert #test(yr,dist,school,history,science,commerce) values(1991,'D1','S1','300','504','305')
insert #test(yr,dist,school,history,science,commerce) values(1991,'D1','S1','358','650',null)

insert #test(yr,dist,school,history,science,commerce) values(1992,'D1','S1','100','400',null)
insert #test(yr,dist,school,history,science,commerce) values(1992,'D1','S1','300','504','305')
insert #test(yr,dist,school,history,science,commerce) values(1992,'D1','S1','358','650',null)

insert #test(yr,dist,school,history,science,commerce) values(1993,'D1','S1','100','400',null)
insert #test(yr,dist,school,history,science,commerce) values(1993,'D1','S1','300','504','305')
insert #test(yr,dist,school,history,science,commerce) values(1993,'D1','S1','358','650',null)

insert #test(yr,dist,school,history,science,commerce) values(1994,'D1','S1','100','400',null)
insert #test(yr,dist,school,history,science,commerce) values(1994,'D1','S1','300','504','305')
insert #test(yr,dist,school,history,science,commerce) values(1994,'D1','S1','358','650',null)

GO

SELECT *
FROM #test
;

WITH stg AS (
select
yr,
dist,
school,
subjct,
studentID,
0 as student_cnt,
score
from (
select yr,
dist
, school
, history
, science
, commerce
, Row_Number() OVER ( ORDER BY Dist ) AS StudentID
from #test) p
unpivot
(score for subjct in (history,science,commerce)
) AS Unpvt
)
SELECT yr,dist, school, subjct, Count(studentID) as studentcnt, Avg(score) avgscore
FROM stg
GROUP BY yr,dist, school, subjct

This should be my final output

Final Report
------------
Year NofExaminees History Science Commerce
1991 3 252 518 305
1992 3 252 518 305
1993 3 252 518 305
1994 3 252 518 305

Note : The Values inside the History,Science,Commerce are averages.

No of Examinees , u can find it in #test table for each year.

Sachin.Nand

2937 Posts

Posted - 2009-08-19 : 05:25:26

select yr as Year,COUNT(dist)as NoOfExaminee,AVG(history)as History,AVG(science) as Science,AVG(commerce)as Commerce
from #test
group by yr

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources