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 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 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 RelatedMy data has 2 fields: Customer Telephone Number, Date of Visit.
Basically I want to add a field ([# of Visits]), which tells me what number of visit the current record is within 6 months.
Customer TN | Date of Visit | # of Visits (Within 6 month - 180 days)
1111 | 01-Jan-2015 | 1
1111 | 06-Jan-2015 | 2
1111 | 30-Jan-2015 | 3
1111 | 05-Apr-2015 | 4
1111 | 07-Jul-2015 | 3
As you can see, the last visit would counts as 3rd because 180 days from 07-Jul-2015 would be Jan-8-2015.
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
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 RelatedHello
I had a requirement to filter records for the current year only... I went back to the user and explained that the logic wasn't sound... eg if somebody did the search early in January then they would probably get no results back. My suggestion was to go back two months if the current date is in January or february.
So, this is what I have.......
Code Snippet
where cs.startdate > case when year(cs.startdate) = year(getdate()) and month(getdate()) > 2 then
convert(char(4),year(getdate()))+'-01-01' else dateadd(month,-2,getdate()) end
Is there a neater/better way of coding this?
Jon
I'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 need to find out All mondays / Tuesdays etc dates for next 6 months from the current date.
I have a table called DayOfWeek and the records are
1Sun
2Mon
3Tue
4Wed
5Thu
6Fri
7Sat
so if I pass "1" to the stored procedure, I need to find all Sunday dates for the next 6 months
And so if I pass "2" to the stored procedure, I need to find all Monday dates for the next 6 months...etc
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 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
l want to run my procedure every month,but it must look at the 1st and the last day of the month.So that when l use my where clause l'll
say where 1st and last day between @startdate and @enddate. How do l do that?
collections_hexagon '2002-05-12','2002-05-12'
Alter Procedure Collections_Hexagon
(
@Startdate datetime,
@Enddate datetime
set @Startdate = 1stdayof currentMonth
set @Startdate = lastdayof currentMonth
)
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
Hi there everbody,
i am trying to get the last day of the Q + 1 month
the first part i have succeeded (dateadd(ms,-3,DATEADD(qq, DATEDIFF(qq,0,getdate() )+1, 0))) ,but how do i add 1 more month ?
would appreciate any suggestion
regards
Yoav
hi,
If today is the 22/4/08
how can I get the day last month,
eg. 22/3/08
?thank you
Hi,I have a simple table called events, which lists the start and end dates of events. I'm using a calendar control that queries the db for events, but at the moment it does a check for every day by passing in the day. (Very inefficient) What I'd like to do is pass in the current month, and get a set of rows that have an event which is in that month. It sounds easy, but I'm a little confused about what to do if the event starts current month -1 and ends currentmonth +1 I obviously need to return results like these also.
View 4 Replies View RelatedI am writing a usage query, I need to determine what the current month and year is. If the current month is 1, then I need Year-1. I keep getting this error Line 5: Incorrect syntax near '='.
If I comment out the case statement just evaluate the (YEAR(User_Date) = YEAR(DATEADD(yy, - 1, GETDATE())))or (YEAR(User_Date) = YEAR(GETDATE())) by itself, the query works. What I am missing? Anybody?1 SELECT CONVERT(char(10), User_Date, 101) AS userdate, COUNT(*) AS CNT, User_Name2 FROM Users3 WHERE (User_Name = 'Joe Bob') AND (MONTH(User_Date) = MONTH(DATEADD(mm, - 1, GETDATE()))) AND 4 case MONTH(User_Date) when 1 then 5 (YEAR(User_Date) = YEAR(DATEADD(yy, - 1, GETDATE())))6 else (YEAR(User_Date) = YEAR(GETDATE()))7 END8 GROUP BY CONVERT(char(10), User_Date, 101), User_Name9 ORDER BY CONVERT(char(10), User_Date, 101), User_Name
how would i take the query and show just the current months unique logins
SELECT DISTINCT YEAR(Login_Time) AS Year, MONTH(Login_Time) AS Month, COUNT(*) AS Login_Cnt
FROM Login
WHERE Login_Time >=#10/1/2005#
GROUP BY YEAR(Login_Time), MONTH(Login_Time)
ORDER BY YEAR(Login_Time), MONTH(Login_Time)
Hi, I'm dabbling in trying to retrieve some info from an SQL database and publish it in Sharepoint data view. I'm trying to create a View in the SQL database that retrieves all records for the current month. So that in July, it automatically shows me July records, and when August ticks over, it automatically displays August's records. I can hard code the dates in to the query no probs (e.g. a statement like ENTRYDATE >= '1/07/2007') but was hoping there is a way to specify current month. So maybe the statement looks like ENTRYDATE >= "CURRENTMONTH".
Thanks in advance!
I am trying to get a query that subtracts a month and a day from current date in SQL Server.
Currently I have SELECT DATEADD("MM", -1,GETDATE()) which subtracts one month from the date but I want to subtract a month and a day from date.
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 RelatedHi,I've a small problem. I have a table in which one column is date. I want tocount the records for statiscs in a temptable grouped by months lets say 12months back.e.g.month 1 counts 164 rec month 2 counts 87 records and so on.I tried to solve this like this with a function SELECT COUNT(*) FROM TABLEWHERE DATEDIFF(m,Col1,GETDATE())=@counter.But I don't know how to get this thing count from 0 up to 11 to get thisthing recursive.Does anyone know how to tackel my problem? I wouls apreciate any answer.Greetz to you all
View 4 Replies View Relatedwhat would be the right way to get results only from 3 months and on. I want to get an avg from the last 3 months.
SELECT Server_Description, Database_Description, AVG(Database_Size_Mb) AS MonthlyDBTotal
FROM dbo.Tbl_Database_Statistics AS Tbl_Database_Statistics_1
WHERE (Database_Size_Datetime = DATEADD(mm, DATEDIFF(mm, - 0, GETDATE()), - 3))
GROUP BY Server_Description, Database_Description
I have a table thats updated daily with monthly data totals:
Month, Total orders1, Total orders2, etc
12/01/2012, 5, 8, etc
11/01/2012, 6, 5, etc
How do I pull data from this table in SQL Server for ONLY the current month? I was thinking using the getdate() function to get the current month, but it doesn't match exactly so I get no results
I need to get previous month data in the table based on current date.
In case of execution of each month, the data for previous month should come with date as between
create table TestDate
(Sno Int,
Name varchar(100),
DateofJoin datetime)
insert into TestDate values (1,'Raj', '2/21/2014')
insert into TestDate values (1,'Britto', '6/12/2014')
insert into TestDate values (1,'Kumar', '5/14/2014')
insert into TestDate values (1,'Selva', '6/27/2014')
insert into TestDate values (1,'Ravi', '5/2/2014')
insert into TestDate values (1,'Gopu', '6/2/2014')
/*
if I execute in month July ( ie: today)
select * from TestDate where dateofjoin between 1-june-2014 and 30-june-2014
Result
5 Ravi 2014-05-02 00:00:00.000
3 Kumar 2014-05-14 00:00:00.000
if I execute in month June
select * from TestDate where dateofjoin between 1-may-2014 and 30-may-2014
Result
6Gopu2014-06-02 00:00:00.000
2Britto2014-06-12 00:00:00.000
4Selva2014-06-27 00:00:00.000
/*
I have the following code block
CREATE TABLE #tbl_1 (event_time DATETIME2, SID INT ,NAME VARCHAR(20) )
INSERT INTO #tbl_1 VALUES ('2015-08-27 13:47:24.123','150','abc')
INSERT INTO #tbl_1 VALUES ('2015-09-27 13:47:24.123','149','acb')
INSERT INTO #tbl_1 VALUES ('2015-10-27 13:47:24.123','148','cba')
CREATE TABLE #tbl_2 (event_time DATETIME2, SID INT ,NAME VARCHAR(20) )
INSERT INTO #tbl_2
SELECT * FROM #tbl_1 where ? SELECT * FROM #tbl_2
My requirement is to insert values into #tbl2 that are in current month which are event_time values '2015-08-27'
Here is my query
SELECT id, (CONVERT(datetime, event_date)) AS event_date, xmlfilename, event_active FROM test WHERE (YEAR(event_date) >= YEAR(GETDATE())) AND (MONTH(event_date) >= MONTH(GETDATE())) AND (DAY(event_date) >= DAY(GETDATE())) ORDER BY event_date ASC
The above query shows me event only upcoming event from the current month.
I want to list all the events happening today and future. I do not want to show past event. How to do this?
My case is I have customers with multiple ordering dates, I'll to run a query to pull only records within 2 months period based on the previous picking records. Ex is below:
create table #tmp_CusttInfo
(patID varchar(20),
enc_date datetime)
go
insert into #tmp_CustInfo
select '111','2015-01-01 09:25:05.000'
[Code] ...
I'd like to get:
CustID OrderDate
111 2015-01-01 09:25:05.000
111 2015-03-01 09:25:05.000
111 2015-05-01 09:25:05.000
222 2015-01-01 09:25:05.000
222 2015-03-01 09:25:05.000
222 2015-05-01 09:25:05.000
Is that possible.
I have a SSRS report using 2008 R2. It prompts the user for the start and end dates. This all works. But now I want the start date parm to default to the first day of the current month and the end date parm to default to the last day of the current month.In the new query window in SQL Server Management Studio, I can run this chunk of code to get the first day of current month:
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
And this code to get the last day of current month:
SELECT DATEADD(DAY, -(DAY(DATEADD(MONTH, 1, GETDATE()))),
DATEADD(MONTH, 1, GETDATE()))
But I don't know how to do this in SSRS 2008. How can I make my start / end parms to get these values.
I need to calculate “NET_SALES” and “MARGIN_PERCENT” for each month of the current year … the following returns the same values for each month in the list, which are for the current month. Taking out the GROUP BY line works fine for an overall number.
SALES_MONTH, NET_SALES, MARGIN_PERCENT
January, 1246627.69, 24
February, 1246627.69, 24
March, 1246627.69, 24
-------------------------------------------------
DECLARE @NetSales DECIMAL(18,6)
DECLARE @Cost DECIMAL(18,6)
SELECT
@NetSales = sum(IL.MERCHANDISE+IL.TAX)
,@Cost = sum(IL.COST)
FROM INVOICELINE IL
[Code] .....
I'm trying to create a WHERE statement that will calculate values from our current fiscal year to the last complete month.I'm using code that was created for us that does the calculations for our entire fiscal years. I thought I had fixed the WHERE statement to work like we wanted last year, but it appears to be broken now after trying it again in January and February. I'm guessing my WHERE statement only works for March and up, but how to get it to work for every month. Most attempts I'm trying it's just returning very large and inaccurate values.
I included my WHERE statement below of what I originally had that worked last year. The @BeginYear/Month/etc are retrieved from a different table and @Month is just set to MONTH(GETDATE())-1.
WHERE
(YEAR(SA3.DocumentDate)=@BeginYear AND MONTH(SA3.DocumentDate)>=@BeginMonth AND MONTH(SA3.DocumentDate)<=@Month)
OR
(YEAR(SA3.DocumentDate)=@EndYear AND MONTH(SA3.DocumentDate)<=@EndMonth AND MONTH(SA3.DocumentDate)>=@Month)
I can't seem to upload images so here's the link: [URL] ....
I need to get the last value of # Orders week avg in the current context (the highlighted value). The calendar dimension here is a week level one so I don't think I can use LASTNONBLANK. The measure is from the facts_sales table which is linked to a calendar dimension via a date key.
I am trying to use one dataset rather than two and was hoping to then filter the data via a table or matrix.
This is my dataset
SELECT
Practice.ibvStaffCategorisation.StaffId
,SUM(Practice.ibvStaffTotalsCL2Y.ChargeableMinutes) AS Sum_ChargeableMinutes
,SUM(Practice.ibvStaffTotalsCL2Y.NonChargeableMinutes) AS Sum_NonChargeableMinutes
,SUM(Practice.ibvStaffTotalsCL2Y.ChargeableAmount) AS Sum_ChargeableAmount
,SUM(Practice.ibvStaffTotalsCL2Y.NonChargeableAmount) AS Sum_NonChargeableAmount
,Practice.ibvStaffTotalsCL2Y.Period
[code]....
I would like to display two rows of data for each StaffId one representing the current period and the other all periods to date so the table would look something like below.
StaffId | Non Chargeable Time | Chargeable Time
CJJ | 0:20 | 4:20
| 4:50 | 19:20
JN | 0:05 | 5:30
| 1:30 | 25:30
The above shows two separate StaffId figures the first line for each shows non chargeable and chargeable for the current period and the second line a total of all periods in that year.
I have managed to get the first row to display only figures from the current period by using a filter however it also applies the same filter to the second row in the group. I have also tried to group the rows but am drawing a blank.