Last 3 Months Query

Jan 31, 2008

Hi,

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





Thx.

View 4 Replies


ADVERTISEMENT

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.

Kamal.

View 7 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?
thanks

View 5 Replies View Related

Rolling 13 Months In Query

Jun 23, 2008

Hi,

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

Select Query To Get Last 6 Months Data

Sep 16, 2004

Hi,
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.

eg: Jan...data
feb...data

TIA,
Ravi

View 1 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

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

thanks for the help.

View 3 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







Customer
pncount
pn%
monthval

ABC
5
30
01/01/2007

DEF
9
20
01/01/2007

GHI
4
40
01/01/2007

ABC




DEF
4
90
02/01/2007

GHI
7
10
02/01/2007

ABC
4
5
03/01/2007

DEF




GHI
6
100
03/01/2007

but I need this









01/01/2007
01/01/2007
02/01/2007
02/01/2007
03/01/2007
03/01/2007

abc
5
30


4
5

def
9
20
4
90



ghi
4
40
7
10
6
100


Any Idea Help

View 13 Replies View Related

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

Jan1
Febnull
Marnull
Aprnull
May10
Junnull
Julnull
Aug20
Sepnull
Octnull
Nov30
Decnull

View 9 Replies View Related

How To Get Months

Nov 20, 2006

I need to get all the data based on the user selection of the months. So, I will provide a dropdown will all list of the months. When user pick the month, for example January, then I will show all the invoice on January. How to get the month of the year in the where clause in T-SQL?
 TIA

View 7 Replies View Related

3 Months Lag

Mar 21, 2008

I have a question i am confused very much with i am doing on MTD Average , i really need some help with code.

the logic behind the code is



3 MTD should be the past two completed months as well as the dates in the current month as well. So taking the example above – if the reporting period for Feb was 01/30/2007 to 02/27/2007, and March was 02/28/2007 to 03/27/2007, the 3 MTD should then be the average from 01/30/2007 to 04/10/2007.



This code does not actually gives me the exact value, please advise on any correction to the code , or a new way to write the code.



Avg
(
[Time].[Month].CurrentMember.Lag(3) :
[Time].[Month].CurrentMember,
[Measures].[A1_Avg]
)

View 4 Replies View Related

Months Between

Jan 23, 2008

Hi, I am trying to determine the amount of months between today and adate stored in the database. But I cannot seem to figure out get thedifference between the two dates. Thanks in advance.

View 2 Replies View Related

How To Get Date From 3 Months Ago

Oct 10, 2006

Hi all,How to get the date from the 3 months ago? So, if user enter a date (10/10/2006), how can I get to 07/10/2006.Thanks in advance...

View 3 Replies View Related

How To Get Months And Year

Nov 20, 2006

Hi All,
I provided the client with the dropdownlist which lists all the months (january - december). This input will be used to filter in the invoiceDT.
How can I write the query where clause to compare the invoiceDT (mm/dd/yyyy) into this input which is only number 1 through 12?
TIA

View 1 Replies View Related

How Do I Pass In # Of Months

Aug 3, 2007

I have a stored procedure that I want to pass in a number of months and use it in my where caluse. I need to minus the number of months that as passed in by @TaskMonths.
But it is minusing days not months.
@TaskMonths intWHERE (tblTasks.Caller = @Caller) AND (tblTasks.DueDate BETWEEN CONVERT(varchar, GETDATE() - @TaskMonths, 101) AND GETDATE()) I apprecaite any help.

View 2 Replies View Related

Retrieving By Months Only

Sep 16, 2004

I have records in a table and 1 column is in the smalldatetime format which stores the date in the format "2004-09-22",2004-09-20",2004-09-12",2004-08-04" etc etc.

Can anyone tell me how to craft an SQL statement so that i can retrieve records for a certain month.For example,if i want to retrieve records for the month of September,i would get "2004-09-22",2004-09-20",2004-09-12" in results.

View 1 Replies View Related

12 Months Back

Jan 18, 2005

