Hi I've got records that indicate when a job starts and when it ends. The End time is not recorded until the job ends, but the start time exists from the time the job starts. What I want is to know how many jobs were running in a given time period. declare @data table ( JobId INT IDENTITY(1,1), StartedAt DATETIME NOT NULL, ...
How do I change: SELECT user.postcode, max(postcode.postcode) as postcode FROM user INNER JOIN postcode ON user.postcode LIKE CONCAT( postcode.postcode, "%" ) GROUP BY user.postcode into an UPDATE similar to? UPDATE user INNER JOIN postcode ON user.postcode LIKE CONCAT(postcode.postcode, "%") SET user.lat = postcode.lat, user.lng ...
I have more tables that I want to cross join them and I want to show each table with fields like this: tb1.filed1 tb1.filed2 tb2.filed1 ..... What should I do? How can i select fields with details like it's table's name. thanks.... ...
I have a query that's selecting a bunch of fields related to names and addresses of customers but it boils down to: SELECT DISTINCT a, b, c, ... FROM big_dumb_flat_table it returns a bunch of records (10986590). When I replace the commas in the select-list to format it as a pipe-separated concatenated string: SELECT DISTINCT a + '|'...
How can I alter Update Stored Procedure so if developer want to set only Size then he/she does not requireed to pass TimeStamp. Moreover, then what will be execute code for this procedure? Scenario: Update TimeStamp = getdate() field whose SizeID = 1 AND Size =Large Note: This field Parameters and condition parameters must be dynamic ...
Assume a table named 'log', there are huge records in it. The application usually retrieves data by simple SQL: SELECT * FROM log WHERE logLevel=2 AND (creationData BETWEEN ? AND ?) logLevel and creationData have indexes, but the number of records makes it take longer to retrieve data. How do we fix this? ...
Hello I have a records like this: start, end , total 830 , 1300, 5 1400, 1430, 2 that I'd like to expand to: instance , total 830 , 5 831 , 5 832 , 5 ... 1299 , 5 1300 , 5 1400 , 2 1401 , 2 ... 1429 , 2 1430 , 2 How can I do this using SQL in MSSQL 2005? EDIT...
I can not for the life of me figure out what is wrong with this code: IF NOT EXISTS(SELECT * FROM sys.columns WHERE name = 'Report_Date' AND object_id = OBJECT_ID('TempTable3')) ALTER TABLE TempTable3 ADD Report_Date datetime --Set "ReportDate" field to the date the data was extracted less one. UPDATE TempTa...
Having a SQL table like UserID |Attribute | Value 1 |Username | Marius 1 |Password | Fubar I want to create an output like: 1 | Marius | Fubar Maybe I'm just too tired to see it, doesn't sound too complicated, but I just can't seem to figure it out. Any help is appreciated. ...
HI all, I'm having some difficulty understanding the rationale behind group by aggregation in sql server 2005. I have the following query which works fine and returns one row for each contact.id and the 1st occurence of event SELECT contact.id ,MIN(eve.date_created) FROM _contact contact WITH(nolock) INNER JOIN table2 tb2 WITH (nolock)...
Hi All, I am planning on using a recursive table in my data model so that I can support an undetermined number of children that can live under a parent. My example is a Table of Contents where I don't know how deep my subsections will be under a chapter. The issue I am stumbling over is what techniques do folks use to populate there ...
Maybe I need more coffee this morning but here goes... I have a very simple inventory system. Right now I have two tables: Items and Inventory. Items Id Title YearReleased Inventory Id ItemId(Foreign key to Items) Quantity QuantityOnHand Each item has one inventory and each inventory belongs to one item. The relationship betwe...
I have two tables A & B, and B has a many:1 relationship with A. When querying rows from A I'd also like to have corresponding B records returned as an array and added to the result array from A, so I end up with something like this: A-ROW field field B-ITEMS item1 item2 item3 Is there a clean way to do th...
I am used to oracle and now been thrown T-SQL, I am doing a course shortly but can you help out until then. I have a list that I need to group in minutes. Sample of rowdate data SELECT ROWDATE,count(rowdate) FROM [mydb].[dbo].[mytable] GROUP BY ROWDATE order by 1 2010-08-16 15:01:18.110 1 2010-08-16 15:01:18.203 1 2010-08-16 15:01:18...
Hi there all, Please can you help me out as Documentation on this is very Poor. I have a local report for an ASP.net Application. There is a Image on this report which i need to Control I need to set the Height and Width of the image with values from the Database. Please can someone out the Help me with this. Many Thanks, Niven So...
Having multiple indices for an SQL table, is there a way to know what index will be used automatically when using a specific query? EDIT: I wanted the question to be general, but I mostly use MySQL, PostgreSQL and SQLite ...
I have several tables in a database. One table (tbl_transactions) has thousands of orphaned records that are not linked to any of the remaining tables. I need to run a script that will delete these records in order to regain some lost space in my database. I tried to run a script that deleted all the records, but the log file consumed...
I'm working with a system which had to create objects in one database based on objects being created in another database. The objects are not duplicates, so I can't simply replicate the objects. I have code below which gives a simplified demonstration of what I'm trying to do. If you uncomment the ALTER DATABASE statements then it will ...
Im having trouble getting my head round subqueries in Mysql. Fairly simple ones are ok, and most tutorials I find rarely go beyond the typical: SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2); What I am trying to pull out of my database is the following (I'll try my best to explain this without any background on our db): Re...
How to output sql statement right before it's launched? To check all placed data inside prepared statement. ...