Answered by:
SQL job to pick average cost of item from total quantity and cost for 1 day in the night

Question
-
User1182587605 posted
I need to create a job to pick the cost of items from the database table P0101 by taking average of cost and items from the same table. Please give me an idea on how to do this. A query would be more appreciated
Regards,
Deepak
Saturday, November 26, 2016 7:44 PM
Answers
-
User-2057865890 posted
Hi Deepak,
Using SQL Server Management Studio
To create a SQL Server Agent job
- In the Object Explorer, click the plus sign to expand the server where you want to create a SQL Server Agent job.
- Click the plus sign to expand SQL Server Agent.
- Right-click the Jobs folder and select New Job….
- In the New Job dialog box, on the General page, modify the general properties of the job.
- On the Steps page, organize the job steps.
- On the Schedules page, organize schedules for the job.
- On the Alerts page, organize the alerts for the job.
- On the Notifications page, set actions for Microsoft SQL Server Agent to perform when the job completes.
- On the Targets page, manage the target servers for the job.
- When finished, click OK.
Using Transact-SQL
USE msdb ; GO EXEC dbo.sp_add_job @job_name = N'Weekly Sales Data Backup' ; GO EXEC sp_add_jobstep @job_name = N'Weekly Sales Data Backup', @step_name = N'Set database to read only', @subsystem = N'TSQL', @command = N'select avg(cost) from table', @retry_attempts = 5, @retry_interval = 5 ; GO EXEC dbo.sp_add_schedule @schedule_name = N'RunOnce', @freq_type = 1, @active_start_time = 233000 ; USE msdb ; GO EXEC sp_attach_schedule @job_name = N'Weekly Sales Data Backup', @schedule_name = N'RunOnce'; GO EXEC dbo.sp_add_jobserver @job_name = N'Weekly Sales Data Backup'; GO
Create a Job
https://msdn.microsoft.com/en-us/library/ms190268.aspx
Schedule a Job
https://msdn.microsoft.com/en-us/library/ms191439.aspxBest Regards,
Chris
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Monday, November 28, 2016 5:29 AM
All replies
-
User-158764254 posted
something like this might get you started.
SELECT item, Avg(cost) AS AverageCost FROM p0101 GROUP BY item
Monday, November 28, 2016 1:30 AM -
User-2057865890 posted
Hi Deepak,
Using SQL Server Management Studio
To create a SQL Server Agent job
- In the Object Explorer, click the plus sign to expand the server where you want to create a SQL Server Agent job.
- Click the plus sign to expand SQL Server Agent.
- Right-click the Jobs folder and select New Job….
- In the New Job dialog box, on the General page, modify the general properties of the job.
- On the Steps page, organize the job steps.
- On the Schedules page, organize schedules for the job.
- On the Alerts page, organize the alerts for the job.
- On the Notifications page, set actions for Microsoft SQL Server Agent to perform when the job completes.
- On the Targets page, manage the target servers for the job.
- When finished, click OK.
Using Transact-SQL
USE msdb ; GO EXEC dbo.sp_add_job @job_name = N'Weekly Sales Data Backup' ; GO EXEC sp_add_jobstep @job_name = N'Weekly Sales Data Backup', @step_name = N'Set database to read only', @subsystem = N'TSQL', @command = N'select avg(cost) from table', @retry_attempts = 5, @retry_interval = 5 ; GO EXEC dbo.sp_add_schedule @schedule_name = N'RunOnce', @freq_type = 1, @active_start_time = 233000 ; USE msdb ; GO EXEC sp_attach_schedule @job_name = N'Weekly Sales Data Backup', @schedule_name = N'RunOnce'; GO EXEC dbo.sp_add_jobserver @job_name = N'Weekly Sales Data Backup'; GO
Create a Job
https://msdn.microsoft.com/en-us/library/ms190268.aspx
Schedule a Job
https://msdn.microsoft.com/en-us/library/ms191439.aspxBest Regards,
Chris
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Monday, November 28, 2016 5:29 AM -
User1182587605 posted
hi,
I am new to creating jobs in SQL server, I have to create a job which runs every midnight, Fetches cost, itemID data from AFS.<Table1> and posts it into BSJ.<Table2> where AFS and BSJ are two different databases in same server. Can anyone please tell me how I can achieve this. It will be a great help. Please give me some code to create this job. Thanks
Thanks,
Deepak
Monday, November 28, 2016 5:48 PM -
User1182587605 posted
Hi Chris,
I am new to creating jobs in SQL server, I have to create a job which runs every midnight, Fetches cost, itemID data from AFS.<Table1> and posts it into BSJ.<Table2> where AFS and BSJ are two different databases in same server. Can anyone please tell me how I can achieve this. It will be a great help.
link: https://forums.asp.net/post/6099769.aspx
Please give me some code it will help me a lot.
Thanks
Deepak
Monday, November 28, 2016 5:51 PM -
User-2057865890 posted
Hi Deepak,
Have you tried my previous reply's steps or using the SSMS GUI?
http://www.c-sharpcorner.com/uploadfile/raj1979/create-and-schedule-a-job-in-sql-server-2008/
Best Regards,
Chris
Tuesday, November 29, 2016 8:21 AM -
User1182587605 posted
Yes chris,
The problem is, I need to select table values from one DB and copy them wih insert query into another DB. How can I do this in one job. Can you please guide me?
Regards,
Deepak
Tuesday, November 29, 2016 12:41 PM