create subtotals dynamically

I am trying to dynamically create subtotals base on values in a column. I also have to group by year. I have gotten the group by year. I used the (previous function) to check for the year and col1 not being equal. I am getting the first subtotal back, don't know how to proceed in my function to return the remaining subtotals

i.e.

col1 col2 col3

aaa 111 2005

bbb 222 2006

aaa 333 2006

ccc 444 2005

ccc 555 2006

bbb 666 2006

bbb 777 2006

ddd 888 2007

2005

aaa subtotal: 1

ccc subtotal: 1

total: 2

2006

aaa subtotal: 1

bbb subtotal: 3

ccc subtotal: 1

total: 5

2007

ddd subtotal: 1

total: 1

grandtotal:

aaa 2

bbb 3

ccc 2

ddd 1

totalnumber 8

I found the solution.

Thanks

create new table from base table

Hi,
I am trying to create a new table from an existing base table. The base
table isn't normalised in any way. The table was given to me and that's
what I have to work with. The problem is that parent ids can have
multiple sectors and the second level records only have 1 sector. I
want to iteratate to the parent record and take all the sector
available and put them in the new table together with the alfacode.
base table:
id, name, parent, alfacode, level, sector
eg.
1, test, 0, 100, 0, 122
1, test, 0, 100, 0, 123
2, test1, 1, 101, 1, 122
3, test2, 1, 102, 1, 122
destination table:
alfacode, sector
The destination table with the data above would become:
100, 122
100, 123
101, 122
101, 123
102, 122
102, 123
Hope I made myself clear.
Any help would be greatly appreciated.
PezkelHi Pezkel
Try something like:
SELECT a.alfacode, a.sector
FROM #structure a
WHERE a.level = 0
UNION
SELECT b.alfacode, b.sector
FROM #structure a
JOIN #structure b ON A.id = b.parent
UNION
SELECT b.alfacode, a.sector
FROM #structure a
JOIN #structure b ON A.id = b.parent
John
"barcode@.dds.nl" wrote:

> Hi,
> I am trying to create a new table from an existing base table. The base
> table isn't normalised in any way. The table was given to me and that's
> what I have to work with. The problem is that parent ids can have
> multiple sectors and the second level records only have 1 sector. I
> want to iteratate to the parent record and take all the sector
> available and put them in the new table together with the alfacode.
> base table:
> id, name, parent, alfacode, level, sector
> eg.
> 1, test, 0, 100, 0, 122
> 1, test, 0, 100, 0, 123
> 2, test1, 1, 101, 1, 122
> 3, test2, 1, 102, 1, 122
> destination table:
> alfacode, sector
> The destination table with the data above would become:
> 100, 122
> 100, 123
> 101, 122
> 101, 123
> 102, 122
> 102, 123
> Hope I made myself clear.
> Any help would be greatly appreciated.
> Pezkel
>|||Hi John,
Thanks. I will give it a spin tomorrow.|||Hi John,
Just ran the query and it works like a charm. Very much appreciated.

create new db

Greetings,
Using a server base database called Respond (fe) and Sql server 2000 as a (be), I was trying to create a new db. Instead of designing from scratch, I was thinking to copy an existing Respond db, delete existing data, and modify it to fit my new requirements. I'm new to sql server db and would like to know
1. How to delete data from the table (be).
2. How to perform an automatic backup daily (or may be every other day) and how to check if the backup is doing what it was suppose to do in sql server 2000.

Thank you in advance,

OCMOCM, this is really basic stuff. Delete data by either issueing a DELETE sql command or a TRUNCATE TABLE sql command. Backups can easily be automated through the Enterprise Manager console.
You need to avail yourself of the wealth of information in Microsoft SQL Server Books Online.|||OCM, this is really basic stuff. Delete data by either issueing a DELETE sql command or a TRUNCATE TABLE sql command. Backups can easily be automated through the Enterprise Manager console.
You need to avail yourself of the wealth of information in Microsoft SQL Server Books Online.|||Backups can easily be automated through the Enterprise Manager console. :)

You just had to tell him that didn't you. :) You can also schedule a job to do them. Check out this weblog: http://weblogs.sqlteam.com/tarad

Create Index on view base on left outer join .

Hi ,
I am trying to Create Index on view but I can not due to the fact that this view is base on left outer join ( between 5 tables).
It is very important to create the index because the current view performance must improve.
Thanks,
EyalSimply, you cant.
There are many requirements for the view to accept creating an index on it.

The SELECT statement in the view cannot contain
1. A derived table
2. Rowset functions
3. UNION operator
4. Outer or self joins
And many other things.

Originally posted by eschapir
Hi ,
I am trying to Create Index on view but I can not due to the fact that this view is base on left outer join ( between 5 tables).
It is very important to create the index because the current view performance must improve.

Thanks,
Eyal

Create DB from Backup

Hi all, is there an easy way to create a Data Base from a Backup file?
I've tried creating the data base and then trying to restore the prevous bac
ked up data base but I get some errors like "Directory lookup for the file (
the mdf file) failed with the operating system error 3 (The system cannot fi
nd the specified path)"
TIA
--
.seb
http://sgomez.blogspot.comDo not create the database. Just do a restore. On the Restore database
window select Options and make sure that the Restore As column shows a valid
path and that you have permissions on it.
Ben Nevarez, MCDBA, OCP
Database Administrator
".seb" wrote:

> Hi all, is there an easy way to create a Data Base from a Backup file?
> I've tried creating the data base and then trying to restore the prevous b
acked up data base but I get some errors like "Directory lookup for the file
(the mdf file) failed with the operating system error 3 (The system cannot
find the specified path)"
> TIA
> --
> ..seb
> http://sgomez.blogspot