tsql

negative selection SQL Query

I have been tasked with returning a negative selection from our sql database. I'll define the criteria as best i can. Thus far I haven't crafted a query that has worked. Business Table [Bus Name] [Bus ID] Activity Table [Activity ID] [Bus ID] Activity Extension Table [Ext ID] [Activity ID] [Bus ID] I need the Business names for a...

Is a T-SQL coniditional TOP clause possible?

I want to dynamically use TOP or not sort of like this... SELECT @SomeNumber CASE WHERE 0 THEN TOP 5 COLUMNNAME ELSE COLUMNNAME END FROM TABLE ...

Split date range into intervals and count seconds in each interval in t-sql

I have a query I am trying to make on a table that has multiple entries for people with a start time and an end time e.g.: name,startime,endtime ('richard','2010-04-21 08:01:15','2010-04-21 08:06:15'), ('bill','2010-04-21 08:07:45','2010-04-21 08:11:15') What I need to do is create a report showing the seconds of each entry within each...

How to get a datetime variable with a value of 00-00 and month ago with a given timezone?

Hello Everybody! I am looking for help to accomplish following task in T-SQL: I have a timezone, and I need to calculate an UTC datetime which is current date minus one month, but has 0 hours and 0 minutes in a given timezone. ...

SQL Server XML Column exist() query

Assuming I have a SQL Server 2005 table with an xml column containing the following values: CREATE TABLE XmlTest ( XMLid int, Data xml) INSERT XMLTest VALUES ( 1 , '<data><item><type v="1" /><value v="12.56" /></item><item><type v="3" /><value v="DEBIT" /></item></data>' ) INSERT XMLTest VALUES ( 2 , '<data><item><type v...

Replace IN with EXISTS or COUNT. How to do it. What is missing here ?

I am using IN keyword in the query in the middle of a section. Since I am using nested query and want to replace In with Exists due to performance issues that my seniors have told me might arise. Am I missing some column, what you are looking for in this query. This query contain some aliases for readibility. How can I remove it. S...

SQL - Converting char to exact length varchar

I am trying to do something like this: SELECT CAST(MYCHARCOL AS VARCHAR(LEN(MYCHARCOL))) FROM MYTABLE How do I convert a char column to varchar that is the exact length of the data inside that char column? ...

Complex dynamic SQL query

I need help in resolving a complex SQL query. I am trying to build up the query one vlock at a time. One issue is: If a parameter for @PubNum is NULL, the query shows "..... where PubNum = '' which is an issue. What I need is if the parameter is NULL, then PubNum should not be in the where clause. A second issue is: If @StartDate IS ...

Assigning a delimited string to a variable

Hi I have this requirement: Pipe delimited text file to a table (TEST) in the SQL db - yes this is fine Now i need to group the results based on the primary key and return the result set as a delimited (pipe deliited string) to a variable. This variable is then used as an input parameter to a stored proc which later processes stuff. ...

Efficient way to compare for a NULL or value for a column in SQL

What's the efficient way to check for a null or value for a column in SQL query. Consider a sql table table with integer column column which has an index. @value can be some integer or null ex: 16 or null. Query 1: Note sure, but it seems one should not rely on the short-circuit in SQL. However, below query always works correctly when ...

Subquery Caching with Sql Server 2008

I am creating a stored procedure with Sql Server 2008 which will return 2 result sets. The first query returns a result set that I would like to resuse as in the second query as a subquery (see example below). However, since the first query and the subquery essentially return the same data, I was wondering if there is some caching meacha...

IDENTITY_INSERT is set to OFF - How to turn it ON?

I have a deleted file archive database that stores the ID of the file that was deleted, I want the admin to be able to restore the file (as well as the same ID for linking files). I do not want to take identity_insert off the entire table, as the increment by one works great. In my insert to TBL_Content store procedure I have somethin...

How to convert newlines (replace \r\n with \n) across all varchar and nvarchar fields in a database

I am recovering from a bug in a system I built where I did not take into account that IE generates Windows-style newlines (\r\n) and other browsers generate Unix-style newlines (\n) when posting HTML forms with text areas. Now I need to convert all Windows-style newlines (\r\n) to Unix-style newlines (\n) throughout the varchar and nvarc...

TSQL Dynamically determine parameter list for SP/Function

I want to write a generic logging snip-it into a collection of stored procedures. I'm writing this to have a quantitative measure of our front-user user experience as I know which SP's are used by the front-end software and how they are used. I'd like to use this to gather a base-line before we commence performance tunning and afterward ...

TSQL format the date difference to be "YY years MM months and DD days"

Ok this is a little bit of a strange request. We are trying to get a formatted "age" statement to come out in a report in PeopleSoft, it provides a "TSQL" based builder which is fairly unhelpful. We cannot use stored functions and we cannot edit the entire SQL statement as one thing. All we can do is say field by field what the formula i...

How to sum data week wise in SQL 2000

Dear All How to sum data week wise in MS-SQL 2000 I have a Table "Reports" with the following columns. TotalSubmissions, ZoneID, RptMonth, RptDay, RptYear 2,1,6,1,2010 1,1,6,2,2010 1,1,6,3,2010 1,2,6,1,2010 1,2,6,2,2010 2,2,6,3,2010 1,2,6,4,2010 1,4,6,1,2010 1,4,6,3,2010 1,4,6,4,2010 I want to make a report week wise for a specifi...

SQL: LIKE where condition specifying a letter followed by a number followed by anything else?

Hi, Is it possible to write a WHERE condition in MS SQL Server that will grab rows that: begin with a specified letter then have a decimal then anything else I basically have a table that contains postcodes and I want all postcodes that belong to Birmingham - B followed by a number. Some postcodes are in there that start B and then a...

Is there an exact equivalent for the .NET Double type in SQL Server?

Possible Duplicate: What represents a double in sql server? Is there an exact equivalent for the .NET Double type in SQL Server? Failing that, is there one which gives a good approximation? EDIT: It looks like this link gives the most definitive answer: Mapping CLR Parameter Data ...

isnull vs is null

I have noticed a number of queries at work and on SO are using limitations in the form: isnull(name,'') <> '' Is there a particular reason why people do that and not the more terse name is not null Is it a legacy or a performance issue? ...

SQL - Where Clause by Another Select Statement?

I have the following query: select r.people_code_id [People Code ID], r.resident_commuter [Campus6], c1.udormcom [AG], aR.RESIDENT_COMMUTER [AG Bridge], ar.ACADEMIC_SESSION, ar.ACADEMIC_TERM, ar.academic_year, ar.revision_date from RESIDENCY r left join AG_Common..CONTACT1 c1 on r.PEOPLE_CODE_ID=c1.key4 left join AG_Common..CONTACT2 c2 ...