Transact-SQL (TSQL) is the extended SQL dialect used in Microsoft SQL Server and Sybase. Please also tag with either [sql-server] or [sybase].
6
votes
1answer
46 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....
-1
votes
2answers
107 views
Calculating price with a sub query
So I have this sub query:
*(select p.value1 From Params as p where p.PAR_Field = 'Taxe' and p.PAR_KEY = 'TVA')*
And I was using it 3 times to get the same value.
...
4
votes
0answers
65 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 ...
1
vote
0answers
14 views
4
votes
2answers
47 views
Adding dynamic column names to a table
I'm using Microsoft SQL Server 2014 Express.
I found myself wanting to add the Columns CompanyNameChangeDate (1 through 10) and ...
3
votes
0answers
17 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 ...
1
vote
1answer
43 views
Demographics information for EDI report
I am going through some of my companies EDI reports to see if I can make them faster. The query below runs in less than one second in our AWS RDS environment, but it takes ~55 seconds on a blade in ...
2
votes
0answers
33 views
Queries within transaction
I am almost finished with my queries which are running inside transactions so it's everything or nothing... Could you please take a look if I am missing something or if everything is where it belongs?
...
8
votes
1answer
103 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 ...
2
votes
2answers
70 views
TSQL query to gather amounts for an employees, spouses, and children in one row
I have a query that works as intended, kind of pulling from multiple records in a table, and flattening the data into one row. I am wondering if there is a faster/more semantically correct way to do ...
1
vote
2answers
59 views
Pivot script for sales numbers
I've recently been tasked with migrating some code stacks for an applicatin that is moving from using Access to using SQL-Server.
I don't usually use PIVOT all ...
4
votes
1answer
67 views
SQL procedure for Archiving and Deleting
I have a requirement for archiving 5 huge tables from PROD to ARCHIVE server without losing the integrity of the tables.
The query makes use of the Linked Server functionality and current idea is to ...
2
votes
0answers
41 views
T-SQL Secure String Comparison
The previous version of my function was scalar-valued and employed a WHILE loop to do the comparison, this is SLOW. This new version is tabled-valued and uses a typical tally table in place of the ...
1
vote
1answer
24 views
Get top row for muliple registers
I came across a piece of code that generates SELECT queries from a template.
The template:
...
19
votes
3answers
530 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 ...
0
votes
0answers
41 views
Validating an NPI in T-SQL (Luhn's Algorithm)
The requirements for validating a national provider identifier can be found here. Here's my attempt at a T-SQL implementation:
...
3
votes
2answers
131 views
Insert or update user data
I inherited an application that uses some stored procedures. Here is a sample of one of the stored procedures used to insert or modify user data.
would like to get the group's opinion on the code. ...
3
votes
2answers
55 views
What are the top voted comments on closed questions, and who made the comments?
Following this linked question and the feedback it generated, I put together a new query which utilizes a JOIN.
...
3
votes
1answer
61 views
What percentage of down votes are users responsible for?
I put together this simple query to calculate the percentage of downvotes each user is responsible for and displaying them in order of downvote percentage. It's generating the desired results, but I ...
6
votes
2answers
77 views
4
votes
2answers
61 views
Top Python badged users from NYC
I forked this StackExchange Data Explorer (SEDE) query from another one, and made it a bit more complex. I'm looking for feedback on my style, as well as answers to other questions (see below).
...
7
votes
2answers
253 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 ...
1
vote
1answer
74 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
306 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
111 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
65 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
67 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
91 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
1answer
82 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
35 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 (...
5
votes
0answers
127 views
4
votes
2answers
84 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
41 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
129 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
83 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
89 views
T-SQL Constant Time Comparison
Explanation is in the function. Please point out any egregious errors:
...
1
vote
0answers
36 views
T-SQL Totp (Rfc6238 SHA2_256) Implementation
Using my previous function as a base I've come up the following:
...
3
votes
1answer
50 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 ...
17
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
86 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
57 views
T-SQL Pbdfk2 (Rfc2898 SHA2_256) Implementation
Using my previous function as a base I've come up the following:
...
4
votes
0answers
154 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
129 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
35 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:
...
17
votes
2answers
262 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
42 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:
<...
5
votes
1answer
181 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 ...
8
votes
2answers
104 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 ...
28
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
1answer
33 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 ...
