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
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
edwin
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
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 RelatedI'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.
I 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.
I 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()))
I 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] ....
I 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
I have a line chart which works great except when the x axis displays duplicate months. I have tried format codes of MM-YYYY and MMM and have the "numeric or time-scale values" checkbox checked. The data displays fine but the X axis in the case of the MMM format code displays as:
Sep Oct Oct Nov Nov
leaving the user to wonder where exactly does October and November start...
I've googled around but don't see off-hand what I am doing wrong. I am display 3 simultaneous lines if that matters...
Thanks!
Craig
I 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 Related
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
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
Hi,
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
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
201510
201509
201508
201507
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())
or
DATEDIFF(MONTH, my_date_column, GETDATE()) <= 3
but it doesn't work.
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
Edwin
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
January
February
March
How i can i do this
Have no index and can't define a index on this field
Thanks for ur time and Help
Regards
Mohd Sufian
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
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?
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 RelatedI 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 RelatedMy 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).
DATEDIFF(month,PAT_ENC_HSP_discharged.HOSP_DISCH_TIME,
GEtDate())
< 13
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
FROM (
VALUES (1)
[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.
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?
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
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,
Manny
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]))))
YoYDifference:=[YTDProjects]-[PYProjects]
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!
PrevProjects:=CALCULATE(SUM([Projects]),DATEADD(CalendarDate[FullDate],-1,QUARTER))
For some reason, this also returns blank in my model:
QTDProjects:=TOTALQTD(SUM('TrendData'[Projects]),CalendarDate[FullDate])
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
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]
)
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 RelatedHi 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 RelatedHi 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
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.