how to automate the execution - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to automate the execution

Author

Topic

kkiranvr
Yak Posting Veteran

54 Posts

Posted - 2008-02-08 : 14:03:39

I am running this procedure to copy the call data info from oma11wicacct01 to oma11csql5tst01 and to vsql4\shared

INSERT INTO [oma11csql5tst01].[rms sql].dbo.WICCallData ( ClientID, ProgramID, Calls, ActSeconds, UsageDate )

SELECT WICCallData.ClientID, WICCallData.ProgramID, Sum(WICCallData.Calls) AS SumOfCalls, Sum(WICCallData.ActSeconds) AS SumOfActSeconds, WICCallData.UsageDate

FROM WICCallData

GROUP BY WICCallData.ClientID, WICCallData.ProgramID, WICCallData.UsageDate

HAVING (Sum(WICCallData.Calls)>0 or Sum(WICCallData.ActSeconds)>0) AND WICCallData.UsageDate>'2/3/2008'

The usage date has to be greater than the max date on the target database!
for example the max date on [oma11csql5tst01].[rms sql].dbo.WICCallData is '11/30/2007' then how can write a variable to that date field to set the maximum date from the table?

How can I automate the execution?

-Thanks N Regards,
Kanthi.

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-08 : 14:07:15

[code]
Declare @MaxDate datetime
Select @MaxDate = Max(UsageDate)
From [oma11csql5tst01].[rms sql].dbo.WICCallData

INSERT INTO [oma11csql5tst01].[rms sql].dbo.WICCallData ( ClientID, ProgramID, Calls, ActSeconds, UsageDate )

SELECT WICCallData.ClientID, WICCallData.ProgramID, Sum(WICCallData.Calls) AS SumOfCalls, Sum(WICCallData.ActSeconds) AS SumOfActSeconds, WICCallData.UsageDate

FROM WICCallData

GROUP BY WICCallData.ClientID, WICCallData.ProgramID, WICCallData.UsageDate

HAVING (Sum(WICCallData.Calls)>0 or Sum(WICCallData.ActSeconds)>0) AND WICCallData.UsageDate > @MaxDate
[/code]

kkiranvr
Yak Posting Veteran

54 Posts

Posted - 2008-02-08 : 15:23:02

quote:

Originally posted by Qualis


Declare @MaxDate datetime
Select @MaxDate = Max(UsageDate)
From [oma11csql5tst01].[rms sql].dbo.WICCallData

INSERT INTO [oma11csql5tst01].[rms sql].dbo.WICCallData ( ClientID, ProgramID, Calls, ActSeconds, UsageDate )

SELECT WICCallData.ClientID, WICCallData.ProgramID, Sum(WICCallData.Calls) AS SumOfCalls, Sum(WICCallData.ActSeconds) AS SumOfActSeconds, WICCallData.UsageDate

FROM WICCallData

GROUP BY WICCallData.ClientID, WICCallData.ProgramID, WICCallData.UsageDate

HAVING (Sum(WICCallData.Calls)>0 or Sum(WICCallData.ActSeconds)>0) AND WICCallData.UsageDate > @MaxDate

Thank you very much for your answer.

-Thanks N Regards,
Kanthi.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources