tsql

Getting a boolean from a date compare in t-sql select

I am wondering if something along the lines of the following is possible in ms-sql (2005) SELECT (expiry < getdate()) AS Expired FROM MyTable WHERE (ID = 1) I basically want to evaluate the date compare to a boolean, is that possible in the select part of the statement? ...

Sql Ordering Hiarchy

I am working on a SQL Statement that I can't seem to figure out. I need to order the results alphabetically, however, I need "children" to come right after their "parent" in the order. Below is a simple example of the table and data I'm working with. All non relevant columns have been removed. I'm using SQL Server 2005. Is there an ...

Getting all possible combinations which obey certain condition with MS SQL

I need to constract an SQL query but I have no idea how to do it. If someone helps, I'll appriciate it very much. I have the following table GroupedBYField ConditionField ToBeSummeField 1 1 1 1 1 2 1 ...

DataAdapter Update() requires input parameter for Auto increment primary key column

While updating a DataTable to a SQL Server database I get the error message "Column 'PK_Column' does not allow nulls" after calling GetErrors() I don't want to provide a value for PK_Column because it is a auto increment primary key column in the database. My insert statement looks like this: INSERT INTO [Order] ([Customer_Id], [OrderTi...

PK, IDs and simulating an 'object' in a table

The object part is misleading. My question is not specific to one type of sql. ATM i am using sqlite but i will be switching to TSQL (It looks to be what my host is offering) and i am rewriting some tables and logic to clean things up. One pattern i notice is i have a bigint that could possible be one of 2+ keys and sometimes if i need...

Combining two T-SQL pivot queries in one

Suppose you had this table: CREATE TABLE Records ( RecordId int IDENTITY(1,1) NOT NULL, CreateDate datetime NOT NULL, IsSpecial bit NOT NULL CONSTRAINT PK_Records PRIMARY KEY(RecordId) ) Now a report needs to be created where the total records and the total special records are br...

Stored Procedure WHERE LIKE ID or NAME

Group, I am trying to create a stored procedure using one variable @Customer. What I want to do is put something in my WHERE clause that says if it is a number search the CustomerID field where the number entered is LIKE CustomerID... If a char is entered search the CustomerName field where the text entered is LIKE CustomerName. Below ...

Inline Select with LINQ

I need to replicate a T-SQL statement that has an inner select, using LINQ: SELECT *, CustomerTypes.Description as CustomerType, (Select Min(Distinct DocumentStatistics.StatDateTime) As LastStatCheck From DocumentStatistics Where DocumentStatistics.CustomerId = Customers.CustomerId) As LastStatCheck FROM Customers INNER JOIN Custome...

SQL: Datatype choice for values -1, 0 and 1

Consider a table whose job is to store a rating of an object. The column of interest here is the one called RATING. The valid range of values to store is: 1 0 -1 The first thought was to store as a tinyint smallint. This would store only one byte two bytes per row. Given the tinyint's range of 0 to 255 smallint's range of -32768 to 3...

SQL Statement from DML Trigger

How can i know which sql statement fired through trigger for select, insert, update and delete on table? Thanks, Paresh Prajapati http:\paresh-sqldba.blogspot.com ...

T-SQL cut XML into pieces

I have a table create table Profiles (id int, xml_data xml ) that has an xml column and for each row i have the same xml that looks like this : <Profile> <personalInfo> <contactInfo> <firstName>John</firstName> <lastName>Doe</lastName> <address>22 Park Avenue</address> <address2 ...

Help needed in getting total resource count (SQL Server 2005)

I have a requirement. I have been given the following table DownLineid UplineId Name DirectResources 1 2 Sarvesh 7 2 NULL Admin 5 3 2 Lonesh 10 4 2 Swapna 2 5 2 Priyanka 12 8 2 Sumi 1 6 ...

Computing aggregated values in a hierarchical structure bottom-up

Hi, I need to recompute quantities in a BOM structure which only guarantees correct quantity values for leaf nodes. If the above is a bit blurry don't worry, below is a simplified example. Consider a hierarchical table defining three columns: ID (PK), PID (parent ID in the hierarchy) and Q (quantity - of something, for the current rec...

Query regarding SQL Insert in SQL Server?

I am using SQL Server 2008 and developing a project which is in maintenance phase. I want to insert record in a table whose primary key is an Integer but not an identity. e.g. table name is tblFiles and fields are ID, FileName, FileContent. Actually that table is in use so I don’t want to make any schema change in it. And I want the ...

Determining sql command type in the trigger in sql server 2000

Is there more recomended way of determining command type in the trigger then testing DELETED and INSERTED tables? Currently i'm using approch: (EXISTS (select 1 from INSERTED) AND NOT EXISTS (select 1 from DELETED)) = INSERT (EXISTS (select 1 from INSERTED) AND EXISTS (select 1 from DELETED)) = UPDATE (NOT EXISTS (select 1 from INSERT...

How can I finding the index of a record with SQL or Stored Procedure?

Using SQL Server 2008, but could relate to other databases as well probably. If I had the following data (table t): id text date 1 Data1 2/1/2009 2 Data2 2/2/2009 3 Data3 2/3/2009 4 Data4 2/4/2009 5 Data5 2/5/2009 How could I find the index of a certain record? indexOf(select id from t where id = 1) = 0 or indexOf(select id from...

TSQL: Retreiving a value where a set of values in one table match a set of value in another

Setup: An order has multiple samples, with each sample having a test. A specific combination of tests is a specific testcode. How can I match a set of rows with specific values with another table of specific values to give a single result? Table: TestCodes ID TestCode Test 1 01a A 2 01b F 3 02a ...

Alternative TSQL

Hi there This query works well as you can see I have to put split between between @SearchCriteria and the rest of the query due to it's varchar. If I forced, the syntax works well BUT it return nothing when you query possible because extra ' Can you help? ALTER PROCEDURE [dbo].[sp_rte_GetRateList] ( @TenantID INT, @Cu...

Can I pass the result of a select statement as a value for a parameter into a stored procedure?

I was just trying this knowing that my select would return one row. Is something like this possible or will I need a temporary variable? lets say my stored procedure took one parameter: exec dbo.GetUserData @UserName = UserName from @MyTempTable where UserId= @UserId Or what if the parameter expected was XML? Is there a way I can do ...

Will SQL 2008 query opmtimizer optimize this query?

Have I to break this query SELECT ISNULL(SUM(AF.[amount]), 0) AS [firm], ISNULL(SUM(ACA.[amount]), 0) AS [cash], ISNULL(SUM(AC.[amount]), 0) AS [client], ISNULL(SUM(AFR.[amount]), 0) AS [fr], ISNULL((SUM(AF.[amount]) + SUM(ACA.[amount]) - (SUM(AC.[amount]) + SUM(AFR.[amount])), 0) AS [total] FROM ... into two: DECLARE ...