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
I have a time dimension that consists out of timestamp fields. Now I like to extract a value for a given month e.g. 8 for August, 10 for October and such. How would I do that?
[Time].[STARTDATE].LastChild holds this value: 2007-10-01T00:00:00
I need to store 01-jan, 02-Feb ----- 12-Dec in another column which is extracted from the date column in the same table can someone tell me how to do this I need to havem month number as 01 not simply 1.
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:
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.
need help i have table employee and the employee insert into table the holidays the date start >>>> to date end now i need to create a view only for next moth , in this view i need to see only the relative dates for the next month
I have a table that i want to query and group the records by themonth, what SQL stanment would i use?Somthing like this? apart from i know that the "Group BY (MM/YYYY)"wont work !:)Select Date,NameFROM TableGroup by (MM/YYYY)Does this make sense?Willa
hii i am having a table which contains fields like projectid projectname,projectcode,startdate,enddate etc.. my problem is to show no of months and years between each project in a datagrid..i need to use sql quires...table dont hvae month ,year field...if anybody can help me.. it will be agreat great help.. thanks in advance.. binoy
I have a CheckDate field and I only what to retrieve dates that fall in the month previous to when the query is run. I'm assuming it will involve DATEADD or DATEPART, but I'm not sure how to do it since the end of the month day will vary from month to month.
For example, today is 04/09/2008 so I want to retrieve checks with a date between 03/01/2008 and 03/31/2008.
I am trying to determine the amount of days in a month to prorate a month end estimate. We measure service calls and need to approximate how many we will have at month end. I would like to automate a query to post on our web and need to know how many days are in the current month.
A possible solution would be to piece together a datetime variable using getdate and dateadd then use a datepart. However , I don't know how to create a datetime variable this way.
I have a program that calls queries (OLAP system) the system includes a dimension of date: Year, Quater, Month, Week
When the result appears in the table, it is not in order? Only the year is in oredr and after that each heirachy is wrong and not in order....not sure how to do this!!!
any help would be grateful!!! not sure what I should be looking at.....
Hi all I this stored procedure is suppose to turn give the users the ability to put the month and date in so they can see how many each inspector did for that month and year.
Create Procedure LookupYearMonth AS SELECT I.[Last Name], I.[First Name], [Date by Month] = DATENAME(month, MT.[Date] + ' ' + DATENAME(year, MT.[DATE]), [Count Of Main Table] = count(*) FROM Inspectors I INNER JOIN [Main Table] MT ON (I.ID = MT.Inspector) GROUP BY I.[Last Name], I.[First Name], DATENAME(month, MT.[Date] + ' ' + DATENAME(year, MT.[DATE]) WHERE (MT.[Date] >= CONVERT(datetime, @EnterMonthYear)) AND (MT.[Date] < DATEADD(month, 1, CONVERT(datetime, @EnterMonthYear)))
I have two parameters in my report (StartDate and EndDate). I want to default these parameters to the previous month.
For example... If today is 5/17/2007, I want StartDate to be 4/1/2007 and EndDate to be 4/30/2007. If today would be January 30th 2007, I would want StartDate to be 12/1/2006 and EndDate to be 12/31/2006.
I am trying to use the custom code in the report but I don't think I am understanding how this is being used.
I have a function to get starting months for a report parameter. The function is below:-
-------------------------------------------------------------- Shared Function GetStartingMonths() as String dim strDefault as string dim CurrentMonth as String Dim SqlString as String
'strDefault = Month(Now) & "/1/" & Year(Now) CurrentMonth = "5/1/2002" Do While CDate(CurrentMonth) <= Now SqlString = SqlString + "Select " & CurrentMonth & " as value, " & MonthName(Month(CurrentMonth)) & " " & Year(CurrentMonth) & " as MonthYear" CurrentMonth = dateadd("m",1,CDate(CurrentMonth))
if Cdate(CurrentMonth) = Now then Exit Do else sqlString = SqlString & " Union " end if
Loop
return SqlString
End Function --------------------------------------------------------------- what i am trying to do here, and hopefully produce a sql string that would fill my dataset of dates and their representation.
In the dataset, I had put the following expression =Code.GetStartingMonths()
However, I can't seem to get the parameter to display the dates. shows up as disabled in my report.
Am I doing something wrong here or is there a better way to doing this ?
Additionally, I was wondering whether there is a better SQL code that would achieve the same thing I am doing ?
I have a date dimension set in the SSAS Cube. I have been trying get quarter,year,month,semester start and end dates using ClosingPeriod() and OpeningPeriod() functions but not getting the exact value. How the get correct dates for a given date.
Hello All, i have three textboxes in a page and i want fill those textboxes with the date, month,year respectively..... i have a datecreated column in discount table in a mm/dd/yy format ...how to extract the date, month, year from this format and put the value in textboxes..? Any help.. Thanks.. Anne
I'm using SQL Server 2012 and I need to run a query against my database that will output the difference between 2 dates (namely, DateOfArrival and DateOfDeparture) into the correct month column in the output.
Both DateOfArrival and DateOfDeparture are in the same table (let's say GuestStay). I will also need some other fields from this table and do some joins on some other tables but I will simplify things so as to solve my main problem here. Let's say the fields needed from the GuestStay table looks like below:
I need my query to output in the following format:
Hello 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.
I 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]
How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?
Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U')) DROP TABLE [dbo].[temp_tbl] GO CREATE TABLE [dbo].[temp_tbl]( [tbl_id] [bigint] NULL, [cs_id] [int] NOT NULL, [USERID] [int] NOT NULL,
Does 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
I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.