tsql

Triangular Multiplication Matrix generation

Trying to generate a Triangular Multiplication Matrix IN T-SQL- Like a triangular multiplication matrix will look like this: 0 0 1 0 2 4 0 3 6 9 0 4 8 12 16 I have not been able to find an efficient solution for this. Any help is appreciated. ...

Count Unique Results in T-SQL

My query is: SELECT DISTINCT IncidentStatus.IncidentStatusName, Incident.IncidentID AS Bob FROM Incident INNER JOIN IncidentMember ON Incident.IncidentID = IncidentMember.IncidentId INNER JOIN IncidentStatus ON Incident.IncidentStatusID = IncidentStatus.IncidentStatusID WHERE ...

SQL constraint for between multiple values

Here is my table Events Start : Datetime End : Datetime I'm trying to make sure that a new Event does not overlap any previously entered events. I'll admit my SQL knowledge is novice at best. The following is a select statement that gets me close but I can't figure out how to turn it into a constraint (would I use check?) SELECT e....

Selecting same column with different where conditions

This query returns the sum of "closed" daily sales for a particular salesperson within a particular date range: SELECT SUM(price) as closed_total FROM dbo.Sales WHERE salesperson_ID = @salesperson_ID AND date_ordered BETWEEN @start_date AND @end_date AND closed = 1 GROUP BY date_ordered The output looks like:...

If-Else Statement in T-SQL

I'm trying to alter the results of a Transact-SQL (MS SQL Server) report that is currently showing: CompanyA DVCE3 CompanyB DVCE2 CompanyC DVCE3 CompanyD NULL CompanyE DVCE3 and I would like it instead to show: CompanyA 36 CompanyB 24 CompanyC 36 CompanyD CompanyE 36 I'm sure the answer to...

Are there Table Literals in Transact-SQL?

According to http://www.storytotell.org/blog/2008/11/14/literal-tables-and-updates-with-joins-in-sql.html the following is valid: SELECT * FROM VALUES ('Lisp', 50, true), ('Scheme', 30, true), ('Clojure', 1, true) AS languages (name, age, lispy) But it doesn't appear to work. The best i c...

How can i adjust this T-SQL query so that it returns one table rather than two? :)

SELECT patron_name, producer.fed_number, tax_number, average_tb_test FROM producer INNER JOIN producer_details ON producer.federal_number = producer_details.federal_number INNER JOIN statement ON producer.patron_number = statement.patron_number WHERE producer.patron_number = @PatronNo (SELECT MAX(statement.statement_number), MAX(p...

Inserting numerous incremental values in a column

Got a table with 1200 rows. Added a new column which will contain incremental values - Candidate1 Candidate2 Candidate3 . . . Candidate1200 What is the best way to insert these values , non manual way. I am using SQL Server 2008 ...

Passing XML to Procedures

I just switched groups and what they currently do is have their middle tier pass XML to the procedures and then use xquery/xpath to parse the xml and use it to get information and return it back to the app (RETURN XML). I was just curious if others use a similar process or if they just pass the data directly into the procedures. I reall...

Updating parent property values in SQL

Hello, I have a SQL Server 2008 database with 2 tables. These tables are defined like this: Device ------ ID Name Description TotalApplications Application ----------- ID Name DeviceID I recently added the "TotalApplications" column in an effort to speed up a query that is taking WAY too long. The number of applications associated w...

Deleting every nth row SQL

Say I have a table (id int, Name varchar) of 1000 rows. Now I wish to delete every nth record (every 2nd, 3rd or 5th) . What is the most efficient way to do this ? ...

How to identify the calling stored proc

I am hoping to add some loging to a particular stored proc that is cross called by about 5000 stored procs in 20 databases. What I'd like to to is add to the top of the called stored proc something like: insert into callLog values (@@caller, getdate()) So after a while I can get a nice list of all the stored procs that are calling th...

Adding an INNER JOIN to a query should not increase the number of rows returned right?

Hi, I have a query like the following that returns the correct number of rows that I would expect. (It has to match a similar query that returns the same data sets but with different associated info from other related tables. SELECT * FROM LK INNER JOIN STC ON LK.V = STC.VI LEFT OUTER JOIN BC ON LK.BC = BC.ID LEFT OUTER JOIN K AS LT ON ...

Selecting records based on two other tables

I have a query with inner join to another table, with this I want also include records which are contained in another column. Example: select name, address from table1 inner join table2 on table1.id = table2.id With this, I want to also include rows which are having table1.recno = (1,2,4). How could I write query for that? One opt...

Database schema dilemma with foreign keys pointing to multiple tables (exclusive arc)

Hopefully my description is a little better than the title, but basically I'm having an issue with one part of a new application schema and i'm stuck on what is the most manageable and elegant solution in table structure. Bare bones table structure with only relevant fields showing would be as follows: airline (id, name, ...) hot...

T-SQL: How to return 0 rows in from stored procedure, and how to use XACT_ABORT and TRY/CATCH

I'm writing a stored procedure and I want to return 0 records when something fails. I can't seem to figure out how to just return 0 rows? I've used SELECT NULL but this returns 1 row with a NULL in row 1 col 1. I have also tried not specifying any SELECT statements in my error code path but when testing the value of @@ROWCOUNT after the ...

table variables created and held in memory or in tempdb ?

Are table variables created in memory or in tempdb? Same for short temp tables? ...

What's the most efficient way of querying the database based on a list of values?

I have a list of record Id's that I want to retrieve from Sql Server. I'm trying to figure out what the most performant way doing this would be. For example in code I have this: var recordsToFind = new List<long>{ 12345, 12346, 45756, 42423 ... } I want to create a stored proc that does this: Select * From Puzzles where ID = {any of ...

SQL Server 2008 First Day Criteria Are Met

Hello. I have a data table (will be millions of records but I will make it simple here) that looks like this. ID APPROVAL_DT DAY_DT TRANS_COUNT SALE_AMOUNT 1 2010-04-22 2010-04-27 2 260 1 2010-04-22 2010-04-28 1 40 2 2010-03-28 2010-04-02 1 ...

tsql update or merge for truncated column : which is better tool?

if a sql server table's column is wiped out in a database and you restore a backup to a new database, would you use merge to restore that column or run an update across the two databases? ...