hi:
i m new to VB.thatswhy i have some problem in connecting VB with sql server.actually i m able to connect but it would nt run the query,giving me the run time error "invalid object name <tablename>".
in the begining i thought i have probelm with the name but i changed the name.
then i thought it might have something to do with database permissions.i did checke that but for some reason the error would nt go away.
here is the code
Private Sub btnSubmit_Click()
Dim Conn As New ADODB.Connection
Dim RecSet As New ADODB.Recordset
Dim SQLQuery
Fname = txtFname.Text 'getting var from text box
Lname = txtLname.Text
'The database is on romote server
SQLQuery = "Insert into introduction (fname,lname) values ('" & Fname & "','" & Lname & "')"
Conn.Open "Provider=SQLOLEDB.1;UID=someID;PWD=myPWD;Intitial Catalog=test;Data Source=MYserver"
RecSet.Open SQLQuery, Conn
Conn.Close
Set RecSet = Nothing
End Sub
This code runs under a command button click.i m getting the values from the text boxes and try to insert them in the database.but i m getting this weird error.
Please help!!!!Who's the schema owner of your INTRODUCTION table? I suspect it's someone other than DBO.|||...pressed on the button too early :)
Preceed the table name with the schema owner in your INSERT:
insert into user_name.introduction ...|||thanks for prompt reply.but the problem still persists.
i checked on ownership of the table .it is DBO.
any ideas!!|||The problem is, that your SQL statements isn't a query, but a DML statement. How do you expect to get a recordset by issueing an INSERT statement?!
Change your line
RecSet.Open SQLQuery, Conn
into
Conn.Execute SQLQuery
and it will work fine.|||Thanks for ur reply.
i did try that ,change the line to "Conn.Execute SQLQuery",and still its giving me the same error
any other suggestions...|||hi
Actually i solved the problem.it was not working with DSN less connection as i was trying to do.
so i created DSN,test the connection,and then used it in my code,and it worked fine.
although i still dont know why it was not working with DSN less connection. i used the same parameters as i was doing it in DSN less,and it worked.
but hey as long it works i happy.
thanks guys
Showing posts with label query. Show all posts
Showing posts with label query. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
RunningValue Function for Year over Year Comparison
I have a query which returns sales dollars grouped on a yearly basis,
along with some other groupings. How would I display the year
information in a matrix and still be able to compare the first year to
the second. The data set returned has a column for dollars and one for
years, it does not seperate dollar values into different columns for
each year. Should I be using the Running Value Function or is there a
different way I should be trying to accomplish this task. This is
fairly common task for our reports, and we need the ablity to return
muliplte years in our data set with an unknown number of years return.
Here is the query.
select
pd.division_desc
,pr.region_desc
,po.opco_desc
,sh.opco_ident
,sh.opco_num
,ocmtt.customer_major_type_desc
,ot.gl_year
,ot.gl_period
,ot.gl_week
,sum(total_sales) total_sales
,sum(total_actual_cost) total_actual_cost
from
pfg_dw.dbo.sales_header sh
inner join
pfg_dw.dbo.opco_time ot
on
sh.opco_num=ot.opco_num
and
sh.day_end_num=ot.day_end_num
inner join
pfg_dw.dbo.opco_customer oc
on
sh.customer_ident=oc.customer_ident
inner join
pfg_dw.dbo.opco_customer_major_type_tbl ocmtt
on
oc.major_type_ident= ocmtt.customer_major_type_ident
inner join
pfg_dw.dbo.pfg_opco po
on
sh.opco_ident=po.opco_ident
inner join
pfg_dw.dbo.pfg_region pr
on
po.region_ident=pr.region_ident
inner join
pfg_dw.dbo.pfg_division pd
on
pd.division_ident=pr.division_ident
where
(
ot.day_end_date between
dateadd(dd,-datepart(dw,getdate())-6,getdate())
and
dateadd(dd,-datepart(dw,getdate())+1,getdate())
)
or
(
ot.day_end_date between
dateadd(yy,-1,dateadd(dd,-datepart(dw,getdate())-6,getdate()) )
and
dateadd(yy,-1,dateadd(dd,-datepart(dw,getdate())+1,getdate()))
)
group by
pd.division_desc
,pr.region_desc
,po.opco_desc
,sh.opco_ident
,sh.opco_num
,o.cmtt.customer_major_type_desc
,ot.gl_year
,ot.gl_period
,ot.gl_weekHere is a better example of what I am trying to do
Company Name 1st Year 2ndYear Difference
xyz 100 200 100
abc 150 200 50
How do I compute the difference column in a matrix if my result set
looks like the following:
Company Year $Total
xyz 1st Year 100
xyz 2nd Year 200
abc 1st Year 150
abc 2nd Year 200
sfibich wrote:
> I have a query which returns sales dollars grouped on a yearly basis,
> along with some other groupings. How would I display the year
> information in a matrix and still be able to compare the first year to
> the second. The data set returned has a column for dollars and one for
> years, it does not seperate dollar values into different columns for
> each year. Should I be using the Running Value Function or is there a
> different way I should be trying to accomplish this task. This is
> fairly common task for our reports, and we need the ablity to return
> muliplte years in our data set with an unknown number of years return.
> Here is the query.
> select
> pd.division_desc
> ,pr.region_desc
> ,po.opco_desc
> ,sh.opco_ident
> ,sh.opco_num
> ,ocmtt.customer_major_type_desc
> ,ot.gl_year
> ,ot.gl_period
> ,ot.gl_week
> ,sum(total_sales) total_sales
> ,sum(total_actual_cost) total_actual_cost
> from
> pfg_dw.dbo.sales_header sh
> inner join
> pfg_dw.dbo.opco_time ot
> on
> sh.opco_num=ot.opco_num
> and
> sh.day_end_num=ot.day_end_num
> inner join
> pfg_dw.dbo.opco_customer oc
> on
> sh.customer_ident=oc.customer_ident
> inner join
> pfg_dw.dbo.opco_customer_major_type_tbl ocmtt
> on
> oc.major_type_ident= ocmtt.customer_major_type_ident
> inner join
> pfg_dw.dbo.pfg_opco po
> on
> sh.opco_ident=po.opco_ident
> inner join
> pfg_dw.dbo.pfg_region pr
> on
> po.region_ident=pr.region_ident
> inner join
> pfg_dw.dbo.pfg_division pd
> on
> pd.division_ident=pr.division_ident
> where
> (
> ot.day_end_date between
> dateadd(dd,-datepart(dw,getdate())-6,getdate())
> and
> dateadd(dd,-datepart(dw,getdate())+1,getdate())
> )
> or
> (
> ot.day_end_date between
> dateadd(yy,-1,dateadd(dd,-datepart(dw,getdate())-6,getdate()) )
> and
> dateadd(yy,-1,dateadd(dd,-datepart(dw,getdate())+1,getdate()))
> )
> group by
> pd.division_desc
> ,pr.region_desc
> ,po.opco_desc
> ,sh.opco_ident
> ,sh.opco_num
> ,o.cmtt.customer_major_type_desc
> ,ot.gl_year
> ,ot.gl_period
> ,ot.gl_week|||Multiply second year with -1 and calculate the total at the end.
While displaying it again multiply it with -1.
Hope this works.
Regards,
Cem
"sfibich" <sfibich@.pfgc.com> wrote in message
news:ep5mtspmEHA.1800@.TK2MSFTNGP15.phx.gbl...
> Here is a better example of what I am trying to do
> Company Name 1st Year 2ndYear Difference
> xyz 100 200 100
> abc 150 200 50
> How do I compute the difference column in a matrix if my result set
> looks like the following:
> Company Year $Total
> xyz 1st Year 100
> xyz 2nd Year 200
> abc 1st Year 150
> abc 2nd Year 200
>
> sfibich wrote:
> > I have a query which returns sales dollars grouped on a yearly basis,
> > along with some other groupings. How would I display the year
> > information in a matrix and still be able to compare the first year to
> > the second. The data set returned has a column for dollars and one for
> > years, it does not seperate dollar values into different columns for
> > each year. Should I be using the Running Value Function or is there a
> > different way I should be trying to accomplish this task. This is
> > fairly common task for our reports, and we need the ablity to return
> > muliplte years in our data set with an unknown number of years return.
> >
> > Here is the query.
> > select
> > pd.division_desc
> > ,pr.region_desc
> > ,po.opco_desc
> > ,sh.opco_ident
> > ,sh.opco_num
> > ,ocmtt.customer_major_type_desc
> > ,ot.gl_year
> > ,ot.gl_period
> > ,ot.gl_week
> > ,sum(total_sales) total_sales
> > ,sum(total_actual_cost) total_actual_cost
> > from
> > pfg_dw.dbo.sales_header sh
> > inner join
> > pfg_dw.dbo.opco_time ot
> > on
> > sh.opco_num=ot.opco_num
> > and
> > sh.day_end_num=ot.day_end_num
> > inner join
> > pfg_dw.dbo.opco_customer oc
> > on
> > sh.customer_ident=oc.customer_ident
> > inner join
> > pfg_dw.dbo.opco_customer_major_type_tbl ocmtt
> > on
> > oc.major_type_ident= ocmtt.customer_major_type_ident
> > inner join
> > pfg_dw.dbo.pfg_opco po
> > on
> > sh.opco_ident=po.opco_ident
> > inner join
> > pfg_dw.dbo.pfg_region pr
> > on
> > po.region_ident=pr.region_ident
> > inner join
> > pfg_dw.dbo.pfg_division pd
> > on
> > pd.division_ident=pr.division_ident
> > where
> > (
> > ot.day_end_date between
> > dateadd(dd,-datepart(dw,getdate())-6,getdate())
> > and
> > dateadd(dd,-datepart(dw,getdate())+1,getdate())
> > )
> > or
> > (
> > ot.day_end_date between
> > dateadd(yy,-1,dateadd(dd,-datepart(dw,getdate())-6,getdate()) )
> > and
> > dateadd(yy,-1,dateadd(dd,-datepart(dw,getdate())+1,getdate()))
> > )
> > group by
> > pd.division_desc
> > ,pr.region_desc
> > ,po.opco_desc
> > ,sh.opco_ident
> > ,sh.opco_num
> > ,o.cmtt.customer_major_type_desc
> > ,ot.gl_year
> > ,ot.gl_period
> > ,ot.gl_week
along with some other groupings. How would I display the year
information in a matrix and still be able to compare the first year to
the second. The data set returned has a column for dollars and one for
years, it does not seperate dollar values into different columns for
each year. Should I be using the Running Value Function or is there a
different way I should be trying to accomplish this task. This is
fairly common task for our reports, and we need the ablity to return
muliplte years in our data set with an unknown number of years return.
Here is the query.
select
pd.division_desc
,pr.region_desc
,po.opco_desc
,sh.opco_ident
,sh.opco_num
,ocmtt.customer_major_type_desc
,ot.gl_year
,ot.gl_period
,ot.gl_week
,sum(total_sales) total_sales
,sum(total_actual_cost) total_actual_cost
from
pfg_dw.dbo.sales_header sh
inner join
pfg_dw.dbo.opco_time ot
on
sh.opco_num=ot.opco_num
and
sh.day_end_num=ot.day_end_num
inner join
pfg_dw.dbo.opco_customer oc
on
sh.customer_ident=oc.customer_ident
inner join
pfg_dw.dbo.opco_customer_major_type_tbl ocmtt
on
oc.major_type_ident= ocmtt.customer_major_type_ident
inner join
pfg_dw.dbo.pfg_opco po
on
sh.opco_ident=po.opco_ident
inner join
pfg_dw.dbo.pfg_region pr
on
po.region_ident=pr.region_ident
inner join
pfg_dw.dbo.pfg_division pd
on
pd.division_ident=pr.division_ident
where
(
ot.day_end_date between
dateadd(dd,-datepart(dw,getdate())-6,getdate())
and
dateadd(dd,-datepart(dw,getdate())+1,getdate())
)
or
(
ot.day_end_date between
dateadd(yy,-1,dateadd(dd,-datepart(dw,getdate())-6,getdate()) )
and
dateadd(yy,-1,dateadd(dd,-datepart(dw,getdate())+1,getdate()))
)
group by
pd.division_desc
,pr.region_desc
,po.opco_desc
,sh.opco_ident
,sh.opco_num
,o.cmtt.customer_major_type_desc
,ot.gl_year
,ot.gl_period
,ot.gl_weekHere is a better example of what I am trying to do
Company Name 1st Year 2ndYear Difference
xyz 100 200 100
abc 150 200 50
How do I compute the difference column in a matrix if my result set
looks like the following:
Company Year $Total
xyz 1st Year 100
xyz 2nd Year 200
abc 1st Year 150
abc 2nd Year 200
sfibich wrote:
> I have a query which returns sales dollars grouped on a yearly basis,
> along with some other groupings. How would I display the year
> information in a matrix and still be able to compare the first year to
> the second. The data set returned has a column for dollars and one for
> years, it does not seperate dollar values into different columns for
> each year. Should I be using the Running Value Function or is there a
> different way I should be trying to accomplish this task. This is
> fairly common task for our reports, and we need the ablity to return
> muliplte years in our data set with an unknown number of years return.
> Here is the query.
> select
> pd.division_desc
> ,pr.region_desc
> ,po.opco_desc
> ,sh.opco_ident
> ,sh.opco_num
> ,ocmtt.customer_major_type_desc
> ,ot.gl_year
> ,ot.gl_period
> ,ot.gl_week
> ,sum(total_sales) total_sales
> ,sum(total_actual_cost) total_actual_cost
> from
> pfg_dw.dbo.sales_header sh
> inner join
> pfg_dw.dbo.opco_time ot
> on
> sh.opco_num=ot.opco_num
> and
> sh.day_end_num=ot.day_end_num
> inner join
> pfg_dw.dbo.opco_customer oc
> on
> sh.customer_ident=oc.customer_ident
> inner join
> pfg_dw.dbo.opco_customer_major_type_tbl ocmtt
> on
> oc.major_type_ident= ocmtt.customer_major_type_ident
> inner join
> pfg_dw.dbo.pfg_opco po
> on
> sh.opco_ident=po.opco_ident
> inner join
> pfg_dw.dbo.pfg_region pr
> on
> po.region_ident=pr.region_ident
> inner join
> pfg_dw.dbo.pfg_division pd
> on
> pd.division_ident=pr.division_ident
> where
> (
> ot.day_end_date between
> dateadd(dd,-datepart(dw,getdate())-6,getdate())
> and
> dateadd(dd,-datepart(dw,getdate())+1,getdate())
> )
> or
> (
> ot.day_end_date between
> dateadd(yy,-1,dateadd(dd,-datepart(dw,getdate())-6,getdate()) )
> and
> dateadd(yy,-1,dateadd(dd,-datepart(dw,getdate())+1,getdate()))
> )
> group by
> pd.division_desc
> ,pr.region_desc
> ,po.opco_desc
> ,sh.opco_ident
> ,sh.opco_num
> ,o.cmtt.customer_major_type_desc
> ,ot.gl_year
> ,ot.gl_period
> ,ot.gl_week|||Multiply second year with -1 and calculate the total at the end.
While displaying it again multiply it with -1.
Hope this works.
Regards,
Cem
"sfibich" <sfibich@.pfgc.com> wrote in message
news:ep5mtspmEHA.1800@.TK2MSFTNGP15.phx.gbl...
> Here is a better example of what I am trying to do
> Company Name 1st Year 2ndYear Difference
> xyz 100 200 100
> abc 150 200 50
> How do I compute the difference column in a matrix if my result set
> looks like the following:
> Company Year $Total
> xyz 1st Year 100
> xyz 2nd Year 200
> abc 1st Year 150
> abc 2nd Year 200
>
> sfibich wrote:
> > I have a query which returns sales dollars grouped on a yearly basis,
> > along with some other groupings. How would I display the year
> > information in a matrix and still be able to compare the first year to
> > the second. The data set returned has a column for dollars and one for
> > years, it does not seperate dollar values into different columns for
> > each year. Should I be using the Running Value Function or is there a
> > different way I should be trying to accomplish this task. This is
> > fairly common task for our reports, and we need the ablity to return
> > muliplte years in our data set with an unknown number of years return.
> >
> > Here is the query.
> > select
> > pd.division_desc
> > ,pr.region_desc
> > ,po.opco_desc
> > ,sh.opco_ident
> > ,sh.opco_num
> > ,ocmtt.customer_major_type_desc
> > ,ot.gl_year
> > ,ot.gl_period
> > ,ot.gl_week
> > ,sum(total_sales) total_sales
> > ,sum(total_actual_cost) total_actual_cost
> > from
> > pfg_dw.dbo.sales_header sh
> > inner join
> > pfg_dw.dbo.opco_time ot
> > on
> > sh.opco_num=ot.opco_num
> > and
> > sh.day_end_num=ot.day_end_num
> > inner join
> > pfg_dw.dbo.opco_customer oc
> > on
> > sh.customer_ident=oc.customer_ident
> > inner join
> > pfg_dw.dbo.opco_customer_major_type_tbl ocmtt
> > on
> > oc.major_type_ident= ocmtt.customer_major_type_ident
> > inner join
> > pfg_dw.dbo.pfg_opco po
> > on
> > sh.opco_ident=po.opco_ident
> > inner join
> > pfg_dw.dbo.pfg_region pr
> > on
> > po.region_ident=pr.region_ident
> > inner join
> > pfg_dw.dbo.pfg_division pd
> > on
> > pd.division_ident=pr.division_ident
> > where
> > (
> > ot.day_end_date between
> > dateadd(dd,-datepart(dw,getdate())-6,getdate())
> > and
> > dateadd(dd,-datepart(dw,getdate())+1,getdate())
> > )
> > or
> > (
> > ot.day_end_date between
> > dateadd(yy,-1,dateadd(dd,-datepart(dw,getdate())-6,getdate()) )
> > and
> > dateadd(yy,-1,dateadd(dd,-datepart(dw,getdate())+1,getdate()))
> > )
> > group by
> > pd.division_desc
> > ,pr.region_desc
> > ,po.opco_desc
> > ,sh.opco_ident
> > ,sh.opco_num
> > ,o.cmtt.customer_major_type_desc
> > ,ot.gl_year
> > ,ot.gl_period
> > ,ot.gl_week
Monday, March 26, 2012
Running T-SQL query against MS Access database
Is it possible to run a T-SQL query against an Access Database?
Such as a query that is not syntactically correct in Access...
select * from
OPENQUERY( anAccessLinkedServer,
'Select
[anSQLServerColumn1] = [anAccessColumn1],
[anSQLServerColumn2] = SUBSTRING([anAccessColumn2],1,1)
from [anAccessDatabaseTable]')
I have a program with large amounts of SQL code such as the above, and I do not want to have to rewrite it all in Access compliant syntax.
I can make it work only if I rewrite the above as...
select * from
OPENQUERY( anAccessLinkedServer,
'Select
[anAccessColumn1] AS [anSQLServerColumn1],
Left$([anAccessColumn2],1,1) AS [anSQLServerColumn2]
from [anAccessDatabaseTable]')
Kind Regards,
Laughton JacksonIf you are using OfficeXP (not sure about 2000 or ealier versions) you can open your database and go to the menu and click on Tools then options. Click on the Table/Queries tab and in the lower righ-hand corner check the box below the phrase "SQL Server Compatible Syntax (ANSI 92)" checkbox is marked "This database." Check the box and click OK.
This should help keep you from rewriting at least most of it.|||This option is not available in 2000.
Also, This Access dataase is being used by other legacy programs and this may affect these program's use of the database.
I was hoping this was possible by using the sp_addlinkedserver, sp_serveroption procedures.
Any other suggestions?
Thanks
Laughton Jackson|||Wish I did. Will keep you in mind and if anything comes up will post again.
Happy Hunting
Such as a query that is not syntactically correct in Access...
select * from
OPENQUERY( anAccessLinkedServer,
'Select
[anSQLServerColumn1] = [anAccessColumn1],
[anSQLServerColumn2] = SUBSTRING([anAccessColumn2],1,1)
from [anAccessDatabaseTable]')
I have a program with large amounts of SQL code such as the above, and I do not want to have to rewrite it all in Access compliant syntax.
I can make it work only if I rewrite the above as...
select * from
OPENQUERY( anAccessLinkedServer,
'Select
[anAccessColumn1] AS [anSQLServerColumn1],
Left$([anAccessColumn2],1,1) AS [anSQLServerColumn2]
from [anAccessDatabaseTable]')
Kind Regards,
Laughton JacksonIf you are using OfficeXP (not sure about 2000 or ealier versions) you can open your database and go to the menu and click on Tools then options. Click on the Table/Queries tab and in the lower righ-hand corner check the box below the phrase "SQL Server Compatible Syntax (ANSI 92)" checkbox is marked "This database." Check the box and click OK.
This should help keep you from rewriting at least most of it.|||This option is not available in 2000.
Also, This Access dataase is being used by other legacy programs and this may affect these program's use of the database.
I was hoping this was possible by using the sp_addlinkedserver, sp_serveroption procedures.
Any other suggestions?
Thanks
Laughton Jackson|||Wish I did. Will keep you in mind and if anything comes up will post again.
Happy Hunting
Running Transact-SQL script from SQL server agent with a different account
Hi,
I whised to run a T-SQL script as a SQL server Job that uses a linked
server to connect to a remote DB.
When I run the stored procedure in Query Analyser (with a specified
user) everything works fone but on SQL Agent I have the following
error:
Access to the remote server is denied because the current security
context is not trusted
How can I specify with wich user should the step run? The "run as" list
on Job Step window is disabled. How to use the new credential?
(The user exists on local and remote server).
ThanksHi
You don't say if your SQL Agent Service is running under a domain account or
not? At a guess you are using LOCALSYSTEM? See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
John
"pedro.f.silva@.gmail.com" wrote:
> Hi,
> I whised to run a T-SQL script as a SQL server Job that uses a linked
> server to connect to a remote DB.
> When I run the stored procedure in Query Analyser (with a specified
> user) everything works fone but on SQL Agent I have the following
> error:
> Access to the remote server is denied because the current security
> context is not trusted
> How can I specify with wich user should the step run? The "run as" list
> on Job Step window is disabled. How to use the new credential?
> (The user exists on local and remote server).
> Thanks
>|||It is using LOCALSYSTEM, I think.
But I would like to use a domain account just for this job. Is it
possible?
Why cannot i use "run as" on job step?
I thought that could be done creating a credential and a proxy but SQL
server agent doesn't allow proxys on T-SQL steps.
What can I do?
John Bell escreveu:
> Hi
> You don't say if your SQL Agent Service is running under a domain account or
> not? At a guess you are using LOCALSYSTEM? See
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> John
> "pedro.f.silva@.gmail.com" wrote:
> > Hi,
> >
> > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > server to connect to a remote DB.
> > When I run the stored procedure in Query Analyser (with a specified
> > user) everything works fone but on SQL Agent I have the following
> > error:
> >
> > Access to the remote server is denied because the current security
> > context is not trusted
> >
> > How can I specify with wich user should the step run? The "run as" list
> > on Job Step window is disabled. How to use the new credential?
> >
> > (The user exists on local and remote server).
> >
> > Thanks
> >
> >|||Hi
With a T-SQL step there is a run as user on the avanced options if you are
running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
John
"pedro.f.silva@.gmail.com" wrote:
> It is using LOCALSYSTEM, I think.
> But I would like to use a domain account just for this job. Is it
> possible?
> Why cannot i use "run as" on job step?
> I thought that could be done creating a credential and a proxy but SQL
> server agent doesn't allow proxys on T-SQL steps.
> What can I do?
> John Bell escreveu:
> > Hi
> >
> > You don't say if your SQL Agent Service is running under a domain account or
> > not? At a guess you are using LOCALSYSTEM? See
> >
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> >
> > John
> >
> > "pedro.f.silva@.gmail.com" wrote:
> >
> > > Hi,
> > >
> > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > server to connect to a remote DB.
> > > When I run the stored procedure in Query Analyser (with a specified
> > > user) everything works fone but on SQL Agent I have the following
> > > error:
> > >
> > > Access to the remote server is denied because the current security
> > > context is not trusted
> > >
> > > How can I specify with wich user should the step run? The "run as" list
> > > on Job Step window is disabled. How to use the new credential?
> > >
> > > (The user exists on local and remote server).
> > >
> > > Thanks
> > >
> > >
>|||I had noticed that there was a "run as" user on advanced options but it
didn't work.
I was expecting it (no working) since there's no place to specify the
user password. How could it run with that identify if I hadn't
specified the password? But there's no place to do it!
John Bell escreveu:
> Hi
> With a T-SQL step there is a run as user on the avanced options if you are
> running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> John
> "pedro.f.silva@.gmail.com" wrote:
> > It is using LOCALSYSTEM, I think.
> >
> > But I would like to use a domain account just for this job. Is it
> > possible?
> >
> > Why cannot i use "run as" on job step?
> >
> > I thought that could be done creating a credential and a proxy but SQL
> > server agent doesn't allow proxys on T-SQL steps.
> >
> > What can I do?
> >
> > John Bell escreveu:
> > > Hi
> > >
> > > You don't say if your SQL Agent Service is running under a domain account or
> > > not? At a guess you are using LOCALSYSTEM? See
> > >
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > >
> > > John
> > >
> > > "pedro.f.silva@.gmail.com" wrote:
> > >
> > > > Hi,
> > > >
> > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > server to connect to a remote DB.
> > > > When I run the stored procedure in Query Analyser (with a specified
> > > > user) everything works fone but on SQL Agent I have the following
> > > > error:
> > > >
> > > > Access to the remote server is denied because the current security
> > > > context is not trusted
> > > >
> > > > How can I specify with wich user should the step run? The "run as" list
> > > > on Job Step window is disabled. How to use the new credential?
> > > >
> > > > (The user exists on local and remote server).
> > > >
> > > > Thanks
> > > >
> > > >
> >
> >|||Hi
I would suggest that you create an account to use for a service account and
change the service account to be that.
John
"pedro.f.silva@.gmail.com" wrote:
> I had noticed that there was a "run as" user on advanced options but it
> didn't work.
> I was expecting it (no working) since there's no place to specify the
> user password. How could it run with that identify if I hadn't
> specified the password? But there's no place to do it!
>
> John Bell escreveu:
> > Hi
> >
> > With a T-SQL step there is a run as user on the avanced options if you are
> > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> >
> > John
> >
> > "pedro.f.silva@.gmail.com" wrote:
> >
> > > It is using LOCALSYSTEM, I think.
> > >
> > > But I would like to use a domain account just for this job. Is it
> > > possible?
> > >
> > > Why cannot i use "run as" on job step?
> > >
> > > I thought that could be done creating a credential and a proxy but SQL
> > > server agent doesn't allow proxys on T-SQL steps.
> > >
> > > What can I do?
> > >
> > > John Bell escreveu:
> > > > Hi
> > > >
> > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > not? At a guess you are using LOCALSYSTEM? See
> > > >
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > >
> > > > John
> > > >
> > > > "pedro.f.silva@.gmail.com" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > server to connect to a remote DB.
> > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > user) everything works fone but on SQL Agent I have the following
> > > > > error:
> > > > >
> > > > > Access to the remote server is denied because the current security
> > > > > context is not trusted
> > > > >
> > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > on Job Step window is disabled. How to use the new credential?
> > > > >
> > > > > (The user exists on local and remote server).
> > > > >
> > > > > Thanks
> > > > >
> > > > >
> > >
> > >
>|||Thanks John.
That works if I want all my Jobs to run with the same user.
Since I only have one it's ok :) - SQL server agent runs with the
account I need to invoke the Stored Procedure.
It would be nice however to be able to run different T-SQL Jobs with
different users.
John Bell escreveu:
> Hi
> I would suggest that you create an account to use for a service account and
> change the service account to be that.
> John
> "pedro.f.silva@.gmail.com" wrote:
> > I had noticed that there was a "run as" user on advanced options but it
> > didn't work.
> >
> > I was expecting it (no working) since there's no place to specify the
> > user password. How could it run with that identify if I hadn't
> > specified the password? But there's no place to do it!
> >
> >
> > John Bell escreveu:
> > > Hi
> > >
> > > With a T-SQL step there is a run as user on the avanced options if you are
> > > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> > >
> > > John
> > >
> > > "pedro.f.silva@.gmail.com" wrote:
> > >
> > > > It is using LOCALSYSTEM, I think.
> > > >
> > > > But I would like to use a domain account just for this job. Is it
> > > > possible?
> > > >
> > > > Why cannot i use "run as" on job step?
> > > >
> > > > I thought that could be done creating a credential and a proxy but SQL
> > > > server agent doesn't allow proxys on T-SQL steps.
> > > >
> > > > What can I do?
> > > >
> > > > John Bell escreveu:
> > > > > Hi
> > > > >
> > > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > > not? At a guess you are using LOCALSYSTEM? See
> > > > >
> > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > > >
> > > > > John
> > > > >
> > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > > server to connect to a remote DB.
> > > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > > user) everything works fone but on SQL Agent I have the following
> > > > > > error:
> > > > > >
> > > > > > Access to the remote server is denied because the current security
> > > > > > context is not trusted
> > > > > >
> > > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > > on Job Step window is disabled. How to use the new credential?
> > > > > >
> > > > > > (The user exists on local and remote server).
> > > > > >
> > > > > > Thanks
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >|||Hi
I don't know what your job is doing or why it needs to communicate with the
remote server, but it seems to be your only option with your current
implementation. If you used SQL 2005 there are more alternatives.
John
"pedro.f.silva@.gmail.com" wrote:
> Thanks John.
> That works if I want all my Jobs to run with the same user.
> Since I only have one it's ok :) - SQL server agent runs with the
> account I need to invoke the Stored Procedure.
> It would be nice however to be able to run different T-SQL Jobs with
> different users.
>
> John Bell escreveu:
> > Hi
> >
> > I would suggest that you create an account to use for a service account and
> > change the service account to be that.
> >
> > John
> >
> > "pedro.f.silva@.gmail.com" wrote:
> >
> > > I had noticed that there was a "run as" user on advanced options but it
> > > didn't work.
> > >
> > > I was expecting it (no working) since there's no place to specify the
> > > user password. How could it run with that identify if I hadn't
> > > specified the password? But there's no place to do it!
> > >
> > >
> > > John Bell escreveu:
> > > > Hi
> > > >
> > > > With a T-SQL step there is a run as user on the avanced options if you are
> > > > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> > > >
> > > > John
> > > >
> > > > "pedro.f.silva@.gmail.com" wrote:
> > > >
> > > > > It is using LOCALSYSTEM, I think.
> > > > >
> > > > > But I would like to use a domain account just for this job. Is it
> > > > > possible?
> > > > >
> > > > > Why cannot i use "run as" on job step?
> > > > >
> > > > > I thought that could be done creating a credential and a proxy but SQL
> > > > > server agent doesn't allow proxys on T-SQL steps.
> > > > >
> > > > > What can I do?
> > > > >
> > > > > John Bell escreveu:
> > > > > > Hi
> > > > > >
> > > > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > > > not? At a guess you are using LOCALSYSTEM? See
> > > > > >
> > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > > > >
> > > > > > John
> > > > > >
> > > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > > > server to connect to a remote DB.
> > > > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > > > user) everything works fone but on SQL Agent I have the following
> > > > > > > error:
> > > > > > >
> > > > > > > Access to the remote server is denied because the current security
> > > > > > > context is not trusted
> > > > > > >
> > > > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > > > on Job Step window is disabled. How to use the new credential?
> > > > > > >
> > > > > > > (The user exists on local and remote server).
> > > > > > >
> > > > > > > Thanks
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>|||I am using SQL Server 2005 :)
Which alternatives do I have?
John Bell escreveu:
> Hi
> I don't know what your job is doing or why it needs to communicate with the
> remote server, but it seems to be your only option with your current
> implementation. If you used SQL 2005 there are more alternatives.
> John
> "pedro.f.silva@.gmail.com" wrote:
> > Thanks John.
> >
> > That works if I want all my Jobs to run with the same user.
> >
> > Since I only have one it's ok :) - SQL server agent runs with the
> > account I need to invoke the Stored Procedure.
> >
> > It would be nice however to be able to run different T-SQL Jobs with
> > different users.
> >
> >
> > John Bell escreveu:
> > > Hi
> > >
> > > I would suggest that you create an account to use for a service account and
> > > change the service account to be that.
> > >
> > > John
> > >
> > > "pedro.f.silva@.gmail.com" wrote:
> > >
> > > > I had noticed that there was a "run as" user on advanced options but it
> > > > didn't work.
> > > >
> > > > I was expecting it (no working) since there's no place to specify the
> > > > user password. How could it run with that identify if I hadn't
> > > > specified the password? But there's no place to do it!
> > > >
> > > >
> > > > John Bell escreveu:
> > > > > Hi
> > > > >
> > > > > With a T-SQL step there is a run as user on the avanced options if you are
> > > > > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> > > > >
> > > > > John
> > > > >
> > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > >
> > > > > > It is using LOCALSYSTEM, I think.
> > > > > >
> > > > > > But I would like to use a domain account just for this job. Is it
> > > > > > possible?
> > > > > >
> > > > > > Why cannot i use "run as" on job step?
> > > > > >
> > > > > > I thought that could be done creating a credential and a proxy but SQL
> > > > > > server agent doesn't allow proxys on T-SQL steps.
> > > > > >
> > > > > > What can I do?
> > > > > >
> > > > > > John Bell escreveu:
> > > > > > > Hi
> > > > > > >
> > > > > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > > > > not? At a guess you are using LOCALSYSTEM? See
> > > > > > >
> > > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > > > > >
> > > > > > > John
> > > > > > >
> > > > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > > > >
> > > > > > > > Hi,
> > > > > > > >
> > > > > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > > > > server to connect to a remote DB.
> > > > > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > > > > user) everything works fone but on SQL Agent I have the following
> > > > > > > > error:
> > > > > > > >
> > > > > > > > Access to the remote server is denied because the current security
> > > > > > > > context is not trusted
> > > > > > > >
> > > > > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > > > > on Job Step window is disabled. How to use the new credential?
> > > > > > > >
> > > > > > > > (The user exists on local and remote server).
> > > > > > > >
> > > > > > > > Thanks
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >|||Hi
Look at the using extensions to the EXECUTE command. But first read
http://www.sommarskog.se/grantperm.html
John
"pedro.f.silva@.gmail.com" wrote:
> I am using SQL Server 2005 :)
> Which alternatives do I have?
> John Bell escreveu:
> > Hi
> >
> > I don't know what your job is doing or why it needs to communicate with the
> > remote server, but it seems to be your only option with your current
> > implementation. If you used SQL 2005 there are more alternatives.
> >
> > John
> >
> > "pedro.f.silva@.gmail.com" wrote:
> >
> > > Thanks John.
> > >
> > > That works if I want all my Jobs to run with the same user.
> > >
> > > Since I only have one it's ok :) - SQL server agent runs with the
> > > account I need to invoke the Stored Procedure.
> > >
> > > It would be nice however to be able to run different T-SQL Jobs with
> > > different users.
> > >
> > >
> > > John Bell escreveu:
> > > > Hi
> > > >
> > > > I would suggest that you create an account to use for a service account and
> > > > change the service account to be that.
> > > >
> > > > John
> > > >
> > > > "pedro.f.silva@.gmail.com" wrote:
> > > >
> > > > > I had noticed that there was a "run as" user on advanced options but it
> > > > > didn't work.
> > > > >
> > > > > I was expecting it (no working) since there's no place to specify the
> > > > > user password. How could it run with that identify if I hadn't
> > > > > specified the password? But there's no place to do it!
> > > > >
> > > > >
> > > > > John Bell escreveu:
> > > > > > Hi
> > > > > >
> > > > > > With a T-SQL step there is a run as user on the avanced options if you are
> > > > > > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> > > > > >
> > > > > > John
> > > > > >
> > > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > > >
> > > > > > > It is using LOCALSYSTEM, I think.
> > > > > > >
> > > > > > > But I would like to use a domain account just for this job. Is it
> > > > > > > possible?
> > > > > > >
> > > > > > > Why cannot i use "run as" on job step?
> > > > > > >
> > > > > > > I thought that could be done creating a credential and a proxy but SQL
> > > > > > > server agent doesn't allow proxys on T-SQL steps.
> > > > > > >
> > > > > > > What can I do?
> > > > > > >
> > > > > > > John Bell escreveu:
> > > > > > > > Hi
> > > > > > > >
> > > > > > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > > > > > not? At a guess you are using LOCALSYSTEM? See
> > > > > > > >
> > > > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > > > > > >
> > > > > > > > John
> > > > > > > >
> > > > > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > > > > >
> > > > > > > > > Hi,
> > > > > > > > >
> > > > > > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > > > > > server to connect to a remote DB.
> > > > > > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > > > > > user) everything works fone but on SQL Agent I have the following
> > > > > > > > > error:
> > > > > > > > >
> > > > > > > > > Access to the remote server is denied because the current security
> > > > > > > > > context is not trusted
> > > > > > > > >
> > > > > > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > > > > > on Job Step window is disabled. How to use the new credential?
> > > > > > > > >
> > > > > > > > > (The user exists on local and remote server).
> > > > > > > > >
> > > > > > > > > Thanks
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>sql
I whised to run a T-SQL script as a SQL server Job that uses a linked
server to connect to a remote DB.
When I run the stored procedure in Query Analyser (with a specified
user) everything works fone but on SQL Agent I have the following
error:
Access to the remote server is denied because the current security
context is not trusted
How can I specify with wich user should the step run? The "run as" list
on Job Step window is disabled. How to use the new credential?
(The user exists on local and remote server).
ThanksHi
You don't say if your SQL Agent Service is running under a domain account or
not? At a guess you are using LOCALSYSTEM? See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
John
"pedro.f.silva@.gmail.com" wrote:
> Hi,
> I whised to run a T-SQL script as a SQL server Job that uses a linked
> server to connect to a remote DB.
> When I run the stored procedure in Query Analyser (with a specified
> user) everything works fone but on SQL Agent I have the following
> error:
> Access to the remote server is denied because the current security
> context is not trusted
> How can I specify with wich user should the step run? The "run as" list
> on Job Step window is disabled. How to use the new credential?
> (The user exists on local and remote server).
> Thanks
>|||It is using LOCALSYSTEM, I think.
But I would like to use a domain account just for this job. Is it
possible?
Why cannot i use "run as" on job step?
I thought that could be done creating a credential and a proxy but SQL
server agent doesn't allow proxys on T-SQL steps.
What can I do?
John Bell escreveu:
> Hi
> You don't say if your SQL Agent Service is running under a domain account or
> not? At a guess you are using LOCALSYSTEM? See
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> John
> "pedro.f.silva@.gmail.com" wrote:
> > Hi,
> >
> > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > server to connect to a remote DB.
> > When I run the stored procedure in Query Analyser (with a specified
> > user) everything works fone but on SQL Agent I have the following
> > error:
> >
> > Access to the remote server is denied because the current security
> > context is not trusted
> >
> > How can I specify with wich user should the step run? The "run as" list
> > on Job Step window is disabled. How to use the new credential?
> >
> > (The user exists on local and remote server).
> >
> > Thanks
> >
> >|||Hi
With a T-SQL step there is a run as user on the avanced options if you are
running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
John
"pedro.f.silva@.gmail.com" wrote:
> It is using LOCALSYSTEM, I think.
> But I would like to use a domain account just for this job. Is it
> possible?
> Why cannot i use "run as" on job step?
> I thought that could be done creating a credential and a proxy but SQL
> server agent doesn't allow proxys on T-SQL steps.
> What can I do?
> John Bell escreveu:
> > Hi
> >
> > You don't say if your SQL Agent Service is running under a domain account or
> > not? At a guess you are using LOCALSYSTEM? See
> >
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> >
> > John
> >
> > "pedro.f.silva@.gmail.com" wrote:
> >
> > > Hi,
> > >
> > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > server to connect to a remote DB.
> > > When I run the stored procedure in Query Analyser (with a specified
> > > user) everything works fone but on SQL Agent I have the following
> > > error:
> > >
> > > Access to the remote server is denied because the current security
> > > context is not trusted
> > >
> > > How can I specify with wich user should the step run? The "run as" list
> > > on Job Step window is disabled. How to use the new credential?
> > >
> > > (The user exists on local and remote server).
> > >
> > > Thanks
> > >
> > >
>|||I had noticed that there was a "run as" user on advanced options but it
didn't work.
I was expecting it (no working) since there's no place to specify the
user password. How could it run with that identify if I hadn't
specified the password? But there's no place to do it!
John Bell escreveu:
> Hi
> With a T-SQL step there is a run as user on the avanced options if you are
> running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> John
> "pedro.f.silva@.gmail.com" wrote:
> > It is using LOCALSYSTEM, I think.
> >
> > But I would like to use a domain account just for this job. Is it
> > possible?
> >
> > Why cannot i use "run as" on job step?
> >
> > I thought that could be done creating a credential and a proxy but SQL
> > server agent doesn't allow proxys on T-SQL steps.
> >
> > What can I do?
> >
> > John Bell escreveu:
> > > Hi
> > >
> > > You don't say if your SQL Agent Service is running under a domain account or
> > > not? At a guess you are using LOCALSYSTEM? See
> > >
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > >
> > > John
> > >
> > > "pedro.f.silva@.gmail.com" wrote:
> > >
> > > > Hi,
> > > >
> > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > server to connect to a remote DB.
> > > > When I run the stored procedure in Query Analyser (with a specified
> > > > user) everything works fone but on SQL Agent I have the following
> > > > error:
> > > >
> > > > Access to the remote server is denied because the current security
> > > > context is not trusted
> > > >
> > > > How can I specify with wich user should the step run? The "run as" list
> > > > on Job Step window is disabled. How to use the new credential?
> > > >
> > > > (The user exists on local and remote server).
> > > >
> > > > Thanks
> > > >
> > > >
> >
> >|||Hi
I would suggest that you create an account to use for a service account and
change the service account to be that.
John
"pedro.f.silva@.gmail.com" wrote:
> I had noticed that there was a "run as" user on advanced options but it
> didn't work.
> I was expecting it (no working) since there's no place to specify the
> user password. How could it run with that identify if I hadn't
> specified the password? But there's no place to do it!
>
> John Bell escreveu:
> > Hi
> >
> > With a T-SQL step there is a run as user on the avanced options if you are
> > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> >
> > John
> >
> > "pedro.f.silva@.gmail.com" wrote:
> >
> > > It is using LOCALSYSTEM, I think.
> > >
> > > But I would like to use a domain account just for this job. Is it
> > > possible?
> > >
> > > Why cannot i use "run as" on job step?
> > >
> > > I thought that could be done creating a credential and a proxy but SQL
> > > server agent doesn't allow proxys on T-SQL steps.
> > >
> > > What can I do?
> > >
> > > John Bell escreveu:
> > > > Hi
> > > >
> > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > not? At a guess you are using LOCALSYSTEM? See
> > > >
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > >
> > > > John
> > > >
> > > > "pedro.f.silva@.gmail.com" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > server to connect to a remote DB.
> > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > user) everything works fone but on SQL Agent I have the following
> > > > > error:
> > > > >
> > > > > Access to the remote server is denied because the current security
> > > > > context is not trusted
> > > > >
> > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > on Job Step window is disabled. How to use the new credential?
> > > > >
> > > > > (The user exists on local and remote server).
> > > > >
> > > > > Thanks
> > > > >
> > > > >
> > >
> > >
>|||Thanks John.
That works if I want all my Jobs to run with the same user.
Since I only have one it's ok :) - SQL server agent runs with the
account I need to invoke the Stored Procedure.
It would be nice however to be able to run different T-SQL Jobs with
different users.
John Bell escreveu:
> Hi
> I would suggest that you create an account to use for a service account and
> change the service account to be that.
> John
> "pedro.f.silva@.gmail.com" wrote:
> > I had noticed that there was a "run as" user on advanced options but it
> > didn't work.
> >
> > I was expecting it (no working) since there's no place to specify the
> > user password. How could it run with that identify if I hadn't
> > specified the password? But there's no place to do it!
> >
> >
> > John Bell escreveu:
> > > Hi
> > >
> > > With a T-SQL step there is a run as user on the avanced options if you are
> > > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> > >
> > > John
> > >
> > > "pedro.f.silva@.gmail.com" wrote:
> > >
> > > > It is using LOCALSYSTEM, I think.
> > > >
> > > > But I would like to use a domain account just for this job. Is it
> > > > possible?
> > > >
> > > > Why cannot i use "run as" on job step?
> > > >
> > > > I thought that could be done creating a credential and a proxy but SQL
> > > > server agent doesn't allow proxys on T-SQL steps.
> > > >
> > > > What can I do?
> > > >
> > > > John Bell escreveu:
> > > > > Hi
> > > > >
> > > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > > not? At a guess you are using LOCALSYSTEM? See
> > > > >
> > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > > >
> > > > > John
> > > > >
> > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > > server to connect to a remote DB.
> > > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > > user) everything works fone but on SQL Agent I have the following
> > > > > > error:
> > > > > >
> > > > > > Access to the remote server is denied because the current security
> > > > > > context is not trusted
> > > > > >
> > > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > > on Job Step window is disabled. How to use the new credential?
> > > > > >
> > > > > > (The user exists on local and remote server).
> > > > > >
> > > > > > Thanks
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >|||Hi
I don't know what your job is doing or why it needs to communicate with the
remote server, but it seems to be your only option with your current
implementation. If you used SQL 2005 there are more alternatives.
John
"pedro.f.silva@.gmail.com" wrote:
> Thanks John.
> That works if I want all my Jobs to run with the same user.
> Since I only have one it's ok :) - SQL server agent runs with the
> account I need to invoke the Stored Procedure.
> It would be nice however to be able to run different T-SQL Jobs with
> different users.
>
> John Bell escreveu:
> > Hi
> >
> > I would suggest that you create an account to use for a service account and
> > change the service account to be that.
> >
> > John
> >
> > "pedro.f.silva@.gmail.com" wrote:
> >
> > > I had noticed that there was a "run as" user on advanced options but it
> > > didn't work.
> > >
> > > I was expecting it (no working) since there's no place to specify the
> > > user password. How could it run with that identify if I hadn't
> > > specified the password? But there's no place to do it!
> > >
> > >
> > > John Bell escreveu:
> > > > Hi
> > > >
> > > > With a T-SQL step there is a run as user on the avanced options if you are
> > > > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> > > >
> > > > John
> > > >
> > > > "pedro.f.silva@.gmail.com" wrote:
> > > >
> > > > > It is using LOCALSYSTEM, I think.
> > > > >
> > > > > But I would like to use a domain account just for this job. Is it
> > > > > possible?
> > > > >
> > > > > Why cannot i use "run as" on job step?
> > > > >
> > > > > I thought that could be done creating a credential and a proxy but SQL
> > > > > server agent doesn't allow proxys on T-SQL steps.
> > > > >
> > > > > What can I do?
> > > > >
> > > > > John Bell escreveu:
> > > > > > Hi
> > > > > >
> > > > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > > > not? At a guess you are using LOCALSYSTEM? See
> > > > > >
> > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > > > >
> > > > > > John
> > > > > >
> > > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > > > server to connect to a remote DB.
> > > > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > > > user) everything works fone but on SQL Agent I have the following
> > > > > > > error:
> > > > > > >
> > > > > > > Access to the remote server is denied because the current security
> > > > > > > context is not trusted
> > > > > > >
> > > > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > > > on Job Step window is disabled. How to use the new credential?
> > > > > > >
> > > > > > > (The user exists on local and remote server).
> > > > > > >
> > > > > > > Thanks
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>|||I am using SQL Server 2005 :)
Which alternatives do I have?
John Bell escreveu:
> Hi
> I don't know what your job is doing or why it needs to communicate with the
> remote server, but it seems to be your only option with your current
> implementation. If you used SQL 2005 there are more alternatives.
> John
> "pedro.f.silva@.gmail.com" wrote:
> > Thanks John.
> >
> > That works if I want all my Jobs to run with the same user.
> >
> > Since I only have one it's ok :) - SQL server agent runs with the
> > account I need to invoke the Stored Procedure.
> >
> > It would be nice however to be able to run different T-SQL Jobs with
> > different users.
> >
> >
> > John Bell escreveu:
> > > Hi
> > >
> > > I would suggest that you create an account to use for a service account and
> > > change the service account to be that.
> > >
> > > John
> > >
> > > "pedro.f.silva@.gmail.com" wrote:
> > >
> > > > I had noticed that there was a "run as" user on advanced options but it
> > > > didn't work.
> > > >
> > > > I was expecting it (no working) since there's no place to specify the
> > > > user password. How could it run with that identify if I hadn't
> > > > specified the password? But there's no place to do it!
> > > >
> > > >
> > > > John Bell escreveu:
> > > > > Hi
> > > > >
> > > > > With a T-SQL step there is a run as user on the avanced options if you are
> > > > > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> > > > >
> > > > > John
> > > > >
> > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > >
> > > > > > It is using LOCALSYSTEM, I think.
> > > > > >
> > > > > > But I would like to use a domain account just for this job. Is it
> > > > > > possible?
> > > > > >
> > > > > > Why cannot i use "run as" on job step?
> > > > > >
> > > > > > I thought that could be done creating a credential and a proxy but SQL
> > > > > > server agent doesn't allow proxys on T-SQL steps.
> > > > > >
> > > > > > What can I do?
> > > > > >
> > > > > > John Bell escreveu:
> > > > > > > Hi
> > > > > > >
> > > > > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > > > > not? At a guess you are using LOCALSYSTEM? See
> > > > > > >
> > > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > > > > >
> > > > > > > John
> > > > > > >
> > > > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > > > >
> > > > > > > > Hi,
> > > > > > > >
> > > > > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > > > > server to connect to a remote DB.
> > > > > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > > > > user) everything works fone but on SQL Agent I have the following
> > > > > > > > error:
> > > > > > > >
> > > > > > > > Access to the remote server is denied because the current security
> > > > > > > > context is not trusted
> > > > > > > >
> > > > > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > > > > on Job Step window is disabled. How to use the new credential?
> > > > > > > >
> > > > > > > > (The user exists on local and remote server).
> > > > > > > >
> > > > > > > > Thanks
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >|||Hi
Look at the using extensions to the EXECUTE command. But first read
http://www.sommarskog.se/grantperm.html
John
"pedro.f.silva@.gmail.com" wrote:
> I am using SQL Server 2005 :)
> Which alternatives do I have?
> John Bell escreveu:
> > Hi
> >
> > I don't know what your job is doing or why it needs to communicate with the
> > remote server, but it seems to be your only option with your current
> > implementation. If you used SQL 2005 there are more alternatives.
> >
> > John
> >
> > "pedro.f.silva@.gmail.com" wrote:
> >
> > > Thanks John.
> > >
> > > That works if I want all my Jobs to run with the same user.
> > >
> > > Since I only have one it's ok :) - SQL server agent runs with the
> > > account I need to invoke the Stored Procedure.
> > >
> > > It would be nice however to be able to run different T-SQL Jobs with
> > > different users.
> > >
> > >
> > > John Bell escreveu:
> > > > Hi
> > > >
> > > > I would suggest that you create an account to use for a service account and
> > > > change the service account to be that.
> > > >
> > > > John
> > > >
> > > > "pedro.f.silva@.gmail.com" wrote:
> > > >
> > > > > I had noticed that there was a "run as" user on advanced options but it
> > > > > didn't work.
> > > > >
> > > > > I was expecting it (no working) since there's no place to specify the
> > > > > user password. How could it run with that identify if I hadn't
> > > > > specified the password? But there's no place to do it!
> > > > >
> > > > >
> > > > > John Bell escreveu:
> > > > > > Hi
> > > > > >
> > > > > > With a T-SQL step there is a run as user on the avanced options if you are
> > > > > > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> > > > > >
> > > > > > John
> > > > > >
> > > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > > >
> > > > > > > It is using LOCALSYSTEM, I think.
> > > > > > >
> > > > > > > But I would like to use a domain account just for this job. Is it
> > > > > > > possible?
> > > > > > >
> > > > > > > Why cannot i use "run as" on job step?
> > > > > > >
> > > > > > > I thought that could be done creating a credential and a proxy but SQL
> > > > > > > server agent doesn't allow proxys on T-SQL steps.
> > > > > > >
> > > > > > > What can I do?
> > > > > > >
> > > > > > > John Bell escreveu:
> > > > > > > > Hi
> > > > > > > >
> > > > > > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > > > > > not? At a guess you are using LOCALSYSTEM? See
> > > > > > > >
> > > > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > > > > > >
> > > > > > > > John
> > > > > > > >
> > > > > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > > > > >
> > > > > > > > > Hi,
> > > > > > > > >
> > > > > > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > > > > > server to connect to a remote DB.
> > > > > > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > > > > > user) everything works fone but on SQL Agent I have the following
> > > > > > > > > error:
> > > > > > > > >
> > > > > > > > > Access to the remote server is denied because the current security
> > > > > > > > > context is not trusted
> > > > > > > > >
> > > > > > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > > > > > on Job Step window is disabled. How to use the new credential?
> > > > > > > > >
> > > > > > > > > (The user exists on local and remote server).
> > > > > > > > >
> > > > > > > > > Thanks
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>sql
Running Totals
If I have a table that contains the following values :
Id Amount
A1 100
B1 50
C1 75
Is it possible to write a query that would output the following :
A1 100 100
B1 50 150
C1 75 225
in other words, add the value of a column in the current row to the value
of a column in the previous row ?
Thanks
Steve
On Sat, 12 Jun 2004 11:06:33 +0100, Steve W wrote:
>If I have a table that contains the following values :
>Id Amount
>A1 100
>B1 50
>C1 75
>Is it possible to write a query that would output the following :
>A1 100 100
>B1 50 150
>C1 75 225
>in other words, add the value of a column in the current row to the value
>of a column in the previous row ?
>Thanks
>Steve
>
Hi Steve,
You'll have to define "previous" row yourself. A table in a relational
database has no fixed ordering. Assuming you want the running total based
on alphabetic ordering by the value if ID, use something like:
SELECT a.Id, a.Amount,
(SELECT SUM(b.Amount)
FROM MyTable AS b
WHERE b.Id <= a.Id) AS RunningTotal
FROM MyTable AS a
ORDER BY a.Id
(untested)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Steve,
You can try this:
use db_dbname
go
select top 10 identity(int,1,1) as num_line, a.value1 as v1, a.value2 as v2
into #temp
from table_name a
where <condition>
go
declare @.valor as float -- or decimal
set @.valor = 0
update #temp
set @.valor = @.valor + v1, v2 = @.valor
go -- the result here...
select * from #temp
go
drop table #temp
Hope this help
Rogrio
Brazil
"Steve W" <lsl@.btconnect.com> escreveu na mensagem
news:%23HfChVGUEHA.2724@.TK2MSFTNGP11.phx.gbl...
> If I have a table that contains the following values :
> Id Amount
> A1 100
> B1 50
> C1 75
> Is it possible to write a query that would output the following :
> A1 100 100
> B1 50 150
> C1 75 225
> in other words, add the value of a column in the current row to the value
> of a column in the previous row ?
> Thanks
> Steve
>
Id Amount
A1 100
B1 50
C1 75
Is it possible to write a query that would output the following :
A1 100 100
B1 50 150
C1 75 225
in other words, add the value of a column in the current row to the value
of a column in the previous row ?
Thanks
Steve
On Sat, 12 Jun 2004 11:06:33 +0100, Steve W wrote:
>If I have a table that contains the following values :
>Id Amount
>A1 100
>B1 50
>C1 75
>Is it possible to write a query that would output the following :
>A1 100 100
>B1 50 150
>C1 75 225
>in other words, add the value of a column in the current row to the value
>of a column in the previous row ?
>Thanks
>Steve
>
Hi Steve,
You'll have to define "previous" row yourself. A table in a relational
database has no fixed ordering. Assuming you want the running total based
on alphabetic ordering by the value if ID, use something like:
SELECT a.Id, a.Amount,
(SELECT SUM(b.Amount)
FROM MyTable AS b
WHERE b.Id <= a.Id) AS RunningTotal
FROM MyTable AS a
ORDER BY a.Id
(untested)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Steve,
You can try this:
use db_dbname
go
select top 10 identity(int,1,1) as num_line, a.value1 as v1, a.value2 as v2
into #temp
from table_name a
where <condition>
go
declare @.valor as float -- or decimal
set @.valor = 0
update #temp
set @.valor = @.valor + v1, v2 = @.valor
go -- the result here...
select * from #temp
go
drop table #temp
Hope this help
Rogrio
Brazil
"Steve W" <lsl@.btconnect.com> escreveu na mensagem
news:%23HfChVGUEHA.2724@.TK2MSFTNGP11.phx.gbl...
> If I have a table that contains the following values :
> Id Amount
> A1 100
> B1 50
> C1 75
> Is it possible to write a query that would output the following :
> A1 100 100
> B1 50 150
> C1 75 225
> in other words, add the value of a column in the current row to the value
> of a column in the previous row ?
> Thanks
> Steve
>
Running Totals
If I have a table that contains the following values :
Id Amount
A1 100
B1 50
C1 75
Is it possible to write a query that would output the following :
A1 100 100
B1 50 150
C1 75 225
in other words, add the value of a column in the current row to the value
of a column in the previous row ?
Thanks
SteveOn Sat, 12 Jun 2004 11:06:33 +0100, Steve W wrote:
>If I have a table that contains the following values :
>Id Amount
>A1 100
>B1 50
>C1 75
>Is it possible to write a query that would output the following :
>A1 100 100
>B1 50 150
>C1 75 225
>in other words, add the value of a column in the current row to the value
>of a column in the previous row ?
>Thanks
>Steve
>
Hi Steve,
You'll have to define "previous" row yourself. A table in a relational
database has no fixed ordering. Assuming you want the running total based
on alphabetic ordering by the value if ID, use something like:
SELECT a.Id, a.Amount,
(SELECT SUM(b.Amount)
FROM MyTable AS b
WHERE b.Id <= a.Id) AS RunningTotal
FROM MyTable AS a
ORDER BY a.Id
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Steve,
You can try this:
use db_dbname
go
select top 10 identity(int,1,1) as num_line, a.value1 as v1, a.value2 as v2
into #temp
from table_name a
where <condition>
go
declare @.valor as float -- or decimal
set @.valor = 0
update #temp
set @.valor = @.valor + v1, v2 = @.valor
go -- the result here...
select * from #temp
go
drop table #temp
Hope this help
Rogério
Brazil
"Steve W" <lsl@.btconnect.com> escreveu na mensagem
news:%23HfChVGUEHA.2724@.TK2MSFTNGP11.phx.gbl...
> If I have a table that contains the following values :
> Id Amount
> A1 100
> B1 50
> C1 75
> Is it possible to write a query that would output the following :
> A1 100 100
> B1 50 150
> C1 75 225
> in other words, add the value of a column in the current row to the value
> of a column in the previous row ?
> Thanks
> Steve
>sql
Id Amount
A1 100
B1 50
C1 75
Is it possible to write a query that would output the following :
A1 100 100
B1 50 150
C1 75 225
in other words, add the value of a column in the current row to the value
of a column in the previous row ?
Thanks
SteveOn Sat, 12 Jun 2004 11:06:33 +0100, Steve W wrote:
>If I have a table that contains the following values :
>Id Amount
>A1 100
>B1 50
>C1 75
>Is it possible to write a query that would output the following :
>A1 100 100
>B1 50 150
>C1 75 225
>in other words, add the value of a column in the current row to the value
>of a column in the previous row ?
>Thanks
>Steve
>
Hi Steve,
You'll have to define "previous" row yourself. A table in a relational
database has no fixed ordering. Assuming you want the running total based
on alphabetic ordering by the value if ID, use something like:
SELECT a.Id, a.Amount,
(SELECT SUM(b.Amount)
FROM MyTable AS b
WHERE b.Id <= a.Id) AS RunningTotal
FROM MyTable AS a
ORDER BY a.Id
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Steve,
You can try this:
use db_dbname
go
select top 10 identity(int,1,1) as num_line, a.value1 as v1, a.value2 as v2
into #temp
from table_name a
where <condition>
go
declare @.valor as float -- or decimal
set @.valor = 0
update #temp
set @.valor = @.valor + v1, v2 = @.valor
go -- the result here...
select * from #temp
go
drop table #temp
Hope this help
Rogério
Brazil
"Steve W" <lsl@.btconnect.com> escreveu na mensagem
news:%23HfChVGUEHA.2724@.TK2MSFTNGP11.phx.gbl...
> If I have a table that contains the following values :
> Id Amount
> A1 100
> B1 50
> C1 75
> Is it possible to write a query that would output the following :
> A1 100 100
> B1 50 150
> C1 75 225
> in other words, add the value of a column in the current row to the value
> of a column in the previous row ?
> Thanks
> Steve
>sql
Running Totals
If I have a table that contains the following values :
Id Amount
A1 100
B1 50
C1 75
Is it possible to write a query that would output the following :
A1 100 100
B1 50 150
C1 75 225
in other words, add the value of a column in the current row to the value
of a column in the previous row ?
Thanks
SteveOn Sat, 12 Jun 2004 11:06:33 +0100, Steve W wrote:
>If I have a table that contains the following values :
>Id Amount
>A1 100
>B1 50
>C1 75
>Is it possible to write a query that would output the following :
>A1 100 100
>B1 50 150
>C1 75 225
>in other words, add the value of a column in the current row to the value
>of a column in the previous row ?
>Thanks
>Steve
>
Hi Steve,
You'll have to define "previous" row yourself. A table in a relational
database has no fixed ordering. Assuming you want the running total based
on alphabetic ordering by the value if ID, use something like:
SELECT a.Id, a.Amount,
(SELECT SUM(b.Amount)
FROM MyTable AS b
WHERE b.Id <= a.Id) AS RunningTotal
FROM MyTable AS a
ORDER BY a.Id
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Steve,
You can try this:
use db_dbname
go
select top 10 identity(int,1,1) as num_line, a.value1 as v1, a.value2 as v2
into #temp
from table_name a
where <condition>
go
declare @.valor as float -- or decimal
set @.valor = 0
update #temp
set @.valor = @.valor + v1, v2 = @.valor
go -- the result here...
select * from #temp
go
drop table #temp
Hope this help
Rogrio
Brazil
"Steve W" <lsl@.btconnect.com> escreveu na mensagem
news:%23HfChVGUEHA.2724@.TK2MSFTNGP11.phx.gbl...
> If I have a table that contains the following values :
> Id Amount
> A1 100
> B1 50
> C1 75
> Is it possible to write a query that would output the following :
> A1 100 100
> B1 50 150
> C1 75 225
> in other words, add the value of a column in the current row to the value
> of a column in the previous row ?
> Thanks
> Steve
>sql
Id Amount
A1 100
B1 50
C1 75
Is it possible to write a query that would output the following :
A1 100 100
B1 50 150
C1 75 225
in other words, add the value of a column in the current row to the value
of a column in the previous row ?
Thanks
SteveOn Sat, 12 Jun 2004 11:06:33 +0100, Steve W wrote:
>If I have a table that contains the following values :
>Id Amount
>A1 100
>B1 50
>C1 75
>Is it possible to write a query that would output the following :
>A1 100 100
>B1 50 150
>C1 75 225
>in other words, add the value of a column in the current row to the value
>of a column in the previous row ?
>Thanks
>Steve
>
Hi Steve,
You'll have to define "previous" row yourself. A table in a relational
database has no fixed ordering. Assuming you want the running total based
on alphabetic ordering by the value if ID, use something like:
SELECT a.Id, a.Amount,
(SELECT SUM(b.Amount)
FROM MyTable AS b
WHERE b.Id <= a.Id) AS RunningTotal
FROM MyTable AS a
ORDER BY a.Id
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Steve,
You can try this:
use db_dbname
go
select top 10 identity(int,1,1) as num_line, a.value1 as v1, a.value2 as v2
into #temp
from table_name a
where <condition>
go
declare @.valor as float -- or decimal
set @.valor = 0
update #temp
set @.valor = @.valor + v1, v2 = @.valor
go -- the result here...
select * from #temp
go
drop table #temp
Hope this help
Rogrio
Brazil
"Steve W" <lsl@.btconnect.com> escreveu na mensagem
news:%23HfChVGUEHA.2724@.TK2MSFTNGP11.phx.gbl...
> If I have a table that contains the following values :
> Id Amount
> A1 100
> B1 50
> C1 75
> Is it possible to write a query that would output the following :
> A1 100 100
> B1 50 150
> C1 75 225
> in other words, add the value of a column in the current row to the value
> of a column in the previous row ?
> Thanks
> Steve
>sql
Running total with "treshold value" detection..?
Hi,
I have to create a query which evalaute, for each year, the employees who
reached a particular number of absences and when this value is reached.
I have 1 000 000 records in my absence table.
my table is like this:
DateID, EmployeeID, ActivityID, AbsenceDurationInDays
I want a list of 1 date by year by employee and by activity when the runin
absenceduration total reached 5 days.
Also, I'll evaluate the same formula but by quarter instead-of year and the
target value is 3 days.
AbsenceDurationInDays is a floating value like:
0.5 = half a day (or 4hours of work)
any guide?
any sample query anywhere?
how to setup my indexes?
I'll store the result in a table using DTS, and I'll evaluate this query 1
time a week.
thanks for your help.
Jerome.
Not sure I understand exactly what you're after but if I guess correctly
you want a query like this (assuming dateID is a smalldatetime or a
datetime):
selectdatepart(year, DateID) as [Year],
EmployeeID,
ActivityID,
sum(AbsenceDurationInDays) as RunningTotal
from AbsencesTable
group by datepart(year, DateID), EmployeeID, ActivityID
having sum(AbsenceDurationInDays) >= 5
If you want to alter the query to deal with quarters instead of years
just change the first argument from "year" to "quarter". If you want to
change the threshold just change the "5" value in the HAVING clause to
whatever you want it to be.
Cheers
Mike
Jj wrote:
> Hi,
> I have to create a query which evalaute, for each year, the employees who
> reached a particular number of absences and when this value is reached.
> I have 1 000 000 records in my absence table.
> my table is like this:
> DateID, EmployeeID, ActivityID, AbsenceDurationInDays
> I want a list of 1 date by year by employee and by activity when the runin
> absenceduration total reached 5 days.
> Also, I'll evaluate the same formula but by quarter instead-of year and the
> target value is 3 days.
> AbsenceDurationInDays is a floating value like:
> 0.5 = half a day (or 4hours of work)
> any guide?
> any sample query anywhere?
> how to setup my indexes?
> I'll store the result in a table using DTS, and I'll evaluate this query 1
> time a week.
> thanks for your help.
> Jerome.
>
|||but this query doesn't return the date when the runningtotal reached the 5
value.
For example, if an employee has 10 days of absences on the same activity
from the 1st feb. to the 10st feb.
then the expected result is the 5th feb. when the employee has cumulated 5
days of absences.
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:eXfyF4JFFHA.3780@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Not sure I understand exactly what you're after but if I guess correctly
> you want a query like this (assuming dateID is a smalldatetime or a
> datetime):
>
> select datepart(year, DateID) as [Year],
> EmployeeID,
> ActivityID,
> sum(AbsenceDurationInDays) as RunningTotal
> from AbsencesTable
> group by datepart(year, DateID), EmployeeID, ActivityID
> having sum(AbsenceDurationInDays) >= 5
> If you want to alter the query to deal with quarters instead of years just
> change the first argument from "year" to "quarter". If you want to change
> the threshold just change the "5" value in the HAVING clause to whatever
> you want it to be.
> Cheers
> Mike
>
> Jj wrote:
I have to create a query which evalaute, for each year, the employees who
reached a particular number of absences and when this value is reached.
I have 1 000 000 records in my absence table.
my table is like this:
DateID, EmployeeID, ActivityID, AbsenceDurationInDays
I want a list of 1 date by year by employee and by activity when the runin
absenceduration total reached 5 days.
Also, I'll evaluate the same formula but by quarter instead-of year and the
target value is 3 days.
AbsenceDurationInDays is a floating value like:
0.5 = half a day (or 4hours of work)
any guide?
any sample query anywhere?
how to setup my indexes?
I'll store the result in a table using DTS, and I'll evaluate this query 1
time a week.
thanks for your help.
Jerome.
Not sure I understand exactly what you're after but if I guess correctly
you want a query like this (assuming dateID is a smalldatetime or a
datetime):
selectdatepart(year, DateID) as [Year],
EmployeeID,
ActivityID,
sum(AbsenceDurationInDays) as RunningTotal
from AbsencesTable
group by datepart(year, DateID), EmployeeID, ActivityID
having sum(AbsenceDurationInDays) >= 5
If you want to alter the query to deal with quarters instead of years
just change the first argument from "year" to "quarter". If you want to
change the threshold just change the "5" value in the HAVING clause to
whatever you want it to be.
Cheers
Mike
Jj wrote:
> Hi,
> I have to create a query which evalaute, for each year, the employees who
> reached a particular number of absences and when this value is reached.
> I have 1 000 000 records in my absence table.
> my table is like this:
> DateID, EmployeeID, ActivityID, AbsenceDurationInDays
> I want a list of 1 date by year by employee and by activity when the runin
> absenceduration total reached 5 days.
> Also, I'll evaluate the same formula but by quarter instead-of year and the
> target value is 3 days.
> AbsenceDurationInDays is a floating value like:
> 0.5 = half a day (or 4hours of work)
> any guide?
> any sample query anywhere?
> how to setup my indexes?
> I'll store the result in a table using DTS, and I'll evaluate this query 1
> time a week.
> thanks for your help.
> Jerome.
>
|||but this query doesn't return the date when the runningtotal reached the 5
value.
For example, if an employee has 10 days of absences on the same activity
from the 1st feb. to the 10st feb.
then the expected result is the 5th feb. when the employee has cumulated 5
days of absences.
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:eXfyF4JFFHA.3780@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Not sure I understand exactly what you're after but if I guess correctly
> you want a query like this (assuming dateID is a smalldatetime or a
> datetime):
>
> select datepart(year, DateID) as [Year],
> EmployeeID,
> ActivityID,
> sum(AbsenceDurationInDays) as RunningTotal
> from AbsencesTable
> group by datepart(year, DateID), EmployeeID, ActivityID
> having sum(AbsenceDurationInDays) >= 5
> If you want to alter the query to deal with quarters instead of years just
> change the first argument from "year" to "quarter". If you want to change
> the threshold just change the "5" value in the HAVING clause to whatever
> you want it to be.
> Cheers
> Mike
>
> Jj wrote:
Running total with "treshold value" detection..?
Hi,
I have to create a query which evalaute, for each year, the employees who
reached a particular number of absences and when this value is reached.
I have 1 000 000 records in my absence table.
my table is like this:
DateID, EmployeeID, ActivityID, AbsenceDurationInDays
I want a list of 1 date by year by employee and by activity when the runin
absenceduration total reached 5 days.
Also, I'll evaluate the same formula but by quarter instead-of year and the
target value is 3 days.
AbsenceDurationInDays is a floating value like:
0.5 = half a day (or 4hours of work)
any guide?
any sample query anywhere?
how to setup my indexes?
I'll store the result in a table using DTS, and I'll evaluate this query 1
time a week.
thanks for your help.
Jerome.
Not sure I understand exactly what you're after but if I guess correctly
you want a query like this (assuming dateID is a smalldatetime or a
datetime):
selectdatepart(year, DateID) as [Year],
EmployeeID,
ActivityID,
sum(AbsenceDurationInDays) as RunningTotal
from AbsencesTable
group by datepart(year, DateID), EmployeeID, ActivityID
having sum(AbsenceDurationInDays) >= 5
If you want to alter the query to deal with quarters instead of years
just change the first argument from "year" to "quarter". If you want to
change the threshold just change the "5" value in the HAVING clause to
whatever you want it to be.
Cheers
Mike
Jj wrote:
> Hi,
> I have to create a query which evalaute, for each year, the employees who
> reached a particular number of absences and when this value is reached.
> I have 1 000 000 records in my absence table.
> my table is like this:
> DateID, EmployeeID, ActivityID, AbsenceDurationInDays
> I want a list of 1 date by year by employee and by activity when the runin
> absenceduration total reached 5 days.
> Also, I'll evaluate the same formula but by quarter instead-of year and the
> target value is 3 days.
> AbsenceDurationInDays is a floating value like:
> 0.5 = half a day (or 4hours of work)
> any guide?
> any sample query anywhere?
> how to setup my indexes?
> I'll store the result in a table using DTS, and I'll evaluate this query 1
> time a week.
> thanks for your help.
> Jerome.
>
|||but this query doesn't return the date when the runningtotal reached the 5
value.
For example, if an employee has 10 days of absences on the same activity
from the 1st feb. to the 10st feb.
then the expected result is the 5th feb. when the employee has cumulated 5
days of absences.
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:eXfyF4JFFHA.3780@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Not sure I understand exactly what you're after but if I guess correctly
> you want a query like this (assuming dateID is a smalldatetime or a
> datetime):
>
> select datepart(year, DateID) as [Year],
> EmployeeID,
> ActivityID,
> sum(AbsenceDurationInDays) as RunningTotal
> from AbsencesTable
> group by datepart(year, DateID), EmployeeID, ActivityID
> having sum(AbsenceDurationInDays) >= 5
> If you want to alter the query to deal with quarters instead of years just
> change the first argument from "year" to "quarter". If you want to change
> the threshold just change the "5" value in the HAVING clause to whatever
> you want it to be.
> Cheers
> Mike
>
> Jj wrote:
I have to create a query which evalaute, for each year, the employees who
reached a particular number of absences and when this value is reached.
I have 1 000 000 records in my absence table.
my table is like this:
DateID, EmployeeID, ActivityID, AbsenceDurationInDays
I want a list of 1 date by year by employee and by activity when the runin
absenceduration total reached 5 days.
Also, I'll evaluate the same formula but by quarter instead-of year and the
target value is 3 days.
AbsenceDurationInDays is a floating value like:
0.5 = half a day (or 4hours of work)
any guide?
any sample query anywhere?
how to setup my indexes?
I'll store the result in a table using DTS, and I'll evaluate this query 1
time a week.
thanks for your help.
Jerome.
Not sure I understand exactly what you're after but if I guess correctly
you want a query like this (assuming dateID is a smalldatetime or a
datetime):
selectdatepart(year, DateID) as [Year],
EmployeeID,
ActivityID,
sum(AbsenceDurationInDays) as RunningTotal
from AbsencesTable
group by datepart(year, DateID), EmployeeID, ActivityID
having sum(AbsenceDurationInDays) >= 5
If you want to alter the query to deal with quarters instead of years
just change the first argument from "year" to "quarter". If you want to
change the threshold just change the "5" value in the HAVING clause to
whatever you want it to be.
Cheers
Mike
Jj wrote:
> Hi,
> I have to create a query which evalaute, for each year, the employees who
> reached a particular number of absences and when this value is reached.
> I have 1 000 000 records in my absence table.
> my table is like this:
> DateID, EmployeeID, ActivityID, AbsenceDurationInDays
> I want a list of 1 date by year by employee and by activity when the runin
> absenceduration total reached 5 days.
> Also, I'll evaluate the same formula but by quarter instead-of year and the
> target value is 3 days.
> AbsenceDurationInDays is a floating value like:
> 0.5 = half a day (or 4hours of work)
> any guide?
> any sample query anywhere?
> how to setup my indexes?
> I'll store the result in a table using DTS, and I'll evaluate this query 1
> time a week.
> thanks for your help.
> Jerome.
>
|||but this query doesn't return the date when the runningtotal reached the 5
value.
For example, if an employee has 10 days of absences on the same activity
from the 1st feb. to the 10st feb.
then the expected result is the 5th feb. when the employee has cumulated 5
days of absences.
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:eXfyF4JFFHA.3780@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Not sure I understand exactly what you're after but if I guess correctly
> you want a query like this (assuming dateID is a smalldatetime or a
> datetime):
>
> select datepart(year, DateID) as [Year],
> EmployeeID,
> ActivityID,
> sum(AbsenceDurationInDays) as RunningTotal
> from AbsencesTable
> group by datepart(year, DateID), EmployeeID, ActivityID
> having sum(AbsenceDurationInDays) >= 5
> If you want to alter the query to deal with quarters instead of years just
> change the first argument from "year" to "quarter". If you want to change
> the threshold just change the "5" value in the HAVING clause to whatever
> you want it to be.
> Cheers
> Mike
>
> Jj wrote:
Running total with "treshold value" detection..?
Hi,
I have to create a query which evalaute, for each year, the employees who
reached a particular number of absences and when this value is reached.
I have 1 000 000 records in my absence table.
my table is like this:
DateID, EmployeeID, ActivityID, AbsenceDurationInDays
I want a list of 1 date by year by employee and by activity when the runin
absenceduration total reached 5 days.
Also, I'll evaluate the same formula but by quarter instead-of year and the
target value is 3 days.
AbsenceDurationInDays is a floating value like:
0.5 = half a day (or 4hours of work)
any guide?
any sample query anywhere?
how to setup my indexes?
I'll store the result in a table using DTS, and I'll evaluate this query 1
time a week.
thanks for your help.
Jerome.Not sure I understand exactly what you're after but if I guess correctly
you want a query like this (assuming dateID is a smalldatetime or a
datetime):
select datepart(year, DateID) as [Year],
EmployeeID,
ActivityID,
sum(AbsenceDurationInDays) as RunningTotal
from AbsencesTable
group by datepart(year, DateID), EmployeeID, ActivityID
having sum(AbsenceDurationInDays) >= 5
If you want to alter the query to deal with quarters instead of years
just change the first argument from "year" to "quarter". If you want to
change the threshold just change the "5" value in the HAVING clause to
whatever you want it to be.
Cheers
Mike
Jéjé wrote:
> Hi,
> I have to create a query which evalaute, for each year, the employees who
> reached a particular number of absences and when this value is reached.
> I have 1 000 000 records in my absence table.
> my table is like this:
> DateID, EmployeeID, ActivityID, AbsenceDurationInDays
> I want a list of 1 date by year by employee and by activity when the runin
> absenceduration total reached 5 days.
> Also, I'll evaluate the same formula but by quarter instead-of year and the
> target value is 3 days.
> AbsenceDurationInDays is a floating value like:
> 0.5 = half a day (or 4hours of work)
> any guide?
> any sample query anywhere?
> how to setup my indexes?
> I'll store the result in a table using DTS, and I'll evaluate this query 1
> time a week.
> thanks for your help.
> Jerome.
>|||but this query doesn't return the date when the runningtotal reached the 5
value.
For example, if an employee has 10 days of absences on the same activity
from the 1st feb. to the 10st feb.
then the expected result is the 5th feb. when the employee has cumulated 5
days of absences.
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:eXfyF4JFFHA.3780@.TK2MSFTNGP09.phx.gbl...
> Not sure I understand exactly what you're after but if I guess correctly
> you want a query like this (assuming dateID is a smalldatetime or a
> datetime):
>
> select datepart(year, DateID) as [Year],
> EmployeeID,
> ActivityID,
> sum(AbsenceDurationInDays) as RunningTotal
> from AbsencesTable
> group by datepart(year, DateID), EmployeeID, ActivityID
> having sum(AbsenceDurationInDays) >= 5
> If you want to alter the query to deal with quarters instead of years just
> change the first argument from "year" to "quarter". If you want to change
> the threshold just change the "5" value in the HAVING clause to whatever
> you want it to be.
> Cheers
> Mike
>
> Jéjé wrote:
>> Hi,
>> I have to create a query which evalaute, for each year, the employees who
>> reached a particular number of absences and when this value is reached.
>> I have 1 000 000 records in my absence table.
>> my table is like this:
>> DateID, EmployeeID, ActivityID, AbsenceDurationInDays
>> I want a list of 1 date by year by employee and by activity when the
>> runin absenceduration total reached 5 days.
>> Also, I'll evaluate the same formula but by quarter instead-of year and
>> the target value is 3 days.
>> AbsenceDurationInDays is a floating value like:
>> 0.5 = half a day (or 4hours of work)
>> any guide?
>> any sample query anywhere?
>> how to setup my indexes?
>> I'll store the result in a table using DTS, and I'll evaluate this query
>> 1 time a week.
>> thanks for your help.
>> Jerome.
I have to create a query which evalaute, for each year, the employees who
reached a particular number of absences and when this value is reached.
I have 1 000 000 records in my absence table.
my table is like this:
DateID, EmployeeID, ActivityID, AbsenceDurationInDays
I want a list of 1 date by year by employee and by activity when the runin
absenceduration total reached 5 days.
Also, I'll evaluate the same formula but by quarter instead-of year and the
target value is 3 days.
AbsenceDurationInDays is a floating value like:
0.5 = half a day (or 4hours of work)
any guide?
any sample query anywhere?
how to setup my indexes?
I'll store the result in a table using DTS, and I'll evaluate this query 1
time a week.
thanks for your help.
Jerome.Not sure I understand exactly what you're after but if I guess correctly
you want a query like this (assuming dateID is a smalldatetime or a
datetime):
select datepart(year, DateID) as [Year],
EmployeeID,
ActivityID,
sum(AbsenceDurationInDays) as RunningTotal
from AbsencesTable
group by datepart(year, DateID), EmployeeID, ActivityID
having sum(AbsenceDurationInDays) >= 5
If you want to alter the query to deal with quarters instead of years
just change the first argument from "year" to "quarter". If you want to
change the threshold just change the "5" value in the HAVING clause to
whatever you want it to be.
Cheers
Mike
Jéjé wrote:
> Hi,
> I have to create a query which evalaute, for each year, the employees who
> reached a particular number of absences and when this value is reached.
> I have 1 000 000 records in my absence table.
> my table is like this:
> DateID, EmployeeID, ActivityID, AbsenceDurationInDays
> I want a list of 1 date by year by employee and by activity when the runin
> absenceduration total reached 5 days.
> Also, I'll evaluate the same formula but by quarter instead-of year and the
> target value is 3 days.
> AbsenceDurationInDays is a floating value like:
> 0.5 = half a day (or 4hours of work)
> any guide?
> any sample query anywhere?
> how to setup my indexes?
> I'll store the result in a table using DTS, and I'll evaluate this query 1
> time a week.
> thanks for your help.
> Jerome.
>|||but this query doesn't return the date when the runningtotal reached the 5
value.
For example, if an employee has 10 days of absences on the same activity
from the 1st feb. to the 10st feb.
then the expected result is the 5th feb. when the employee has cumulated 5
days of absences.
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:eXfyF4JFFHA.3780@.TK2MSFTNGP09.phx.gbl...
> Not sure I understand exactly what you're after but if I guess correctly
> you want a query like this (assuming dateID is a smalldatetime or a
> datetime):
>
> select datepart(year, DateID) as [Year],
> EmployeeID,
> ActivityID,
> sum(AbsenceDurationInDays) as RunningTotal
> from AbsencesTable
> group by datepart(year, DateID), EmployeeID, ActivityID
> having sum(AbsenceDurationInDays) >= 5
> If you want to alter the query to deal with quarters instead of years just
> change the first argument from "year" to "quarter". If you want to change
> the threshold just change the "5" value in the HAVING clause to whatever
> you want it to be.
> Cheers
> Mike
>
> Jéjé wrote:
>> Hi,
>> I have to create a query which evalaute, for each year, the employees who
>> reached a particular number of absences and when this value is reached.
>> I have 1 000 000 records in my absence table.
>> my table is like this:
>> DateID, EmployeeID, ActivityID, AbsenceDurationInDays
>> I want a list of 1 date by year by employee and by activity when the
>> runin absenceduration total reached 5 days.
>> Also, I'll evaluate the same formula but by quarter instead-of year and
>> the target value is 3 days.
>> AbsenceDurationInDays is a floating value like:
>> 0.5 = half a day (or 4hours of work)
>> any guide?
>> any sample query anywhere?
>> how to setup my indexes?
>> I'll store the result in a table using DTS, and I'll evaluate this query
>> 1 time a week.
>> thanks for your help.
>> Jerome.
Running total with "treshold value" detection..?
Hi,
I have to create a query which evalaute, for each year, the employees who
reached a particular number of absences and when this value is reached.
I have 1 000 000 records in my absence table.
my table is like this:
DateID, EmployeeID, ActivityID, AbsenceDurationInDays
I want a list of 1 date by year by employee and by activity when the runin
absenceduration total reached 5 days.
Also, I'll evaluate the same formula but by quarter instead-of year and the
target value is 3 days.
AbsenceDurationInDays is a floating value like:
0.5 = half a day (or 4hours of work)
any guide?
any sample query anywhere?
how to setup my indexes?
I'll store the result in a table using DTS, and I'll evaluate this query 1
time a week.
thanks for your help.
Jerome.Not sure I understand exactly what you're after but if I guess correctly
you want a query like this (assuming dateID is a smalldatetime or a
datetime):
select datepart(year, DateID) as [Year],
EmployeeID,
ActivityID,
sum(AbsenceDurationInDays) as RunningTotal
from AbsencesTable
group by datepart(year, DateID), EmployeeID, ActivityID
having sum(AbsenceDurationInDays) >= 5
If you want to alter the query to deal with quarters instead of years
just change the first argument from "year" to "quarter". If you want to
change the threshold just change the "5" value in the HAVING clause to
whatever you want it to be.
Cheers
Mike
Jj wrote:
> Hi,
> I have to create a query which evalaute, for each year, the employees who
> reached a particular number of absences and when this value is reached.
> I have 1 000 000 records in my absence table.
> my table is like this:
> DateID, EmployeeID, ActivityID, AbsenceDurationInDays
> I want a list of 1 date by year by employee and by activity when the runin
> absenceduration total reached 5 days.
> Also, I'll evaluate the same formula but by quarter instead-of year and th
e
> target value is 3 days.
> AbsenceDurationInDays is a floating value like:
> 0.5 = half a day (or 4hours of work)
> any guide?
> any sample query anywhere?
> how to setup my indexes?
> I'll store the result in a table using DTS, and I'll evaluate this query 1
> time a week.
> thanks for your help.
> Jerome.
>|||but this query doesn't return the date when the runningtotal reached the 5
value.
For example, if an employee has 10 days of absences on the same activity
from the 1st feb. to the 10st feb.
then the expected result is the 5th feb. when the employee has cumulated 5
days of absences.
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:eXfyF4JFFHA.3780@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Not sure I understand exactly what you're after but if I guess correctly
> you want a query like this (assuming dateID is a smalldatetime or a
> datetime):
>
> select datepart(year, DateID) as [Year],
> EmployeeID,
> ActivityID,
> sum(AbsenceDurationInDays) as RunningTotal
> from AbsencesTable
> group by datepart(year, DateID), EmployeeID, ActivityID
> having sum(AbsenceDurationInDays) >= 5
> If you want to alter the query to deal with quarters instead of years just
> change the first argument from "year" to "quarter". If you want to change
> the threshold just change the "5" value in the HAVING clause to whatever
> you want it to be.
> Cheers
> Mike
>
> Jj wrote:sql
I have to create a query which evalaute, for each year, the employees who
reached a particular number of absences and when this value is reached.
I have 1 000 000 records in my absence table.
my table is like this:
DateID, EmployeeID, ActivityID, AbsenceDurationInDays
I want a list of 1 date by year by employee and by activity when the runin
absenceduration total reached 5 days.
Also, I'll evaluate the same formula but by quarter instead-of year and the
target value is 3 days.
AbsenceDurationInDays is a floating value like:
0.5 = half a day (or 4hours of work)
any guide?
any sample query anywhere?
how to setup my indexes?
I'll store the result in a table using DTS, and I'll evaluate this query 1
time a week.
thanks for your help.
Jerome.Not sure I understand exactly what you're after but if I guess correctly
you want a query like this (assuming dateID is a smalldatetime or a
datetime):
select datepart(year, DateID) as [Year],
EmployeeID,
ActivityID,
sum(AbsenceDurationInDays) as RunningTotal
from AbsencesTable
group by datepart(year, DateID), EmployeeID, ActivityID
having sum(AbsenceDurationInDays) >= 5
If you want to alter the query to deal with quarters instead of years
just change the first argument from "year" to "quarter". If you want to
change the threshold just change the "5" value in the HAVING clause to
whatever you want it to be.
Cheers
Mike
Jj wrote:
> Hi,
> I have to create a query which evalaute, for each year, the employees who
> reached a particular number of absences and when this value is reached.
> I have 1 000 000 records in my absence table.
> my table is like this:
> DateID, EmployeeID, ActivityID, AbsenceDurationInDays
> I want a list of 1 date by year by employee and by activity when the runin
> absenceduration total reached 5 days.
> Also, I'll evaluate the same formula but by quarter instead-of year and th
e
> target value is 3 days.
> AbsenceDurationInDays is a floating value like:
> 0.5 = half a day (or 4hours of work)
> any guide?
> any sample query anywhere?
> how to setup my indexes?
> I'll store the result in a table using DTS, and I'll evaluate this query 1
> time a week.
> thanks for your help.
> Jerome.
>|||but this query doesn't return the date when the runningtotal reached the 5
value.
For example, if an employee has 10 days of absences on the same activity
from the 1st feb. to the 10st feb.
then the expected result is the 5th feb. when the employee has cumulated 5
days of absences.
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:eXfyF4JFFHA.3780@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Not sure I understand exactly what you're after but if I guess correctly
> you want a query like this (assuming dateID is a smalldatetime or a
> datetime):
>
> select datepart(year, DateID) as [Year],
> EmployeeID,
> ActivityID,
> sum(AbsenceDurationInDays) as RunningTotal
> from AbsencesTable
> group by datepart(year, DateID), EmployeeID, ActivityID
> having sum(AbsenceDurationInDays) >= 5
> If you want to alter the query to deal with quarters instead of years just
> change the first argument from "year" to "quarter". If you want to change
> the threshold just change the "5" value in the HAVING clause to whatever
> you want it to be.
> Cheers
> Mike
>
> Jj wrote:sql
Running Total Query
Hi Everyone,
I have a query that returns list of accounts with their tranasaction
amount, date, and a running total for the balance. It looks like this:
SELECT acount,transDate,Amount,(SELECT SUM(AMOUNT) FROM transactions B
where B.transDate<=A.transDate and B.Account=A.Account) as Balance from
transactions A
It produces something like the following:
Account trnasDate Amount Balance
-- -- -- --
aaaaaa 01/01/2005 10 10
aaaaaa 01/01/2005 20 30 <--
bbbbbb 01/01/2005 50 50
bbbbbb 01/01/2005 -30 20 <--
I need to get the ending balance of each account for each day. So for
account 'aaaaaa' I need to get 30 and 20 for account 'bbbbbb'. In
Microsoft Access I used to use LAST(Column) method. But I do not know
now how to do it in SQL Server. For example in Access I would run the
following queery against the above query and it will do it:
SELECT transDate, LAST(Balance) from aboveQuery group by
account,transDate order by account,transDate;
Thanks
*** Sent via Developersdex http://www.examnotes.net ***Select Account, TransDate
, Sum(Amount) As DailyTotal
, Sum(Balance) As DailyBalance
From Transactions As T
Group By T.Account, T.TransDate
If you wanted the balances for a specific range of dates, simply add a Where
clause like so:
Select Account, TransDate
, Sum(Amount) As DailyTotal
, Sum(Balance) As DailyBalance
From Transactions As T
Where T.TransDate >= '20050101' And T.TransDate <= '20050131'
Group By T.Account, T.TransDate
HTH
Thomas|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Something like this:
SELECT C.Account, C.transDate, C.Balance
FROM (
SELECT A.Account, A.transDate, A.Amount,
(SELECT SUM(B.Amount)
FROM transactions B
WHERE B.transDate <= A.transDate
AND B.Account=A.Account) As Balance
FROM transactions A ) As C
WHERE C.transDate = (SELECT MAX(D.transDate)
FROM transactions D
WHERE D.Account=C.Account)
ORDER BY C.Account, C.transDate
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQmmkc4echKqOuFEgEQLuOgCgubKOpyICO2eW
tqXffbr7NZxG9cIAoPCu
pmxZbB9udscUSv55WJgi+qbN
=qdg0
--END PGP SIGNATURE--
Hussain Al-Dhaheri wrote:
> Hi Everyone,
> I have a query that returns list of accounts with their tranasaction
> amount, date, and a running total for the balance. It looks like this:
> SELECT acount,transDate,Amount,(SELECT SUM(AMOUNT) FROM transactions B
> where B.transDate<=A.transDate and B.Account=A.Account) as Balance from
> transactions A
> It produces something like the following:
> Account trnasDate Amount Balance
> -- -- -- --
> aaaaaa 01/01/2005 10 10
> aaaaaa 01/01/2005 20 30 <--
> bbbbbb 01/01/2005 50 50
> bbbbbb 01/01/2005 -30 20 <--
> I need to get the ending balance of each account for each day. So for
> account 'aaaaaa' I need to get 30 and 20 for account 'bbbbbb'. In
> Microsoft Access I used to use LAST(Column) method. But I do not know
> now how to do it in SQL Server. For example in Access I would run the
> following queery against the above query and it will do it:
> SELECT transDate, LAST(Balance) from aboveQuery group by
> account,transDate order by account,transDate;|||Thank you for the reply. The first query is a view which is used by so
many other queries in the system. And really there is no problem with it
and doing what it is supposed to do. My problem is with the second query
in which I can not choose the last record of records with the same date.
I tried MAX(transDate), but since the date are the same ('2005/01/01')
is returns the first occurance.
*** Sent via Developersdex http://www.examnotes.net ***|||Thank you for the reply. The first query is a view which is used by so
many other queries in the system. And really there is no problem with it
and doing what it is supposed to do. My problem is with the second query
in which I can not choose the last record of records with the same date.
I tried MAX(transDate), but since the date are the same ('2005/01/01')
is returns the first occurance.
*** Sent via Developersdex http://www.examnotes.net ***|||On Fri, 22 Apr 2005 21:18:36 -0700, Hussain Al-Dhaheri wrote:
>Thank you for the reply. The first query is a view which is used by so
>many other queries in the system. And really there is no problem with it
>and doing what it is supposed to do. My problem is with the second query
>in which I can not choose the last record of records with the same date.
>I tried MAX(transDate), but since the date are the same ('2005/01/01')
>is returns the first occurance.
Hi Hussain,
IN a relational database, there is no such thing as a "first" or "last"
row. A table is, by definition, an UNordered collection or rows. The
terms "first" and "last" can only have meaning in the context of an
ordering imposed on the rows - and that ordering can onlyt be based on
data that is stored in the table.
If the transDate column includes time as well as date, then you can use
the query posted by MGFoster.
If you have other data in your tables that can determine which of the
rows with the same transDate is the "last", then you can probably use a
variation on MGFoster's code - but I can't tell for sure without first
knowing WHAT data in your tables can be used to determine which row is
"last", and HOW to determine it.
If there is no way to determine "last" row using only the data in your
table, than there is no way to return the information you need. You'll
have to change the table design, to include the information needed to
identify the "last" row. And then, you'll have to manually repair the
rows already in the database that lack this information.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I agree with you that RD does not provide any mechanizem for moving
sequentially through records without any specific information(key). The
first query I mentioned is a VIEW that I can not change. I want to write
another query which gets the last 'balance' of each account for each
day. None of the supplied queries do the job. The transaction date
includes time to the millisecond. My problem is when I want to group by
transaction date I have to include 'balance' as an aggregate field which
I can not. FIRST in Access allows me to get the value of 'balance'
without beging aggregated. So, is there a way to get 'balance' with
SUM/AVG/MAX/so on?
*** Sent via Developersdex http://www.examnotes.net ***|||On Sun, 24 Apr 2005 00:41:30 -0700, Hussain Al-Dhaheri wrote:
>I agree with you that RD does not provide any mechanizem for moving
>sequentially through records without any specific information(key). The
>first query I mentioned is a VIEW that I can not change. I want to write
>another query which gets the last 'balance' of each account for each
>day. None of the supplied queries do the job. The transaction date
>includes time to the millisecond. My problem is when I want to group by
>transaction date I have to include 'balance' as an aggregate field which
>I can not. FIRST in Access allows me to get the value of 'balance'
>without beging aggregated. So, is there a way to get 'balance' with
>SUM/AVG/MAX/so on?
Hi Hussain,
If the time portion is included in the view as well, then your problem
is equivalent to the problem of extracting the total amount and closing
balance from the following table:
CREATE TABLE Trans
(Account char(6) NOT NULL,
TransDate datetime NOT NULL,
Amount decimal (10,2) NOT NULL,
Balance decimal (10,2) NOT NULL,
PRIMARY KEY (Account, TransDate)
)
go
INSERT INTO Trans (Account, TransDate, Amount, Balance)
SELECT 'aaaaaa', '2005-01-01T10:00:00.000', 10, 10 UNION ALL
SELECT 'aaaaaa', '2005-01-01T20:00:00.000', 20, 30 UNION ALL
SELECT 'bbbbbb', '2005-01-01T13:00:00.000', 50, 50 UNION ALL
SELECT 'bbbbbb', '2005-01-01T14:00:00.000',-30, 20 UNION ALL
SELECT 'bbbbbb', '2005-01-02T12:00:00.000',-40,-20
-- Added the last row to test correct result for each date
go
I think the following query will return the results you're after:
SELECT a.Account, a.TransDay,
(SELECT SUM(Amount)
FROM Trans AS b
WHERE b.Account = a.Account
AND b.TransDate >= a.TransDay
AND b.TransDate < DATEADD(day, 1, a.TransDay)) AS Amount,
a.Balance
FROM (SELECT c.Account, c.Balance,
DATEADD(day,
DATEDIFF(day, '20000101', c.TransDate),
'20000101') AS TransDay
FROM Trans AS c
WHERE c.TransDate = (SELECT MAX(d.TransDate)
FROM Trans AS d
WHERE d.Account = c.Account
AND DATEDIFF(day,
c.TransDate,
d.TransDate) = 0)) AS a
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank all for the help you provided. I used your inputs and could at
last get it to work.
*** Sent via Developersdex http://www.examnotes.net ***
I have a query that returns list of accounts with their tranasaction
amount, date, and a running total for the balance. It looks like this:
SELECT acount,transDate,Amount,(SELECT SUM(AMOUNT) FROM transactions B
where B.transDate<=A.transDate and B.Account=A.Account) as Balance from
transactions A
It produces something like the following:
Account trnasDate Amount Balance
-- -- -- --
aaaaaa 01/01/2005 10 10
aaaaaa 01/01/2005 20 30 <--
bbbbbb 01/01/2005 50 50
bbbbbb 01/01/2005 -30 20 <--
I need to get the ending balance of each account for each day. So for
account 'aaaaaa' I need to get 30 and 20 for account 'bbbbbb'. In
Microsoft Access I used to use LAST(Column) method. But I do not know
now how to do it in SQL Server. For example in Access I would run the
following queery against the above query and it will do it:
SELECT transDate, LAST(Balance) from aboveQuery group by
account,transDate order by account,transDate;
Thanks
*** Sent via Developersdex http://www.examnotes.net ***Select Account, TransDate
, Sum(Amount) As DailyTotal
, Sum(Balance) As DailyBalance
From Transactions As T
Group By T.Account, T.TransDate
If you wanted the balances for a specific range of dates, simply add a Where
clause like so:
Select Account, TransDate
, Sum(Amount) As DailyTotal
, Sum(Balance) As DailyBalance
From Transactions As T
Where T.TransDate >= '20050101' And T.TransDate <= '20050131'
Group By T.Account, T.TransDate
HTH
Thomas|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Something like this:
SELECT C.Account, C.transDate, C.Balance
FROM (
SELECT A.Account, A.transDate, A.Amount,
(SELECT SUM(B.Amount)
FROM transactions B
WHERE B.transDate <= A.transDate
AND B.Account=A.Account) As Balance
FROM transactions A ) As C
WHERE C.transDate = (SELECT MAX(D.transDate)
FROM transactions D
WHERE D.Account=C.Account)
ORDER BY C.Account, C.transDate
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQmmkc4echKqOuFEgEQLuOgCgubKOpyICO2eW
tqXffbr7NZxG9cIAoPCu
pmxZbB9udscUSv55WJgi+qbN
=qdg0
--END PGP SIGNATURE--
Hussain Al-Dhaheri wrote:
> Hi Everyone,
> I have a query that returns list of accounts with their tranasaction
> amount, date, and a running total for the balance. It looks like this:
> SELECT acount,transDate,Amount,(SELECT SUM(AMOUNT) FROM transactions B
> where B.transDate<=A.transDate and B.Account=A.Account) as Balance from
> transactions A
> It produces something like the following:
> Account trnasDate Amount Balance
> -- -- -- --
> aaaaaa 01/01/2005 10 10
> aaaaaa 01/01/2005 20 30 <--
> bbbbbb 01/01/2005 50 50
> bbbbbb 01/01/2005 -30 20 <--
> I need to get the ending balance of each account for each day. So for
> account 'aaaaaa' I need to get 30 and 20 for account 'bbbbbb'. In
> Microsoft Access I used to use LAST(Column) method. But I do not know
> now how to do it in SQL Server. For example in Access I would run the
> following queery against the above query and it will do it:
> SELECT transDate, LAST(Balance) from aboveQuery group by
> account,transDate order by account,transDate;|||Thank you for the reply. The first query is a view which is used by so
many other queries in the system. And really there is no problem with it
and doing what it is supposed to do. My problem is with the second query
in which I can not choose the last record of records with the same date.
I tried MAX(transDate), but since the date are the same ('2005/01/01')
is returns the first occurance.
*** Sent via Developersdex http://www.examnotes.net ***|||Thank you for the reply. The first query is a view which is used by so
many other queries in the system. And really there is no problem with it
and doing what it is supposed to do. My problem is with the second query
in which I can not choose the last record of records with the same date.
I tried MAX(transDate), but since the date are the same ('2005/01/01')
is returns the first occurance.
*** Sent via Developersdex http://www.examnotes.net ***|||On Fri, 22 Apr 2005 21:18:36 -0700, Hussain Al-Dhaheri wrote:
>Thank you for the reply. The first query is a view which is used by so
>many other queries in the system. And really there is no problem with it
>and doing what it is supposed to do. My problem is with the second query
>in which I can not choose the last record of records with the same date.
>I tried MAX(transDate), but since the date are the same ('2005/01/01')
>is returns the first occurance.
Hi Hussain,
IN a relational database, there is no such thing as a "first" or "last"
row. A table is, by definition, an UNordered collection or rows. The
terms "first" and "last" can only have meaning in the context of an
ordering imposed on the rows - and that ordering can onlyt be based on
data that is stored in the table.
If the transDate column includes time as well as date, then you can use
the query posted by MGFoster.
If you have other data in your tables that can determine which of the
rows with the same transDate is the "last", then you can probably use a
variation on MGFoster's code - but I can't tell for sure without first
knowing WHAT data in your tables can be used to determine which row is
"last", and HOW to determine it.
If there is no way to determine "last" row using only the data in your
table, than there is no way to return the information you need. You'll
have to change the table design, to include the information needed to
identify the "last" row. And then, you'll have to manually repair the
rows already in the database that lack this information.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I agree with you that RD does not provide any mechanizem for moving
sequentially through records without any specific information(key). The
first query I mentioned is a VIEW that I can not change. I want to write
another query which gets the last 'balance' of each account for each
day. None of the supplied queries do the job. The transaction date
includes time to the millisecond. My problem is when I want to group by
transaction date I have to include 'balance' as an aggregate field which
I can not. FIRST in Access allows me to get the value of 'balance'
without beging aggregated. So, is there a way to get 'balance' with
SUM/AVG/MAX/so on?
*** Sent via Developersdex http://www.examnotes.net ***|||On Sun, 24 Apr 2005 00:41:30 -0700, Hussain Al-Dhaheri wrote:
>I agree with you that RD does not provide any mechanizem for moving
>sequentially through records without any specific information(key). The
>first query I mentioned is a VIEW that I can not change. I want to write
>another query which gets the last 'balance' of each account for each
>day. None of the supplied queries do the job. The transaction date
>includes time to the millisecond. My problem is when I want to group by
>transaction date I have to include 'balance' as an aggregate field which
>I can not. FIRST in Access allows me to get the value of 'balance'
>without beging aggregated. So, is there a way to get 'balance' with
>SUM/AVG/MAX/so on?
Hi Hussain,
If the time portion is included in the view as well, then your problem
is equivalent to the problem of extracting the total amount and closing
balance from the following table:
CREATE TABLE Trans
(Account char(6) NOT NULL,
TransDate datetime NOT NULL,
Amount decimal (10,2) NOT NULL,
Balance decimal (10,2) NOT NULL,
PRIMARY KEY (Account, TransDate)
)
go
INSERT INTO Trans (Account, TransDate, Amount, Balance)
SELECT 'aaaaaa', '2005-01-01T10:00:00.000', 10, 10 UNION ALL
SELECT 'aaaaaa', '2005-01-01T20:00:00.000', 20, 30 UNION ALL
SELECT 'bbbbbb', '2005-01-01T13:00:00.000', 50, 50 UNION ALL
SELECT 'bbbbbb', '2005-01-01T14:00:00.000',-30, 20 UNION ALL
SELECT 'bbbbbb', '2005-01-02T12:00:00.000',-40,-20
-- Added the last row to test correct result for each date
go
I think the following query will return the results you're after:
SELECT a.Account, a.TransDay,
(SELECT SUM(Amount)
FROM Trans AS b
WHERE b.Account = a.Account
AND b.TransDate >= a.TransDay
AND b.TransDate < DATEADD(day, 1, a.TransDay)) AS Amount,
a.Balance
FROM (SELECT c.Account, c.Balance,
DATEADD(day,
DATEDIFF(day, '20000101', c.TransDate),
'20000101') AS TransDay
FROM Trans AS c
WHERE c.TransDate = (SELECT MAX(d.TransDate)
FROM Trans AS d
WHERE d.Account = c.Account
AND DATEDIFF(day,
c.TransDate,
d.TransDate) = 0)) AS a
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank all for the help you provided. I used your inputs and could at
last get it to work.
*** Sent via Developersdex http://www.examnotes.net ***
running total from stored proc
i need to return the number of rows from a query as something
eg select ID,column from table where column = something
then return ID,column,expr1 (which is the total of records returned)
is this possible ?
my query looks like
CREATE PROCEDURE CSR_Performance
@.csr varchar(75),
@.From varchar(50),
@.To Varchar(50)
AS
DECLARE @.fromdate datetime
select @.fromdate=convert(datetime, @.from)
DECLARE @.todate datetime
select @.todate=convert(datetime, @.to)
SELECT DateLeadReceived , LeadLoggedBy,Type
FROM Lead
WHERE (DateLeadReceived BETWEEN @.fromdate AND @.todate) AND
(LeadLoggedBy = @.csr) AND (Type = 'Telephone')mark
You can look at an OUTPUT parameters in stored procedure (see BOL)
CREATE PROCEDURE CSR_Performance
@.csr varchar(75),
@.From varchar(50),
@.To Varchar(50)
AS
DECLARE @.ret INT
DECLARE @.fromdate datetime
select @.fromdate=convert(datetime, @.from)
DECLARE @.todate datetime
select @.todate=convert(datetime, @.to)
SELECT @.ret=COUNT(*), DateLeadReceived , LeadLoggedBy,Type
FROM Lead
WHERE (DateLeadReceived BETWEEN @.fromdate AND @.todate) AND
(LeadLoggedBy = @.csr) AND (Type = 'Telephone')
GROUP BY DateLeadReceived ,LeadLoggedBy,Type
RETURN @.ret
"mark" <mark@.remove.com> wrote in message
news:3t-dnSGix75OI_HfRVn-pQ@.giganews.com...
> i need to return the number of rows from a query as something
> eg select ID,column from table where column = something
> then return ID,column,expr1 (which is the total of records returned)
> is this possible ?
> my query looks like
> CREATE PROCEDURE CSR_Performance
> @.csr varchar(75),
> @.From varchar(50),
> @.To Varchar(50)
> AS
> DECLARE @.fromdate datetime
> select @.fromdate=convert(datetime, @.from)
> DECLARE @.todate datetime
> select @.todate=convert(datetime, @.to)
> SELECT DateLeadReceived , LeadLoggedBy,Type
> FROM Lead
> WHERE (DateLeadReceived BETWEEN @.fromdate AND @.todate) AND
> (LeadLoggedBy = @.csr) AND (Type = 'Telephone')
>
>
>|||Uri
What's the difference between using return @.var or using output paramter ?
"Uri Dimant" wrote:
> mark
> You can look at an OUTPUT parameters in stored procedure (see BOL)
>
> CREATE PROCEDURE CSR_Performance
> @.csr varchar(75),
> @.From varchar(50),
> @.To Varchar(50)
> AS
> DECLARE @.ret INT
> DECLARE @.fromdate datetime
> select @.fromdate=convert(datetime, @.from)
> DECLARE @.todate datetime
> select @.todate=convert(datetime, @.to)
> SELECT @.ret=COUNT(*), DateLeadReceived , LeadLoggedBy,Type
> FROM Lead
> WHERE (DateLeadReceived BETWEEN @.fromdate AND @.todate) AND
> (LeadLoggedBy = @.csr) AND (Type = 'Telephone')
> GROUP BY DateLeadReceived ,LeadLoggedBy,Type
> RETURN @.ret
>
> "mark" <mark@.remove.com> wrote in message
> news:3t-dnSGix75OI_HfRVn-pQ@.giganews.com...
>
>|||Well , a difference as you said RETURN (unlike OUTPUT) statement terminates
the batch and none of the statements within SP are executed. RETURN
statement is usually used to reterun an error number or @.@.idenitity value.
"Mal .mullerjannie@.hotmail.com>" <<removethis> wrote in message
news:F94557D7-2AAA-4B8A-8942-49821BA120E9@.microsoft.com...
> Uri
> What's the difference between using return @.var or using output paramter ?
>
> "Uri Dimant" wrote:
>|||One is a return value and the other is output parameters. For a return value
, it can only be int and
most of us only use this to communicate success/error. You can have several
output parameters and
they can be of almost any datatype.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mal .mullerjannie@.hotmail.com>" <<removethis> wrote in message
news:F94557D7-2AAA-4B8A-8942-49821BA120E9@.microsoft.com...
> Uri
> What's the difference between using return @.var or using output paramter ?
>
> "Uri Dimant" wrote:
>|||that code didnt seem to work, but ive solved the problem like this
thanks
mark
CREATE PROCEDURE CSR_Performance
@.csr varchar(75),
@.From varchar(50),
@.To Varchar(50)
AS
declare @.total int
DECLARE @.fromdate datetime
select @.fromdate=convert(datetime, @.from)
DECLARE @.todate datetime
select @.todate=convert(datetime, @.to)
SELECT DateLeadReceived , LeadLoggedBy,Type
FROM Lead
WHERE (DateLeadReceived BETWEEN @.fromdate AND @.todate) AND
(LeadLoggedBy = @.csr) AND (Type = 'Telephone')
select @.total=@.@.rowcount
select @.total as totalcolumn
GO
eg select ID,column from table where column = something
then return ID,column,expr1 (which is the total of records returned)
is this possible ?
my query looks like
CREATE PROCEDURE CSR_Performance
@.csr varchar(75),
@.From varchar(50),
@.To Varchar(50)
AS
DECLARE @.fromdate datetime
select @.fromdate=convert(datetime, @.from)
DECLARE @.todate datetime
select @.todate=convert(datetime, @.to)
SELECT DateLeadReceived , LeadLoggedBy,Type
FROM Lead
WHERE (DateLeadReceived BETWEEN @.fromdate AND @.todate) AND
(LeadLoggedBy = @.csr) AND (Type = 'Telephone')mark
You can look at an OUTPUT parameters in stored procedure (see BOL)
CREATE PROCEDURE CSR_Performance
@.csr varchar(75),
@.From varchar(50),
@.To Varchar(50)
AS
DECLARE @.ret INT
DECLARE @.fromdate datetime
select @.fromdate=convert(datetime, @.from)
DECLARE @.todate datetime
select @.todate=convert(datetime, @.to)
SELECT @.ret=COUNT(*), DateLeadReceived , LeadLoggedBy,Type
FROM Lead
WHERE (DateLeadReceived BETWEEN @.fromdate AND @.todate) AND
(LeadLoggedBy = @.csr) AND (Type = 'Telephone')
GROUP BY DateLeadReceived ,LeadLoggedBy,Type
RETURN @.ret
"mark" <mark@.remove.com> wrote in message
news:3t-dnSGix75OI_HfRVn-pQ@.giganews.com...
> i need to return the number of rows from a query as something
> eg select ID,column from table where column = something
> then return ID,column,expr1 (which is the total of records returned)
> is this possible ?
> my query looks like
> CREATE PROCEDURE CSR_Performance
> @.csr varchar(75),
> @.From varchar(50),
> @.To Varchar(50)
> AS
> DECLARE @.fromdate datetime
> select @.fromdate=convert(datetime, @.from)
> DECLARE @.todate datetime
> select @.todate=convert(datetime, @.to)
> SELECT DateLeadReceived , LeadLoggedBy,Type
> FROM Lead
> WHERE (DateLeadReceived BETWEEN @.fromdate AND @.todate) AND
> (LeadLoggedBy = @.csr) AND (Type = 'Telephone')
>
>
>|||Uri
What's the difference between using return @.var or using output paramter ?
"Uri Dimant" wrote:
> mark
> You can look at an OUTPUT parameters in stored procedure (see BOL)
>
> CREATE PROCEDURE CSR_Performance
> @.csr varchar(75),
> @.From varchar(50),
> @.To Varchar(50)
> AS
> DECLARE @.ret INT
> DECLARE @.fromdate datetime
> select @.fromdate=convert(datetime, @.from)
> DECLARE @.todate datetime
> select @.todate=convert(datetime, @.to)
> SELECT @.ret=COUNT(*), DateLeadReceived , LeadLoggedBy,Type
> FROM Lead
> WHERE (DateLeadReceived BETWEEN @.fromdate AND @.todate) AND
> (LeadLoggedBy = @.csr) AND (Type = 'Telephone')
> GROUP BY DateLeadReceived ,LeadLoggedBy,Type
> RETURN @.ret
>
> "mark" <mark@.remove.com> wrote in message
> news:3t-dnSGix75OI_HfRVn-pQ@.giganews.com...
>
>|||Well , a difference as you said RETURN (unlike OUTPUT) statement terminates
the batch and none of the statements within SP are executed. RETURN
statement is usually used to reterun an error number or @.@.idenitity value.
"Mal .mullerjannie@.hotmail.com>" <<removethis> wrote in message
news:F94557D7-2AAA-4B8A-8942-49821BA120E9@.microsoft.com...
> Uri
> What's the difference between using return @.var or using output paramter ?
>
> "Uri Dimant" wrote:
>|||One is a return value and the other is output parameters. For a return value
, it can only be int and
most of us only use this to communicate success/error. You can have several
output parameters and
they can be of almost any datatype.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mal .mullerjannie@.hotmail.com>" <<removethis> wrote in message
news:F94557D7-2AAA-4B8A-8942-49821BA120E9@.microsoft.com...
> Uri
> What's the difference between using return @.var or using output paramter ?
>
> "Uri Dimant" wrote:
>|||that code didnt seem to work, but ive solved the problem like this
thanks
mark
CREATE PROCEDURE CSR_Performance
@.csr varchar(75),
@.From varchar(50),
@.To Varchar(50)
AS
declare @.total int
DECLARE @.fromdate datetime
select @.fromdate=convert(datetime, @.from)
DECLARE @.todate datetime
select @.todate=convert(datetime, @.to)
SELECT DateLeadReceived , LeadLoggedBy,Type
FROM Lead
WHERE (DateLeadReceived BETWEEN @.fromdate AND @.todate) AND
(LeadLoggedBy = @.csr) AND (Type = 'Telephone')
select @.total=@.@.rowcount
select @.total as totalcolumn
GO
Friday, March 23, 2012
Running the same subquery multiple times
Hi,
I was wondering if this can be done...
I have a complex query which has to do a few calculations. I'm using subqueries to do the calcs, but most of the calcs have to use a value gotten from the first subquery. I don't want to have to type the subquery out each time, so is there a way of assigning it to a variable or putting it in a UDF or SP?
E.g.
I have a table with 2 cols - amount, date.
SELECT total_amount, closing_amount,
FROM table1
GROUP BY month(date)
Total amount is the SUM(amount) for the month.
Closing amount is the Total Amount plus the amounts for the current month with a few extra calcs.
As I have to use SUM(amount) in the second subquery, is there a way I can do it without having to type hte subquery out again?
This is only a basic example, what I'm trying to do will invovle a lot more calcultions.
Hope someone can help,
Thanks,
Stuartuse a VIEW
:)|||--Run this in a query analyzer or sp
Declare @.Total_Amount numeric (9)
Declare @.closing_amount numeric (9)
Set @.Total_Amount = (SELECT sum(total_amount) FROM table1 GROUP BY month(date))
--Make sure you only return 1 record here
Set @.closing_Amount = (SELECT closing_amount FROM table1 Where Column_Value = my_Value GROUP BY month(date))
print @.Total_Amount
print @.closing_Amount|||db0, i don't believe you can assign a column of values to a variable|||What I did eventually was use a whole load of user-defined functions. Some of them get called multiple times. but it seems quick enough.|||and the reason you chose not to use a VIEW was... ?
I was wondering if this can be done...
I have a complex query which has to do a few calculations. I'm using subqueries to do the calcs, but most of the calcs have to use a value gotten from the first subquery. I don't want to have to type the subquery out each time, so is there a way of assigning it to a variable or putting it in a UDF or SP?
E.g.
I have a table with 2 cols - amount, date.
SELECT total_amount, closing_amount,
FROM table1
GROUP BY month(date)
Total amount is the SUM(amount) for the month.
Closing amount is the Total Amount plus the amounts for the current month with a few extra calcs.
As I have to use SUM(amount) in the second subquery, is there a way I can do it without having to type hte subquery out again?
This is only a basic example, what I'm trying to do will invovle a lot more calcultions.
Hope someone can help,
Thanks,
Stuartuse a VIEW
:)|||--Run this in a query analyzer or sp
Declare @.Total_Amount numeric (9)
Declare @.closing_amount numeric (9)
Set @.Total_Amount = (SELECT sum(total_amount) FROM table1 GROUP BY month(date))
--Make sure you only return 1 record here
Set @.closing_Amount = (SELECT closing_amount FROM table1 Where Column_Value = my_Value GROUP BY month(date))
print @.Total_Amount
print @.closing_Amount|||db0, i don't believe you can assign a column of values to a variable|||What I did eventually was use a whole load of user-defined functions. Some of them get called multiple times. but it seems quick enough.|||and the reason you chose not to use a VIEW was... ?
Running the same query on multiple servers
Hi, I periodically run the same query on multiple servers. Currently I
do this via query analyzer and would like to cut down the time it takes
to switch servers.
Is there a tool I can configure multiple connections on and run the same
query on selected servers.
Thanks
Dilan
create linked servers and run the queries as
select ... from servername.databasename.objectname
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT Community Leader]
SQL Server Technologist
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Dilan A" <dilan.a@.youtelus.net> wrote in message
news:up7hf.129920$S4.96172@.edtnps84...
> Hi, I periodically run the same query on multiple servers. Currently I do
> this via query analyzer and would like to cut down the time it takes to
> switch servers.
> Is there a tool I can configure multiple connections on and run the same
> query on selected servers.
> Thanks
> Dilan
|||Dandy Weyn [Dandyman] wrote:
> create linked servers and run the queries as
> select ... from servername.databasename.objectname
>
Thanks. I neglected to mention that the statements are always almost
updates to existing stored procedures and are supplied by our vendor as
patches. I received them as attachments in emails and have to save them
and then run them on about 8 servers.
Needless to say I am looking to click and go...
|||You can either use DMO or oSql to do this pretty easily. Both methods allow
you to easily connect to another server and issue the same commands. You do
have to write some code but it should only be a few lines and a google
search will most likely find some examples.
Andrew J. Kelly SQL MVP
"Dilan A" <dilan.a@.youtelus.net> wrote in message
news:up7hf.129920$S4.96172@.edtnps84...
> Hi, I periodically run the same query on multiple servers. Currently I do
> this via query analyzer and would like to cut down the time it takes to
> switch servers.
> Is there a tool I can configure multiple connections on and run the same
> query on selected servers.
> Thanks
> Dilan
|||I tend to just use a batch file e.g. create 3 folders Output,Servers,Source
under a main folder (in my example it's DBADeploy). Create a batchfile as
below called deploy.bat in C:\DBADeploy
@.echo off
FOR /F %%f in (C:\DBADeploy\Servers\servers.txt) do
C:\DBADeploy\Source\Update.bat %%f
In the Servers folder create a file called servers.txt which is a list of
the target servers
In the Source folder create a file called update.sql with you code in it
(including USE database statements) and a batch file called Update.bat with
the contents below
@.echo off
set server=%1
set outputtemp=%1
set outputtemp=%outputtemp:\=_%
set outputfile="C:\DBADeploy\Output\%outputtemp%.txt"
osql -S %server% -d master -n -E -w 200 -h-1 -l 15 -i
"C:\DBADeploy\Source\update.sql" -o %outputfile%
echo Completed %1
Once that's setup then anything you want to deploy you can just stick in
update.sql and run deploy.bat and you're done. You can check the output for
each server in the Output folder.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dilan A" <dilan.a@.youtelus.net> wrote in message
news:up7hf.129920$S4.96172@.edtnps84...
> Hi, I periodically run the same query on multiple servers. Currently I do
> this via query analyzer and would like to cut down the time it takes to
> switch servers.
> Is there a tool I can configure multiple connections on and run the same
> query on selected servers.
> Thanks
> Dilan
do this via query analyzer and would like to cut down the time it takes
to switch servers.
Is there a tool I can configure multiple connections on and run the same
query on selected servers.
Thanks
Dilan
create linked servers and run the queries as
select ... from servername.databasename.objectname
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT Community Leader]
SQL Server Technologist
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Dilan A" <dilan.a@.youtelus.net> wrote in message
news:up7hf.129920$S4.96172@.edtnps84...
> Hi, I periodically run the same query on multiple servers. Currently I do
> this via query analyzer and would like to cut down the time it takes to
> switch servers.
> Is there a tool I can configure multiple connections on and run the same
> query on selected servers.
> Thanks
> Dilan
|||Dandy Weyn [Dandyman] wrote:
> create linked servers and run the queries as
> select ... from servername.databasename.objectname
>
Thanks. I neglected to mention that the statements are always almost
updates to existing stored procedures and are supplied by our vendor as
patches. I received them as attachments in emails and have to save them
and then run them on about 8 servers.
Needless to say I am looking to click and go...|||You can either use DMO or oSql to do this pretty easily. Both methods allow
you to easily connect to another server and issue the same commands. You do
have to write some code but it should only be a few lines and a google
search will most likely find some examples.
Andrew J. Kelly SQL MVP
"Dilan A" <dilan.a@.youtelus.net> wrote in message
news:up7hf.129920$S4.96172@.edtnps84...
> Hi, I periodically run the same query on multiple servers. Currently I do
> this via query analyzer and would like to cut down the time it takes to
> switch servers.
> Is there a tool I can configure multiple connections on and run the same
> query on selected servers.
> Thanks
> Dilan
|||I tend to just use a batch file e.g. create 3 folders Output,Servers,Source
under a main folder (in my example it's DBADeploy). Create a batchfile as
below called deploy.bat in C:\DBADeploy
@.echo off
FOR /F %%f in (C:\DBADeploy\Servers\servers.txt) do
C:\DBADeploy\Source\Update.bat %%f
In the Servers folder create a file called servers.txt which is a list of
the target servers
In the Source folder create a file called update.sql with you code in it
(including USE database statements) and a batch file called Update.bat with
the contents below
@.echo off
set server=%1
set outputtemp=%1
set outputtemp=%outputtemp:\=_%
set outputfile="C:\DBADeploy\Output\%outputtemp%.txt"
osql -S %server% -d master -n -E -w 200 -h-1 -l 15 -i
"C:\DBADeploy\Source\update.sql" -o %outputfile%
echo Completed %1
Once that's setup then anything you want to deploy you can just stick in
update.sql and run deploy.bat and you're done. You can check the output for
each server in the Output folder.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dilan A" <dilan.a@.youtelus.net> wrote in message
news:up7hf.129920$S4.96172@.edtnps84...
> Hi, I periodically run the same query on multiple servers. Currently I do
> this via query analyzer and would like to cut down the time it takes to
> switch servers.
> Is there a tool I can configure multiple connections on and run the same
> query on selected servers.
> Thanks
> Dilan
Running the exec command on the second machine...
I have a query to execute. The query is :-
exec master.dbo.xp_fileexist @.nFilePath
This i can execute on the current machine.
But how do i run this one on another machine.
I took the machinename as a parameter and executed like :-
exec @.nMachinename.master.dbo.xp_fileexist @.nFilePath
But the exec command is not taking this...
Giving me errors.
Now how can i give the second machines name as a paramter and run dynamically??
Thanks,
Sandu.not sure about this but try using linked server.|||Okies..
I got it...
I have to set the value into a variable of the above mentioned query and then execute the variable...
Anyway thanks...
--Sandu.|||try
declare @.sql nvarchar(1000)
select @.sql = 'exec ' + @.nMachinename + '.master.dbo.xp_fileexist ''' + @.nFilePath + ''''
exec (@.sql)
exec master.dbo.xp_fileexist @.nFilePath
This i can execute on the current machine.
But how do i run this one on another machine.
I took the machinename as a parameter and executed like :-
exec @.nMachinename.master.dbo.xp_fileexist @.nFilePath
But the exec command is not taking this...
Giving me errors.
Now how can i give the second machines name as a paramter and run dynamically??
Thanks,
Sandu.not sure about this but try using linked server.|||Okies..
I got it...
I have to set the value into a variable of the above mentioned query and then execute the variable...
Anyway thanks...
--Sandu.|||try
declare @.sql nvarchar(1000)
select @.sql = 'exec ' + @.nMachinename + '.master.dbo.xp_fileexist ''' + @.nFilePath + ''''
exec (@.sql)
Running Sum Query ?
Following query is calculating running sum and running average of an Invento
ry Item, positive quantities are receipts and negative quantities are issued
, last two columns are running sum of quantity and amount column.
While running sum of quantity is ok, but Amt running sum is not correct, any
idea what am I missing ?
My table data is as under:-
Please note, negative quantites have no rates and no amounts, this will be c
alculated on average basis in above query.
Sno field is just an auto numbering field for query purpose.
PRITC QTY RATE PRAMT SNO
-- -- -- -- --
1111-034 200 1494.5 298900 1
1111-034 218 1559 339862 2
1111-034 -150 3
1111-034 -5 4
1111-034 -200 5
1111-034 -5 6
1111-034 -50 7
Best Regards,
Luqman
select a.sno,a.qty,
case when a.qty>0 then a.rate
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0) end rate,
case when a.qty>0 then a.pramt
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
end Amt,sum(b.qty),
sum(case when a.qty>0 then a.pramt
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
end) Amt
from test a, test b
where a.sno>=b.sno
group by a.sno,a.qty,a.pramt,a.rate
SNO QTY RATE AMT SUM(B.QTY) AMT
-- -- -- -- --
--
1 200 1494.5 298900 200 298900
2 218 1559 339862 418 679724
3 -150 1528.13876 -229220.81 268 -687662.44
4 -5 1528.13876 -7640.6938 263 -30562.775
5 -200 1528.13876 -305627.75 63 -1528138.8
6 -5 1528.13876 -7640.6938 58 -45844.163
7 -50 1528.13876 -76406.938 8 -534848.56Hello, Luqman
Please post DDL (as "CREATE TABLE" statements) and sample data (as
"INSERT INTO ... VALUES ..." statements), like this:
CREATE TABLE test (
PRITC varchar(10) NOT NULL,
QTY numeric(10,3) NOT NULL,
RATE numeric(18,4) NULL,
PRAMT numeric(18,4) NULL,
SNO int IDENTITY PRIMARY KEY,
CHECK (PRAMT=QTY*RATE)
)
INSERT INTO test VALUES ('1111-034',200,1494.5,298900)
INSERT INTO test VALUES ('1111-034',218,1559,339862)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-150)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-5)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-200)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-5)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-50)
The above DDL is just a guess; please correct it if necessary.
Your current query returns the following error: "Cannot perform an
aggregate function on an expression containing an aggregate or a
subquery." The reason is obvious: you cannot use SUM() for an
expression that contains a subquery (for the last column in your
query).
You probably want something like this:
SELECT a.sno, a.qty, a.rate, a.pramt, (
SELECT SUM(qty) FROM test b
WHERE b.sno<=a.sno
) as qty_sum, (
SELECT SUM(pramt) FROM test b
WHERE b.sno<=a.sno AND qty>0
) as pramt_sum, (
SELECT SUM(pramt)/SUM(qty) FROM test b
WHERE b.sno<=a.sno AND qty>0
) as avg_rate
FROM test a
If you want something else, please post the expected results.
Razvan|||The following query will output something like this.
select a.sno,a.qty,
case when a.qty>0 then a.rate
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0) end rate,
case when a.qty>0 then a.pramt
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
end Amt,sum(b.qty),
case when a.qty>0 then a.pramt
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
end Bal
from test a, test b
where a.sno>=b.sno
group by a.sno,a.qty,a.pramt,a.rate
sno qty rate Amount qtyTotal AmtTotal
1 200.000 1494.500000 298900.000000 200.000 298900.000000
2 218.000 1559.000000 339862.000000 418.000 339862.000000
3 -150.000 1528.138755 -229220.813250 268.000 -229220.813250
4 -5.000 1528.138755 -7640.693775 263.000 -7640.693775
5 -200.000 1528.138755 -305627.751000 63.000 -305627.751000
6 -5.000 1528.138755 -7640.693775 58.000 -7640.693775
7 -50.000 1528.138755 -76406.937750 8.000 -76406.937750
Currently the Qtytotal is ok, but AmtTotal is not ok, I want the correct
running sum of Amount Field in AmtTotal.
The correct display should be:
sno qty rate Amount qtyTotal AmtTotal
1 200.000 1494.500000 298900.000000 200.000 298900.000000
2 218.000 1559.000000 339862.000000 418.000 638762.000000
3 -150.000 1528.138755 -229220.813250 268.000 409541.19
4 -5.000 1528.138755 -7640.693775 263.000 401900.4962
5 -200.000 1528.138755 -305627.751000 63.000 96272.7462
6 -5.000 1528.138755 -7640.693775 58.000 88632.0524
7 -50.000 1528.138755 -76406.937750 8.000 12225.1144
Any idea please ?
Best Regards,
Luqman
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1149586142.846012.210890@.f6g2000cwb.googlegroups.com...
> Hello, Luqman
> Please post DDL (as "CREATE TABLE" statements) and sample data (as
> "INSERT INTO ... VALUES ..." statements), like this:
> CREATE TABLE test (
> PRITC varchar(10) NOT NULL,
> QTY numeric(10,3) NOT NULL,
> RATE numeric(18,4) NULL,
> PRAMT numeric(18,4) NULL,
> SNO int IDENTITY PRIMARY KEY,
> CHECK (PRAMT=QTY*RATE)
> )
>
> INSERT INTO test VALUES ('1111-034',200,1494.5,298900)
> INSERT INTO test VALUES ('1111-034',218,1559,339862)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-150)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-5)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-200)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-5)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-50)
> The above DDL is just a guess; please correct it if necessary.
> Your current query returns the following error: "Cannot perform an
> aggregate function on an expression containing an aggregate or a
> subquery." The reason is obvious: you cannot use SUM() for an
> expression that contains a subquery (for the last column in your
> query).
> You probably want something like this:
> SELECT a.sno, a.qty, a.rate, a.pramt, (
> SELECT SUM(qty) FROM test b
> WHERE b.sno<=a.sno
> ) as qty_sum, (
> SELECT SUM(pramt) FROM test b
> WHERE b.sno<=a.sno AND qty>0
> ) as pramt_sum, (
> SELECT SUM(pramt)/SUM(qty) FROM test b
> WHERE b.sno<=a.sno AND qty>0
> ) as avg_rate
> FROM test a
> If you want something else, please post the expected results.
> Razvan
>|||Luqman wrote:
> The correct display should be: [...]
OK, then let's save the SELECT statement in my previous post as a view:
create view myview as
SELECT a.sno, a.qty,
ISNULL(a.rate, (
SELECT SUM(pramt)/SUM(qty) FROM test b
WHERE b.sno<=a.sno AND qty>0
)) as rate, (
SELECT SUM(qty) FROM test b
WHERE b.sno<=a.sno
) as qtyTotal, (
SELECT SUM(pramt) FROM test b
WHERE b.sno<=a.sno AND qty>0
) as pramt_sum
FROM test a
And now we can use the following query:
SELECT x.sno, x.qty, x.rate, x.qty*x.rate as Amount,
x.qtyTotal, x.pramt_sum+isnull((
select sum(qty*rate) from myview y
where y.sno<=x.sno and y.qty<0
),0) as AmtTotal
FROM myview x
The above query returns the expected results (I'm not sure what is the
number of decimals that you want; the number of decimals in the result
changes according to the data types in the table).
However, to be sure that the above query meets all your requirements,
you should also post some sample data where there is a positive value
AFTER some negative values and expected results for this new sample
data.
Razvan|||Hi,
Did you work on my queries, please ?
Best Regards,
Luqman
"Luqman" <pearlsoft@.cyber.net.pk> wrote in message
news:%232Kn49ZiGHA.3884@.TK2MSFTNGP04.phx.gbl...
> The following query will output something like this.
> select a.sno,a.qty,
> case when a.qty>0 then a.rate
> else
> (select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0) end rate,
> case when a.qty>0 then a.pramt
> else
> (select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
> end Amt,sum(b.qty),
> case when a.qty>0 then a.pramt
> else
> (select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
> end Bal
> from test a, test b
> where a.sno>=b.sno
> group by a.sno,a.qty,a.pramt,a.rate
> sno qty rate Amount qtyTotal
> AmtTotal
> 1 200.000 1494.500000 298900.000000 200.000 298900.000000
> 2 218.000 1559.000000 339862.000000 418.000 339862.000000
> 3 -150.000 1528.138755 -229220.813250 268.000 -229220.813250
> 4 -5.000 1528.138755 -7640.693775 263.000 -7640.693775
> 5 -200.000 1528.138755 -305627.751000 63.000 -305627.751000
> 6 -5.000 1528.138755 -7640.693775 58.000 -7640.693775
> 7 -50.000 1528.138755 -76406.937750 8.000 -76406.937750
> Currently the Qtytotal is ok, but AmtTotal is not ok, I want the correct
> running sum of Amount Field in AmtTotal.
> The correct display should be:
> sno qty rate Amount qtyTotal
> AmtTotal
> 1 200.000 1494.500000 298900.000000 200.000 298900.000000
> 2 218.000 1559.000000 339862.000000 418.000 638762.000000
> 3 -150.000 1528.138755 -229220.813250 268.000 409541.19
> 4 -5.000 1528.138755 -7640.693775 263.000 401900.4962
> 5 -200.000 1528.138755 -305627.751000 63.000 96272.7462
> 6 -5.000 1528.138755 -7640.693775 58.000 88632.0524
> 7 -50.000 1528.138755 -76406.937750 8.000 12225.1144
> Any idea please ?
> Best Regards,
> Luqman
>
> "Razvan Socol" <rsocol@.gmail.com> wrote in message
> news:1149586142.846012.210890@.f6g2000cwb.googlegroups.com...
>|||Razvan Socol wrote:
Luqman wrote:
> Did you work on my queries, please ?
My last message was:
http://groups.google.com/group/micr...br />
5b133039
Did you read it ?
If yes, you didn't post any additional information. If you need more
help, post sample data AND EXPECTED RESULTS where there is a positive
value after some negative values.
Razvan
ry Item, positive quantities are receipts and negative quantities are issued
, last two columns are running sum of quantity and amount column.
While running sum of quantity is ok, but Amt running sum is not correct, any
idea what am I missing ?
My table data is as under:-
Please note, negative quantites have no rates and no amounts, this will be c
alculated on average basis in above query.
Sno field is just an auto numbering field for query purpose.
PRITC QTY RATE PRAMT SNO
-- -- -- -- --
1111-034 200 1494.5 298900 1
1111-034 218 1559 339862 2
1111-034 -150 3
1111-034 -5 4
1111-034 -200 5
1111-034 -5 6
1111-034 -50 7
Best Regards,
Luqman
select a.sno,a.qty,
case when a.qty>0 then a.rate
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0) end rate,
case when a.qty>0 then a.pramt
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
end Amt,sum(b.qty),
sum(case when a.qty>0 then a.pramt
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
end) Amt
from test a, test b
where a.sno>=b.sno
group by a.sno,a.qty,a.pramt,a.rate
SNO QTY RATE AMT SUM(B.QTY) AMT
-- -- -- -- --
--
1 200 1494.5 298900 200 298900
2 218 1559 339862 418 679724
3 -150 1528.13876 -229220.81 268 -687662.44
4 -5 1528.13876 -7640.6938 263 -30562.775
5 -200 1528.13876 -305627.75 63 -1528138.8
6 -5 1528.13876 -7640.6938 58 -45844.163
7 -50 1528.13876 -76406.938 8 -534848.56Hello, Luqman
Please post DDL (as "CREATE TABLE" statements) and sample data (as
"INSERT INTO ... VALUES ..." statements), like this:
CREATE TABLE test (
PRITC varchar(10) NOT NULL,
QTY numeric(10,3) NOT NULL,
RATE numeric(18,4) NULL,
PRAMT numeric(18,4) NULL,
SNO int IDENTITY PRIMARY KEY,
CHECK (PRAMT=QTY*RATE)
)
INSERT INTO test VALUES ('1111-034',200,1494.5,298900)
INSERT INTO test VALUES ('1111-034',218,1559,339862)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-150)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-5)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-200)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-5)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-50)
The above DDL is just a guess; please correct it if necessary.
Your current query returns the following error: "Cannot perform an
aggregate function on an expression containing an aggregate or a
subquery." The reason is obvious: you cannot use SUM() for an
expression that contains a subquery (for the last column in your
query).
You probably want something like this:
SELECT a.sno, a.qty, a.rate, a.pramt, (
SELECT SUM(qty) FROM test b
WHERE b.sno<=a.sno
) as qty_sum, (
SELECT SUM(pramt) FROM test b
WHERE b.sno<=a.sno AND qty>0
) as pramt_sum, (
SELECT SUM(pramt)/SUM(qty) FROM test b
WHERE b.sno<=a.sno AND qty>0
) as avg_rate
FROM test a
If you want something else, please post the expected results.
Razvan|||The following query will output something like this.
select a.sno,a.qty,
case when a.qty>0 then a.rate
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0) end rate,
case when a.qty>0 then a.pramt
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
end Amt,sum(b.qty),
case when a.qty>0 then a.pramt
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
end Bal
from test a, test b
where a.sno>=b.sno
group by a.sno,a.qty,a.pramt,a.rate
sno qty rate Amount qtyTotal AmtTotal
1 200.000 1494.500000 298900.000000 200.000 298900.000000
2 218.000 1559.000000 339862.000000 418.000 339862.000000
3 -150.000 1528.138755 -229220.813250 268.000 -229220.813250
4 -5.000 1528.138755 -7640.693775 263.000 -7640.693775
5 -200.000 1528.138755 -305627.751000 63.000 -305627.751000
6 -5.000 1528.138755 -7640.693775 58.000 -7640.693775
7 -50.000 1528.138755 -76406.937750 8.000 -76406.937750
Currently the Qtytotal is ok, but AmtTotal is not ok, I want the correct
running sum of Amount Field in AmtTotal.
The correct display should be:
sno qty rate Amount qtyTotal AmtTotal
1 200.000 1494.500000 298900.000000 200.000 298900.000000
2 218.000 1559.000000 339862.000000 418.000 638762.000000
3 -150.000 1528.138755 -229220.813250 268.000 409541.19
4 -5.000 1528.138755 -7640.693775 263.000 401900.4962
5 -200.000 1528.138755 -305627.751000 63.000 96272.7462
6 -5.000 1528.138755 -7640.693775 58.000 88632.0524
7 -50.000 1528.138755 -76406.937750 8.000 12225.1144
Any idea please ?
Best Regards,
Luqman
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1149586142.846012.210890@.f6g2000cwb.googlegroups.com...
> Hello, Luqman
> Please post DDL (as "CREATE TABLE" statements) and sample data (as
> "INSERT INTO ... VALUES ..." statements), like this:
> CREATE TABLE test (
> PRITC varchar(10) NOT NULL,
> QTY numeric(10,3) NOT NULL,
> RATE numeric(18,4) NULL,
> PRAMT numeric(18,4) NULL,
> SNO int IDENTITY PRIMARY KEY,
> CHECK (PRAMT=QTY*RATE)
> )
>
> INSERT INTO test VALUES ('1111-034',200,1494.5,298900)
> INSERT INTO test VALUES ('1111-034',218,1559,339862)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-150)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-5)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-200)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-5)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-50)
> The above DDL is just a guess; please correct it if necessary.
> Your current query returns the following error: "Cannot perform an
> aggregate function on an expression containing an aggregate or a
> subquery." The reason is obvious: you cannot use SUM() for an
> expression that contains a subquery (for the last column in your
> query).
> You probably want something like this:
> SELECT a.sno, a.qty, a.rate, a.pramt, (
> SELECT SUM(qty) FROM test b
> WHERE b.sno<=a.sno
> ) as qty_sum, (
> SELECT SUM(pramt) FROM test b
> WHERE b.sno<=a.sno AND qty>0
> ) as pramt_sum, (
> SELECT SUM(pramt)/SUM(qty) FROM test b
> WHERE b.sno<=a.sno AND qty>0
> ) as avg_rate
> FROM test a
> If you want something else, please post the expected results.
> Razvan
>|||Luqman wrote:
> The correct display should be: [...]
OK, then let's save the SELECT statement in my previous post as a view:
create view myview as
SELECT a.sno, a.qty,
ISNULL(a.rate, (
SELECT SUM(pramt)/SUM(qty) FROM test b
WHERE b.sno<=a.sno AND qty>0
)) as rate, (
SELECT SUM(qty) FROM test b
WHERE b.sno<=a.sno
) as qtyTotal, (
SELECT SUM(pramt) FROM test b
WHERE b.sno<=a.sno AND qty>0
) as pramt_sum
FROM test a
And now we can use the following query:
SELECT x.sno, x.qty, x.rate, x.qty*x.rate as Amount,
x.qtyTotal, x.pramt_sum+isnull((
select sum(qty*rate) from myview y
where y.sno<=x.sno and y.qty<0
),0) as AmtTotal
FROM myview x
The above query returns the expected results (I'm not sure what is the
number of decimals that you want; the number of decimals in the result
changes according to the data types in the table).
However, to be sure that the above query meets all your requirements,
you should also post some sample data where there is a positive value
AFTER some negative values and expected results for this new sample
data.
Razvan|||Hi,
Did you work on my queries, please ?
Best Regards,
Luqman
"Luqman" <pearlsoft@.cyber.net.pk> wrote in message
news:%232Kn49ZiGHA.3884@.TK2MSFTNGP04.phx.gbl...
> The following query will output something like this.
> select a.sno,a.qty,
> case when a.qty>0 then a.rate
> else
> (select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0) end rate,
> case when a.qty>0 then a.pramt
> else
> (select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
> end Amt,sum(b.qty),
> case when a.qty>0 then a.pramt
> else
> (select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
> end Bal
> from test a, test b
> where a.sno>=b.sno
> group by a.sno,a.qty,a.pramt,a.rate
> sno qty rate Amount qtyTotal
> AmtTotal
> 1 200.000 1494.500000 298900.000000 200.000 298900.000000
> 2 218.000 1559.000000 339862.000000 418.000 339862.000000
> 3 -150.000 1528.138755 -229220.813250 268.000 -229220.813250
> 4 -5.000 1528.138755 -7640.693775 263.000 -7640.693775
> 5 -200.000 1528.138755 -305627.751000 63.000 -305627.751000
> 6 -5.000 1528.138755 -7640.693775 58.000 -7640.693775
> 7 -50.000 1528.138755 -76406.937750 8.000 -76406.937750
> Currently the Qtytotal is ok, but AmtTotal is not ok, I want the correct
> running sum of Amount Field in AmtTotal.
> The correct display should be:
> sno qty rate Amount qtyTotal
> AmtTotal
> 1 200.000 1494.500000 298900.000000 200.000 298900.000000
> 2 218.000 1559.000000 339862.000000 418.000 638762.000000
> 3 -150.000 1528.138755 -229220.813250 268.000 409541.19
> 4 -5.000 1528.138755 -7640.693775 263.000 401900.4962
> 5 -200.000 1528.138755 -305627.751000 63.000 96272.7462
> 6 -5.000 1528.138755 -7640.693775 58.000 88632.0524
> 7 -50.000 1528.138755 -76406.937750 8.000 12225.1144
> Any idea please ?
> Best Regards,
> Luqman
>
> "Razvan Socol" <rsocol@.gmail.com> wrote in message
> news:1149586142.846012.210890@.f6g2000cwb.googlegroups.com...
>|||Razvan Socol wrote:
Luqman wrote:
> Did you work on my queries, please ?
My last message was:
http://groups.google.com/group/micr...br />
5b133039
Did you read it ?
If yes, you didn't post any additional information. If you need more
help, post sample data AND EXPECTED RESULTS where there is a positive
value after some negative values.
Razvan
Running Sum on Query
Hye Guys,
I am new in this forums and programming..
I have data like
S.N Item Qty
1 ABC 10
2 ABC 20
3 ABC 5
4 XYZ 2
5 XYZ 10
6 XYZ 1
And I want the result
S.N Item Qty RunTot select t.[S.N], t.Item, t.Qty, sum(t1.Qty) as RunTot http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=23349&DisplayTab=Article Hope that helps! BobP|||Hye friends, Always Ready 2 Learn If it is for a report, let the reporting application calculate it. The other thing is to pre-process the statement, and store the results in a table, and just select from that table when you need the data. You could setup the pre-process to happen at night, or every hour, etc... You would just need to dump the results to a temp table, start a transaction, delete from the processed table, populate the processed table from the temp table, and then commit the transaction. To the users it is transparent, except that you dont have "real-time" data. BobP||| Dear guys, Previously For such running totals I use to have a seperate column in a transaction table. And I used to calculate the value during the insertion of the record and store it in the respective column. that was quite easy way but by any means my rows get unsorted or the order of the transaction has to be changed manually I have w calculate the values again and maintain the column sum once more. which was totally manual calculation based. So I am trying to find the way out fo it... Always there 2 learn
1 ABC 10 10
2 ABC 20 30
3 ABC 5 35
4 XYZ 2 2
5 XYZ 10 12
6 XYZ 1 13
Plz Help meAre you going to be presenting these rows in a client application? If so, it will be VASTLY more efficient to do these sub-totals there. It is easy as you're looping one by one through the individual rows to keep a tally of Qty for like Item values. With T-SQL, you will have to use a sub-query that is evaluated for every row, and this is going to put your performance into the toilet.
> Hye Guys, >
> I am new in this forums and programming.. >
> I have data like >
> S.N Item Qty
> 1 ABC 10
> 2 ABC 20
> 3 ABC 5
> 4 XYZ 2
> 5 XYZ 10
> 6 XYZ 1 >
> And I want the result >
> S.N Item Qty RunTot
> 1 ABC 10 10
> 2 ABC 20 30
> 3 ABC 5 35
> 4 XYZ 2 2
> 5 XYZ 10 12
> 6 XYZ 1 13 > >
> Plz Help me
>|||Try this one:
from
YourTable as t
inner join YourTable as t1
on t.Item = t1.Item and t.[S.N]>=t1.[S.N]
group by t.[S.N], t.Item, t.Qty|||Here is an article on it. I have not tested it, I just came across it once and bookmarked it..
The replies you have posted had helped me but as one of our friend said I tried it in a database containing large volumes of records.. and the result is ...... 2 slow. So need some other technique..
Kabindra Bakey|||Are there any reasons why it has to be in one select statement?
If it is for an application, let the app calc it.
Kabindra Bakey
Subscribe to:
Posts (Atom)