Questions tagged [t-sql]

Transact-SQL (TSQL) is the extended SQL dialect used in Microsoft SQL Server and Sybase. Please also tag with either [sql-server] or [sybase].

Filter by
Sorted by
Tagged with
36
votes
3answers
2k views

Excuse me, how many of you are new here? Mind if I JOIN you?

I had perceived an uptick in new reviewers around here, and I wanted to see if the data backed that observation up. It's been a while since I've written any SQL, so this was a nice little exercise in ...
32
votes
7answers
260k 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, ...
24
votes
6answers
679 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 ...
24
votes
3answers
574 views

Stack Exchange User Activity Score

About This is a SEDE query which will calculate your activity and participation on a site. It's geared for Programming Puzzles & Code Golf where questions are values more, but the values can be ...
22
votes
3answers
323 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. ...
22
votes
1answer
395 views

Calculating Lost Reputation

A discussion arose not long ago on the 2nd Monitor about how much reputation has been lost due to the reputation caps. There are a number of queries on the SEDE which try to address this: A Users ...
19
votes
2answers
1k views

Find & Delete Duplicate Records on All Tables in current MSSQL Database

The purpose of this code is to search the entire database for duplicate records and produce a script that a user could then run to delete all of the duplicates. The stored procedure takes the ...
18
votes
2answers
1k views

Searching for bad Minecraft questions on Gaming.SE

To preface this post a little bit, I'll explain a little bit of the "backstory" here. The Stack Exchange site, Gaming.SE, also known as Arqade, often has a large influx of bad questions about the ...
14
votes
1answer
9k views

Conditional Create: must be the only statement in the batch

I only want to create this SQL function if the dependent Assembly exists. I can do it using dynamic SQL, but it seems messy and I lose syntax checking (in management studio). This function's ...
14
votes
1answer
1k views

A wounded Zombie finder, or: how to find the almost dead undead?

The Zombies are on their way to eat our statistics. Fortunately, we have a way to get rid of them: our votes. However, we need to find them first. The /unanswered section is too large and contains ...
14
votes
2answers
107 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 ...
13
votes
3answers
200 views

SEDE Top Sponsors

I wanted to see the site's top sponsors - users that have paid bounties on questions that they didn't own. I started off with a bounty-related existing query, selected the details into a subquery, ...
12
votes
4answers
10k 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 CalendarDate...
12
votes
2answers
204 views

Query to count interactions between users

