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?
...
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 ...
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 ...
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...
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...
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...
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 ...
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...
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...
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
...
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 ...
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 ...
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...
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 ...
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...
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...
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 ...
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...
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 ...
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 ...