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.

Rolling Window DataBase

I need a 7 day rolling window average for each date shown on a query, for example: Date range 10 / 1 / 2008 - 10 / 31 / 2008 Fields: Daily Average MTD Average Previous Month Average 12 Month Average 7 day rolling window average Explination: Daily average comes from the primary table, just the

calculating mean daily average Excel

Hi, I would like to be able to calculate the mean daily average for several parameters contained within a monthly data file. The data for each parameter are P and range from row 7 to row 2886 or row 2982 depending on the month. Each day contains 96 values for each parameter. Could this be done with a combination of formulas or will macros be required? Thank you, Andrea Excel Discussions Microsoft Excel (1) AVERAGE (1) Macros (1) Daywhile (1) Bfd134a1 (1) Beff (1) Day (1) Andrea, Since you have the same number of data points for each day, the mean daily average (or, the _average_ of the averages) is the same as the average of all the

AVG BETWEEN DAYS DataBase

Hello, Within my qry I would like to calculate the average daily production for the month. The data Im working with is: production volume, dates (which is a daily info.) and ID number. I am working with tons of data that have production for the year dating back 5 or more yrs. However I'd like to narrow my daily data to only 6months prior summed up to monthly data and averaged. Please help! Thanks you should be able to do this with a query which groups by year and month of the each date and averages the production volume, e.g. SELECT YEAR([ProductionDate]) AS [ProductionYear], MONTH([ProductionDate]) AS [ProductionMonth], SUM([ProductionVolume]) AS [TotalMonthlyProduction], AVG([ProductionVolume]) AS [AverageDailyProduction] FROM [YourTable] WHERE [ProductionDate DATESERIAL(YEAR(DATE()), MONTH(DATE())-6, 1) GROUP BY YEAR([ProductionDate]), MONTH([ProductionDate]); This will restrict the results to

Daily Average Excel

I prepare a report containing the daily average for every month in the year. How can I set the formula (prefer not VBA) which can automatically identify number of days for every month, i.e. in August, get the average of dividing by 31; in September, get the average by dividing of 30 and so on ? Excel Miscellaneous Discussions Office XP (1) Excel (1

Formula Error Excel

Hi, I have a spreadsheet, one sheet per month (ie November, December etc), which has this formula to calculate the daily average of sales (column P, and column U is for account sales, of which there may be not be any for the month), AVERAGE(IF(P10:P70> 0, (P10:P70)+(U10:U70))) CTRL+SHIFT+ENTER. This is in cell are not calculated. I have a summary page for this current financial year, and each month contains this formula in cells B6, B8, B10 - B28: = 'NOVEMBER 07'!F72 (as per it's month), however, in Dec 07 - June 08 the cells return the DIV / 0 error message. Then i'm tying to do is enter a formula in cell B34 that gives a daily average over the 12 months(or at this stage, would like to see the daily

Calculate interest amount based on average daily balance DataBase

I would like to know whether there is a way I can calculate the interest amount based on average daily balance and how I can calculate the average daily balance for every quarter. For example: The beginning balance is $421, 315.17 as of 09 10, 000.00 If I do the calculation in excel it will calculate the average daily balance from 7 / 1 / 09 to 9 / 30 / 09 and gives me a daily average

Calculating Average Daily Balances Excel

I have a simple checkbook ledger in Excel 2007. I want to compute the Average Daily Balance. This is much harder than it appears. . . I have my entry dates in column is active, so the actual balance may or may not change on any given day; average daily balance should change every day. I had thought about calculating the number of days from 29 / 08 100.00 11 / 2 / 08 9.00 11 / 10 / 08 45.00 The average daily balance is $40.75, calculated manually. Calculated by doing the following math: (100 * 4 days

Average Daily Balance Excel

This might be a simple question. . .but I seem to be struggling to balance and then multiple rows of activtity (either deposits or withdrawls) and sometimes there is daily activity. . . . sometimes we can go weeks without activity I am trying to establish a formula that will give me the Average Daily Balance in the account using the rows of data factoring in the Deposit / Withdrawl amounts and the dates. Any suggestions? Excel Miscellaneous Discussions Excel (1) SUMPRODUCT (1) AVERAGE (1) SUMIF (1) Date (1) Copy (1) First, we need to determine what the daily balance is each day. Assuming starting balance is in F1, with dates in a2:a10 SUMIF(A$2:A$10, E2, C$2:C$10) Copy down as needed. Running average balance then, in G2: = AVERAGE(F$1:F2) Copy down as needed - - Best Regards, Luke

Daily Average by quarter DataBase

I have a field that I need to calculate a conditional running operand without an operator". The formula I have is: = DAvg("[Strght Avg Abdn%] ", "[Service Guarentee Daily QRY]", "Date Between #" & DateAdd("d", Datediff("d", if("d", if(month[date]<4, dateserial(year[date], 1, 1), if(month[date]<7, dateserial(year[date], 4, 1), if(month[date]<10, dateserial(year[date], 7, 1), dateserial(year[date], 10, 1))), [Date]) & "# AND #" & [Date for but you need to use IIf() rather than If(). Also, you have expressions like "Month[date]" which I assume should be "Month([date])". If you really must use "date" as a field name, try to always wrap