Transact-SQL (TSQL) is the extended SQL dialect used in Microsoft SQL Server and Sybase. Please also tag with either [sql-server] or [sybase].
5
votes
1answer
36 views
Searching posts/comments by keywords
I've put together another simple query that allows you to search post/comments with specific keywords. At the moment, only post bodies are checked.
...
0
votes
0answers
17 views
Merge inserted and deleted tables in SQL trigger
I have a table with a Balance column of type decimal(18,0).
I want to get updates to its ...
8
votes
3answers
379 views
Popular questions by view count
I made this query to create a graph of a user's popular questions and the view count on that question. It allows for a minimum of 500 views, and a score of ...
4
votes
0answers
30 views
Finding which users have the most questions with unaccepted answers
I was looking at a few profiles this afternoon and noticed a few users had plenty of open questions. This led me to wonder which users actually had the highest amount of questions with unaccepted ...
3
votes
2answers
32 views
Percentage of questions that are unanswered or answered - Part 2
I've refactored my previous query and added better checks when selecting the data. The query link can be found here. The formulas used to calculate the two percentages are the same, and can be seen ...
2
votes
0answers
13 views
Trigger that updates counter
I've got the following schema, which can not be modified since it's a problem taken from an exam:
...
10
votes
2answers
287 views
Percentage of questions that are unanswered or answered
I put together this simple query which calculates the total percentage of answered questions and unanswered questions. The formulas used to calculate these are shown below:
$$
\text{Answered ...
1
vote
0answers
29 views
Get Next and Previous “Load Day”
I keep looking at this trying to figure if there's a better way. All three functions work, but I wanted to get some insights and ideas from others.
IsLoadDay ...
5
votes
2answers
94 views
SQL procedure with cursor to check if values in table 1 are also in table 2
The problem is that I'm checking if result from cursor exists in CDN.ZamNag table for every iteration. Is there any better way to check if values in table 1 are ...
5
votes
2answers
127 views
Finding questions to answer - Part 2
I've refactored my previous question finder, and added multi-tag support, and support for searching for keywords in the question titles. Essentially, what it does is find questions that could be ...
2
votes
1answer
42 views
Backing up two tables using SQL transactions
I'm using a transaction statement in SQL Server 2012 for the first time.
I want to insert Table1's data in Table1_Backup before ...
8
votes
2answers
125 views
Finding questions to answer
In light of the recent "SQL frenzy" of sorts in The 2nd Monitor, I decided to take a stab at writing my own SEDE query. Essentially, what it does is find questions that could be answered based on the ...
16
votes
2answers
246 views
“How can I make this SEDE query better?” (bad title finder)
It was suggested on Meta for July 2015 Community Challenge to make a bot that can find questions with bad titles. I wrote this query to help others find examples to help them make write their code.
...
17
votes
4answers
2k views
Top users under age 30, sorted by age, with rank
In the pursuit of SQL enlightenment, I wrote a quick SEDE query to display Rep, Age and Overall ...
3
votes
2answers
203 views
Creating Sequential IDs
I'm using SQL Server 2014 with Visual Studio 2013. When I add a new record, identity column (seed 1, increment 1) "ID" increases by 1, and record added to the database.
My table consist of two rows:
...
4
votes
1answer
36 views
Checking that data has been created in multiple tables
I have 4 tables that I want to check for data. When there are Rows in all the tables, I want to print a message for success. When there aren't any rows in any table I print an error (when 1 or more ...
5
votes
2answers
50 views
1st questions with answers within X hours
I recently challenged @Hosch520 with writing a query as such:
Find first questions with answers posted within 24 hours
And he did great. I figured I would have a go at it too. Turns out I ...
1
vote
1answer
46 views
Perform SELECT INTO Statement and add Primary Key afterwards
How can I improve this TSQL Statement in terms of performance?
...
4
votes
0answers
55 views
Split string in chunks preferable at spaces
I'm working on a tool to import data from one database to another. One requirement is that I have to split a string from one source field into three (shorter) fields at the target. If possible the ...
1
vote
1answer
36 views
Select Queries From Parent-Child Tables Model
I have two tables (parent-child model): Loans & Transactions.
Each Loan row has multiple Transaction rows.
I want to select all rows from Loans together with most recent row from Transactions.
...
4
votes
3answers
170 views
Stack Exchange Data Explorer T-SQL to Find Users by Most Badges
I very infrequently use SQL. I need more experience, so I have created a data explorer query to find those users with most of any particular badge (have to edit the query itself right now to alter ...
6
votes
1answer
170 views
Generate random records for table in SQL
I'm doing a project in ms sql and I have to fill table with random records.
Here is a definition of table:
...
0
votes
1answer
92 views
EF Code First Seed Method for States/Countries Part I
I am working to complete this even further, but here's what I've got thus far...
(See my other post for the definition.)
...
2
votes
2answers
103 views
Haversine formula in SQL
This is an implementation of the Haversine formula in Microsoft Transact SQL.
How can I simplify the function?
...
3
votes
1answer
70 views
Is the usage of SQL Cursor appropriate here?
I'm looking for following kind of feedback:
Firstly, is the use of a cursor an overkill here?
Is there a simpler way to do what I'm doing in SQL?
Could the script go terribly wrong?
I have limited ...
7
votes
2answers
143 views
What are my highest activity streaks?
I have written the following query to figure out activity streaks on a per-user basis. I find it... Ugly... And would love to improve it!
Limitations
Those are explained as commented text at the ...
17
votes
6answers
635 views
Winterbash 2014 “Red Shirt” Estimation
Some fellow code reviewers (hi @Janos!) have been inquiring about a SEDE query to allow to check progress of the Red Shirt "hat" progression.
Try it here!
Background
Red Shirt
cast 5 ...
10
votes
6answers
16k views
Select first value if exists, otherwise select default value
Given an instance of SQL Server, imagine there's a table named Configuration, which has three columns: ID, ...
13
votes
2answers
83 views
Bling Accounting - Badge summaries
A question was raised in chat (hi @malachi) about how you can count the number of badges of a given metal (bronze, silver, gold). This can be hard to do because the medal details are not stored in the ...
3
votes
2answers
66 views
Running validation procedures on a table
I am working on a project that has to parse through a text file and perform validations on certain fields in the file. Some of the validations are basic (i.e. data types), and other's are a little ...
2
votes
2answers
42 views
3
votes
2answers
1k views
Calculating Standard Deviation using SQL
I have been reading a bit about statistics to improve the way I understand data. This is my attempt at calculating Standard Variance using data tables. I solved this simple question:
This is the ...
4
votes
3answers
377 views
For a query over multiple databases, is it faster to do GROUP BY first or UNION first?
Having several databases with the same table, I need to group the union of the records.
The xTables are stored in separate databases named by the year (this is by ...
22
votes
2answers
1k views
Calculate amount of mugs StackExchange should give away so I'll receive one
Description
Calculate how many mugs StackExchange should give away(for graduation) to top reputed users on codereview.stackexchange.com so I'll also receive one. (output is divisible by 50).
Code
...
11
votes
4answers
744 views
LW, MTD, STD, YTD Sales… all in line and looking pretty
That's Last Week, Month-to-Date, Season-to-Date, and Year-to-Date sales.
I have a table-valued function for each one, that takes a date and returns all ...
3
votes
2answers
47 views
Extract last record inserted for each key identifier
I have a table with this schema
ID, int primary key
PathKey, string not null
InsertDate, datetime not null
Value, int not null
In the table there are a ...
5
votes
1answer
53 views
Saving and updating records
I have made two SQL statements as I was unable to figure out how to solve my problem. One is called Product_GetID and the other ...
6
votes
2answers
154 views
Follow up to Top Active Answerers on Stack Exchange site
This is a follow up to Top Active Answerers on a Stack Exchange site
Actual SEDE Query --> Top Active Answerers on site
Do I have a good understanding of the ...
3
votes
2answers
282 views
Top Active Answerers on a Stack Exchange site
I created this query back in January to find the top active answerers on Code Review and thought that it might be a good idea to get other opinions on my SQL coding using a database that we can all ...
3
votes
3answers
195 views
Basic T-SQL to compare two same length strings
I've come up with the following T-SQL to compare similar same length strings.
Example usage would be:
OCR returns a value which is expected to be in the database.
PATINDEX is used to check the ...
3
votes
1answer
105 views
Query for combining two SELECTs to get active users and 1 inactive user
What this query does:
Gets multiple users with the first select in the union; those are the active users.
But when a user is ...
2
votes
1answer
28 views
Optimizing sales report stored procedure
I need to optimize the following stored proc. Please let me know of any techniques or modifications that I can make to optimize this piece of code.
The procedure is for a report that needs to run ...
4
votes
3answers
76 views
Retrieve Father and Child structure in a Database Table
I have a table with this schema:
CREATE TABLE [td].[MyTable] (
[ID] [int] NOT NULL
,[FatherID] [int] NULL
)
(Note: I have excluded all the columns not ...
4
votes
1answer
108 views
Unduplicated counts over various dimensions
I'm working with some data on college students, which has a unique key of SSN, Campus, ...
5
votes
1answer
83 views
SQL query with dynamic unpivot+pivot for cross product
In this Data Explorer query I am trying to do the following:
For each tag:
Compute sum of answer scores in this tag ("S")
Compute count of answers in this tag ("A")
For each tag class (Bronze, ...
20
votes
6answers
2k views
Average time to first answer
Just trying out my T-SQL foo.
Wrote a script to get the average time (in seconds) to first answer for a specified language.
...
4
votes
2answers
144 views
Delete from multiple tables using temp table variable
I'm previewing the data I'm about to delete, but this SQL looks a little redundant. Is there a better way to write this?
...
6
votes
3answers
417 views
SQL Stored Procedure Get Distinct and Update
The idea is to show how many times a user (by EmployeeID) is in the TblTableList and then update the ...
6
votes
2answers
104 views
Put it in a bucket
You're running a jeans company. Your system is collecting detailed orders' data, but it's a bit archaic and it's storing the number of units ordered per size in a delimited string with 20 "fields", ...
6
votes
3answers
248 views
Sorting three Decimal values
I have been given three values: @lLength, @lWidth, @lHeight. I need to take these ...
