I need to present a flag - 0 if a condition is not meet, 1 if it is - and I can do it in two different ways : Get Employee ID, name, 1 if has others in suborder - 0 if not : SELECT e.ID , e.Name , ISNULL ( ( SELECT TOP 1 1 FROM Employee se WHERE se.ManagerID = e.ID ) , 0 ) AS HasSubordinates FROM Employee e or SELECT ...
I have two date fields with times.They are 1.2008-06-09 10:18:00.000 2.2008-06-10 11:20:00.000 I have to find the difference between the above two dates in the format - "24 hour:2 minutes:0 seconds" Is there any method to get this result in sql 2000? ...
Hai guys, I ve used the following split function, CREATE FUNCTION dbo.Splitfn(@String varchar(8000), @Delimiter char(1)) returns @temptable TABLE (items varchar(8000)) as begin declare @idx int declare @slice varchar(8000) select @idx = 1 if len(@String)<1 or @String is null retur...
I've read the definition of logical reads from: http://msdn.microsoft.com/en-us/library/ms184361.aspx it says: "Number of pages read from the data cache." I have two tables (Row count of table_1 is 141, and table_2 is 16.811), when I run those two queries, it gives the following result. SELECT * FROM Table_1 results Scan count 1, ...
I am using the Function in stored procedure , procedure contain transaction and update the table and insert values in the same table , while the function is call in procedure is also fetch data from same table. i get the procedure is hang with function. Can have any solution for the same? ...
I have an encrypted SQL Server stored proc that I run with (or the vb .net equivalent code of): declare @p4 nvarchar(100) set @p4=NULL declare @p5 bigint set @p5=NULL exec AA_PAY_BACS_EXPORT_RETRIEVE_S @PS_UserId=N'ADMN', @PS_Department=N'', @PS_PayFrequency=2, @PS_ErrorDescription=@p4 output select @p4, @p5 this returns 2 datasets a...
I have what seems to be a really easy SQL query I can't figure out and its driving me nuts. This is SQL 2008. Basically, there is a status field where the can pick "pending", "satisfied" or all. If they send in "pending" or "satisfied" there's no problem. But when they pick all I'm having problems. Mostly because I can't figure out how ...
Is there a way in TSQL to do something like this: select a,b,c, case when a=1 then 5 when a=2 then 6 end as d from some_table where d=6 The actual case statement is really complex, so I'm trying to avoid repeating it in the where clause? Are there any tricks to do this? (I think there's a trick in MySQL to use "having d=6"). ...
My ERP database uses non-nullable datetime fields. However, it enters '' for the datetime when one isn't available and returns ‘1900-01-01 00: 00: 00.000’ as the value. I want to suppress the 1900 dates while stripping the Date only from the Datetime field. I created the following UDF to do that: CREATE FUNCTION ExtractDate(@DirtyDat...
Is there a way to declare a list of items in an sql server stored procedure using T-SQL and then loop through the items? I'm trying to do something like this: input_string = 'my dog has fleas.' list_remove = 'a', 'e', 'i', 'o', 'u' for each item in list remove input_string = replace(input_string, item, '') end And in the end inp...
Let's say I have a table like this: Customer,Invoice Type,Balance A,Good,50 A,Good,10 A,Bad,20 B,Good,20 And I want to make a report like this: Customer,Invoice Type,Balance,Total Adjusted Balance A 40 Good 50 10 Bad 20 B 20 Good 20 Where total adjusted bal...
I'm a C# developer who has done some basic database work in T-SQL. However, I need to write a very complicated stored procedure, well above my T-SQL knowledge. Will writing a stored procedure in C# using the .net CLR as part of SQL Server 2008 cause my stored procedure to be less efficient than if it were written in T-SQL? Is the differ...
I recently had to root out the cause of intermittent ODBC errors in a 12 year old SQL Based application. The culprit turned out to be this statement at the end of one of the stored procedures: DUMP TRAN NotTheRealDBName WITH NO_LOG I wasn't familiar with this command, so I did some research and found that it makes a backup of the tra...
Hai guys, I have a table with a column named Is_Deleted and my query is select Is_Deleted from Stock where Stock.Mat_Id=1 Now i have to write a condition to check whether all values are 1 else i have to terminate my loop.. How it can be done? any suggestions... ...
I want to think of a way to display and query comments. Each comment can have children and siblings. I would like to display 40 comments in a page. However, I would like to show all the children. The problem here is if a child has more then 40 comments beneath it this will not work out. Also, I have no idea how I might get the comments....
I want one record from a table having unique Primary Key and duplicate Foreign Key Please see attached image below Thanks ...
I have a problem , got three Tables Table A ID Employee 1 1 2 2 3 3 Table B Id EMployee HoursWorked HoursCode 1 1 10 Basic Hours 2 1 20 Holiday Pay 3 2 10 ...
Hello all, I have strings like that OPEN SYSTEMS SUB GR (GM/BTIB(1111)/BTITDBL(2222)/BTVY(4444)/ACSVTYSAG) and I need to extract 2222 from it. What I was doing is this on the GROUPS String: SUBSTRING(GROUPS, CHARINDEX('(',GROUPS, CHARINDEX('(',GROUPS, CHARINDEX('(',GROUPS,0)+1)+1)+1, 4 ) AS GroupNo However I see that it is no...
Say for instance I'm joining on a number table to perform some operation between two dates in a subquery, like so: select n ,(select avg(col1) from table1 where timestamp between dateadd(minute, 15*n, @ArbitraryDate) and dateadd(minute, 15*(n+1), @ArbitraryDate)) from numbers whe...
I have a problem/question that I may have been staring at too long today. I have a stored procedure that receives data from an web application. The data comes in as smalldate time format. I am trying to pass this information to a second stored procedure but the second one won't fire unless the data is in single quotes. Would it be ...