Tagged Questions
13
votes
1answer
179 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 ...
12
votes
2answers
297 views
11
votes
3answers
156 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, ...
11
votes
2answers
214 views
Find records with duplicate serial number
I was tasked today with cleaning up data from a SSRS report put together by a DB programmer. The original report was to find duplicate serial number sales within a user-defined period of time. I was ...
10
votes
3answers
300 views
Is there any way to speed up this stored procedure?
Is there any way to optimize this stored procedure? Maybe something instead of so many joins?
It takes some time to execute. Maybe there are other options that I could look into?
...
10
votes
2answers
225 views
Another way to run reports on a SpiceWorks SQLite database using a linked server in SSMS?
I created a linked server in SSMS so that I could run reports on a SpiceWorks SQLite database. there are a few quirks in the whole system.
These simple queries are becoming some of the most ...
9
votes
1answer
62 views
Loading Data Warehouse with Dynamic SQL
For a data warehousing project I ran into the following:
Custom fields that users can create, modify and delete, that should be loaded into the data warehouse as they are when the ETL happens.
On ...
8
votes
3answers
366 views
8
votes
1answer
105 views
Can you replace a REPLACE statement, or 9?
I would really like something to replace all of these REPLACE Statements.
...
8
votes
2answers
59 views
SE Data Explorer Query: Average score for questions and answers, by tag
Being curious to see which tags on this site get the most attention, I developed a Stack Exchange Data Explorer query:
...
7
votes
5answers
2k views
7
votes
1answer
126 views
Fluent Nhibernate, is the mapping correct?
I'm very new to NHibernate and DB. I have three tables which are connected with many to many relationship.
Here are the tables creation Script:
...
7
votes
1answer
459 views
Improve upon this SQL Server stored procedure boilerplate?
I often write little stored procedures to do this and that. Sometimes they are just for utility to return some diagnostic-level information, sometimes they are horrendously large, sometimes they are ...
7
votes
1answer
109 views
Auditing a complex mix of reasonably small tables
I plan to use the following approach to audit the tables that represent user-editable configuration of an automated system (over the course of system's life these will be inevitably extended in their ...
7
votes
3answers
175 views
Return Data and Update Row without Multiple Lookups?
I have a stored procedure that looks up an article based on the article's title. But I also need to increment a column in the same table that counts the number of times the article has been viewed.
...
6
votes
3answers
63 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
258 views
Multiple SQL statements in a single transaction
Using the following query I'm doing transactions with two tables
...
6
votes
2answers
90 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
2k 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 ...
6
votes
1answer
2k 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 ...
6
votes
1answer
107 views
6
votes
1answer
48 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 ...
6
votes
0answers
63 views
Executing large SQL script file with GO statements using ADO
I wrote a small component (still in the works but working) which takes a large SQL script file, splits it into different "blocks" based on GO statements, and executes them one by one.
The only major ...
5
votes
1answer
464 views
Can I populate this table faster?
I have the following SQL query which takes ~3 seconds to run, maybe ~4. This populates a consultants table with their expected payments for the month.
Unfortunately, the page only loads when this ...
5
votes
1answer
90 views
SQL Join code check and peer review
I am still fairly green when it comes to SQL joins. The below code works but I want to check that I am doing it the best way before I copy and paste errors into other work in the future.
The idea of ...
5
votes
1answer
57 views
EF query for calculating monthly trends
My application has a fairly important query that is used in a lot of places. Unfortunately it takes about 14 seconds to run, so I'd like to find a way of possibly improving it.
The application ...
5
votes
2answers
61 views
Update column based on input variable in stored procedure
The purpose of the below code is to update a column based on the name of the field name sent from the .NET code. This allows one piece of code to handle multiple rows when a user is only ...
5
votes
1answer
307 views
Fastest image upload into SQL database
Hello I am trying to upload an image (screenshot from desktop) to SQL database as fast as possible. I would like to optimize my current procedure in terms of speed:
...
5
votes
2answers
202 views
SQL Query Tuning
I have a mammoth SQL statement, however it's taking a long time to load (27 secs on the server). I think the issue lies with the IN statements towards the bottom (the IN statement is repeated too, can ...
4
votes
3answers
591 views
Speed up application and avoid SQL Timeouts
The code works just fine, but recently performace has taken a hit and it has SQL Timeouts far too often. I have it set up so I can run 50 different version of the application two for each different ...
4
votes
3answers
196 views
SQL percentage calculation
I have created a query in which there is a column that calculates a percentage:
...
4
votes
1answer
215 views
SQL - How's my formatting?
I'm somewhat new to SQL (using it in the past but only being exposed to it heavily in my current role). Unfortunately nobody at my current company has really given me any advice on formatting. How can ...
4
votes
2answers
483 views
Will somebody optimize this sql query
I want to get distinct of two columns from an sql table.
I am getting this result can i optimize this query.
...
4
votes
3answers
60 views
Relational schema and query for multiplayer tabletop game
As an exercise, I was asked to design a database schema (for MS SQL Server) for a tabletop game. The requirements were simple: players compete in matches and there are specific match types (e.g. 1v1 ...
4
votes
2answers
193 views
Inner cursor performance issues [closed]
Before I tried to avoid using cursor because of performance issues. But now I have to do some calculations to reach some goals. I have a stored procedure that has a code like this:
...
4
votes
2answers
456 views
Selecting the number of working days minus weekend days and UK bank holidays
I am fairly new to coding TSQL script and am looking for some second view on my script.
The goal here is to to pull in some data and amongst that data to show a field with a counting the number of ...
4
votes
1answer
103 views
Small query - removing XML tags
I have some code that I wrote almost a year ago exactly (1/9/2013) and I would like to know if I wrote it well or if it can be improved. I don't have any fun input or output, as these are not set ...
4
votes
1answer
69 views
Batch of SQL statements in a single query
I'm using the following query for multiple transactions. This is the scenario.
Generate Salary (this is not in the scope of the query)
I have employee attendance in ...
4
votes
1answer
42 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 ...
3
votes
4answers
3k views
Inner join with first result
In SQL Server, there is two tables: Houses, and their images.
I need a list with 20 houses with the first of their images (only one). I tried:
...
3
votes
4answers
352 views
Shifting records in SQL Database while sorting with algorithm
I have a table in a SQL Server database, which holds information of some images, and the relevant gallery of them. The columns are like:
ImageId, ...
3
votes
3answers
121 views
Code conversion from Access to T-SQL
I have an Access database that I'm converting to SQL Server 2008. One of the queries in the database uses the LAST function. The table has a AutoNumber ID so true order is simple.
...
3
votes
2answers
907 views
Which SQL statement is faster
My co-worker claims that using a parameterized Like statement is equivalent to dynamic sql and won't have its execution plan cached for reuse. He says that using ...
3
votes
2answers
46 views
Calculating percentage of 3 test forms
Please help me to simplify the script below. It has a lot of variables and a lot of values are getting assigned to them. Can I make it simpler?
It has to calculate the percentage of 3 test forms: ...
3
votes
2answers
193 views
SQL PreparedStatement; Am I doing it right?
I am building a web app with a single (not pooled) full time (jdbc) connection between static classes and the database. This is expected to be a low traffic site and the static methods are ...
3
votes
1answer
179 views
Backup a database over an SQL connection
Developing some industrial WinForms application for some industrial setting, I wanted to provide users of our software with a convenient way to back up the database the software uses (to send it to ...
3
votes
1answer
43 views
Best way to get two columns from a table
Basically I have taken over a project and found a table that has the following video and video title fields in it (why they didn't create a separate linked table is beyond me):
...
3
votes
2answers
228 views
Using SQL with encryption
This is my first project I am doing in VB.NET, and also my first real programming project. There is sensitive data, so I am utilizing Microsoft's Encryption/Decryption class (...
3
votes
1answer
104 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
1answer
192 views
Is it necessary to replace this cursor in SQL Server 2005?
The Problem
I have a cursor that I am trying to replace (perhaps unnecessarily) in an attempt to clean up a stored procedure. Essentially what it is doing is counting each note for each member in a ...
