First Day Of Month Of 3 Months Ago
Jul 20, 2005How can I get the date of the first day of the month of
3 months ago in one statement ?
(And with no time in it ??)
Arno de Jong, The Netherlands.
ADVERTISEMENT
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
Get Last Day Of Each Month For Months Between 2 Dates
Sep 14, 2012I'm trying to write some code that will get me the last day of each month for the months that fall between 2 dates.
So if i have a table with a record with a begin date of 01-01-2012 and an end date of 09-14-2012, i would want a result set of the following 9 records:
01-31-2012
02-29-2012
03-31-2012
04-30-2012
05-31-2012
06-30-2012
07-31-2012
08-31-2012
09-30-2012
So i basically get the last day of each month between the dates listed, including the months that the dates are in themselves (inclusive list).
I have a date dimension table with 1 row for every day from 1990 to 2025. There is also a field with the Last day of Month for a given date. My issue is that I'm not sure how to write the T-SQL to get what I need.
Get Current Month And Previous 2 Months
Feb 10, 2014I have table 'Open_Months' as shown below
Emp_IdMonthYear
00FBG 42013
I need current month and previous 2 months, for this i have concatinated month and year column into date formate, the query for this as shown below
SELECT cast(CONVERT(datetime, CONVERT(varchar(4), OM_Year) + RIGHT('0' + CONVERT(varchar(2), OM_Month), 2) + '01', 112) AS Datetime) from Open_Months where OM_Emp_ID = '00FBG'
Date
2013-04-01
From the above date I need to find current month and its previous 2 months.
Get Records From Last 3 Months But Without Current Month
Apr 3, 2014I have a problem with a date in my sql view, I need the records from the 3 last months but without the current month, if I execute my view right now I have the records from January to april but I just need from January to march, I must have always the 3 previous month but I don't know how I can do it
(dbo.frhkrg.fakdat >= DATEADD(MM, - 3, GETDATE()))
Need Report Also Showing Months Even When No Data For That Month
May 13, 2008Hi 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
edwin
How To Show 6 Previous Months In A Table Based On An Input Month
Nov 3, 1999I do appreciat your help, I want to run a store procedure which will show 6 months . I do not know how to write the procedure, here in the notion in my mind, I want to be able to pass an input parameter (month) to the procedure which will then run a query to show 6 months prior the input parameter month, how can I do that, thanks for your help
Ali
Analysis :: Dynamic Set Needed For Last 12 Months Based On Current Month Selected By User
Sep 30, 2015I need to create a set so that when a user selects a month in filter (say 201506) then it should give me a list of months from 201406 to 201506. Any appropriate MDX query.
View 7 Replies View RelatedSQL Server 2012 :: Select Query - Get Result As Month And Values For All Months Whether Or Not Data Exists
Jul 27, 2015I 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
T-SQL (SS2K8) :: Use Previous Month Data In Column As Following Months Data Different Column
Aug 20, 2014I have a table with Million plus records. Due to Running Totals article, I have been able to calculate the Trial_Balance for all months.
Now I am trying to provide a Beginning Balance for all months and the Logic is the Beginning Balance of July would be the Trial_Balance of June. I need to be able to do this for multiple account types. So the two datasets that need to be included in logic is actindx and Calendar_Month.
For actindx of 2 and Calendar_Month of 2014-01-01The Trial_Balance_Debit is 19585.46 This would make the Beginning_Balance of actindx 2 and Calendar_Month of 2014-02-01 19585.46
I am trying to do some type of self join, but not sure how to include each actindx number differently.
Table creation and data insert is below.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TrialBalance](
[Trial_Balance_ID] [int] IDENTITY(1,1) NOT NULL,
[Code] ....
Parameter Selection Of Month, Showing Selected Month And Sum Up To That Month In Another Row
Apr 5, 2008Hello what I'd like to display the following in a matrix report:
Parameter selected: 3 (March), 2008 (Year)
Monthly TO Summed up
ArtNo March <=March
1210 20,500 50,900
1220 21,200 64,000
1230 15,400 40,300
... ... ...
So, in the rows I have the articles and in the column the selected month via parameter. In another column I need to sum up all monthly values up to the selected month, meaning in this example the sum of jan, feb and mar per article.
T-SQL (SS2K8) :: Get Last Record In A Month When No Guarantee Month Exists Of Unique Dates?
Apr 22, 2015following table global_usage
ID varchar (contains alphanumeric values,not unique)
Territory (combined with ID unique)
Total_Used int can be null
Date_ date (date of the import of the data)
ID Territory Total_Used Date_
ACASC CAL071287 2014-06-01
ACASC CAL071287 2014-08-01
ACASC CAL071288 2014-09-01
[Code] .....
Now the problem,per month I need the most recent value so I'm expecting
ACASC CAL071287 2014-06-01
ACASC CAL071287 2014-08-01
ACASC CAL071288 2014-09-01
ACASC CAL071288 2014-11-01
ACASC CAL071190 2014-12-14
ACASC CAL071286 2015-01-22
ACASC CAL071165 2015-02-01
ACASC CAL071164 2015-03-01
I've tried a few thing like group,having even row_number() but I keep getting wrong results
Transact SQL :: Show (0) Amount For A Month If No Data Exists In The Table For That Month?
Nov 9, 2015I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.
I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount
My Desired output
My current query
SELECT
RG.Id AS Id,
RG.Name AS Name,
ISNULL(SUM(AC.Amount), 0) AS Amount,
RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]
[Code] ....
T-SQL (SS2K8) :: Convert Number Of Month In Integer Into How Many Year And Month
Sep 10, 2014This is my table and data
CVID | WorkExperience
--------------------------------
2838736
68181101
96568122
1135484
I need to convert into this result
CVID | WorkExperience
--------------------------------
283873 years
681818 years 5 months
9656812 years 2 months
1135484 months
Transact SQL :: Displaying Sales Data In A Month By Month Grid
Aug 11, 2015Most of the data is in one table.Â
Company 1-Jan 1-Feb 1-Mar 1-Apr
RSP RSP RSP RSP
NON-RELO $295 1 $0 0 $1,400 7 $0 0 $1,195 4 $0 0 $4,700 8 $0 0
AMERICAN ESCROW & CL//AECC $2,650 4 $0 0 $3,720 8 $0 0 $2,339 4 $0 0 $2,460 2 $0 0
American Internation//AIRCO $9,131 30 $2,340 9 $10,927 35 $2,340 9 $9,142 31 $2,600 10 $18,406 54 $3,900 15
American Internation//AIR $20,611 63 $1,820 8 $23,892 75 $1,040 4 $35,038 111 $3,120 12 $3,778 16 $1,560 6
American Internation//Ab $64,248 206 $6,240 24 $59,800 187 $5,200 20 $87,115 264
I did something similar doing just record counts but this is far more complicated. I'm at a loss that this is even possible.
 SUM(CASE datepart(month, tbFile.openedDate) WHEN 1 THEN 1 ELSE 0 END) AS 'January',Â
