Transact-SQL (TSQL) is the extended SQL dialect used in Microsoft SQL Server and Sybase. Please also tag with either [sql-server] or [sybase].
7
votes
2answers
132 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 ...
2
votes
1answer
35 views
SQL Inserting up to 10'000 records from a table to a secondary table
I'm relatively new to ASP.net and working with larger data sets as a fresh graduate, and I am currently maintaining/developing for an ASP webform and T-SQL system at the moment where we have a ...
3
votes
3answers
273 views
SQL Procedure that will calculate the Last Price paid for a certain Part
I am trying to write an SQL Procedure that will calculate the Last Price paid for a certain Part.
...
6
votes
2answers
103 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 ...
5
votes
1answer
44 views
Report for a restaurant showing hourly sales information for a given timespan
I have been tasked with creating a report for a high-volume restaurant that shows hourly sales information for a given timespan. When generating this report for a single day, it's very quick, but when ...
7
votes
2answers
44 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 ...
12
votes
2answers
79 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 ...
4
votes
0answers
27 views
Coin flips and Dice rolls in a stored procedure
I saw a post in chat a few days ago, of what I thought was an interesting (albeit fairly simple) statistical challenge:
By @skiwi:
If I shoot once with 50% chance of target A and 50% of target B, ...
4
votes
1answer
28 views
Selecting time metadata off a calendar table
I have a report on SSRS (that's SQL Server Reporting Services) that I need to be able to parameterize so that it can run for..
Yesterday (scheduled every morning)
The week that just elapsed ...
4
votes
0answers
72 views
4
votes
2answers
64 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.
...
2
votes
2answers
33 views
Sql Server: Getting the highest value based on two uniqueidentifiers - possible to avoid union?
I have two riscProfileTypeIds (both of type uniqueidentifier) of which I need the one with the highest sequence number
(the ...
3
votes
2answers
60 views
University Database
I am building a database for a hypothetical university. This is my SQL definition, with generated data added for my future use, such as demonstrating queries:
...
2
votes
1answer
75 views
Counting email addresses with different status
I want to count email addresses with different status. I use ASP.NET MVC and this query is supposed to run every 5 minutes on these tables, which every table is on different .ndf files. About 5 ...
2
votes
1answer
52 views
T-SQL Constant Time Comparison
Explanation is in the function. Please point out any egregious errors:
...
1
vote
0answers
16 views
T-SQL Totp (Rfc6238 SHA2_256) Implementation
Using my previous function as a base I've come up the following:
...
3
votes
1answer
45 views
Returning two dates based on input
I created a function that will return 2 dates that depend on an input parameter.
When the parameter is:
1 - return start date of current week and end date of current week
2 - return start date of ...
16
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 ...
5
votes
1answer
35 views
SQL with SUBSTRING / CHARINDEX
I've got a VARCHAR variable of this kind:
@string = "BLABLA10;GOGOGO30;RES777;ANOTHER;"
(several keyword + number separated ...
3
votes
0answers
37 views
T-SQL Pbdfk2 (Rfc2898 SHA2_256) Implementation
Using my previous function as a base I've come up the following:
...
4
votes
0answers
80 views
T-SQL Hmac (Rfc2104 SHA2_256) Implementation
I'm trying to implement an Hmac in T-SQL using this question as an guide. Please critique this:
...
5
votes
1answer
77 views
Working Days DateDiff function
I have a calendar table with a record for every date I need to care about (i.e. 365 records per year), with various meta-information about each date, including the weekday, the fiscal calendar week, ...
2
votes
1answer
28 views
Find foreign key membership including owner in SQL Server
Was hoping someone could sanity-check this SQL statement. I need to find the owner (schema), table, and column on both sides of a foreign key:
...
16
votes
2answers
143 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 ...
6
votes
1answer
34 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
143 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
91 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
94 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
24 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
55 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.
...
1
vote
2answers
72 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
387 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
35 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 ...
3
votes
2answers
49 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
308 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
46 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
500 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
131 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
64 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
318 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
39 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
62 views
Perform SELECT INTO Statement and add Primary Key afterwards
How can I imrpove the performance of this T-SQL statement?
...
6
votes
1answer
673 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
131 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
293 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
78 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
150 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
646 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 ...
11
votes
6answers
46k 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, ...
