Select Query To Get Last 6 Months Data

Sep 16, 2004

I need to write a select query to get last six months data from a table , on monthly wise.Can anyone help me in writing this query
I am using sql server 2000.



View 1 Replies


SQL Server 2012 :: Select Query - Get Result As Month And Values For All Months Whether Or Not Data Exists

Jul 27, 2015

I have a table with dates and values and other columns. In a proc i need to get the result as Month and the values for all the months whether or not the data exists for the month.

The Similar table would be-

create table testing(
DepDate datetime,
val int)
insert into testing values ('2014-01-10 00:00:00.000', 1)
insert into testing values ('2014-05-19 00:00:00.000', 10)
insert into testing values ('2014-08-15 00:00:00.000', 20)
insert into testing values ('2014-11-20 00:00:00.000', 30)

But in result i want the table as -

Month Value


View 9 Replies View Related

Select Last Months Data

Aug 5, 2004

Hi, i am trying to create a t-sql statement that will retrieve last months data (ie. if i run the query on 9th August, i only want to retrieve Julys data, 1st Sept will retrieve all of Augusts data etc). The query will be used once a month to populate a table, can anyone advise me on the correct where clause to use ?

Thanks in advance


View 3 Replies View Related

Select Data From Table An Return All All Months

Jul 23, 2005

I have the following query:SELECT Month, Sum(Hits) AS Hits FROM tblHits GROUP BY Month ORDER BYMonthUnfortunately it only returns rows for months that have data assignedto them.How can I tweak this so that months 1-12 are returned, and Hits = 0 formonths with no data in the base table?Thanks.

View 2 Replies View Related

Very Hard Question On How To Query Data And Return By Months

Oct 18, 2007

I have a data set that returns the following











but I need this






Any Idea Help

View 13 Replies View Related

Selecting Data Within 1 Month, 6 Months, 12 Months

Mar 27, 2008

I have the following table

FeedBack Type Date

test2 positive 03/15/08

tes3 negative 03/01/08

.. ....

in my page i need to select the number of negative/positive comments within the last

1 month, 6 months, 12 months

How can I accomplish that?

View 5 Replies View Related

SELECT Where Date &>= 14 Months Ago

Nov 29, 2004


I need a select statement with the condition that the date (a timestamp with format yyyy-mm-dd) is either equal to or longer than 14 months from today's date.

I am using SQL Server

Can anyone point me in the right diretion?


View 1 Replies View Related

How To Select Only Three Months Recored From Database

Jan 27, 2008

Hi guys,
Could anyone help me to write sql query to select only three months record from database
if the cur month is march then i need jan, feb and mar data , if it is jan, then i need nov , dec and jan can i write sql query for this, i need this for creating reports
and also how cal i write queries based on days...mean to say that i need to select all records of particular month that falls on monday or tuesday...
Help me out.
Thanks in advance

View 1 Replies View Related

Getdate() To Select Dates Less Than 15 Months

Apr 7, 2008

I need help. I want to select dates less than 15 months, as i am new to sql server , can anyone advise me.

I tried this query but it gave me the dates that are greater than 15 months

Selec calendardate from table
where calendardate < getdate()-457


View 3 Replies View Related

T-SQL (SS2K8) :: Select All CustomerIDs Where Their Last PaymentDate Is More Than 12 Months Ago

Sep 22, 2014

I have the following query:

;WITH CTE_LastOrder (CustomerID, LastOrderDate) As
SELECT CustomerID, MAX(PaymentDate) LastOrderDate
FROM [Order]
GROUP By CustomerId
SELECT * from dbo.Customer C
JOIN CTE_LastOrder LO ON C.CustomerId = LO.CustomerId
WHERE LO.LastOrderDate > (dateAdd(YEAR,-1, GetDate()))

That should select all customers from the customers table where they haven't had a Paid Order (ascertained via PaymentDate not being null) in the last 12 months - this could also include customers who don't appear in the Order table at all.

The query is giving me results but shows a LastOrderDate for date ranges in 2014..

View 5 Replies View Related

Query For Due Months

May 13, 2008

Hi All,

I need to display the rent not paid months in the year. How can I form the query?

Help me.


View 7 Replies View Related

Last 3 Months Query

Jan 31, 2008


I need to count last 3 months records. The query I'm trying to use was based on last 8 days. How can I use this query in months and not days? Or should I use a new query?

Code Snippet
SELECT COUNT(Column1) AS [Nº de Cartões], Column2 AS [Tipo de Cartão], Column4 AS Periodicidade
FROM main_client_file
WHERE (Column9 >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 3), 0)) AND (Column9 <= DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 1), 0))
GROUP BY Column2, Column4


View 4 Replies View Related

