Relative Dates For The Next Month-help
Apr 18, 2008
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
"tb_all_holiday before"
date_start date_end
----------------------------------------------------------
15/03/2008 00:00:00 17/09/2008 00:00:00
20/04/2008 00:00:00 12/05/2008 00:00:00
i must to covert it like this
to this - see relative dates for the next month
"VIEW_all_holiday after -next month only "
date_start date_end
----------------------------------------------------------
01/05/2008 00:00:00 31/05/2008 00:00:00
01/05/2008 00:00:00 12/05/2008 00:00:00
TNX for help
View 2 Replies
ADVERTISEMENT
Apr 22, 2015
following 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
View 6 Replies
View Related
Dec 4, 2007
hii all
i am havie a database with given fileds like projectid,projectcode,projectname,startdat,enddate,etc....
my problem is to show all months and year between each projects startdate and enddate in a datagrd..and the table dont have month, year field..
i need to use sql quiery..
if anybody can help me in this matter.. it will be greatful..
thanks in advance..binoy
View 3 Replies
View Related
Sep 14, 2012
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.
View 4 Replies
View Related
Jul 20, 2005
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
View 1 Replies
View Related
Dec 4, 2007
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
View 1 Replies
View Related
Oct 10, 2013
I need to retrieve the last record for each month between two given dates from a unique table that contains on record per day.
View 3 Replies
View Related
May 22, 2008
hello, how can I query dates using "between" function but grouped by months? for example:
QUERY:
FROM: 15/DIC/2007 TO: 15/FEB/2008
RESULT:
DECEMBER-2007 --- $49,535
JANUARY-2008 --- $45,352
FEBRUARY-2008 --- $52.345
Thanks in advance-!
View 4 Replies
View Related
Apr 9, 2008
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.
View 13 Replies
View Related
Nov 28, 2007
I have one SQL Table with 2 columns as below
Column1: ProductionDate - DateTime - Not NULL
Column2: Quantity - Int - Not NULL
Now There are 2 Records in Table
1-1-2007, 5
1-3-2007, 7
Output of Result should be as below
1-1-2007 5
1-2-2007 0
1-3-2007 7
1-4-2007 0
1-5-2007 0
1-6-2007 0
.
.
.
1-31-2007 0
Means Query should return all the dates of Month with Quantity and if no entry in Table then 0 for Quantity.
How to Do it? Please suggest with Query
View 5 Replies
View Related
Mar 28, 2000
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.
Thanks in advance
View 1 Replies
View Related
Feb 28, 2005
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.....
View 14 Replies
View Related
Apr 2, 2007
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)))
GO
View 14 Replies
View Related
May 17, 2007
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.
How can I do this?
View 2 Replies
View Related
Jan 23, 2015
How to retrieve only Monday dates from each month in sql server.
If i pass any value (Let say GETDATE()) then i should get all monday values from the current month.
I want to calculate bi weekly range data in the sql.
View 9 Replies
View Related
May 19, 2015
I need some with selecting the number of days, in a month, between a date range. For example, my data looks like:
FileNumb | startdate | enddate
1 04/25/2015 05/02/2015
2 05/01/2015 05/10/2015
The output I am looking for would be:
FileNumb | Year | Month | Days
1 2015 4 6
1 2015 5 2
2 2015 5 10
View 9 Replies
View Related
Mar 29, 2007
Hi all,
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 ?
thanks !
Bernard Ong
View 15 Replies
View Related
Sep 24, 2015
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.
View 2 Replies
View Related
Sep 23, 2014
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:
How to write this query?
View 9 Replies
View Related
Jan 13, 2015
I've got some records like this:
ID_________Jan Feb...........................Dec
0000030257 0 0 0 0 0 0 1 1 1 1 1 0
where each month field has a 0 or 1, depending on if the person was enrolled that month.
I'm being asked to generate a table like this:
ID_________ Start_Date End_Date
0000030257 July 1, 2014 Nov 30, 2014
Is there some slam dunk way to do this without a bunch of If/Then statements?
The editor compressed all my space fields, so the column headers are off in some places.
View 8 Replies
View Related
Apr 5, 2008
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.
View 3 Replies
View Related
Apr 16, 2008
I have my SqlDataSource with my connection string:<add name="ConnectionString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:UsersLaurensDesktopstockmanASP****.accdb" providerName="System.Data.OleDb"/> but I wanted to make a relative path from it because when I put it online i get an error. Thanks in advance
View 6 Replies
View Related
Mar 30, 2006
We have a growing issue where we have a relative dtsconfig file (which stores the absolute base path of the ETL packages). This way we can keep the ETL projects failry portable - only having to modify one value in the dtsconfig file. The master package that defines the dtsconfig location (which is config/Default.dtsconfig) usually interpretates this location to be relative the project. The problem is that every now and again when you open this package in .NETStudio, the path is interpreted differently and causes: config/Default.dtsconfig to state invalid path. But when we delete the variable (which defines the dtsconfig path), save/close and open/recreate it works again. This may or may not be supported MS method, but I was curious to know why this gets messed up. Is there somehwere in the .NET framework that defines what "/" is relatively under?
For example: Our absolute config path is "D:Program FilesMicrosoft SQL Server90DTSPackagesETLProjectETLBaseconfigDefault.dtsconfig" but using: "config/Default.dtsconfig" for xml file value works. However, sometimes we will get an error stating that this file cannot be found, and when we just try to delete (without saving and closing) and immediatelly try to put "config/Default.dtsconfig" again and hit next, we get an error and the path is now:
'D:Program FilesMicrosoft SQL Server90DTSPackagesDEVDataExchangeETLBaseconfigconfigDefault.dtsconfig'.
Ideas?
View 6 Replies
View Related
Jan 23, 2007
I have host my ASP.NET application on gate.com, and there I have access to a SQL Server 2005 instance, and I am trying to make relative paths in my web.config work, (|Data Directory|), but I don't know how to change the default value of |Data Directory|, and it isn't the root of my application, I'm pretty sure. My setup is like so, and the file was created using SQL express 2005, I am not sure if that is my problem, because gate uses the full version.
/<root>/App_Data/Leads.mdf
here is the stack trace:
[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735091 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +84 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197 System.Web.Profile.SqlProfileProvider.GetPropertyValuesFromDatabase(String userName, SettingsPropertyValueCollection svc) +766 System.Web.Profile.SqlProfileProvider.GetPropertyValues(SettingsContext sc, SettingsPropertyCollection properties) +428 System.Configuration.SettingsBase.GetPropertiesFromProvider(SettingsProvider provider) +410 System.Configuration.SettingsBase.GetPropertyValueByName(String propertyName) +117 System.Configuration.SettingsBase.get_Item(String propertyName) +89 System.Web.Profile.ProfileBase.GetInternal(String propertyName) +36 System.Web.Profile.ProfileBase.get_Item(String propertyName) +68 System.Web.Profile.ProfileBase.GetPropertyValue(String propertyName) +4 ProfileCommon.get_Cart() in c:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Files
oot27a74ff4376615bdApp_Code.ceobor1b.4.cs:102 Default2.UpdateTotal() in \shared.hosting.localfscust98045754089webOrder.aspx.cs:45 Default2.Page_Load(Object sender, EventArgs e) in \shared.hosting.localfscust98045754089webOrder.aspx.cs:37 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +34 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061
View 2 Replies
View Related
Apr 24, 2007
Hi all,
Is it possible in the XML Source to specify a relative path for the xsd file? If not, do you have any idea why?
Best regards,
pc
View 5 Replies
View Related
Sep 21, 2007
I'm trying to use the drill-through feature to link to a report in different solution, on the same server, but in a different project. Local help files say: "The list of report names includes all reports in the current report server project. If the drillthrough report is on the report server but is not in the project, type the name of the report. The report name can contain a relative or absolute path to the report."
The report will be passing a parameter to the target report through drill-through. The target report is located on different solution and project but on a same server (report manager). Any help will be be appreciated.
Can someone give an example of a relative path? Nothing I've tried has worked, and I've posted this on sqlteam to no avail.
Thanks,
Lalon
View 1 Replies
View Related
Jul 26, 2006
So I want to insert 40 values into a table, starting at a particular column. Like this:
INSERT INTO MyTable (1) VALUES (...my forty values...)
Pretending that 0 (zero) indexes the first column, and 1 indexes the second column, the purpose is to skip the (first) column that contained an identity value (since normally you can't insert into an identity column anyway).
The only way I currently know how to solve this problem, is to use highly verbose syntax, like this:
INSERT INTO MyTable (...my forty column names...) VALUES (...my forty values...)
But yuck, who wants to explicitly mention all forty column names, ONLY BECAUSE I'm trying to avoid inserting a value into the first column which contains the identity?
View 1 Replies
View Related
Nov 9, 2015
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]
[Code] ....
View 6 Replies
View Related
Nov 10, 2005
How do I query to ONLY pull the location for the most recent date?
Date Location
1/2/97 ABC
3/4/02 DEF
2/8/89 GHI
4/30/05 JKL
2/28/03 MNO
View 1 Replies
View Related
Feb 28, 2008
At my office, we've been slowly working on putting together a data warehouse.
We're a financial services company and one of the services that we offer is debt collection. As far as reports go, our clients are interested in knowing how much money we collect over time. In particular, they want to know how many payments we've gotten 5, 10, and 15 months (and so on) after we receive a case. (Obviously, the 5-month payments are also included in the 10 and 15-month calculations).
When I wrote this report using our transactional database, I was completely new to SQL and the ever-resourceful Patron Saint took pity on me, so you can see a good description of the details at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78510
Now that I'm no longer a total newbie at SQL, and having been through a relatively extensive seminar on data warehousing, I've been entrusted with researching certain aspects of data warehouse development (rest easy, though, folks - the real DWH work is not being done by the very inexperienced me, but by an actual professional :) ).
My question:
how would you model this kind of "relative time" in a data warehouse? How would you display the 5-month, 10-month, and 15-month payments in a DWH? I can't really imagine that the kinds of joins necessary to do this in a transactional database would be desirable in a data warehouse.
We have the following:
1.) FACT_Payment: A fact table showing each each payment to the most detailed granularity. One attribute of this table is the payment date. Another attribute is a foreign key to the case dimension described below.
2.) DIM_Case: A dimension table showing information on each case, including the case start date. DIM_Case
3.) DIM_Date: A date dimension table.
(For added clarification: The FACT_Payment payment date has to be 5, 10, 15 months etc... after the DIM_Case start date.)
Any ideas, comments, experience with something like this?
Thank you.
View 7 Replies
View Related
Apr 20, 2007
hello again!, this time I'm trying to run a Master Package from the SQL Server Agent but I can't set relative paths to the connections for all the child packages that the master package contains.
It only finishes execution when I set absolute paths for all connections in the connection manager within the SSIS Project.
Is there any property in the SQL Server Agent or mayby a workaround to solve this?
View 7 Replies
View Related
Oct 30, 2007
I'm trying to create a view from a query using a temp table and I'm running into some strange issues. When I try to enter the query into the view it gives me the following error, but then the query runs and returns the expected data:
"Unable to parse query text."
In an attempt to get around this (I assumed that temp tables just weren't supported with in a view) I put my query into a Stored Procedure and attempted to call the SP from the view. This gave me the same results as before (an error and then a result set) but the error was the following:
"The EXECUTE SQL construct or statement is not supported."
The query seems to run properly in both forms, it just gives an error on the first run and won't allow me to save the view because of the syntax error. The stored procedure runs just fine and returns the right data, so I'm pretty sure that my query is good. I just don't know how to make it execute into a view without getting these errors.
Any help would be greatly appreciated.
Thanks!
Phil
View 13 Replies
View Related
Jan 11, 2008
I am new to the wonders of CLR and, as can be expected, have hit a snag. We have multiple environments to run this off of (dev, test, production, etc.) and need to be able to use the same scripts to install them. Here is our basic setup:
Batch script runs a master SQL file using sqlcmd utility
Master SQL file runs secondary SQL files, including the one with the CLR definitions
Secondary SQL file with CLR calls the CREATE ASSEMBSLYI want to be able to call
CREATE ASSEMBLY assemblyName FROM 'myAssembly.dll'
or
CREATE ASSEMBLY assemblyName FROM '..myAssembly.dll'
or something to the effect. However, when I try this, it says I cannot do it or it cannot find the file, even though the file is located in the same folder.
Any ideas?
View 3 Replies
View Related
