SQL and statistical issue - 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

SQL Server 2005 Forums

Transact-SQL (2005)

SQL and statistical issue

Author

Topic

liuk
Starting Member

5 Posts

Posted - 2007-04-02 : 09:42:37

Hi
i store car accident information in a db .
Now i need to get statistics on accident , and i'd like to get them in an efficient way.
Starting from my DB table :
ACCIDENT : IdAccident, Date, Street, StreetType, City
ACCIDENT_PERSON: idaccident, idperson, InjuryType
PERSON : idperson, Name, BirthDate, MaleFemale

i create a temporary table having a row for each accident
IdAccident, Street, StreetType, AccidentYear, AccidentMonth, ....

then i perform calculation over it.

When i have been told to get number of accident per InjuryType i have added some column to my temp table i.e. injuryTypeA, injuryTypeB, injuryTypeC
where, for each accident, i calculate the number of people involved into the accident with that injury.

i don't think this is a good solution, in fact if i'd like to get the same statistic but reporting also Male and Female information, or, even worse, the age of person, this would lead to cerate a table with many column .

Now the question is...
... which is the best way to do this type of elaboration?
Do i have to store data in different way?
Any suggest will be appreciated.
thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-02 : 09:48:01

Please provide some sample data and expected output.

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2007-04-02 : 09:48:05

>> Any suggest will be appreciated.

Sure, read the hint link in my sig and post what it asks for, but I doubt the solution will use a temp table

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-02 : 10:02:56

I think what you need is CROSS-TAB query, but more sample data and expected output will be more helpful.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-02 : 10:10:09

Or just a simple GROUP BY statement with some JOINs (INNER or LEFT)

CREATE A VIEW like this

SELECT a.IdAccident, a.Date, DATEPART(YEAR, a.Date) AS theYear, DATEPART(MONTH, a.Date) AS theMonth,
a.Street, a.StreetType, a.City, ap.idperson, ap.InjuryType, p.Name, p.BirthDate, p.MaleFemale
FROM Accident AS a
INNER JOIN Accident_Person AS ap ON ap.IdAccident = a.IdAccident
INNER JOIN Person AS p ON p.IdPerson = ap.IdPerson

THEN THE "STATISTICS".

SELECT theYear AccidentYear, theMonth AccidentMonth, InjuryType, COUNT(*)
FROM VIEWDEF
GROUP BY theYear, theMonth, InjuryType
order by theYear, theMonth, InjuryType

Peter Larsson
Helsingborg, Sweden

liuk
Starting Member

5 Posts

Posted - 2007-04-02 : 10:31:02

Well, it's not easy for me to write in english.
The purpose is to let the user choose which kind of statistic to perform at runtime. He/She should be capable to choose how to aggregate data.

So, given the table's content below:
ACCIDENT
id---------date--------StreetType
1 2007-04-02 A

PERSON
id------malefemale-------birthdate
20 m 1940-01-01
21 f 1980-02-01

PERSON_ACCIDENT
idprs----idacc----injurytype
20 1 INJ_A
21 1 INJ_A

The user could ask, for example, for :
a) number of accident per year
b) number of accident per streettype
c) number of injured per injurytype

My first solution has been to create a temporary table(whithin a stored procedure):

idaccident----accidentyear---streettype---inj_A---inj_B---inj_C
1 2007 A 2 0 0

The data above meaning than in the accident two person report injury of type "A"

And if the user want to get the "number of injured classified per gender (male/female)"?

Should i have this temporary table ?
idaccident-MALEInjuryA-FEMALEinjuryA-MALEInjuryB-FEMALEInjuryB.....
1 1 1 0 0

I don't think this is a good way to face the problem but i don't know how to do.

things are getting worse if the user want to classify data on person age, for example.

I need a generic and flexible way to model data and perform calculation over it. Any suggest?

I hope i was clear.
thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-02 : 10:37:07

Well, you could design standard set of reports like Accidents by Injury Type, Accidents by Gender, Accidents by Age and then design separate SPs to deal with each of these reports.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ElManiak
Starting Member

4 Posts

Posted - 2007-04-02 : 16:38:38

Generic reports work,
or also you can use cubes.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources