Aim – Calculate the number of days between CreatedDate and [Date_Docs_In_Complete__c],count how many Ids, Fall within a specific month by year, and then work out the avg number of days it took for each month
My query so far
select
ID,
left(CreatedDate,10) as CreatedDate,
left([Date_Docs_In_Complete__c],10) as [Date_Docs_In_Complete__c],
DATEDIFF(day,CreatedDate,[Date_Docs_In_Complete__c]) as Days
from #build
What this does is, it calculates the median of the medians over the period of 3 years. But, what I'm looking for is the overall median of the underlying measure over a period of 3 years.
Which works fine for all my other calculations except this, where I need a distinct count. CNT is a calculated measure. The browser would look like this:
Category Year1 Year2 ..... MDX what I have now MDX what I need
I would like to run queries with data that sometimes span two days. The queries require start and end dates as well as start and end times. The following code works fine if the start time is less than the end time:
select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and
'2006-01-27' and convert(varchar, [DateTime],114) between '09:00:00' and
'17:00:00' order by [DateTime]
However, if I try to run a query where the start time is greater than the end time (e.g., start time 5:00pm on one day until 9:00am the next day), the query returns an empty table.
select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and
'2006-01-27' and convert(varchar, [DateTime],114) between '17:00:00' and
'09:00:00' order by [DateTime]
I need a way to indicate that the start and end times span two days. Can anybody help with this?
The problem I am having is creating a formula in MS SQL, so that when the 'DueBackDate' column is more than todays date, I want the 'DaysOverdue' column to be incremented accordingly.
I have tried creating an SQL Query something like this:
UPDATE rental
SET DaysOverdue = DaysOverdue + 1
WHERE ({ fn NOW() } > DueBackDate)
But the problem with this that it only increments the 'DaysOverdue' field by 1. When I want it to basically subtract todays date by 'DueBackDate'.
I have tried to also create a formula within MS SQL but I come up with errors. I have done something like this in Access which worked. The Formula looked like this: IIF(Now()>[DueBackDate],int(Now()-[DueBackDate]),0)
Is there a way to convert this formula so that is would work in MS SQL?
How can I calculate the no. of days in a month.I know it is a simple question but I am not able to find any code to calculate that. Can someone help me?
SELECT a.* FROM Unscheduled_Arrival a INNER JOIN (SELECT Person_ID ,[arrival date] ,[leaving date] ,[unique stay id] ,[area code] FROM Unscheduled_Arrival
[Code] ....
And what I am trying to do is find instances where a person has arrived back at a different area within 7 days of leaving from a planned visit to any other area different from the last. so lets say we had person ID = 1 and they had had 3 planned visits to one area (not equal to area_gh) in the last 4 months but after the 2nd planned trip to the other area, and before the commencement of the 3rd planned trip to any area not equal to area_gh, they had arrived unscheduled to area_gh, and this unplanned visit was within 7 days of leaving the 2nd last planned visit to any area not equal to area_gh.
The query I have shows data from unscheduled arrivals to the area_gh for a person who has also had visits to any area other than area_gh, but each record for unscheduled visits to area_gh duplicates for the amount of times the person has had scheduled visits to any area not equal to area_gh. This is the first problem but is as a result of not having the correct SQL for the refinement of the data.
This refinement requires that I return only the data that relates to the unscheduled arrival that is within 7 days of the last scheduled arrival, not duplicate rows of area_gh visits for all the scheduled arrivals.
Because my data is showing the data I want but the count is related to the scheduled arrivals I think I need to rewrite the query or alter it.
The data is already restricted to a year's worth of data.
I just need to find any instances of unscheduled arrivals (for arrival at area_gh) within 7 days of the end of any scheduled arrival (determined using leaving date) to area equal to area_gh.
There can be any number of scheduled arrivals and any number of unscheduled arrivals for each person. The [unique stay id] is distinct for each visit so person 1 can have 3 stays, all with a different [unique stay id].
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?
I am facing a strange issue with regards to generation of a report from my application in in Live. One of our client is using a report “Auditors Report”. When I try to generate the report for period of 1 month i.e. from 01 May 2015 – 31 May 2015, the report fails when the data is bound to report control. However if I try to generate the report for period of 1 day or 15 days for within the same month, report gets generated.
Now the same application and same database when attached to our local machine, the report gets generated properly for 1 month. I am not sure why the issue is coming on the server.
Hello, Can anyone out there tell me if there's a simple way to calculate the number of week days between two dates in TSQL? Need it to calc. average turnaround times, excluding weekends. Can do it v. easily in VB, but gets a little more tricky in TSQL as there's no way to return the number of Sundays and Saturdays between the two dates. Any help much appreciated !
Jon Reade Sql Server DBA NEC Technologies (UK) Ltd.
Hello, Can anyone out there tell me if there's a simple way to calculate the number of week days between two dates in TSQL? Need it to calc. average turnaround times, excluding weekends. Can do it v. easily in VB, but gets a little more tricky in TSQL as there's no way to return the number of Sundays and Saturdays between the two dates. Any help much appreciated !
Jon Reade Sql Server DBA NEC Technologies (UK) Ltd.
I'm trying to calculate the time difference between a date field and today's date in days. The date field is not mandatory and can therefore be blank. I'm trying to execute the following query:
SELECT employee_code, Civil_ID, DATEDIFF(Day, Civil_ID, GETDATE()) FROM ODEV_VIEW_Credentials_Expiry_Dates WHERE Civil_ID IS NOT NULL AND Civil_ID != '' ORDER BY employee_code
I keep getting the following message:
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Warning: Null value is eliminated by an aggregate or other SET operation.
No matter what filter I use to process non-blank dates, it never works.
I have got this matrix and I am trying to calculate the average amount of working days in a month. At the moment, I have divided the total number of jobs by 21 for every month which is a hard coded value. However, I am not sure how to retrieve this value dynamically. Is there any formula that can find out the working days?
I'm using an olap cube in a report (dash board). The last "actual" period should be viewed when the user opens the report. I'm using the MDX tail function for that. Once the report is opened the users want to select another period. Then I want to put period as a parameter. But that is not possible since the parameter check box is no longer available when using a MDX filter expression.
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.
Hi I have a problem, when i do a select records from the production server which is situated in melbourne,around 20,000 records need to be fetched but sunddenly after 13,000 records the connection is broken why is this heppening what is the cause for this how to overcome this pls let me know asap with the evidence thanks venkat
Hi , I am running a process from from my client having TCP/IP sockets connected to WIN NT sql server 7.0. I get a error msg [MICROSOFT][ODBC SQL SERVER DRIVER][TCP/IP SOCKETS]ConnectionCheckForData (null())). Connection Broken .
The same thing happened in other NT SQL server as well,but it is ran o.k if i run from the server itself. Checked Microsoft documents they advised me to use Named Pipes and to check the client and server network utility. Check every detail but still facing same errors. Can anyone provide an solution for this.
As we have just got SQL at work I had decided to play with SQL on my home computer to find out what made it tick.
I am using the 120 day demo enterprise version. While playing in the user accounts area (under security) I denied access to the database on the guest account (like you disable the guest account on domain logins) and (if you have not guessed already) I can't get access to the server anymore. I did try reinstalling but I guess that there are still some settings in the registry that don't get deleted. Any suggestions how I can get back in.
My PC at home is a XP home edition, no domain etc. SQL was installed onto the PC and I was accessing (playing with it on this PC). The setup I was playing with has no data (except what comes with the SQL) so I am not worried about losing anything.
CREATE INDEX [NC_DateAcctTotal] ON [dbo].[BilledPackages] ([pu_date], [acct_no], [delete_flag], [billing_weight_charge]) WITH FILLFACTOR = 90 ON [PRIMARY]
::::: ERROR ::::
Server: Msg 823, Level 24, State 2, Line 1 I/O error (torn page) detected during read at offset 0x000006d5f2c000 in file 'E:MSSQLDataManagement_Data2_Data.NDF'. Connection Broken
I am trying to set up a filtered paging ObjectDataSource for a gridvoew control.
My code works, my query doesn't...
This is OK: Get all rows of data: "SELECT UCO, country_code, country_name, enabled, concurrency FROM CountryCodes " Returns all records as expected
This is OK: Getting rows of data with no Filter: "SELECT UCO, country_code, country_name, enabled, concurrency FROM (SELECT ROW_NUMBER() OVER (ORDER BY country_name ) As Row, UCO, country_code, country_name, enabled, concurrency FROM CountryCodes ) As TempRowTable WHERE Row >= 0 AND Row <= 10" Returns expected data
This is OK: Making sure my WHERE filter works: "SELECT UCO, country_code, country_name, enabled, concurrency FROM CountryCodes WHERE country_name LIKE '%u%' " Returns 85 records
This is BROKEN!!!: I want to return 10 rows of filtered data: "SELECT UCO, country_code, country_name, enabled, concurrency FROM (SELECT ROW_NUMBER() OVER (ORDER BY country_name ) As Row, UCO, country_code, country_name, enabled, concurrency FROM CountryCodes ) As TempRowTable WHERE ( Row >= 0 AND Row <= 10 ) AND ( country_name LIKE '%u%' ) " It Returns 0 records!!!
What am I doing wrong in the last query? Or even better how do I fix it?
I have had countless email alerts thru today alerting me to people replying to threads I have commented on. And I can see the person's comments.
1) WhenI click on the link it takes me to a page saying the post doesn't exist
2) When I try and browse to the thread manually I can't see the reply.
3) On one occasion I replied to one of these threads asking if someone had posted something and then deleted it. After I submitted the post I could see all the other posts in the thread which were supposedly missing before. The next time I browse to the thread (in a new browser window) I can't see any of the posts.
BROKEN!!!! I for one won't be coming back here until they fix it. Its pathetic.
I have posted something on the bug report forum by the way.
I downloaded and registered Visual Studio Express Edition. I got this nice email back with links to demos, tutorials, and starter kits. Unfortunately, the links to most of the starter kits are broken. Can I still get these things?
While performing a restore, one of the transaction logs was apparently corrupt. So the database gets partially restored with an error saying :
Backup or restore operation successfully processed 1 pages in 0.505 seconds Processed 6 pages for database 'royal', file 'royal_Log' on file 1. Server: Msg 9004, Level 21, State 2, Line 1 The log for database ' ' is corrupt. ODBC: Msg 0, Level 16, State 1 Communication link failure
Can anyone give me some tips how to restore? any help appreciated. bye Eva
Hi there, wasn't quite sure where to put this but I still regard myself as a bit of an SQL Server noob so thought it'd be ok here....hopefully?! lol
Right....I have a database called CDR and a Login set up for this on the server called CDMLogin. The CDR database is a non-live backup of another database stored elsewhere. In order to update this for testing purposes I needed to make a backup of the live database and then do a 'restore database' over the top of the old CDR one. This has worked fine before.....however, on this occasion, the link between the CDR database and the CDMLogin appears to have been broken. I have since read about how this can be an issue when restoring databases but I have still not found a fix. Any ideas?
Version: Microsoft SQL Server 2000 - 8.00.2040 (Intel X86)
Problem:
Server: Msg 8964, Level 16, State 1, Line 1 Table error: Object ID 515532920. The text, ntext, or image node at page (1:377289), slot 1, text ID 897099563008 is not referenced. Server: Msg 8964, Level 16, State 1, Line 1 Table error: Object ID 515532920. The text, ntext, or image node at page (1:377447), slot 1, text ID 897100939264 is not referenced. <... and 4 same errors>
DBCC results for 'CC_Document'. The repair level on the DBCC statement caused this repair to be bypassed. <same messages>
The repair level on the DBCC statement caused this repair to be bypassed. There are 192 rows in 6 pages for object 'CC_Document'. CHECKTABLE found 0 allocation errors and 8 consistency errors in table 'CC_Document' (object ID 515532920). repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (crmproded.dbo.CC_Document repair_fast).
I won't run checktable with 'repair_allow_data_loss' before all records aren't find out
Child fragment at Page (1:377237) Slot 0 Offset: 8080
Child fragment at Page (1:377238) Slot 0 Offset: 16160
Child fragment at Page (1:377239) Slot 0 Offset: 24240
Child fragment at Page (1:377288) Slot 0 Offset: 32320
Child fragment at Page (1:377289) Slot 0 Offset: 36572
There are 5 links at Slot 1 , that's generate error. But why? What does it mean - "The text, ntext, or image node at page (1:377289), slot 1, text ID 897099563008 is not referenced" ???
I saw other pages (not 'broken') - there is: Blob Fragment don't contains links - links are in other pages.
What are these 'broken' records in my example - they are chunks of lost information, that i can't recover?
Or not? May be exists a way, that i find all 'broken' records? Because, my table contains 192 records. But there is BLOB field - and i can't check consistency of every file, that uploaded in my table. In addition I will fully appreciate this problem.
At last, if i find record, i run checktable with repair_allow_data_loss, then upload file again.
I have a SSIS which have a user string variable named connectionstring. In the package definition it has empty value. But in my package.dtsconfig file I set the value of this variable.
But I am not getting the value inside a script component. I tried debugging with a msgbox echoing the variable value ... but I see only empty value i.e. at runtime the value is not being read from config file
What can be wrong here?
next if I put the value ("Data Source=<Server>;Initial Catalog=<Database>;Integrated Security=True;") in the variable definition itself I get the following error
[Execute SQL Task] Error: Failed to acquire connection "<ADO Connection>". Connection may not be configured correctly or you may not have the right permissions on this connection.
The SSIS was running fine previously ... it broke after I updated by machine with the latest windows updates today.
I am working on a 64 bit Windows 2003 SP1 Server box with Visual Studio 2005 SP1, SQL Server 2005 SP1
I have made a chart that displays data for my company. However, when I have the report.rdl on the "live" serverand use the "live" data source, I can't get the report manager to display the chart, only the famuos x of a broken image link. If I place the report.rdl on the test server and use the "live" data source the chart is displayed.
What is the difference between having the report on the test server versus the "live" server?
Report manager -> report on test server -> live data source = working
Report manager -> report on "live" server -> live data source = no image
I dont think the problem is sequrity related, but I dont know for sure...
Running a clustered webserver, windows 2000, IIS v5, and a sql server, v2000. Using OLE Db as connection, running stored procedures, no sql created on the fly. The website handles about 5000 transaction a day.
Every now and again, we get a problem whereby an error 41 - Broken Pipe is reported. I can't find any info on this error on Technet, MSDN, etc. Google returns info, but only really affecting solaris, unix, linux, etc.
Anyone ever come across this error before? or know what might cause it? and possible resolutions?
