Hi, I have a question about a SQL statement generated by a LINQ2SQL query. I have two database tables (VisibleForDepartmentId is a foreign key): AssignableObject Department ---------------------- ------------ AssignableObjectId ┌────> DepartmentId AssignableObjectType │ VisibleForDepartmentId ───┘ ...
I'm struggling with a T-SQL procedure and I am hoping you can help. I need to know if A row exists in a table for a given ID If one (or more) does exist then the latest one has another ID set to 5. So the first table we need to get the row out of has two relevant ID's: The CaseID and LocationID, these are both integers. The second ...
I have a table with column ID that is identity one. Next I create new non-identity column new_ID and update it with values from ID column + 1. Like this: new_ID = ID + 1 Next I drop ID column and rename new_ID to name 'ID'. And how to set Identity on this new column 'ID'? I would like to do this programmatically! ...
Hey guys. I have a column of data containing a string with the following format: blablablabla(XYZ) and I would like to discard everything outside the () - and the parenthesis themselves - and to set update that field with the value inside the (). This implies that, in this particular case, the "blablabla" would be discarded and that ...
I was writing a Stored procedure today, and I ran into a problem where if one of the values is null (whether it be from a SELECT statement or whether it be a passed in parameter) my expression would evaluate to false where it ought to evaluate to true. SET ANSI_NULLS ON; DECLARE @1 INT; DECLARE @2 INT; DECLARE @3 INT; DECLARE @4 INT; S...
The problem: I need to find all active [GiftPledges] that have the last three [GiftDetails] have a zero amount. SELECT gp.PledgeId FROM GiftPledge gp INNER JOIN GiftDetail gd ON gp.PledgeId = gd.PledgeId WHERE gp.PledgeStatus = 'A' GROUP BY PledgeId HAVING COUNT(PledgeId) >= 3 Now, I have all my [GiftPledges] t...
There is a simple way to get top N rows from any table: SELECT TOP 10 * FROM MyTable ORDER BY MyColumn Is there any efficient way to query M rows starting from row N For example, Id Value 1 a 2 b 3 c 4 d 5 e 6 f And query like this SELECT [3,2] * FROM MyTable ORDER BY MyColumn /* hypothetical syntax */ queries...
Given two date/times: @start_date = '2009-04-15 10:24:00.000' @end_date = '2009-04-16 19:43:01.000' Is it possible to calculate the time elapsed between the two dates in the following format 1d 9h 19m ...
hi guys, I have date in mm/dd/yyyy format in database.I want to display in form as dd/mm/yyyy. Can anybody help?I want to get time along with date. ...
I am trying to get data from different sever, and the sever name might change. So I set the server name as a parameter. The idea of my sql store procedure is something like this CREATE PROCEDURE [dbo].[GetData] @ServerName AS BEGIN SELECT * FROM @ServerName.ClientDataBase.dbo.Client END Does anyone know how to achieve this...
Duplicate: How to do a Select in a Select I have 2 tables: TABLE1 Table1Id TABLE2 Table2Id Table1Id UserId TABLE2 has thousands of entries in it. I want to return a list of TABLE1 entries where there isn't an entry in TABLE2 for it for a particular user. So, where there isn't a foreign key entry in TABLE2. A query like: select...
We’re a team of SQL Servers database developers. Our clients are a mixed bag of C#/ASP.NET, C# and Java web services, Java/Unix services and some Excel. Our client developers only use stored procedures that we provide and we expect that (where sensible, of course) they treat them like web service methods. Some our client developers don...
I have a table with a datetime field. I want to retrieve a result set grouped by the month/year combination and the number of records that appear within that month/year. How can this be done in LINQ? The closest I've been able to figure out is in TSQL: select substring(mo,charindex(mo,'/'),50) from ( select mo=convert(varchar(2),mont...
Hi guys, following is my stored procedure. It contains a subquery Select StartTime From DrTimings Where DrID = @DrID If this subquery returns more than one value, I get an error. Subquery returns multiple rows. I want to get each @StartTime and @EndTime in cursor. Means I want to "fetch next from Doctor into @StTime and @EndTime" C...
In TSQL I can state: insert into myTable (ID) values (5) GO select * from myTable In MySQL I can't write the same query. What is the correct way to write this query in MySQL? ...
I am writing a report to return details about an object ('files') in my database. My application lets users create their own flags for use against file objects. Flags basically consist of a name, then flag instances store a bit value to indicate whether it is set for the parent file object. I want to write a query that returns one row p...
DECLARE @ID INT ; This statement parses fine with MS SQL Server, but gives me You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @ID INT' at line 1 Does anyone have any idea about the reason? ...
Google search turns up some links to tsql questions. I was wondering what would SO experts would ask in an interview for TSQL. ...
The question is quite simple, as the title. Added a column to table I want a unique constraint , the column will insert guids, but to allow for existing data before column was added I need to allow for nulls in the check ...
I have a very high-traffic table with a char(50) field which takes part in several indexes. This char(50) field allows NULLS, and in that case a NULL value is considered to be the same as a non-NULL, zero-length string for my purposes. I also disregard leading & trailing whitespace and, while I scrub the data before I insert it, it may...