Returning Up To Previous Sunday Relative To Today
Apr 18, 2008
I'm quite a newbie with MDX.
I have a report that uses MDX to return Figures by day. The user selects the Month/Year eg.[Nov F2007] that he wants to see the figures for.
Here is the catch:
The user wants the figures to cut off to the previous Sunday. So if its Thursday, the result set will still only return results to Sunday.
What I'm thinking is something like this:
STRTOMEMBER('[Nov F2007]').FirstChild : STRTOMEMBER('[Nov F2007]').[LastSunday].
I'm not sure if I'm on the right track, but its a mute point if I can't get the previous sunday as a normal day member (ie. 4 November 2007)
Thanks,
Jason
View 3 Replies
ADVERTISEMENT
Aug 1, 2015
Is it possible to show previous week Sunday date as default in Date Parameter in Reports.
View 2 Replies
View Related
Oct 22, 2015
I would like the following code to display Sunday of the Week Sunday to Saturday.
print DATEADD(WEEK, DATEDIFF(WEEK, 0, '2015-10-18'), 0)
If I input a date between '2015-10-18' and '2015-10-24' I would like to print '2015-10-18 as well
View 3 Replies
View Related
Sep 15, 2013
I have written a piece of SQL which I thought was wokring fine. I wanted to show all data for the current week from Monday - Sunday
I wanted to pick up all data from Monday 00:00:01 to Sunday 23:59:59 and then when the data runs again on the next Monday, a completley new set of data starts to be loaded in
On the off chance I have come into work today and realise that no data is being diaplyed, and today is a Sunday
I know for a fact that my SQL will pick up data as soon as it hits 00:00:01 tonight
I really want my SQL to pick up data for a Sunday
BETWEEN DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) AND DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 6)
View 1 Replies
View Related
Mar 9, 2006
I have time in the Integer Field ( The Field Represents milliseconds Past Midnight w.r.t GMT )
I want to have a function so that I can pass this integer field to the function and get the DATETIME Value.
In that function I want to code it for Day light Saving time also
How to get First Sunday of APRIL
and Last Sunday of OCTOBER
Eg
ms past midnight w.r.t 47602656
SELECT 47602656 , DATEADD(hh , -5 , DATEADD(MS, 47602656 , CONVERT(VARCHAR(10), GETDATE(), 101)) )
Thx
Venu
View 1 Replies
View Related
Sep 10, 2013
Need getting a query which I will get previous year, previous month first day everytime i run the query.
Ex: If i run the script on 9/10/2013 then result should be 8/1/2012. (MM/DD/YYYY)
View 4 Replies
View Related
Nov 20, 2013
How to get first sunday of june dynamically?
using getdate()
View 3 Replies
View Related
Mar 28, 2002
Hi Guys,
Can you please help me on how to find the sunday's date of a certain week?
Eg: I want to find the date of the sunday of certain week.
View 1 Replies
View Related
Apr 30, 2008
Hi,
i am using sql server 2005.
i want to know how to find no of sunday in a month(in any month)
Thank you.
View 2 Replies
View Related
Mar 26, 2007
I want to find the date of second sunday of March 2007 to March 2015 (For daylight saving rule).Is there any function in SQL Server which will retrun that?
Thanks.
I want following..
declare @year int
select @year = 2007
select fn_findSecondMonday(@year)
_________________________________
03/11/2007 -- second sunday of March
Sanjeev Shrestha
12/17/1971
View 8 Replies
View Related
Aug 1, 2006
Hi
I have a string which contains the year and month, now i wanted to know what is the starting day of that month in that particular year.
For Ex: string STR = "Jan2006" then by any chance i can know the starting day of this month as whether it is Sunday etc. I need the string "sunday" as output. Also, I need to know how many number of days that particular month contains. Any function or stored procedure which will give out these two outputs will be much appreciated.
Thanks!
Santhosh
View 3 Replies
View Related
Jan 12, 2007
I'm using VB.Net and SQL Server. Here's the scenario I have that I can't figure out how to accomplish:
(btw, this is all manual - I'm NOT using an ASP.Net Calendar control) I want to provide a week's worth of calendar data automatically. Let's say I have all the calendar dates and times in a table. But - I only want to show the data from the current week.
So, no matter which day the end user loads the page (Monday, Tuesday, Saturday, etc), I want to be able to find the previous Sunday (or Monday?), and then provide data from the table for events that are happening from that Monday, through the next Sunday.
Finding the last Monday (or Sunday?) is the first problem, and then getting only data between that date and the following Sunday is the second problem - how to create the code and hw to create the sql statement - -
any ideas?
View 8 Replies
View Related
Jul 17, 2014
I would like to show employee work hours daily from Monday to Sunday. I have managed to write a query to get the total hours.
This is the query giving total hours from and to date.
(select sum(t.timespent)/60 from timeitems t
where t.employee = e.code
and t.project = p.code
and t.ndate >= '2014-07-15'
AND t.ndate <= '2014-07-15') as Hours
from projemplink pl
View 1 Replies
View Related
Mar 20, 2004
I would like to AUTOMATICALLY count the event for the month BEFORE today
and
count the events remaining in the month (including those for today).
I can count the events remaining in the month manually with this query (today being March 20):
SELECT Count(EventID) AS [Left for Month],
FROM RECalendar
WHERE
(EventTimeBegin >= DATEADD(DAY, 1, (CONVERT(char(10), GETDATE(), 101)))
AND EventTimeBegin < DATEADD(DAY, 12, (CONVERT(char(10), GETDATE(), 101))))
Could anyone provide me with the correct syntax to count the events for the current month before today
and
to count the events remaining in the month, including today.
Thank you for your assistance in advance.
Joel
View 1 Replies
View Related
May 28, 2015
I need to generate the week ranges like this format :
Here from date and to date would be picked up from the table but just to make you understand i have hard coded it but this is the real date which is falling inside the table.
Note : Week should be generated from Monday to Sunday within desired date range
View 6 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 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
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 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
Feb 26, 2015
I am looking for the script to find the CPU usage for each database like this
Test1DB76.00%
Test2DB12.00%
Test3DB1.21%
Test4DB20.08%
View 4 Replies
View Related
Feb 28, 2015
Have a bit of an SQL challenge in that I need to create a well performing view in SQL2008R2 which calculates hours remaining at any given point in time. This is not a simple sum but has some absolute values included.
---------------------------------------------------------------------------------------------------
-- Basic simplified structure of maintenance and usage tables
---------------------------------------------------------------------------------------------------
CREATE TABLE #Maintenance (Id int NOT NULL
IDENTITY(1, 1)
CONSTRAINT PK_Maintenance PRIMARY KEY,
MaintenanceDate smalldatetime NOT NULL, -- When maintenance hours are reset
HoursAvailable numeric(5, 1) NULL -- Available hours until next maintenance
)
[Code] ....
---------------------------------------------------------------------------------------------------
-- Required output view so that at any given point in time I can determine hours remaining
---------------------------------------------------------------------------------------------------
-- AtDate HoursAvailable
-- '01-Jan-2015 15:00' NULL -- before first maintenance hence unknown
-- '10-Jan-2015 10:00' 10.1 -- maintenance hours reset
-- '11-Jan-2015 07:30' 8.9 -- subtract 1.2
-- '11-Jan-2015 11:10' 2.9 -- subtract 6.0
-- '15-Jan-2015 00:00' -0.1 -- subtract 3.0
-- '01-Feb-2015 00:00' 12.2 -- maintenance hours reset
-- '02-Feb-2015 13:00' 10.0 -- subtract 2.2
View 4 Replies
View Related
Apr 20, 2006
I am creating a application in Visual C++ 2005 that has database
connections in it. I have gone through the process of setting up the
data source and it will work great on my computer. However, the second
I move either the project or the created executable to another computer
the application keeps crashing. I know that it is crashing due to the
database connection string. The string is setup to work only on my
computer, I am wondering how do I make, or edit, the string to work on
any computer? Thanks for the help.
View 1 Replies
View Related
Nov 29, 2007
Does anyone know if there is a rough guide in terms of how much memory SQL server will normally use relative to the size of the actual databases that it is hosting?
For example, I'm working on a server that hosts only about 250-300 MB worth of data (including all of the full text indexes, etc.) but the sqlservr.exe process is using 1.25 GB of ram. Also Page Reads/sec is around 1200 -- despite there being over 5 GB of RAM available and not being used (the PF is about 3.25 GB, total memory in the machine is 8GB). Below that w3wp.exe uses only 290 MB and lsass.exe uses only about 225 MB. The machine is a web/database server that is hosting ASP.NET 2, ASP.NET & Classic ASP pages (and the associated databases). For the amount of hardware, the server seems to be responding to requests on the slow side. While some of this is probably due to the legacy code-base, there seems to be a noticeable difference in speed after SQL Server is restarted.
Thanks,
Jeremy
View 1 Replies
View Related
Apr 23, 2007
Dear Sirs,
We use NLB + ISA , Win2003/SP1 + Reporting Services 2005
We use host name URL to access Reporting Serives reports, like http://www.hostname.com/Reportserver?rc%3aParameters=false&%2MyReports%2f....
When reports have grouping , the drill down images(+/-) and links failed to be displayed. We checked the HTML source code and found, instead of the reative path, the image/link URLs are using FULL path and using IP address, like http://10.102.40.55/Reportserver/.....
Because the company policy do not allow use IP to access reports , those drill down images and links failed to be displayed.
How can I configure Reporting Service to use host name or relative path for the drill down images and links.
PS:
We have changed the <UrlRoot> in rsreportserver.config and <ReportServerUrl> in rsWebApplication.config to use host name, but no worked.
Best regards,
Roger (roger_lo@gss.com.tw)
View 1 Replies
View Related
May 20, 2004
Hi all, I have a question that relates to a relationship in my stock performance database. It is a conceptual one, but is the only aspect of my database design that I am "losing sleep over" *LOL* (as if that could happen with me... ;) )
Anyway, Here are my tables/Keys, as setup
1) CurrentList - Primary Key is PortfolioID, StockID, BuyDate, Selldate.
- essentially, this table defines stock portfolios. A PortfolioID has one-to-many StockIDs (stocks in the portfolio), and each PortfolioID/StockID pair can further be grouped by BuyDate and Selldate (because a single stock may come in and out of a single portfolio over time).
2) StockProperty - Primary Key is StockID, CreateDate. This table is built on a daily basis from rows in the Currentlist. It represents the affected stocks currently in ANY portfolio. For example, the IBM stock may exist in more than one PORTFOLIO (PortfolioID) but there will still only be ONE row for IBM on a particular date (CreateDate) in the StockProperty table (since the stock is the same stock, regardless of how many portfolios it is in).
I am trying to figure out which is the parent table, and which the child...or if there IS an "enforceable" relationship at all!?!? None of the non-key data is the same in either table, so the only columns with a relationship is StockID, and StockProperty.Createdate - which must be between CurrentList.BuyDate and CurrentList.SellDate.
I guess that's my quandry (or one of myriad quandries in my life ;) ) - I can't really think of how to enforce (via foreign keys, etc) the relationship in the date range...AND...how to explain the relationship between the two tables (I lean toward CurrentList being Parent to StockProperty, but can't come to grips with what the probably obvious "standard" relationship description would be.
Any (preintable) thoughts?
Thanks!
View 2 Replies
View Related