Power Pivot :: Comparing YTD Data To Average Of Data In Last 6 Months

Jun 9, 2015

I am trying to build various reports that compares data over time. I have one that measures Year Over Year % difference for number of incoming projects. I managed to do that easily by calculating the following

YTDProjects:=if(ISBLANK(SUM('TrendData'[Projects])),blank(),CALCULATE(SUM('TrendData '[Projects]),DATESYTD(CalendarDate[FullDate])))
PYProjects:=if(ISBLANK(SUM('TrendData'[Projects])),blank(),CALCULATE(sum('TrendData '[Projects]),SAMEPERIODLASTYEAR(DATESYTD(CalendarDate[FullDate]))))
YoYPercProjects:=IF([PYProjects]=0, BLANK(), [YoYDifference]/[PYProjects])

Where Projects is the metric in question, TrendData is the table that contains project data and CalendateDate is the Date Table. But now I am trying to compare the same YTD projects data to number of projects that came in the last 6 months. How do our projects compare to average number of projects that came in last 6 month period.

I tried the the DATEADD function instead but got no luck and data came out wrong!


For some reason, this also returns blank in my model:


View 2 Replies View Related

Rolling 13 Months In Query

Jun 23, 2008


I'm writing a query that will be calculating a number of business performance measures including the following...

Average Stock:
This is calculated by taking the actual stock value of the product category per month and dividing it by 13 if we have over 12 month's history, or if a new product, then by the number of months we have had it in stock for.

Cost of Goods Sold Annualised:
This is an average figure for the year of the sold landed costs for the product category over a 13 month period if we have over 12 months history. If it's a new product, then it's for the number of months we have had stock.

My question is, how do I tell the query to go back 13 months from the current month?

View 1 Replies View Related

Query For Getting Missing Dates In Months

Feb 5, 2008

I have a table like FK_ID, Value, Date (here FK_ID is  foreign key)this table getting updated frequently by daily bases that means one record per one day(For example in January month it has maximum 31 records or minimum 0 records, in February it has maximum 28 or 29 or minimum 0 records, based on calender year)I need to query this table to get missing dates in between particular monthsfor example for one FK_ID has only 25 records in Jan 2008 month and in Feb 2008 it has 10 records , so i need to get those missing 6 dates from JAN month and 18 dates from FEB monthhow can i query this 

View 2 Replies View Related

Get The Row Counts For Each Day Going Back To 6 Months (was Query Help)

Jun 15, 2006

I have a proc to get the rowcounts for the given date range.
I have to get the row counts for each day going back to 6 months on the table.

With this proc i can get one day's row couts.. i need to loop through for all dates.

Please can someone get me the code for this.

create proc p_rowcounts

@Date1 datetime,
@Date2 datetime

count (*) as 'Number of Rows', @Date1 as Date
Table1 (nolock)
WHERE ModifyTime >= @Date1 and ModifyTime < @Date2

thanks for the help.

View 3 Replies View Related

Data For Last 13 Months

May 8, 2008


I want to select data from a table for last 13 months.
Can anyone tell me how to write the T-sql code for this?

The table looks like this.

FileMonth Type

2007-04-01 00:00:00.000 Total NULL 199886109.65
2007-04-01 00:00:00.000 Total Line 170724936.92
2007-05-01 00:00:00.000 Total Loan 29161172.73
2007-05-01 00:00:00.000 InstLend NULL 49780163.49
2007-06-01 00:00:00.000 InstLend Line 42450387.44

So I wanna pull data from 04/01/07 to 04/01/08.

So I wanna write a query some thing like this.

Select *
from table1
where Filemonth BETWEEN PreviousMonth AND Last13thMonth


View 1 Replies View Related

Return The Last 3 Months Data

Mar 12, 2007


I have the following SSAS MDX query, currently taking a From & To date. How would I alter the MDX below so that just the prior 3 months data was returned for a chart of balances. I do not wish to retain the date parameters

