Running Totals

Hi Folks,

I have a problem that I know that i should be able to code up but have drawn a blank due to it being monday. Anyway.....

Have a table :

create table test_registrations
(
date_maint smalldatetime,
user_name1 varchar (255),
user_address1 varchar (255),
total_users int
)
go

If i have a number of registrations on a particular date then I can tell all how many users have registered in any date by :

select date_maint , count (1)
from test_registrations
group by date_maint
order by date_maint desc
go

The qestion is how can I keep a total registared users count. Say if I have 10 users join on the 1st of Jan and 15 on the 3rd then I want
the total users for the users on 1st to read 10 and total users on the 3rd to read 25.

I know i should be able to code this up but I'm being a dumb ass. Can someone show me a way to code it. Is it some sort of correlated sub query to keep a running total ?select
distinct convert(varchar(10),date_maint,101),
(
select
count(user_name1)
from
test_registrations
where
convert(varchar(10),date_maint,101) <= convert(varchar(10),b.date_maint,101)
)
from
test_registrations b

Try this ... am not sure whether it will work or not ... not near a server right now to test the query|||Excellent mate. Thanks again. I knew it was a subquery.

Cheers|||Is the example above a correlated subquery?

Running SMO inside a SCRIPT TASK

Dear folks,

I'm trying to call SMO classes from SSIS package but I don't know which reference to use..

Mighn't be Imports Microsoft.SqlServer.Management? But Intellisense don't give me such info.

Let me know where I'm failing.

TIA

http://www.google.co.uk/search?hl=en&q=smo+namespace&meta=

-Jamie

|||

thanks Jamie,

It seems that must be moved some assemblies to \Microsoft.NET\Framework\v2.0.xxxxx in order to acquire its full features from a package.

|||

Correct. But only at design-time

http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2341.aspx

-Jamie

|||

thanks for the response but I don't get the point colleague.

What do you mean in design-time? I want to instantiate SMO classes from Script Task. I know that I can do smo stuff by mean Foreach Loop Container and using as enumerator SMO but my goal is run (as I did before with sql2k and DMO throught Visual Basic Script) SMO fully from scripting

|||

You only need the DLLs in \Microsoft.NET\Framework\v2.0.xxxxx when you are desinging the package. When you run it they don't need to be there.

This is an important consideration when you deploy your packages because it means you don't have to deploy DLLs to \Microsoft.NET\Framework\v2.0.xxxxx on the server on which you run your packages.

-Jamie

|||

ok, thanks again.

But anyway no namespaces appears in my Script Task about SMO after that.

|||

Did you add a reference and add the Imports statement?

-Jamie

|||thanks a lot!!!!!!!! you must be tons of patience|||

enric vives wrote:

thanks a lot!!!!!!!! you must be tons of patience

I'm sure my girlfriend wouldn't say that !!!

-Jamie

|||Well then I hope she is not a programmer/dba or something like that..|||

Definately definately not!

|||!!