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

4
votes
1answer
31 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
45 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
39 views

Perform SELECT INTO Statement and add Primary Key afterwards

How can I improve this TSQL Statement in terms of performance? ...
3
votes
0answers
47 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
33 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
161 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
104 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
70 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
80 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
63 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
140 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
633 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
8k 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
81 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
57 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 more ...
2
votes
2answers
39 views

Cluster date time values

I came up with this TSQL for SQL Server 2008: ...
3
votes
2answers
611 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
368 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 ...
21
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
625 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
45 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
153 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
279 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
163 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
100 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
69 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
96 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
76 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
1k 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
140 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
310 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
199 views

Sorting three Decimal values

I have been given three values. @lLength, @lWidth, @lHeight. I need to take these ...
5
votes
1answer
814 views

Dynamically filtering, sorting, and paging using a stored procedure

Is there a better way (I'm pretty sure there is) to create a stored procedure to do dynamic paging, filtering and sorting than the following code? ...
5
votes
2answers
101 views

Better way of 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 ...
7
votes
4answers
130 views

SEDE-Query for Rep-Trivia

The code should be relatively self-explanatory. I was wondering how much rep the Top Voters on sites did "generate" on a site, in comparison to how much rep they "own". Therefore I created a little ...
6
votes
1answer
204 views

Count and group rows for six months prior

I've written the following query for SQL server: ...
5
votes
1answer
58 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
107 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 ...
7
votes
3answers
148 views

Return the top 5 of each kind of record

I have this currently working code, but it's extremely slow. I'm talking almost an hour to execute, if it executes at all (our server isn't all that great to start with). Is there a better way to ...
15
votes
1answer
250 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
171 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, ...
4
votes
3answers
90 views

Remove duplication in SELECT statement

Say I have the following SQL: SELECT amount, amount*.1, (amount*1)+3, ((amount*1)+3)/2, (((amount*1)+3)/2)+37 FROM table Instead of ...
2
votes
1answer
134 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 ...
3
votes
2answers
183 views

SQL query clustered with repeated function calls

I have the following SQL query that computes for every date the week it falls in (a week begins on Sunday and ends on Saturday): ...
1
vote
2answers
407 views

Maintaining a total balance of debit/credit sales transactions

The overall intent is to consolidate individual sales transactions to total debits/credits summing up the totals by year and month--retaining a running balance from start to end. This code uses ...
1
vote
2answers
76 views

Store procedure timing out sometimes

Can someone please find out why this store procedure is timing out? ...