SELECT NON EMPTY { [Measures].[EOD Book Balance] } ON COLUMNS, NON EMPTY { ([Time].[Simple Date].[Simple Date].ALLMEMBERS * [Products].[Product ID].[Product ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@FromTimeDate, CONSTRAINED) : STRTOMEMBER(@ToTimeDate, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Products].[Product Type].&[Term] } ) ON COLUMNS FROM ( SELECT ( { [Book Balance Type].[Balance Type].&[Credit Balance] } ) ON COLUMNS FROM [DailyBalances]))) WHERE ( [Book Balance Type].[Balance Type].&[Credit Balance], [Products].[Product Type].&[Term] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

View 7 Replies View Related

Transact SQL :: Getting Data For Last 3 Months

Oct 14, 2015

I have a date into format YYYYMM. Data type is int. I need to make query where every time it will return me last 3 months, but without current one. For example I have data for months below


Query should return all records for 201509, 201508, 201507.

I was trying lot of solutions founded in internet like this one:

Date_Column >= DATEADD(MONTH, -3, GETDATE())
DATEDIFF(MONTH, my_date_column, GETDATE()) <= 3

but it doesn't work.

View 4 Replies View Related

Showing Data For All Months

Apr 3, 2008

I have a Report Parameter Non-queried list for all months (Labels January, Februari... and Values 01,02...).
When I pick a month in my report all works fine
But I would also like to have data for the complete year (via an All selection)
How should I get this done . Any help appreciated

View 4 Replies View Related

Sorting Data Having Months

Sep 21, 2006

Dear all,

I have a field [Month] in my table AA.having Data like ( January,February,March,April etc.)

I need to sort them by starting of month

lets say as




How i can i do this

Have no index and can't define a index on this field

Thanks for ur time and Help


Mohd Sufian

View 12 Replies View Related

Extract Data For Last Day Of Previous Months

Jun 12, 2014

I need to extract records for the last day of previous months (Up till January of the same year) from a table SALES, according to a date parameter ASOFDATE that the user enters.

For Example

If user keys in ASOFDATE as 10-May-2014, I would have

ASOFDATE Data1 Data2
10-MAY-2014 123 443
30-APR-2014 222 234
31-MAR-2014 544 875
28-FEB-2014 546 908
31-JAN-2014 957 896

How do I do that?

View 2 Replies View Related

DB Engine :: How To Restore Only Last 1 Or 2 Months Data In Server

May 12, 2015

I have 5 TB of data in production and my development team requested to restore only last month data(<=500 MB ) in staging server and last 2 months data in pre production.

View 3 Replies View Related

Transact SQL :: Pulling The Most Recent 12 Months Of Data

Jul 1, 2015

My goal is to show the most recent 12 months of data including the current month based on a patients discharge date (relative to the query execution time). There could be many years of data so I want to be sure I am pulling the most recent 12 monthsThis seems to work (on the surface anyway … not quite sure if this is the best logic to use).

< 13

View 5 Replies View Related

Need Report Also Showing Months Even When No Data For That Month

May 13, 2008

Hi All,

would like to make a report with sales figures per month even is there are no sales figures that month.
Report should still show the month.

Any help to get me started in the wright direction much appreciated


View 1 Replies View Related

T-SQL (SS2K8) :: Date Logic - How To Get Previous 6 Months Data

Sep 29, 2014

I am working in sqlserver 2008 R2 and below is my sample research query. I am trying to get previous 6 months data.

WITH CutomMonths
AS (
SELECT UPPER(convert(VARCHAR(3), datename(month, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0)))) Month
,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0) startdate
,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N + 1, 0) enddate

[Code] ...

Current output what i am getting:

Expected Output:

I found why the April month i didn't get the $20 because the startdate of my perks CTE '2014-04-03'. If it is '2014-04-01' then i will get the expected output.

But i should not change the the date on perks. How to neglect this date issue and consider the month instead to get the expected output.

View 6 Replies View Related

SQL Server 2012 :: Delete XML Data Older Than 2 Months

Aug 18, 2015

I have a table which has XML data, so it takes lot of time to delete it.

Here's the condition:

Archive all data older than 2 months and delete it.

What would be the good approach for this?

View 3 Replies View Related

T-SQL Scritp To Calculate The Last 6 Months Of Data In A Date Field.

Oct 12, 2006

Hi there,

if any body out there has done this before, can you please post the T-SQL script that calculate whatever last months of data based on a Date field.

Thanks very much,


View 2 Replies View Related

Select Query As Column Data

Aug 8, 2013

Outer query: select op.const_cd, (select const_hin from constituency c where c.const_cd=op.const_cd) from PollingStation op, constituency c

where op.const_cd=c.const_cd

group by op.const_cd
order by op.const_cd

Result is:
122 a 205
123 b 205
124 c 235
125 d 191
126 e 226
127 f 159
128 g 165
129 h 175
130 i 225
131 j 213

Inner Query result of: select const_cd, count(polling_cd) from PollingStation p where right(polling_cd,1)='A' group by p.const_cd order by p.const_cd:

122 4
123 4
124 2
125 3
126 1
127 6
128 11
129 9
130 2

I want this output:

const_cd const_name count inner query
122 a 205 4
123 b 205 4
124 c 235 2
125 d 191 3
126 e 226 1
127 f 159 6
128 g 165 11
129 h 175 9
130 i 225 2
131 j 213

View 8 Replies View Related

How To Write Query To Select Second Row Column Data

Nov 18, 2013

I have a requirement like below .

