SQL Server 2012 :: Using DateDiff To Work As Excel Network Days
Jan 2, 2015
So I am trying to work out the difference between today's date (GETDATE()) and a Target Date in a specific table (targetdate)
When I use the DATEDIFF function it is including non working days in the calculation (weekends and bank holidays). Although our date calandar table provided to us from a third party supplier will tell you the weekends, it does not tell you the bank holidays.
Luckily there is another table in the database called - ih_non_work_days.
The format of the date is "2014-12-25 00:00:00.000" for example in that table.
How do I using my "targetdate" and today's date calculate in days the differance - excluding the dates that exist in the ih_non_work_days database?
So for now my basic script looks like -
SELECT com.comm_reference AS 'Referance'
,com.current_task_target_date AS 'TargetDate'
, DATEDIFF(D,com.current_task_target_date,GETDATE()) AS 'Incorrect Date Calculation'
FROM [dbo].[em_communication] as com
i've tried using the SELECT SQL sentence on PPC with UNION and INNER JOIN in combination with DATEDIFF function which works fine on ordinary SQL but obviously does not work on compact edition. Is there some workaround?
SQL sentence that works:
Code Snippet
SELECT * FROM ( SELECT 'O' Type, O.Name , OC.Name Contact, Birthday FROM OutletContact OC INNER JOIN Outlet O ON OC.OutletID=O.OutletID UNION SELECT 'W' Type, W.Name,WC.Name Contact, Birthday FROM WholesalerContact WC INNER JOIN Wholesaler W ON W.WholesalerID=WC.WholesalerID ) S WHERE Birthday IS NOT NULL AND (DATEDIFF(day,GETDATE(),Birthday) BETWEEN 0 AND 14)
I'm looking for a DATEDIFF function which will give me the BUSINESS days difference between 2 dates. e.g: datediff(day,'08/08/2007','08/14/2007) would normally result in 6 (i think), however in business days this would be 4. is there such a function? Thx
Hi! I'm trying to create a query to calculate the number of days between two dates, but I only want to include working days. Is there a way to do this?
with datediff all I can get it to return is months or a total of thedays...so if the difference in dates is 12 months 4 days how do I adjust theSQL to accommodate both?
I am trying to use the DateDiff function to calculate the difference between two dates in working days only... Is this possible in SSRS 2005, or can anyone suggest an alternate solution?
Aim – Calculate the number of days between the [CreatedDate] and getdate, however if stage name = ‘Live Transactions’ then Calculate the number of days between [CreatedDate] & [CloseDate]
This is my query so far
SELECT [CreatedDate] ,[StageName] ,[CloseDate] ,DATEDIFF(dd,CONVERT(datetime,[CreatedDate]),GETDATE()) as Age FROM [FDMS].[Dan].[Raz_Reporting_LCS]
I have added one webpage designed in ASP.Net with C# and sql server 2005 as database. There is table for user registration in which there is a column for ProfileCreationDate the data type of that column is date time .
I would like to fetch data of those user who have created profile within 7 days. For getting desired result I am trying this query.
select Name ,Profession,ProfileCreationDate from tblRegistration where DATEDIFF ( Day , '" + System.DateTime.Now + "',ProfileCreationDate)<7 order by ProfileCreationDate DESC
System.DateTime.Now is a function for getting current date time in C#
The query is neither giving error nor giving desired result.
I am going put in the whole query but my question is on changing a datediff minutes amount into DD:HH:MM.
Query is: select c.APPLICANT_ID as [Applicant ID], aetc.EVENT_TYPE as [Event Type], cast(aetr.CREATE_DATE as date) as [Registration Date], cast(aetc.CREATE_DATE as date) as [C Creation Date], datediff(mi,cast(aetr.CREATE_DATE as datetime),cast(aetc.CREATE_DATE as datetime)) as [time diff],
[Code] .....
I want this as dd:hh:mm rather than just minutes.
Currently this field is just minutes so a figure such as 20, depending on the time difference between the 2 dates.
I have tried the convert statement convert (char(5) ...........,108) within this but that's not working and I have used floor before to do this type of thing but not sure where that should go.
Hello,I'm an absolute newbie when it comes to SQL. I was told that SQLserver does not function well on a WAN where network latency between,say, the SQL server and a front-end server is greater than 250ms.I can't find anything information supporting this claim online, so Iwas hoping someone here could tell me if this is true or not?Thank You!!
I have 2 networked PC's both running vista ultimate
1st is Laptop and is running its own SQL Server at laptoplaptopSQL 2nd is Desktop and is running its own SQL Server at desktopdesktopSQL
Now both machines have seperate windows login accounts.
When I go SQL Server management studio I go to browse and each machine can see the other machines SQL Server, but when I go to login I get SQL Login falied for users" The user is not associaed with a trusted SQL server connection".
So I then go to logins new login and try to add my other pc's user account. The problem I see is that when I go to search and then location it only shows its own PC's location and not the location of my other networked pc? So if I am on Desktop and in my theory want to add laptopuser to the desktop SQL Server logins I get:
"create failed for login laptopuser
An exception occurred while executing Transact SQL statement laptopuser is not a valid windows NT name. give the complete name
I need some advise/help how to deploy a small sql server compact 3.5 database on networkshare where about 8 people have access to it(install de client ) and can modify the database with aprox 3000 records. I'm using visual basic 2008 and created a compact database and filled it with one table of an export of an access database done with data port console. And created a new windows forms application and saved the project. Publish is not the problem but how too set it up...... There is not much info to find on how to get a sql server 3.5 database running on a network share. Who can help ???
I have a requirement where i need to calculate the age of a work order excluding the Weekends (Sat,Sun) in an SQL table, this i need to updated as a formula for a particulay column in the SQl table so when a task startdate is enterred and submitted the Age field gets populated with the number of working days.
Example: startdate = '04/09/2010' currentdate = '04/12/2010' the result should show 1 day and not 3 days.
I'm trying to write a query that returns last 30 days data and sums the amount by day. However I need to do it for every year not just the current one(I need to go back as far as 2000).
declare @t table (id int identity(1,1), dt datetime, amt MONEY) insert into @t (dt, amt) select '2014-11-30 23:39:35.717' as dt, 66 as amt UNION ALL select '2014-11-30 23:29:16.747' as dt, 5 as amt UNION ALL select '2014-11-22 23:25:33.780' as dt, 62 as amt UNION ALL
[Code] ....
--expected output select '2014-11-30' AS dt, 71 AS Amt UNION ALL select '2014-11-22' AS dt, 62 AS Amt UNION ALL select '2014-11-20' AS dt, 66 AS Amt UNION ALL select '2014-11-18' AS dt, 102 AS Amt UNION ALL
select count (*) from MEMBERS,dbo.MEMBER_PROFILE where MEMBER_PROFILE.member_no = members.member_no AND JOIN_DATE between '07-01-2013 00:01' and '07-31-2013 11:59' and email <> 'selfbuy_customer@cafepress.com' and ROOT_FOLDER_NO is not null and email not like '%bvt.bvt'
This returns the count for the month but I want to see what the total each day was.
get the desired results for the following sample data set. I was able to come up with a query that returns the the expected results however only for a given day, so I'd need to union several select statements the get the desired results which is definitely not ideal. I'd like to pass a parameter in (number of days) instead of doing a unions for each select.
DECLARE @T TABLE (Id INT, Category VARCHAR(1), [Date] DATE) INSERT INTO @T SELECT 1 AS Id, 'A' AS Category, '2015-5-13' AS ActivationDate UNION ALL SELECT 1, 'A', NULL UNION ALL SELECT 1, 'A', '2015-5-13' UNION ALL SELECT 1, 'A', NULL UNION ALL
I have a date that I need to add 'n' number of business days to. I have a calendar table that has a 'IsBusinessDay' flag, so it would be good if I was able to use this to get what I need. I've tried to use the 'LEAD' function in the following way;
SELECT A. Date, B.DatePlus3BusinessDays FROM TableA A
LEFT JOIN (Select DateKey, LEAD(DateKey,3) OVER (ORDER BY datekey) AS DatePlus3BusinessDays FROM Calendar WHERE IsBusinessDay = 1) B ON A.DateKey = B.DateKey
Problem with this is that because I am filtering the Calendar for business days only, when there is a date that is not a business day in TableA, a NULL is being returned.
Is there any way to do a conditional LEAD, so it skips rows that are not business days? Or do I have do go with a completely different approach?
I have the table below and like to create a view to show the no of days the property was vacant or void and rent loss per month. The below explanation will describe output required
For example we have a property (house/unit/apartment) and the tenant vacates on 06/09/2014. Lets say we fill the property back on 15/10/2014. From this we know the property was empty or void for 39 days. Now we need to calculate the rent loss. Based on the Market Rent of the property we can get this. Lets say the market rent for our property is $349/pw. So the rent loss for 39 days is 349/7*39 = $1944.43/-.
Now the tricky part and what im trying to achieve. Since the property was void or empty between 2 months, I want to know how many days the property was empty in the first month and the rent loss in that month and how many days the property was empty in the second month and the rent loss incurred in that month. Most of the properties are filled in the same month and only in few cases the property is empty between two months.
As shown below we are splitting the period 06/09/2014 - 15/10/2014 and then calculating the void days and rent loss per month
Period No of Void Days Rent Loss 06/09/2014 - 30/09/2014 24 349/7*24 = 1196.57 01/10/2014 - 15/10/2014 15 349/7*15 = 747.85
I have uploaded a screenshot of how the result on this link: [URL] ....
Declare @void Table ( PropCode VARCHAR(10) ,VoidStartDate date ,LetDate date ,Market_Rent Money
I have already created a table name 'tblHolidays' and populated with 2014 Holidays. What I would like is be able to calculate (subtract or add) number of days from a date. For example subtract 2 days from 07/08/2014 and function should return 07/03/2014.
CREATE FUNCTION [dbo].[ElapsedBDays] (@Start smalldatetime, @End smalldatetime) RETURNS int AS BEGIN /* Description: Function designed to calculate the number of business days (In hours) between two dates.
i am trying to run a data transformation service from my win 98 machine running the sql7 server client. i keep getting the error 'general network error'
it used to work before but doen't anymore. any ideas?
I was using linked servers to import Excel spreadsheets into SQL Server Express 2005. This worked fine with Windows XP and Office 2003.
I have just migrated all my stuff to Vista and Office 2007. Linked servers just can't be created:
TITLE: Microsoft SQL Server Management Studio Express ------------------------------ "The linked server has been created but failed a connection test. Do you want to keep the linked server?" ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo) ------------------------------ Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XXX". OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XXX" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476 ------------------------------ BUTTONS: &Yes &No ------------------------------
The parameter values I used are:
Provider: Microsoft Jet 4.0 OLE DB Provider Product name: Excel Data source: D:...somelocalfile.xls Provider string: Excel 8.0
I gave full access rights to the Data source file and folder to NETWORK SERVICE and SQLServer2005MSSQLUser$MOZART$SQLEXPRESS where mozart is my PC name.
If I change Data source or Provider string to some garbage string, the exact same error message appears. So it appears the error might be in the Provider?
Hello,I am writing a query to select records added to a table today, in the last 3 days, in the last 7 days, and so on.Here is what I have (which seems that its not working exactly). -- total listed today SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 0-- total listed yesterday SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 1-- total listed in the last 3 days SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 3I'd like to be able to select the count for records added within the last X number of days. Can someone please help me out? Thanks so much in advance.
I created a dbo.Calendar table that stores dates and a work day flag (1=work day, 0=non-work day) so I can use it to calculate the next business date from a date using a function. I'm using a while group to count only the work days and a couple other internal variables but I'm not sure if I can even use them in a function.
Assuming Sats & Suns are all non-work days in April 2014, if my @WorkDays = 10 for 10 work days and my @DateFromValue - 4/1/2014, I would expect my return date to be 4/15/2014.
------ Messages after I click execute on my query window that has my function ------------------------------------------------------ Msg 444, Level 16, State 2, Procedure FGetWorkDate, Line 19 Select statements included within a function cannot return data to a client. Msg 207, Level 16, State 1, Procedure FGetWorkDate, Line 20 Invalid column name 'WorkDay'. Msg 207, Level 16, State 1, Procedure FGetWorkDate, Line 22 Invalid column name 'Date'.
------ my function code ---------------------------- CREATE FUNCTION [dbo].[FGetWorkDate]( @WorkDays VARCHAR(5), @DateFromValue AS DateTime ) RETURNS DATETIME
ERROR SUMMARY Below is a summary of the errors, details of these errors are listed later in the log. * Activation of http://webdev.ci.lubbock.tx.us/ReportServer$SQL05/ReportBuilder/ReportBuilder.application resulted in exception. Following failure messages were detected: + Downloading http://webdev.ci.lubbock.tx.us/ReportServer$SQL05/ReportBuilder/ReportBuilder.application did not succeed. + The remote server returned an error: (401) Unauthorized.
COMPONENT STORE TRANSACTION FAILURE SUMMARY No transaction error was detected.
WARNINGS There were no warnings during this operation.
OPERATION PROGRESS STATUS * [4/10/2006 11:34:29 AM] : Activation of http://webdev.ci.lubbock.tx.us/ReportServer$SQL05/ReportBuilder/ReportBuilder.application has started.
ERROR DETAILS Following errors were detected during this operation. * [4/10/2006 11:34:29 AM] System.Deployment.Application.DeploymentDownloadException (Unknown subtype) - Downloading http://webdev.ci.lubbock.tx.us/ReportServer$SQL05/ReportBuilder/ReportBuilder.application did not succeed. - Source: System.Deployment - Stack trace: at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next) at System.Deployment.Application.SystemNetDownloader.DownloadAllFiles() at System.Deployment.Application.FileDownloader.Download(SubscriptionState subState) at System.Deployment.Application.DownloadManager.DownloadManifestAsRawFile(Uri& sourceUri, String targetPath, IDownloadNotification notification, DownloadOptions options, ServerInformation& serverInformation) at System.Deployment.Application.DownloadManager.DownloadDeploymentManifestDirectBypass(SubscriptionStore subStore, Uri& sourceUri, TempFile& tempFile, SubscriptionState& subState, IDownloadNotification notification, DownloadOptions options, ServerInformation& serverInformation) at System.Deployment.Application.DownloadManager.DownloadDeploymentManifestBypass(SubscriptionStore subStore, Uri& sourceUri, TempFile& tempFile, SubscriptionState& subState, IDownloadNotification notification, DownloadOptions options) at System.Deployment.Application.ApplicationActivator.PerformDeploymentActivation(Uri activationUri, Boolean isShortcut) at System.Deployment.Application.ApplicationActivator.ActivateDeploymentWorker(Object state) --- Inner Exception --- System.Net.WebException - The remote server returned an error: (401) Unauthorized. - Source: System - Stack trace: at System.Net.HttpWebRequest.GetResponse() at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
COMPONENT STORE TRANSACTION DETAILS No transaction information is available.
Does anyone know how that I can get the reportbuilder.application to run on a client on the network. It seems that if I have to remote into the actual server to get the report builder to work would be pointless for users on the network. I get the 401 Unauthorized all the time. Any comments would help.
When using my Work laptop while at home, how can I log into an SQLServer Express 2005 database on a home server, using Windows Authentication, without having to VPN in and log onto the network at Work? If I VPN into work then I can connect to my home db just fine.
I have a laptop which I carry from work to home each day. I'm a Domain Admin at work so my network login name is DOMAINjoe. At home I have 5 PCs in a home network, WorkGroup is WORKGROUP. When I carry my laptop home I log in exactly as I do at work. All is fine. File sharing, printer sharing, & internet all work between my 5 PCs.
I installed SQLServer Express 2005 on a home PC I use as a server (Vista Business edition). Home server's name is STAR. I developed a database called AddressBk on my laptop (Visual Studio 2008) using my laptop's local copy of SQLServerExpress. App works fine. I copied the Db files onto my home server STAR. My user name on STAR is Joe2, so while logged onto Star as Joe2 I used Star's SQLServer Management Studio Express to connect the AddressBk db to Star's copy of SQLServer Express. All is fine.
Now if I use my LapTop on my home network and try to connect to AddressBk on STAR I get a connection error. I get the error whether connecting using SQLServer Management Studio Express or using Visual Studio 2008. The SQLServer Management Studio Express connection screen shows I'm connecting using Windows Authentication and my User Name is DOMAINjoe. I presume this is the problem as I can not add a user on Star with a Domain Name. Other PCs on my home network can connect to AddressBk db on Star without a problem. If my labtop VPNs into work then I can connect just fine.
Here is the connection string my App uses. Data Source=STARSQLEXPRESS;Initial Catalog=AddressBk;Integrated Security=True;connection timeout=60
How can I connect to my db without having to VPN into work?
SELECT e1.EntityIdentity as CompanyID FROM dbo.Entitye1 --company JOIN dbo.EntityAssociationea ON e1.EntityID = ea.EntityID1 JOIN dbo.Entitye2 --user ON ea.EntityID2 = e2.EntityID
This query occurs as a sub-query in many stored procedures where exists a WHERE clause that includes CompanyID IN (above query).
Since dbo.Entity and dbo.EntityAssociation change infrequently I thought that an indexed view would really improve performance. But I've found one of the seemingly undocumented Microsoft features when trying to create the clustered index and get the following error msg:
Msg 1947, Level 16, State 1, Line 1 Cannot create index on view "ROICore.dbo.vEntityEntityAssociation_CompanyUser". The view contains a self join on "ROICore.dbo.Entity".
I really need to improve performance on this subquery. Entity currently has over 20m rows and EntityAssociation over 35m rows and both are growing.
How to improve performance? Indexes on both tables for the most part give index seeks, but I thought my saviour might be the index view. Obviously this will not work.
I have a query to run a report where the results has a column named “Due Date” which holds a date value based on the project submission date.Now, I need to add 4 columns named, “45 Days Expectant”, “30 Days Overdue”, “60 Days Overdue” and “90 Days Overdue”.I need to do a calculation based on the “Due Date” and “System (I mean default computer date) Date” that if “System Date” is 45 days+ to “Due Date” than put “Yes” in “45 Days Expectant” row.
Also, if “Due Date” is less than or equal to system date by 30 days, put “Yes” in “30 Days Overdue” and same for the 60 and 90 days.how to write this Case Statement? I have some answers how to do it in SSRS (Report Designer) but I want to get the results using T-SQl.
Now for the complicated part, a person can take absence(sick,holiday,other) for any part of a day or whole day(s). For these absence periods only the worked time on that day needs to be negated off, not the whole period of time.
So for example
If this person
had a days holiday on the 22nd, shown in the HOLIDAY table as
The 'Leave of Absence' on the 20th had no effect on the total for the day as it was between planned work times. how to do this within T-SQL, as simple as possible as I've got to had this code over to other staff members to maintain, who have not had much SQL experience yet?
I've tried doing it as a temp table, with dual insert/select commands, splitting the times over midnight, which partially worked but missed some of the combinations.
