I need to provide a minimum value over a 12 hour time range of data. I'm struggling with performance issues due to the amount of data. Currently I log about 100 devices reporting once per minute into a table. Also about once per minute I need to pull the minimum value reported for each device in the last 12 hours. Currently I'm maintaining a separate table with entries for just the last 12 hours and just performing a Select Min(Temp) Where DeviceID=x, but it already holds about 700,000 records at any given time. The number of devices will increase substantially and this will no longer be viable.
Sample Table ID DeviceID Temp InsertDate 1 10 55 04-28-2015 8:00 AM 2 65 74 04-28-2015 8:00 AM 3 44 23 04-28-2015 8:00 AM 4 10 87 04-28-2015 8:01 AM 5 65 65 04-28-2015 8:01 AM
We have an application that has about 100 users at a time. Roughly once a day, we experience a complete slowdown on the server. All users notice it. The network seems fine because I can ping the server. Also, I can attach to drives on the server quite fast so I don`t think it`s server resources. When I manage to get in and do an sp_who, certain processes are blocking others. Talking to the users who were blocking, they were not doing anything out of the ordinary - one was even doing just a select. The error log is full of 17824 and 1608 errors. Is there some configuration setting that I should change? This is getting serious!
Please find below my query and result , how to display [Total Service Time ] in HH:Min format (Currently values in minutes)
Query: SELECT  DISTINCT  dbo.sectn_dept.sectn_sc AS Customer, MONTH(dbo.incident.date_logged) AS Month_Number, DATENAME(month, dbo.incident.date_logged) AS Month, YEAR(dbo.incident.date_logged) AS Year, dbo.incident.incident_ref PM_ref, dbo.product.product_n "Product",
[Code] .... Â Â Result: Need to Display [Total Service Time] in below Format:
With SASS Database i have created Data mining Structure Using Time series algorithm, while processing the SSAS db, Data mining  taking long time to process, so how we can  reduce processing time ???
Here is my Query, I don't know whether I'm getting it right?
--Quarter 1 SELECTD.MerchantName, A.MID, A.TID, ISNULL(SUM(A.SumTrxnMon), 0) AS SumTrxnMon, E.FullName, E.DxBEmail INTO#Quarter1 FROMdbo.tblRPT_Spend AS A INNER JOIN dbo.tblMer_DeployORetrieveTerm AS B ON A.MID = B.MID AND A.TID = B.TID INNER JOIN
Can anyone advise me as to how I can add the date and time to 2 columns in the sql server database for each record that is added. I'd prefer not to use the webform. Can sql server add the date automatically to the row? thanks
I've just spent the most frustrating 4 hours trying to install SP2.
I kept getting "unable to start SQL Server service....see BOL ...re starting the service manually" messages; with the only options being to Retry or Cancel. Retry resulted in the same message. Cancel resulted in all components except SQL (sqlexpress) being installed - therefore totally useless
No mater what I did i could start the SQLservice as it did'nt show in the list of Services; nor did the SQL EE programme show in the Add/Remove Programs...evenn though the other components did.
I deleted/removed everything that was istalled; cleaned up the Registry; and tried again...
..and again
..and again
FIVE install / re-install failures; numerous registry cleanups!!!!
I was at the point where it looked like i'd have to reformat my hard disk, and completely re-install XP, all aplications i.e. Office 2003 etc in order for the thing to work.
I gave it one more shot, but this time, during install
- selected all components except SDK
- I unchecked the Start SQL / BROWSER at startup
- Checked the Enable User Instances and Add to Administrators boxes
Lo and behold....it installed!!!
I'm still very worried as the registry is full of "duplicate" entries i.e. Blah blah blah and blah blah blah.1
But at least the thing is installed.
Would interested to hear if anyone has had similar or other problems with the SP2 install/upgrade
----------------------
For info:
I ran the install as Administrator
IIS not installed
Win XP Sp2 fully updated
Dell Inspiron XPS M1710 laptop with 2gb Ram; 2ghz Duo core processors
I receive the following result set from TableA (In Time)
7/9/2013 9:27:00.000 AM 7/9/2013 10:24:00.000 AM 7/9/2013 11:25:00.000 AM 7/9/2013 1:23:00.000 PM 7/10/2013 7:27:00.000 AM
Then we receive the following result from TableB (Out Time)
7/9/2013 9:30:00.000 AM 7/9/2013 10:29:00.000 AM 7/9/2013 1:37:00.000 PM [NULL] [NULL]
We may not always get Out Times in TableB so I want to merge these into one table to have the In Time and Out Time in separate columns in that one table. In this example with the red type those should be In Time and Out Time for mapped unique identifiers from each table and yet the purple color coded example would have an In Time of 11:25 AM and the Out Time would remain as NULL.
I am using this block of code but is not working the way I want it to because the 11:25am In Time is getting mapped to the 1:37pm Out Time.
and out_time = (select min (out_time) FROM tableB WHERE tableB.record# = tableA.record# and tableB.loc_id = tableA.loc_id
GROUP BY tableB.record#, tableB.loc_id )
It seems I need to focus on the minimum datediff for each record line but can't figure that part out.
I have a table called Tickets which contains ticket information for a machine. Each machine can have more than one ticket number opened at the same time. The ticket number contains start date/time and end date/time of the ticket. Thereefore the table looks something like this:
I want to be able to calculate total duration time(in hours) that EACH MACHINE had a ticket open...but here is the tricky part. The total duration time that a machine had ticket open has to encompas any tickets that may fall in the same time period. For example: If Machine A has a ticket open at 8:30 and the ticket is closed at 10:00. Meanwhile, Machine A had another separate ticket open at 9:30 which was closed at 10:30. In this case, the total duration time for this machine would be from 8:30 to 10:30 for a total of 2 hrs duration time.
Can anyone help me get started in tackling this problem or provide any examples?
IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'Fn_Get_Consensus_Curve_41_Data') AND ((type = 'P') OR (type = 'IF') OR (type = 'TF') OR (type = 'FN')))) DROP FUNCTION [dbo].Fn_Get_Consensus_Curve_41_Data
GO
*/ declare @p_ENTITYID INT declare @p_CUSTOMERID INT
Declare @p_Login_Type int Declare @p_Result_Status int set @p_Login_Type = (SELECT DBO.GET_USER_LOGIN_TYPE_ID(@p_UserID))
If @p_Login_Type=1 and not (@p_CustId is null or @p_CustId='') Set @p_Result_Status = 1 Else if @p_Login_Type > 1 Set @p_Result_Status = 2 Else Set @p_Result_Status = 0
If @p_Result_Status > 0 -- if user is valid and given enough parameters than Begin If @p_Result_Status = 1 -- if User is trader and gives customer id Begin Declare Cur_Fetch_Curve_Cust_Data cursor for Select Distinct Customerid From PricesRR PRR Where Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,@p_Match_Date,101) And Sector_Id = @p_Sector_Id And Location_Code = @p_Location_Code And CustomerID = @p_CustId And --CustomerID <> 0 --CustomerID not in (0, -1, -2, -3, -100, -200) CustomerId Not In (Select CustomerId From Fn_Get_PricesRR_Not_To_Include_Cust_Id('V')) and isnull(PRR.Record_Last_Action,'N') <> 'D' and Version = dbo.GET_PRICESRR_MAX_VERSION(@p_Location_Code, @p_Sector_Id, @p_Match_Date, PRR.EntityID, @p_CustId, PRR.Date)
Declare Cur_Fetch_Curve_Entity_Data cursor for Select Distinct EntityID From PricesRR PRR Where Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,@p_Match_Date,101) And Sector_Id = @p_Sector_Id And Location_Code = @p_Location_Code AND EntityId IN ( Select Distinct Entity_Id from Fn_Get_Allowed_Entity_List(@p_Location_Code , @p_Sector_Id , @p_Match_Date ,@p_UserID )) and isnull(PRR.Record_Last_Action,'N') <> 'D' and Version = dbo.GET_PRICESRR_MAX_VERSION(@p_Location_Code, @p_Sector_Id, @p_Match_Date, PRR.EntityID, @p_CustId, PRR.Date)
End Else If @p_Result_Status = 2 -- if User is higher than trader.. means broker or higher Begin Declare Cur_Fetch_Curve_Cust_Data cursor for Select Distinct Customerid From PricesRR PRR Where Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,@p_Match_Date,101) And Sector_Id = @p_Sector_Id And Location_Code = @p_Location_Code And --CustomerID <> 0 --CustomerID not in (0, -1, -2, -3, -100, -200) CustomerId Not In (Select CustomerId From Fn_Get_PricesRR_Not_To_Include_Cust_Id('V')) and isnull(PRR.Record_Last_Action,'N') <> 'D' --and Version = dbo.GET_PRICESRR_MAX_VERSION(@p_Location_Code, @p_Sector_Id, @p_Match_Date, PRR.EntityID, @p_CustId, PRR.Date)
Declare Cur_Fetch_Curve_Entity_Data cursor for Select Distinct EntityID From PricesRR PRR Where Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,@p_Match_Date,101) And Sector_Id = @p_Sector_Id And Location_Code = @p_Location_Code and isnull(PRR.Record_Last_Action,'N') <> 'D' --and Version = dbo.GET_PRICESRR_MAX_VERSION(@p_Location_Code, @p_Sector_Id, @p_Match_Date, PRR.EntityID, @p_CustId, PRR.Date)
End delete from @Temp_Curve_Submission_Data
----------------------- -----------------------
Open Cur_Fetch_Curve_Cust_Data fetch next from Cur_Fetch_Curve_Cust_Data into @p_CUSTOMERID WHILE @@FETCH_STATUS = 0 BEGIN
IF @@FETCH_STATUS <> 0 break BEGIN ----------------------- ----------------------- Open Cur_Fetch_Curve_Entity_Data fetch next from Cur_Fetch_Curve_Entity_Data into @p_ENTITYID WHILE @@FETCH_STATUS = 0 BEGIN
Hi guys, I am having difficulty calculating the time duration between receiving process to shipping process. I have a table that consists of: Order#, Processes, Time_In, Time_Out. Order# can be 1, 2, 3, 4, 5. While at the same time Order# 1 can go through more than one process, i.e.: Receiving, VisualTest, MechanicalTest, ..., Shipping. Every Order# does not necessarily goes through all processes, but surely they will go through receiving process and shipping process. For each process we will have recorded time when the order# comes in and when it finishes with each process. I need to calculate the length of time from Time_In from Receiving to Time_Out in Shipping.
I have a table with column value like '123 345 678 143 648' like that. What I need to do is I have to take each code value and put it as a new record in another table. So, if I say 'Select substring(column_name,1,3) from table' then it is very fast (fraction of second). But since I need to take each code and the # of codes in each record may vary, I am using a while loop to take each code and so I delclared a variable @i and now my select statement is like this: 'Select substring(column_name,@i,3) from table'. Interesting now this select statement is taking almost 2 mins for each iteration.
Why it is like this? Is there any way I can reduce the time taken to execute each iteration?
Hi guys, I am having difficulty calculating the time duration between receiving process to shipping process. I have a table that consists of: Order#, Processes, Time_In, Time_Out. Order# can be 1, 2, 3, 4, 5. While at the same time Order# 1 can go through more than one process, i.e.: Receiving, VisualTest, MechanicalTest, ..., Shipping. Every Order# does not necessarily goes through all processes, but surely they will go through receiving process and shipping process. For each process we will have recorded time when the order# comes in and when it finishes with each process. I need to calculate the length of time from Time_In from Receiving to Time_Out in Shipping.
I have a report that calculates mean, min, max, stddev of somer exercises for a class (pushups, situps, trunk lifts, etc). I also have to calculate those for the 1-mile run time (ex: data -- 7:56, 6:35, 9:45 ( in minuteseconds). Obviously the standard Avg(), Min(), Max(), StdDev() functions won't work for time. So I put in some custom code to convert the time to seconds so I can use the standard functions on the seconds and also code to convert that answer back to minuteseconds. Max() works, but for example when I try to calculate the min(), it includes nulls from the dataset as zeros (not every student has to do the mile run). So the min is always 0. How can I exclude nulls from being calculated. The min() function excludes nulls so it returns the correct min() on integer data. What else can I do?
I have a customer time duration format like "12:15" that means 12 minutes and 15 seconds. I want to convert to be "12.25 " that means 12.25 minutes. Any suggestion please. Thanks a lot.
Thanks for setting up such a great site and forum.
Here is my problem:
I have a table like the following in SQL Server 2005:
order | taskid | main_person | temp_person | start_assign_date 1 | 3 | John | John | 2008-01-01 10:20:22 2 | 3 | John | Joe | 2008-02-05 15:20:22 3 | 3 | John | John | 2008-02-07 20:25:20 4 | 6 | Joe | Joe | 2008-01-01 10:20:22 5 | 6 | Joe | Mike | 2008-02-01 10:20:22 6 | 10 | Doug | Doug | 2008-01-01 10:20:22 7 | 7 | Russ | Russ | 2008-02-01 11:20:22 8 | 7 | Russ | Mike | 2008-02-08 12:20:22 9 | 7 | Russ | John | 2008-02-10 20:05:12
It was made to record who was in charge of a specific task at a specific time. Each task has its own main responsible person and some substitutes for that person as Temporary Persons (who did the task while main person was away). The Main Person's name is in the temp_person column when he is doing the task by himself.
I'd like to generate a report that shows: - in a specific time period - which persons were in charge of a specific task and - for how long
Something like this:
From 2008-##-## to 2008-##-## Task 3 - John - 15 days Task 3 - Joe - 5 days Task 6 - Joe - 18 days Task 6 - Mike - 2 days Task 10 - Doug - 20 days
I have some ideas to do that when there are both start and end dates for every record but I couldn't find a way to use the next assignment start date for each task, as the end date for its previous record (in that task group) to calculate the duration for that record.
I can group the tasks and users and put them in the chronological order but I can't indicate the next start date as the end date for the previous record (in specific task group) to use the date difference functions.
Hi,I have a task at hand to reduce the time taken for search query toexecute. The query fetches records which will have to sorted bydegrees away from the logged in user. I have a function whichcalculates the degrees, but using this in the search query slows theexecution and takes about 10 secs to complete which is unacceptable.Please advice. Your help is much appreciatedFor more details plz see:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97021ThanksIsfaar
I am working with a proprietary database that records the date, time,location, and speed of a vehicle. It is pulling this information fromGPS unit tied to a vehicle. The table is populated with values thatare pulled from the GPS unit every 30 seconds. I need to find theduration of time for when a vehicle is stopped. I have created acursor that runs though all of the tables, and gathers the data forwhen the vehicle's speed is equal to zero. I have inserted this datainto a temp table, where I am having a problem is identifying theduration of time the vehicle is stopped. I cannot figure out how toquery the table and grab the first row when the speed is zero and thelast row where the speed is zero.The data looks like this…Date TimeVehIDLatLongSpeed12/31/2003 66144 295708535220842.92747-83.63003012/31/2003 66159 295708535220842.92696-83.62935012/31/2003 66179 295708535220842.9271 -83.62902 012/31/2003 66269 295708535220842.92709-83.62903012/31/2003 66359 295708535220842.9271 -83.62901012/31/2003 66449 295708535220842.92709-83.62904012/31/2003 66539 295708535220842.92708-83.62903012/31/2003 66629 295708535220842.92708-83.629 012/31/2003 66719 295708535220842.92708-83.62903012/31/2003 67414 295708535220842.9269 -83.630920Any help would be greatly appreciated…Thanks,Dave
Hey Guys,I have been trying to work out how I would delete a record that was created more then 10 minutes ago.I can use this to delete records older then a day.DELETE FROM DownloadQueue WHERE Downloading = '0' AND QueuePos = '0' AND DateTime < GETDATE() - 1Just need something now that will do it for just 10 minutes.Cheers.
Would like to know if it is possible to calculate the duration of a Datetime Start and End Dates ignoring all overlapps? Eg: 1) StartTime 10:00:00 EndTime 11:00:00 Duration: 01:00:00 2) StartTime 10:30:00 EndTime 11:15:00 Duration: 00:45:00 Total Duration should be 01:15:00 and not 01:45:00
Hey All,I think this is something like locking, but not quite. I need to select a record in a database and display the information to the end user. The end user has a few minutes to review it before giving the ok at which point I will update the record.However, while the user is reviewing the record, i dont want anyone else to be able to grab that record. I also want to make sure that if the user doesnt submit the page, that the record will be freed up and not indefinitely marked as taken.What would be the best way to do this? This is with .net 2.0 and sql2000
============================ ALTER proc [dbo].[sp_CalculateMedianTimeInDepartmentMinutes]
@StartDate date ,@EndDate date as --== Check if count is even or odd declare @modulo int select @modulo = (Select COUNT(*)%2 from ED_data where AdmitDateTime between @StartDate and @EndDate ) --=== Get Median
[Code] ....
My fellow developer is using this code to calcuate a madians in many columns (see below). The problem is that it takes about 2 minutes to execute this code. Is there a way to reduce the time of execution?
I attach also a sample of the view
============== ALTER PROCEDURE [dbo].[sp_ED_Measures] @StartDate date, @EndDate date, @Hospital varchar(5) AS BEGIN SET NOCOUNT ON;
CREATE TABLE #Attendance( [ID] [int] IDENTITY(1,1) NOT NULL, [StudentID] [int] NOT NULL, [ClassID] [int] NOT NULL, [DateAdded] [datetime] default getdate() NOT NULL ) ON [PRIMARY]
insert into #Attendance(StudentID,ClassID,DateAdded) values(1,1,'2014-10-07 10:38:02.900')
[Code] ....
DateAdded column in first table is nothing but in and out time.
Now I want to prepare a query where I want to consider MIN DateAdded and max DateAdded and calculate the duration of student present in the class.
Validations i need to consider are:
If class is starting at 10am then student can come at 9:50am, i.e. Dateadded column should consider as student present in that class if value is less that 10 minutes of StartTime from #ClassAttendance table. Class End time i want to calculate depending upon ClassMinutes from #ClassAttendance
Also DateAdded column should be 10 minutes plus compared to calculated endtime. If its more than that consider lower DateAdded time.
And by using this thingIi want to calculate total number of minutes student present in the class and number of minutes absent.
If there is only one DateAdded for class then consider as a absent student.