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

learn more… | top users | synonyms

7
votes
0answers
28 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 ...
5
votes
0answers
14 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: ...
4
votes
0answers
132 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 ...
1
vote
0answers
49 views

Updating datetime fields from UTC to local time zone

I have created an SQL query to update datetime fields from UTC to local time zone. Can someone review this? ...
8
votes
2answers
70 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 ...
26
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 ...
4
votes
0answers
22 views

Tag wikis under a certain length

I wrote a SEDE query to list all tag wikis with a body or an excerpt under a given number of characters. I've used this to find empty wikis or wikis with very little information. The tags can be ...
5
votes
1answer
48 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
30 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 ...
9
votes
3answers
382 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 ...
3
votes
2answers
34 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
20 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
295 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
1answer
40 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
124 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
130 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
50 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 ...
3
votes
2answers
230 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
38 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 ...
3
votes
1answer
53 views

Perform SELECT INTO Statement and add Primary Key afterwards

How can I imrpove the performance of this T-SQL statement? ...
6
votes
1answer
300 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
104 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
144 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
71 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
147 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
639 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
25k 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
86 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
75 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
43 views

Cluster date time values

I came up with this TSQL for SQL Server 2008: ...
11
votes
4answers
926 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
286 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
232 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
113 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
30 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
1answer
119 views

Unduplicated counts over various dimensions

I'm working with some data on college students, which has a unique key of SSN, Campus, ...
4
votes
2answers
145 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
495 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
3answers
264 views

Sorting three Decimal values

I have been given three values: @lLength, @lWidth, @lHeight. I need to take these ...
5
votes
2answers
112 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
257 views

Count and group rows for six months prior

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

SEDE query to find users with exactly one post in a particular tag

I'm doing some research for a meta post on Gardening.SE (where I'm a pro tempore moderator). I'm using the Stack Exchange Data Explorer (SEDE) to find information about first time users who post ...
6
votes
2answers
116 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 ...
6
votes
1answer
56 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 ...
17
votes
1answer
293 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 ...
11
votes
3answers
180 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, ...
2
votes
1answer
141 views

Query too slow - Optimization

I am having an issue with the following query returning results a bit too slow and I suspect I am missing something basic. My initial guess is the 'CASE' statement is taking too long to process its ...