SELECT K.TRANS_TYPE,K.COMPANY,K.DIVISION,K.SALES_FORCE,K.SALES_LINE,K.SALES_REGION,K.SALES_ZONE,K.FORECAST_ DATE,C.STATISTICS_PERIOD,K.PRODUCT_NUMBER,
K.CUSTOMER_CODE,K.WAREHOUSE,0,0,0,K.FORECAST_QTY
FROM KPI_SIGNOFF AS K inner join ROUNDED_CALENDAR as C on K.FORECAST_DATE
between C.START_DATE and C.END_DATE


Hi this is the code that I am using to populate a table let us call new_table.
However what I would like to do is get the max forecast date and get the statistics_period for that max forecast date.

Once I get that I would set that as my current statistics period.

Then I want to create a new column in the same new_table call stat_period-1, which would be the one period back.

Please note that the statistics period is like the months 1 – 12.
Does not go higher that 12 and does not go lower than 1.

So for stat_period-1 when statistics period is equal to 1 it would have to be set to 12.

This is what I want but do not know how to place this into the code, please help me with this.
THanks

View 10 Replies View Related

Months Between 2 Dates

May 24, 2008

Hi Friends,

i need a Month name between 2 dates...

Example :

If i give start date(MM/dd/yyyy) is 03/20/2008
and End Date(MM/dd/yyyy) is 05/24/2008)

The Output Should be

March,
April,
May

How can i get this...

Thank you...

View 1 Replies View Related

Ordering By Months

Jun 9, 2004

I am wondering if someone maybe able to help me, I am needing to order my data via months in the calendar sense not alphabetically, below is what I currently have, but it only does it alphabetically.

select to_char(created,'yyyy-Mon'), matdesc, count(*)
from test
group by to_char(created,'yyyy-Mon'), matdesc
order by to_char(created,'yyyy-Mon') desc

any help would be greatly appreciated

Thank you

Stephen

View 11 Replies View Related

Previous Months

Mar 19, 2007

I have a View there I need my criteria to select 13 months from previous month.

Example. This is 3/1/2007 - I need to query 2/1/2007 to 2/1/2006.

View 7 Replies View Related

Group By Months

Jun 24, 2007

Hi all.
I have probs on grouping by...

Sample data...
table - hrEmpPayrollSummary

empno period1 period2 pagibig_share
1 1/1/2007 1/15/2007 2000
1 1/16/2007 1/30/2007 5000
1 2/1/2007 2/16/2007 6000
2 1/1/2007 1/15/2007 8000
3 1/16/2007 1/28/2007 9000
3 12/1/2007 1/15/2007 10000
3 12/1/2007 12/15/2007 10000

expected result...
result for the year 2007

empno january february march april may june july august september october november december

1 7000 6000 0 0 0 0 0 0 0 0 0 0
2 8000 0 0 0 0 0 0 0 0 0 0 0
3 9000 0 0 0 0 0 0 0 0 0 0 10000

-Thanks





-Ron-

"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."

View 13 Replies View Related

Months As Columns

Dec 14, 2007

Hi i need a sql stored procedure below, this returns 2 columns of data, what i now need is to display the months as columns

ie.
TotalQty - OL_St_Code - Jan - Feb - March

then each month column shows a TotalQty for that month, do i need a query of query? if so how would i go about this


select count(OL_Qty)as TotalQty, OL_St_Code
from dbo.X_TBL_ORDER_LINEWEB LW

LEFT JOIN dbo.X_TBL_ORDERWEB OW ON OW.O_ID = LW.OL_O_ID

WHERE MONTH(O_Date) Between '11' AND '12'
group by Month(O_Date), OL_St_Code

View 19 Replies View Related

First Day Of Month Of 3 Months Ago

Jul 20, 2005

How can I get the date of the first day of the month of3 months ago in one statement ?(And with no time in it ??)Arno de Jong, The Netherlands.

View 1 Replies View Related

Data For Last 13 Months

May 8, 2008



Hi,

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


Thanks

View 1 Replies View Related

Use DateDiff To Get Age In Months

Apr 16, 2008