Get Last Day Of Month For Agiven Month And Year
Aug 2, 2002Does anyone know how I can get last day of month
if I pass a function a given month and and given year.
@Month = 2
@Year = 2004
The result I would need is 29 because there are 29 in
the month of February in the 2004.
Any help on this is greatly appreciated.
Kellie
SQL 2012 :: Use Date Trunc Or Date Function To Select Date Range For Month On Month View
Jul 29, 2015My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table
Month-to-month Function
Sep 10, 2007Hi I trying to find a way to determine the number of working days per month starting from the current date to the last day of the current month.And within the same store procedure determine the number of working days as normal (each month is independent from the next). For example: The store procedure is executed
September:
@CurrentDate = 9/10/2007
@EndDate = last working day 9/30/2007
Total# of working days = 15
October:
@CurrentDate = 10/1/2007
@EndDate = last working day 10/31/2007
Total# of working days = 23
November:
@CurrentDate = 11/1/2007
@EndDate = last working day 11/30/2007
Total# of working days = 22
etc.
Any ideas of how i can approch this?
Thanks in advance.
Grouping My Month/year Desending By Month/year
Dec 11, 2006i have some classes that I want to group by month/year (note:i dont need the day of the month)
how do i wirte my sql so it only gives me the dictinct groups month/year of the classes I have so that it comes out like so..
11/2006
12/2006
1/2007
3/2007
i try with my sql below but i cant get the groups th come out in order. i dont think it sees it as a date value.
dbo.classgiven.classdate date of the class.thank you all
SELECT DISTINCT { fn MONTH(dbo.classgiven.classdate) } " + "/" + "{ fn YEAR(dbo.classgiven.classdate) } AS monthyear,{ fn MONTH(dbo.classgiven.classdate) } AS monthcode FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL)", conNorthwind )
How To Get Months
Nov 20, 2006I 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
3 Months Lag
Mar 21, 2008I 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]
)
Months Between
Jan 23, 2008Hi, 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 RelatedHow To Get Date From 3 Months Ago
Oct 10, 2006Hi 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 RelatedHow To Get Months And Year
Nov 20, 2006Hi 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
How Do I Pass In # Of Months
Aug 3, 2007I 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.
Retrieving By Months Only
Sep 16, 2004I 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.
12 Months Back
Jan 18, 2005SELECT 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
Query For Due Months
May 13, 2008Hi All,
I need to display the rent not paid months in the year. How can I form the query?
Help me.
Kamal.
Months Between 2 Dates
May 24, 2008Hi 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...
Ordering By Months
Jun 9, 2004I 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
Previous Months
Mar 19, 2007I 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.
Group By Months
Jun 24, 2007Hi 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."
Months As Columns
Dec 14, 2007Hi 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