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 

Answer 1

User-158764254 posted

something like this might get you started.

SELECT item,
       Avg(cost) AS AverageCost
FROM   p0101
GROUP  BY item

Answer 2

User-2057865890 posted

Hi Deepak,

Using SQL Server Management Studio

To create a SQL Server Agent job

1. In the Object Explorer, click the plus sign to expand the server where you want to create a SQL Server Agent job.
2. Click the plus sign to expand SQL Server Agent.
3. Right-click the Jobs folder and select New Job….
4. In the New Job dialog box, on the General page, modify the general properties of the job. 
5. On the Steps page, organize the job steps. 
6. On the Schedules page, organize schedules for the job. 
7. On the Alerts page, organize the alerts for the job. 
8. On the Notifications page, set actions for Microsoft SQL Server Agent to perform when the job completes. 
9. On the Targets page, manage the target servers for the job.
10. 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.aspx 

Best Regards,

Chris

Answer 3

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

Answer 4

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

Answer 5

User-2057865890 posted

Hi Deepak,

Have you tried my previous reply's steps or using the SSMS GUI?

https://blogs.msdn.microsoft.com/sqlagent/2010/10/12/create-a-database-backup-job-using-sql-server-management-studio/ 

http://www.c-sharpcorner.com/uploadfile/raj1979/create-and-schedule-a-job-in-sql-server-2008/ 

Best Regards,

Chris

Answer 6

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