Path of leaning adivce would be appreciated immensely.

[isnomagic]

I was hoping to receive some 'road to travel'
advice in regards to following a course of
learning that would put me in a position as
such that I will be able to achieve programs/reports
on the same scale and power as I did with the UNIX
based software system we are abandoning in favour
of an SQL server 2000 system.
In brief, I would be asked to and successfully
write reports that would consist of calculated
virtual records made up of selective data from
many files with restrictions based on categories
such as date period, customer categories or product
categories etc that may be part of a user input at
the start of the routine.
For example it may ask for a start date a finish date
to be input, then a product group, then do you to include
customers from a certain category (Y/N?), essentially any
restrictions could be introduced and input at the start of
the report program.
I used UNIBASIC routines, either as subroutines for virtual
attributes (fields) or as stand alone programs that just worked with the databases directly.
It was fairly easy for me to get any information with any restrictions from any database file and compile this information for report purposes.
I do not believe this is 'easily' achievable at an SQL level alone.
I do not yet know enough about T-SQL to state anything about it's capabilities to do this sort of thing. For example I was forever doing GOSUB's, IF THEN ELSE, FOR NEXT loops, gathering individual fields from many files and doing calculations on these alone to create a desired field
result for the output, before stepping to the next line of output to be created for the reports.
I will be asked to reproduce some or all of the existing programs/reports as time goes by, so I need to arm myself with something that will enable me to do this.
Can any person suggest a road to travel?
Is T-SQL enough or should I seriously consider learning another program language that handles SQL tables and records and fields to the level required?
Any advice would be much appreciated.

 

[isnomagic]

I do believe learning advice would be more appreciated than leaning advice sorry.

 

[r937]

hey, you can lean on us!

as long as your data is in sql/server tables, rather than outside it in external files, what we call "flat" files, then...

"Is T-SQL enough"

yes

"should I seriously consider learning another program language that handles SQL tables and records and fields to the level required"

no

i would go so far as to suggest that ANSI SQL, the core around which microsoft's transact-sql is built, can solve most of your reporting requirements

transact-sql is nice because when you have to -- and only when you have to (as i keep insisting to people who have come from a procedural programming background) -- you can perform processes and logic "outside" basic set operations, such as using a cursor to process table rows sequentially (shock! horror!)

if you're new to relational databases, perhaps you have not yet seen the power and complexity you can achieve with ordinary sql -- not transact-sql, just ordinary sql

for an example, have a look at

Statistics in SQL

http://www.oreilly.com/catalog/transqlcook/chapter/ch08.html

Chapter 8 of O'Reilly's Transact-SQL Cookbook -- Means, modes, medians, Standard deviations, Variances, Standard errors, Confidence intervals, Correlations, Moving averages, Weighted moving averages

yes, the entire chapter, with examples, on the web


rudy

http://rudy.ca/

 

[SQLSister]

GEe, you don't want to learn SQL the way most of did through the traditional 'trial by fire' method? Can't understand why.

Seriously though, my first recommendation is one that pretty much all the newbies around here get directed to which is SQL Books Online which comes with the SQL server program. Lots of good stuff in there, especially when you are looking for the syntax of a partuclar command you don't use very often.

My next recommedation is that you get yourself to a big bookstore and get books on the following topics:
SQL server database administration, T-SQL language reference,and a stored procedure programming guide. If you will have a need to import or export a lot of data, I also recommend getting a book on DTS.

Another great way to learn is to hang around here and read all the threads, not just the ones with topics you are currently interested in.

SQL Server is also only a database, it doesn't have a user interface front end. If you are going to need one of them, you probably neeed to decide which one.

T-SQL is very good at manipulating data in complex ways. Data manipulation is its purpose in life after all. As R937 said, it is a set-oriented language which is a differnt beast than a procedural language. Cursors are a good example of a procedural oriented function which you can use in T-SQL but which you should avoid at all ocsts. Cursors are slow and T-SQL has other faster methods that can do the same thing most of the time. I can't think of a single instance where we have had to use a cursor and we do very complex things with our databases.

Good luck.