locked
SQL job to pick average cost of item from total quantity and cost for 1 day in the night RRS feed

  • 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

    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

    • 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

    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

    • 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
  • 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