I have to produce an ad hock report on the number of transactions made with different credit card types. For the purposes of the report it is fine to assume that all credit cards that start with a 4 are VISA cards and that those that start with a 5 are MasterCard. This query works well for the above distinctions: select card_type = ...
public static int MassEmpNumUpdate(string empToUpdateFrom, string empToUpdateTo) { string sql; int retval; using (cn = new SqlConnection(ConnectionString())) { cn.Open(); sql = "uspUpdateDet"; // THIS IS THE 1ST SP using (cmd = new SqlCommand(sql, cn)) { cmd.CommandType = Comman...
What is wrong with this statement?? SELECT ID, datediff("mi", Start, End) as Total FROM TimeTable WHERE Total is not null I get an error "Invalid column name" ...
I've been to on a few interviews for middle tier roles. I've gotten a lot of questions about TSQL, indexing, database fundamentals, but not a single question on ADO. Is this a normal experience or is there a reason for this? ...
I have a Function called dbo.GetFoo(). I also have a unit-testing Stored Procedure called AssertEqual (which takes @TargetValue sql_variant, @ExpectedValue sql_variant, and @Message varchar) I want to call GetFoo() and check to see if it's returning the right value 'X'. My T-SQL statement is: exec AssertEqual dbo.GetObjectType(), 'S', ...
I'm only dealing with one database table / entity object: NodePath. Given a particular Node, I want to get only a subset of all its NodePaths according to this query: select * from NodePath where NodeId = @GivenNodeId and Id in ( --active paths select a.Id from NodePa...
I have a table Auditing logins, I'd like to just extract the latest login for each user, which the below sql accomplishes. How would I format the sql query into a linq to sql query? SELECT * FROM AuditTable adt1 inner join UserTable usr on adt1.[UserName] = usr.[User_Id] WHERE [TimeStamp] = ( SELECT MAX([TimeStamp]) ...
Given the following result set: --------------------------------------------------------- CustomerID Service TransType SubTotal Tax NetTotal --------------------------------------------------------- 106 A CREDIT 12.52 - 12.52 106 A CREDIT 10.07 - 10.07 106 ...
I am trying to create a stored procedure that takes a bit parameter which if true orders by one column and if false orders by another column. How would I implement this? Here is what I have so far CREATE PROCEDURE [dbo].[CLICK10_GetCP] @switch AS BIT AS BEGIN SELECT acct_nbr, acct_name FROM acct ...
This will be a long question so I'll try and explain it as best as I can. I've developed a simple reporting tool in which a number of results are stored and given a report id, these results were generated from a particular quote being used on the main system, with a huge list of these being stored in a quotes table. Here are the current...
I tried UPDATE TABLENAME SET COLUMNNAME = REPLACE(COLUMNNAME, '\t', '') But I don't know how to write the TAB in t-sql ...
In T-SQL, how would you check if a string doesn't contain another string? I have an nvarchar which could be "Oranges Apples". I would like to do an update where, for instance, a columm doesn't contain "Apples". How can this be done? ...
Goal: When everybody else does SELECT * FROM mytable they see one version of the table. But when a specific user does SELECT * FROM mytable they see another version of the table. I think I'm like halfway there with creating a new role and putting the single user in it. Then creating a copy of the default table with SELECT * INTO newro...
hi What is faster query select...where Fname = 'mark' or select...where Fname like 'mark' thank's ...
I'm trying to create a DDL trigger for a specific table and this is the best I could come up with: CREATE TRIGGER MyTrigger ON DATABASE FOR DDL_TABLE_EVENTS AS DECLARE @EventData xml SET @EventData=EVENTDATA() IF @EventData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(50)')='TABLE' AND @EventData.value('(/EVENT_INSTANCE/O...
I am using SQL Server 2005. I have three tables - Users, Groups, and GroupUsers. GroupUsers contains the two PKs for a many-to-many relationship. I have a view to get all the user information for a group as follows: SELECT * FROM GroupUsers JOIN Users ON GroupUsers.UserID = Users.UserId I want to create the inverse of this view - I w...
If I've got a string that consists of other strings delimited with "/" character (xxx...xxx/xxx/xxxx) how can I get the last and the almost last (the one before last) part with t-sql? It should probably be some combination of charindex() and right(). ...
Shog9 keeps on making my link lists look awesome. Essentially, I write a bunch of queries that pull out results from the Stackoverflow data dump. However, my link lists look very ugly and are hard to understand. Using some formatting magic Shog9 manages to make the link lists look a lot nicer. So, for example, I will write a query t...
I have a question about TSQL function Update. For example, I have a table with a field Name. If I check if the field Name is changed or not in a After Update trigger likes this: if Update(Name) Begin -- process End Will the Update still return TRUE even if Name is not changed? The following update statement will update it wi...
I've searched high and low for the answer to this, but I can't figure it out. I'm relatively new to SQL Server and don't quite have the syntax down yet. I have this datastructure (simplified): Table "Users" | Table "Tags": UserID UserName | TagID UserID PhotoID 1 Bob | 1 1 1 2 ...