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.
...
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 ...
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....
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:...
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...
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...
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...
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
...
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...
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...
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 ?
...
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...
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 ...
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...
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...
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 ...
Are table variables created in memory or in tempdb? Same for
short temp tables?
...
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 ...
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 ...
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?
...