SQL Server - MONTH RANGE QUERY - Asked By farrukh on 11-Dec-11 10:31 AM

HI ALL,

 HOW TO WRITE A QUERY OR PROCEDURE THAT WILL RUN EVERY TIME FROM NOVEMBER(ANY YEAR SELECT) AND END WITH OCTOBER AND GET THE DAILY AVG OF SUPPOSE PARAMETER ( FUEL) AND  DIVIDED  BY  IF  MONTH IS  JANUARY IT FUEL/31  , IF MONTH IS  FEBRUARY FUEL/28 AND SO ON?

  Nov Dec Jan Feb Mar Apr May June July Aug Sep Oct TOTAL
AVG DAY FUEL 300 300 300 300 300 300 300 300 300 300 300 300  
Days Prod 30 31 31 28 31 30 31 30 31 31 30 31 365
FUEL/Days Prod 9.00 9.30 9.30 8.40 9.30 9.00 9.30 9.00 9.30 9.30 9.00 9.30 109.50



THANKS
HAMMEED
Neha Garg replied to farrukh on 11-Dec-11 10:32 AM
Hello Farrukh,

Here is the query:

SELECT November as A,
December as B,
January as C,
February as D,
March as E,
April as F,
May as G,
June as H,
July as I,
August as J,
September as k,
October as l,* FROM announcement where parameter

or else

store the month in a temp folder and starts from November...

farrukh replied to Neha Garg on 11-Dec-11 10:37 AM
HI Nilesh Kumar,
 
I HAVE EDITED THE POST AND PASTE THE TABLE INFORMATION IN IT, HOW CAN I DO THIS VIA QUERY OR STORED PROCEDURE?

THANKS
HAMMEED
Riley K replied to farrukh on 11-Dec-11 07:49 PM



You can Schedule a  job that will run this stored procedure

In SSMS, go expand the SQL Server Agent node under the DB server, right click the "Jobs" folder and select "New job...". That will take you through a wizard to schedule a sproc to run on whatever schedule you want.

To create a job

  • http://msdn.microsoft.com/en-us/library/ms190268.aspx

  • http://msdn.microsoft.com/en-us/library/ms181153.aspx

  • http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.agent.job.aspx

http://msdn.microsoft.com/en-us/library/ms186273.aspx



Regards
Jitendra Faye replied to farrukh on 11-Dec-11 11:09 PM
You can schedule query for this.

1) Expand the SQL Server Agent node and right click the Jobs node in SQL Server Agent and select 'New Job'


2) In the 'New Job' window enter the name of the job and a description on the 'General' tab.


3) Select 'Steps' on the left hand side of the window and click 'New' at the bottom.


4) In the 'Steps' window enter a step name and select the database you want the query to run against.


5) Paste in the T-SQL command you want to run into the Command window and click 'OK'.


6) Click on the 'Schedule' menu on the left of the New Job window and enter the schedule information (e.g. daily and a time).


7) Click OK - and that should be it.


Try this and let me know. 

for more help follow these links=--

http://technet.microsoft.com/en-us/library/ms191439.aspx
http://msdn.microsoft.com/en-us/library/ms191439.aspx
sanjeev katiyar replied to farrukh on 13-Dec-11 03:13 AM

 Hi,

  select months_between( sysdate, month_name)/(month_day) from dual;

 use this code.