Input table will be like below:

accountID deviceID timestamp speedKPH address
--------- -------- ---------- -------- -------------
preva1 bolero 1359089006 15 Ullalu Road
preva1 bolero 1359088796 0 Ullalu Road
preva1 bolero 1359088886 0 Ullalu Road
preva1 bolero 1359088888 8.47 Ullalu Road
preva1 bolero 1359088986 0 Ullalu Road
preva1 bolero 1359088988 45 Ullalu Road
preva1 bolero 1359088996 21 Ullalu Road
preva1 bolero 1359088998 0 Ullalu Road
preva1 bolero 1359089006 15 Ullalu Road
preva1 bolero 1359089009 12 Ullalu Road
preva1 bolero 1359089006 15 Ullalu Road
preva1 bolero 1359089016 0 Ullalu Road
preva1 bolero 1359089026 0 Ullalu Road

So here i need output table like below:

accountID deviceID from_timestamp to_timestamp diff
--------- -------- ---------- -------- -------------
preva1 bolero 1359088796 1359088888 92
preva1 bolero 1359088986 1359088988 2
preva1 bolero 1359088998 1359089006 8
preva1 bolero 1359089016

How to write mysql query for the above requirement.

View 3 Replies View Related

Select Query For Retrieving Data Using 3 Tables?

Apr 21, 2015

I want to retrieve the data from table "Document" and i need to check the below condition using 3 tables.


Here Project and Group is an another table.

Query : Select Document.Name from Document, Group, Project where
Document.ID = Group.ID and Document.ID= Project.ID.

is this right a way?

View 3 Replies View Related

Data Access :: How To Get Min Of More Than One Column In Select Query

Nov 24, 2015

I am using SQL select query to select MIN from column data but I have 12 columns and want to select MIN of all these 6 columns in one SQL select statement.

I have written as SELECT MIN(temp_sv,humidity_sv,SH1,SH2,SH3,SH4) from production but its not working... How to do this....

View 4 Replies View Related

Difference Between Select Query And SelectCommand In Data Adapters

May 27, 2008


Can any one tell me the difference between Select Query and
SelectCommand in data adapters?


Using SelectCommand:


string selectSQL = "SELECT CustomerID, CompanyName FROM Customers WHERE Country = @Country AND City = @City"; SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");SqlDataAdapter custDA = new SqlDataAdapter();           SqlCommand selectCMD = new SqlCommand(selectSQL, nwindConn);custDA.SelectCommand = selectCMD; // Add parameters and set values.selectCMD.Parameters.Add("@Country", SqlDbType.NVarChar, 15).Value = "UK";selectCMD.Parameters.Add("@City", SqlDbType.NVarChar, 15).Value = "London"; DataSet custDS = new DataSet();custDA.Fill(custDS, "Customers");  Using Select Query: 

da = new SqlDataAdapter("select P_UID,P_EMIAL,P_NAME from p_users where p_email=@p_email
and p_pwd=@p_pwd", con);

            da.SelectCommand.Parameters.Add("@p_email", SqlDbType.VarChar, 50).Value
= etxt1.Text;

            da.SelectCommand.Parameters.Add("@p_pwd", SqlDbType.VarChar, 50).Value = pwdtex.Text;   Can you plz tell vat is the need for giving custDA.SelectCommand and adding parameters to that command instead of  directly giving Select Query in Data Adapter?  Similarly  Update Command: custDA.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID") custDA.UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName")Dim myParm As SqlParameter = custDA.UpdateCommand.Parameters.Add("@OldCustomerID", _ SqlDbType.NChar, 5, "CustomerID")myParm.SourceVersion = DataRowVersion.Original[C#]custDA.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID"); custDA.UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName"); SqlParameter myParm = custDA.UpdateCommand.Parameters.Add("@OldCustomerID", SqlDbType.NChar, 5, "CustomerID");myParm.SourceVersion = DataRowVersion.Original; instead of above process, y cant v do above method in below procedure? 

private void UpdateStudent(int
RollNo, string SName, string
Course, DateTime Doj, Decimal


conn = new SqlConnection("Data Source=UMASHANKAR\SQLSERVER2005;Initial
Catalog=employee;Persist Security Info=True;User ID=sa;pwd=sa1 ");


cmd = new SqlCommand("UPDATE Student SET SName='" + SName + "',Course='" + Course + "',Doj='" + Doj + "',FeePaid='" + FeePaid + "'
WHERE RollNo=" + RollNo, conn);




(conn.State == ConnectionState.Closed)



        catch {










  IN this way I want difference OR ANY ADVANTAGES for InsertCommand, DeleteCommand also

View 2 Replies View Related

Copyrights 2005-15, All rights reserved