Hi All, I'm probably missing something (looking at it too long), but in a stored procedure can I select all distinct values from one table and then do a for each loop based on each of those returned rows, which themselves build up sql statements based on the distinct values? Cheers. ...
I tried to reproduce the situation of question [1]. On table, taken and filled with data from wiki's "Isolation (database systems)" [2], in SQL Server 2008 R2 SSMS, I executed: 1) first in first tab (window) of SSMS -- transaction isolation level in first window does not influence results (?) -- initially I thought that second ...
Given these two tables: [dbo].[Task] [Id] [Duration] [ScheduledStart] int int Nullable DateTime [dbo].[TaskDependencies] [Id] [PredecessorTaskId] [TaskId] int FK_Task_Id FK_Task_Id I'm trying to find the latest end date of the Tasks immediate predecessors. For the Task table, ScheduledStart is nullab...
Hi, I want to insert a page_number in a record that continuously count base on duplicate in a column. example output: ID PAGE_ORDER PAGE_NUMBER 1 7 1 2 7 1 3 7 1 4 7 1 5 7 1 6 10 2 7 10 2...
How can rows with Non-Ascii Characters be returned using SQL Server? If you can show how to do it for one column would be great. I am doing something like this now but it is not working select * from Staging.APARMRE1 AS ar where ar.Line like '%[^!-~ ]%' For extra credit, if it can span ALL VARCHAR columns in a Table would be outst...
I have an application which connects to SQL Server 2000 (using a generic SQL Login and SQL Authentication). I would like to implement some logging via triggers to track data changes. I can't use USER_NAME() because that returns the generic account. I've poked through master..sysprocesses and it doesn't seem to record the username (alt...
On an every minute interval, I would like to consolidate values in my SQL table to compress space. If I have a table with values such as: UserId Value Date 1 2 10/08/2010 10:30:00 1 2 10/08/2010 10:30:10 1 2 10/08/2010 10:30:20 1 2 10/08/2010 10:30:30 1 2 10/08/2010 10:30:40 1 2 10/...
I probably didn't word the question right buy I have a query that is taking a substantial amount of time because of 'or'. select stuff from table1 T1 left join table2 T2 on T2.field1 = T1.field or T2.field2 = T1.field where some condition I have to check both fields to join. Is there a better way to do this? ...
MySQL provides a string function named FIELD() which accepts a variable number of arguments. The return value is the location of the first argument in the list of the remaining ones. In other words: FIELD('d', 'a', 'b', 'c', 'd', 'e', 'f') would return 4 since 'd' is the fourth argument following the first. This function provides t...
The query below fetches the wageTypeID based on the current active hiring info record and salary record. SELECT wt.wageTypeID FROM TimeSheet t INNER JOIN Employee e ON e.employeeID = t.employeeID INNER JOIN Salary s ON s.salaryID = t.salaryID INNER JOIN Wage w ON w.wageID = s.wageID INNER JOIN EmpHiringInfo ehf ON ehf.Emp...
I have a table-valued-function working functionally well. I wanted to reuse its result in multiple SELECT statements. How can I do that? ...
The question Given a path like thus: G:\path\foo\..\bar\baz.txt Which we can immediately parse in our minds, and as the OS will parse as: G:\path\bar\baz.txt What's the fastest way to do that programmatically? In other words, does a library function already exist that will do this for me cleanly so that I don't botch it myself. M...
This is probably a really easy question, it's just very hard to google a word like "All". SELECT a.Id, Max(b.Number) FROM Table1 a JOIN Table2 b ON a.FK = b.Id GROUP BY a.Id But I want to add a where clause that specifies that all b.Id's linked to an a.FK must have values. So basically I don't want to select the a.Id grouping of b...
As well as CHAR (CHARACTER) and VARCHAR (CHARACTER VARYING), SQL offers an NCHAR (NATIONAL CHARACTER) and NVARCHAR (NATIONAL CHARACTER VARYING) type. In some databases, this is the better datatype to use for character (non-binary) strings: In SQL Server, NCHAR is stored as UTF-16LE and is the only way to reliably store non-ASCII charac...
I have a query that fetches employee's name, date, working hours, wage and calculate salary based on working hours * wage. The minutes in working hours that is being calculated are discarded. I only get the value in full hour. Snapshot example: My main concern is on workingHours and wageAmount workingHours is displayed as 5....
Assume i have the following decimal value: 4.584406 I need a simple quick way to truncate the decimal without rounding up, so the output would be 4.5 I'm using T-SQL (SQL Server 2005/2008). Any help will be appreciated. ...
I have heard joins should be preferred over nested queries. Is it true in general? Or there might be scenarios where one would be faster than other: for e.g. which is more efficient way to write a query?: Select emp.salary from employee emp where emp.id = (select s.id from sap s where s.id = 111) OR Select emp.salary from ...
Assume i have 4.9 value. I would like to split 4 and .9 ... How can i do it? I could isolate 4 by using FLOOR(). What about the .9? How can i isolate it? I'm using t-sql sql server 2005/2008 ...
Hi What will happen if an error occurred during a- Declaring Cursor b- Fetch data What will happen if error occurred before Cursor is closed, does it close automatically? When I use a Cursor, What is the best practice for handling errors? Thanks ...
SELECT COUNT(td.timeSheetID) FROM TimeSheet t INNER JOIN TimeSheetDetail td ON t.timeSheetID = td.timeSheetID WHERE (CONVERT(DateTime, t.setDate, 23) BETWEEN '2012-08-10' AND '2012-08-12') AND t.employeeID = 300 The above code returns the value i'm looking for. However, when i do the following, i get nothing: SELECT COUNT(td.ti...