The source data represents interaction between people. Some are internal, others are external. The internals are recorded in the Users table (represented as the <...
11
votes
2answers
359 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 ...
11
votes
1answer
92 views

The sniper: a Zombie searcher for specific tags

Sometimes, it's not enough to know what the easy prey is. You also need to know whether you will be able to hunt it down. What good is an almost turned haskell zombie if you cannot read the functional ...
11
votes
1answer
184 views

Safe dynamic SQL for generic search

Prompted by discussion about SQL injection, I wanted to put a proof of concept forward to get feedback about whether this is in fact safe and protected against SQL injection or other malicious use. ...
10
votes
3answers
396 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 ...
9
votes
2answers
124 views

SQL to find table containing all specified columns

I have the below code to list all tables which have columns DataAreaId and CountryRegionId. This works, but requires me to ...
9
votes
1answer
117 views

Top 10 editors partitioned by year

I wanted to get some stats on who has done the most edits on Code Review, so I wrote the following query on SE Data Explorer. All improvement suggestions are welcome. Note that I used a cursor to ...
9
votes
1answer
119 views

The seasoned Zombie sniper: a query to find unanswered questions with specific tags

The query (on SEDE) This query builds upon the previous and includes the suggestions by rofl as well as the now mentioned activity index. ...
9
votes
0answers
242 views

Executing dynamic SQL programmatically [closed]

Usage (all parameters are optional): ...
8
votes
3answers
586 views

SQL trigger to log when employee records are updated

I am learning TSQL (SQL in general for that matter), and I wrote this trigger that inserts logs about updated employees into the tblEmployeeAudit table. When I was ...
8
votes
2answers
916 views

Replacement for sp_MSforeachdb

Having been informed that sp_MSforeachdb is undocumented and unsupported, I have been working on creating a replacement stored procedure based off the work done ...
8
votes
2answers
175 views

Execute every Monday that is not the Monday following last Saturday in month

I think the following is good but wondering if it can be simplified or improved. My condition for code execution is the following: It is a Monday AND It is not the Monday following the last Saturday ...
8
votes
2answers
193 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 ...
8
votes
1answer
2k 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: ...
8
votes
1answer
62 views

Finding closest points without duplicates and given distance threshold

Given a bunch of latitudes and longitudes stored as Points, I would like to determine the points that are closest to each point given some maximum threshold in meters. This is my first stab at this: <...
7
votes
3answers
2k 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 ...
7
votes
3answers
1k views

Sorting three Decimal values

I have been given three values: @lLength, @lWidth, @lHeight. I need to take these ...
7
votes
3answers
195 views

Inefficient query actually more efficient?

I have two queries; one runs significantly faster than the other, but the faster one is the one that I would have bet money on to be less efficient. These are both being executed through a front end C#...
7
votes
2answers
98 views

Deduplicating value pairs between 2 columns

This was asked in the SQL Helpline room: ...
7
votes
2answers
164 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 RANK ...
7
votes
1answer
8k views

Splitting an address into fields in SQL

I have the following code to split an address string in T-SQL. Excepting the unit number, which is already in its own field, it would affect too much of the application to split the address into ...
7
votes
1answer
864 views

Count and group rows for six months prior

I've written the following query for SQL server: ...
7
votes
1answer
59 views

Aggregating Conditional Sums

I have recently forked/rewritten a SEDE query (here) that aims at figuring out where a site stands in terms of avid users and distribution of reputation scores, compared to a specific target (number ...
7
votes
1answer
55 views

Who likes that post?

Following on from my last question, I've been continuing to look at what information is available from the SE Data Explorer, I thought it would be interesting to know who else had favorited a question....
7
votes
1answer
225 views

Stored Procedure code shortening and optimization

This stored procedure takes user defined table type as parameter. It contains data imported from an Excel file. Values inside that type are the same as in ...
7
votes
0answers
137 views

Modeling tabular structure in MSSQL to store data like Excel sheet applying Column inheritance

I've created this database structure to store tabular information (it's a simplification of my real structure but enough to show the point). In it I have Sheets and ...
6
votes
2answers
102 views

Find how much reputation a user had on a given date

I saw this question on MSE and went ahead and wrote a solution to it: https://meta.stackexchange.com/questions/313561/determining-users-reputation-as-of-particular-date This calculates the ...
6
votes
2answers
448 views

FizzBuzz in T-SQL

I've written a simple FizzBuzz in TSQL using some IF loops. Here's a SEDE link to run it in your browser. ...
6
votes
2answers
4k 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 ...
6
votes
2answers
115 views

Finding customer searches and graphing their items

One of the reports I am working on requires me to locate searches a customer makes, and graph them in certain manners. I have one query I'm particularly concerned about. This one takes the most ...
6
votes
2answers
136 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 ...
6
votes
1answer
61 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
206 views

Calculating a time in a time zone from multiple dates in SQL

I'm trying to calculate what time a certain time in a time zone is today, so I can schedule something to happen at that time in that time zone. I've got a table with what I have termed the Nominal ...
6
votes
1answer
53 views

Data Explorer query that makes bar graphs

I wrote a query for SEDE that I think is pretty cool. It uses the currently available graphing capabilities (scatter plots) to create a bar graph. I have used it as the subject for a self-answer on ...
6
votes
1answer
67 views

Stored procedure to describe the reason that a particular employee is unsuitable for a particular task

I used the following Stack Overflow questions as references for writing this code: Select columns from result set of stored procedure What is the equivalent of String.Join on TSQL? The idea behind ...
6
votes
2answers
69 views

Query to get distinct items at the top of results and duplicates following

I need to query the table below and list the distinct items at the top of my result set and the duplicates will follow, the order of the duplicates does not matter. I am working with Sql Server 2016 ...
6
votes
1answer
173 views

Stored procedure to insert a new person

I have not written very many stored procedures before, and this is the first one I have written to be used in a real business application to write new data into the database, so I would like to get ...