Hi, I've just noticed that there is a proc in my database using a person's age in a where clause but it seems to be rounding the age. The idea is select records where the age (using the person's birthdate) is between @xMonths and @yMonths.




Code Snippet
select datediff(month, '4-30-2006', '4-1-2008');





Result: 24

If 4/30/06 is the birthdate and 4/1/08 is today, it needs to be 23 months. Can someone explain how it determines the month boundaries and how to exclude partial months?

View 7 Replies View Related

Calculate Elaped Months

Apr 3, 2008

I have a start date saved and an end date saved but when I query I only want to return the amount of months that have elapsed between the two, any idea?

View 4 Replies View Related

Getting Months From Daily Items

May 9, 2008

I have a database with multiple items each day. I'm looking to extract monthly information from this data. So I'm thinking that I would like to have a drop down list with each available month in the list. How would I extract which months (for multiple years) have data from a datatype = smalldatetime?
I'm using c#
 Thanks!

View 9 Replies View Related

How To Include Months Not In The Records??

Oct 19, 2005

Hi,I have 2 tables created. One is the Sale Persons (PersonID, PersonName) and the other is the Sales Detail (PersonID, Month, Year, TotalSales)PersonID | PersonName                  PersonID | Month | Year | Total Sales==================                ==================================ID1         | Sally                              ID1         | 1         | 2005 | 1000ID2         | David                             ID2         |  2        | 2005  | 1500Is it possible to write in a SQL statement to return all the sales person & the total sales for the 12 months for a particular year (even though some month data are not in the table)I would like the result to be like the following:PersonID | Month | Year  | Total Sales===================================ID1         | 1         | 2005  | 1000ID1         | 2         | 2005  | 0ID1         | 3         | 2005  | 0(For month 4 - 12) Total Sales will be 0 too as no records exist in the first placeID2         | 1         | 2005  | 0ID2         | 2         | 2005  | 1500.......... 

View 2 Replies View Related

How Calculate 18 Months Past ?

Nov 13, 2000

I have a database of the table payments
and I want to create another table with
the olds payments (18 months past)
a have a field with the payment date
date_pay.
How I can do that?

Thanks

Luiz Lucasi

View 2 Replies View Related

Listing All Months Regardless Of Values

Jun 15, 2005

I have written the following query which returns the number of orders received grouped by the year and month:
SELECT DATEPART(yyyy, order_placeddate) AS year, DATEPART(mm, order_placeddate) AS month, count(order_id) AS orders
FROM orders
GROUP BY DATEPART(yyyy, order_placeddate), DATEPART(mm, order_placeddate)
ORDER BY year, month
year month orders
---- ----- ------
2004 6 17
2004 7 37
2004 8 30
2004 9 42
2004 10 34
2004 11 46
2005 1 25
2005 2 7
2005 4 1
The obvious problem with the above is that it misses out the months that have no orders, i.e. December, March, May, etc.

Is there a way I can amend my query so that it shows all months regardless of whether any orders were placed?

I have thought about trying to LEFT OUTER JOIN the above to a table that has rows with values of 1 – 12, but I’m not convinced this is the answer... and I don’t really know how to do it!

Do let me know if any of the above is unclear – what I’m after is the following:
year month orders
---- ----- ------
2004 6 17
2004 7 37
2004 8 30
2004 9 42
2004 10 34
2004 11 46
2004 12 0
2005 1 25
2005 2 7
2005 3 0
2005 4 1
2005 5 0
Many thanks

View 1 Replies View Related

SELECT Where Date &>= 14 Months Ago

Nov 29, 2004

Hi,

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?

Thanks

View 1 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

Dave

View 3 Replies View Related

Date Difference In Months

Feb 24, 2006

Hi to all,

I want to make an sql query that will subtract two dates and the difference will be number in months.

i have this table TestTable with Fields ACost, Adate, AMonth

my formula is this X = (Now-Adate), should give me number in months
and Y = (ACost/AMonth)
i want to get the product of X and Y, (X*Y).

how can i make this in sql views?

can anyone help me with this?

thanks a lot!


best regards,

saldiboy

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved