Just wondering what is the best time to ensure that we only return data when the datetime field is the same when compared between two datetimes within a minute difference.
As in the following should return the data:
'2015-04-09 09:00:20' compared to '2015-04-09 09:00:50'
And the following should not return the data:
'2015-04-09 09:01:20' compared to '2015-04-09 09:00:50'
The problem, is that I'm merging data from three different result sets, which they all have data for every minute, however, the timestamp can be different by seconds or milliseconds.
So, I'm only interested to return the data when the two fields that I'm comparing are equal within a minute. I need to ignore seconds and milliseconds.
I have collected perfmon data that is in every 15 seconds. I need to run a query that will only retrun rows that are 5 minutes from the last row starting at a specific date/time.
Im doing a report on total sales, however my statement below will return values that are equal to both fields ONLY.For example I want to do a query using two text boxes 'from' and 'to 'and count the total sales between the product dates 'Veh_Tyres_Date' and Veh_Parts_Date and 'Veh_Tyres Price' and Veh_ Parts Price'. however it works but if for example I do a search for 01/05/2015 from 31/05/2015 it will not return anything if the second field doesnt contain a sales date between that period.
SELECT tblVehicles.Veh_Parts, tblVehicles.Veh_Parts_Date, tblVehicles.Veh_Tyres, tblVehicles.Veh_Tyres_Date FROM tblVehicles WHERE (((tblVehicles.Veh_Parts_Date) Between [Enter From Date] And [Enter To]) AND ((tblVehicles.Veh_Tyres_Date) Between [Enter From Date] And [Enter To]));
We have data that consists of an employee number, a start time and a finish time, similar to the example below
EMP STARTTIME ENDTIME
00001 10-Feb-2012 06:00:00 10-Feb-2012 10:00:00
00002 10-Feb-2012 07:15:00 10-Feb-2012 10:00:00
00003 10-Feb-2012 08:00:00 10-Feb-2012 10:00:00
I am trying to come up with a procedure in SQL that will give me each 15 minute block throughout the day and a count of how many employees are expected to be at work at the start of that 15 minute block. So, given the example above I would like to return
In my report, I am having Listbox for users to choose Country, City & Company. The user can choose Country. Based on the country selection, cities will be listed out. Based on the city selection, Companies will be listed out. They can choose companies.
Now, I have to show two set of results.
A. List of Companies as per selection ( dataset with equal to selection )
B. List of Companies which are not selected ( ie dataset with not equal to selection )
I have created a dataset with all companies and filter it by selection. When I tried with the filter option in the Dataset, I am able to check for only one value and not for multiple value. If the selection is one company, then I can filter it. But if they choose 5 companies, I am not not able to filter it. Is there any other option I can try out.
I'm having a hard time to getting back an xml data back from a stored procedure executed by an Execute SQL task.
I'm passing in an XML data as a parameter and getting back resulting XML data as a parameter. The Execute SQL task is using ADO connection to do this job. The two parameters(in/out) are type of "string" and mapped as string.
When I execute the task, I get the following error message.
[Execute SQL Task] Error: Executing the query "dbo.PromissorPLEDataUpload" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 2 ("@LogXML"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I also tried mapping the parameter as XML type, but that didn't work either.
If anyone knows what's going on or how to fix this problem please let me know. All I want to do is save returning XML data in the parameter to a local package variable.
I have a SQL table that has data filled with million records and the date is in minutes it looks like :-
RowDateTime Meter 1 Meter 2 Meter 3
25/05/2006 02:49:00 1220 450 489
25/05/2006 02:50:00 1223 470 500
25/05/2006 02:51:00 1227 490 511
25/05/2006 02:52:00 1230 510 522
25/05/2006 02:53:00 1233.5 530 533
25/05/2006 02:54:00 1236.9 550 544
25/05/2006 02:55:00 1240.3 570 555
25/05/2006 02:56:00 1243.7 590 566
I want to make a query to the above table and convert the data to houlry by summing Meter1,Meter 2 and Meter 3 to be the average. I want to import all the hourly data to a new table that will look like :-
I have a situation where I have table with over a billion records and needs to be scrubbed. Table does have a field with date time timestamp. I have been deleting rows from the table using the script below which basically provides me delete statements by date for records older than 90 days.
But now on each day row count is over 30 million rows and it takes forever to delete by date and transaction log becomes humongous.
So I would like to scrub it in 5 minute intervals instead of daily for records older than 90 days. Even in 5 minute intervals the record count tends to be around a million. This will keep the delete slice small enough to not a gigantic transaction log.
declare @startdate Datetime declare @enddate Datetime set @startdate = getdate()-480 set @enddate = getdate()-90
WHILE (@startdate < @enddate) BEGIN print 'delete from vending where DetectedDate < ''' + CONVERT(varchar(10), @startdate, 101) +'''' set @startdate = @startdate+1 END
I am hoping to modify the script above to produce a script with statements like this for a window between last 90 and 120 days:
delete from vending where DetectedDate <'6/15/2015 8:25:00 PM' go delete from vending where DetectedDate <'6/15/2015 8:30:00 PM' go delete from vending where DetectedDate <'6/15/2015 8:35:00 PM' go
I'm trying to create a query to return Open, Close, Max and Min Price for each 1 minute interval. Source data has two fields - Price, and Datestamp at 5 second intervals.
I can calculate the Max and Min (below) and set the datestamp to the middle of the interval, but get stuck on how to also return the Open and Close price for each interval.
SELECT MAX(price) AS MaxPrice, MIN(price) AS MinPrice, DATEADD(ss, 30, DATEADD(n,DATEDIFF n, '1/1/2006', DateStamp),'1/1/2006')) AS DateStamp FROM MasterData GROUP BY DATEDIFF(n, '1/1/2006',DateStamp)
The following is the full error message. I am posting the code after.
Server Error in '/XprtDr' Application.
The data types text and nvarchar are incompatible in the equal to operator. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: The data types text and nvarchar are incompatible in the equal to operator.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
Hi, If I make a select query on my table, this error appears: "The data types text and varchar are incompatible in the equal to operator"
In my table , I have 4 fields with "text" datatype. My query is like : "Select * from table where field1='test'" It's seems to be simple, but it doesn't work!!!
Hi, i have a table in sqlexpress named Contacts: ID (int) -primary key- name (varchar(30)) lastname (varchar(30)) phone (varchar(15)) fax (varchar(15)) desc (text) In my default.aspx page, i have a GridView that has the conecction to this table. The GridView has the Editing and Deleting checkbox enabled but my problem is that i can't edit or delete any row when the page is running and the massage is this: "The data types text and nvarchar are incompatible in the equal to operator" It would have to work, but i don't know what happen, Please, any help!
If I want to return to a point in time for all my databases in SQL 2000 can I just copy all the files in the data directory to a safe place. And then when I want to go back to that point in time, just copy them all back?
I need to return all the rows. Where the ESNnumber only returns the most recent one that is associsted with the Asset.
Basically, I need the info most current ESN number only. They are 19,00 rows of each ESN number but it returns 40,000. Duplicates.
SELECT TOP (100) PERCENT dbo.AssetType.Description, dbo.AssetCustomAttributeDef.Name AS [Custom Asset], dbo.ESN.EsnNumber AS [ESN #], dbo.AssetAttribute.AssetDescription AS [Description Detail], dbo.Asset.Barcode, dbo.Asset.SKU, dbo.InventoryOrigin.WarehouseDescription AS [Inventory (W/H)], dbo.ESN.DateImplemented, dbo.ESNTracking.TraceTime, dbo.ESNTracking.PreviousTraceTime, dbo.ESNTracking.HasMoved, dbo.ESNTracking.DistanceMiles, dbo.ESNTracking.Direction, dbo.ESNTracking.Landmark, dbo.ESNTracking.FemaLocation AS Fema, dbo.ESNTracking.ReportTime AS [Report Time], dbo.ESNTracking.CurrLocStreet AS Address, dbo.ESNTracking.CurrLocCity AS City, dbo.ESNTracking.CurrLocState AS State, dbo.ESNTracking.CurrLocZip AS Zipcode, dbo.ESNTracking.CurrLocCounty AS County, dbo.ESNTracking.MapUrl AS [Map Link], dbo.ESNTracking.ReplaceByDate AS [Replace Batt.], dbo.ESNTracking.CurrMileFromStratix AS [From Stratix Now], dbo.ESNTracking.PrevMileFromStratix AS [From STratix Then] FROM dbo.AssetType INNER JOIN dbo.Asset ON dbo.AssetType.AssetTypeId = dbo.Asset.AssetTypeId INNER JOIN dbo.InventoryOrigin ON dbo.Asset.WarehouseId = dbo.InventoryOrigin.WarehouseId INNER JOIN dbo.AssetAttribute ON dbo.Asset.AssetAttributeId = dbo.AssetAttribute.AssetAttributeId INNER JOIN dbo.EsnAsset ON dbo.Asset.AssetId = dbo.EsnAsset.AssetId INNER JOIN dbo.ESN ON dbo.EsnAsset.EsnId = dbo.ESN.EsnId LEFT OUTER JOIN dbo.ESNTracking ON dbo.EsnAsset.EsnId = dbo.ESNTracking.EsnId LEFT OUTER JOIN dbo.AssetVehicle ON dbo.EsnAsset.AssetId = dbo.AssetVehicle.AssetId LEFT OUTER JOIN dbo.AssetCustomAttribute ON dbo.EsnAsset.AssetId = dbo.AssetCustomAttribute.AssetId LEFT OUTER JOIN dbo.AssetCustomAttributeDef ON dbo.AssetCustomAttribute.AssetTypeId = dbo.AssetCustomAttributeDef.AssetTypeId
i have using BCP to output SP return data into txt file, however, when it return nothing , it give SQLException like "no rows affected" , i have try to find out the solution , which include put "SET NOCOUNT ON" command before select statement, but it doesn't help :(
anyone know how to handle the problem when SP return no data ?
I get this error: The data types text and text are incompatible in the equal to operator. when trying to execute this query SELECT id FROM users WHERE username=@userName
How to return time & number format that has set in the regional setting using stored procedure. Following is my sp for getting current date format from Sql Server.
How to design at database level such a way so that when I implement a SQL query that returns one hundred thousand rows only display 25 rows at the client (Web page at a time). How can I accomplish this?
Once I display first 25 rows then how do I bring next 25 rows and so on. Can it be done via paging or there are other techniques. However I asked to design this in the database level. I am using MS SQL Server 2008. Front end Visual Studio 2010. I use C# for coding.
As part of analyzing our fire department's response times, we measure the time it takes to get a certain amount of people on a fire scene. I have two table that contains incident data. The incident table contains one row per incident. The apparatus table contains multiple rows per incident with each row contain the information for each unit. They are connected by the incident key with a one to many relationship.
The column apparatus.num_personnel contains the count of personnel for each fire truck. The goal is to capture the response time of the unit that is carrying the 10th person to the scene. In the example below, E78 was the unit that the sum of apparatus.num_personnel was => 10. There response time of 0:06:18 is returned to the incident table.
I realize I will need to create a logical test (calculated column on the incident table) that sums the number of personnel for each incident and then when that number => 10, then return the MIN Response Time of that row. From past projects, I understand I can create a temporary table (ADD COLUMNS) which will iterate over each incident row, comparing the units for that incident. I"m just not sure how to put it all together.
Overview of data model. The incident and apparatus are my data tables and the rest are lookup tables. The parameter table is used for a percentile measure, but doesn't play a role with this project.
USE [Testing] GO /****** Object: Table [dbo].[Testing] Script Date: 4/25/2014 11:08:18 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
[Code] ....
It seems to work fine with one million records.
Each primary key is unique, but the begindate is non-unique, and i guess even if i use datetime2 and add nanoseconds, from what i have read, there is a chance that i could have a duplicate datetime since the date is imported via XML from multiple sources.
We are using SQL Server 2008 as our database and use Access as a GUI. I am looking to create a form in Access where employees can access their time card and request changes from management. I want to use the format from the attached screen shot for the form. I pretty much know how to do it all, the only point of complication is trying to figure out the easiest way to get the transaction punch record data on employee_punch_record into a format where I can easily populate the form in the horizontal format you see in the screen shot.
I am not super strong in SQL, but figure I can do it using a formatting table of some sort. quick and easy way to move transaction records into a more horizontally oriented record?
Is it possible expire a report cache after less than one minute? I'm looking for a way to only have a report hit the database once every 10 seconds, no matter how many people are hitting it. Thanks.
Hello Probably a very simple problem, but im stumped. I have a table which gives the start-time and end-time of an employees work day. I want to create a view which contains a line of data for each 5 minute period worked. Please help.
Hi ! I have a textbox and a Search button. When the user inputs a value and press the button, i want a datagrid to be filled. The following code runs: Dim connect As New Data.SqlClient.SqlConnection( _ "Server=SrvnameSQLEXPRESS;Integrated Security=True; UID= ;password= ; database=dtbsname") connect.Open() Dim cmd As New SqlCommand Dim valor As New SqlParameter("@valor", SqlDbType.VarChar, 50) cmd.CommandText = "Ver_Contactos_Reducido" cmd.CommandType = CommandType.StoredProcedure cmd.Connection = connect cmd.Parameters.Add(valor) cmd.Parameters("@valor").Value = texto Dim adapter As New SqlDataAdapter(cmd) Dim ds As New System.Data.DataSet() adapter.Fill(ds) GridViewContactos.DataSource = ds GridViewContactos.DataBind() connect.Close() I drag a datagrid on the page and only changed its Id. Into the SQL database the stored procedure is the following: ALTER PROCEDURE [dbo].[Ver_Contactos_Reducido] (@Valor VARCHAR(100)) AS BEGIN SET NOCOUNT ON; SELECT NombreRazonSocial, Nombre, Apellido,TelefonoLaboral, Interno, TelefonoCelular, Email1, Organizacion FROM CONTACTOS WHERE NombreRazonSocial = @Valor OR Nombre = @Valor OR Apellido = @Valor OR TelefonoLaboral = @Valor OR Interno = @Valor OR TelefonoCelular =@Valor OR Email1 = @Valor OR Organizacion= @Valor END When i run the page i can't see the datagrid, and after i enter a text and press the button, nothing happens. What am i doing wrong?? Thks!!
Is there any non-obnoxious way (eg: without have to result to using datepart a million times) to do this? For instance, Oracle provides a function called Trunc which does it, but I cannot find an SQL Server equivalent. Anyone? TIA!!!
What I'd like to do with the sample query above is take an AVG for one hour for the values ZNT, SAT, RAT, RH...like I stated the records are every five minutes in the table, I'd like to create 60 minute AVGs.
hi, i need to synchronize a table from a database to two tables on a different database. I was thinking in a stored procedure to do it and create a job that execute it every minute.
I don't know if this is the right method... maybe it can be a bad decision for the server performance.
