Transact-SQL (TSQL) is the extended SQL dialect used in Microsoft SQL Server and Sybase. Please also tag with either [sql-server] or [sybase].
3
votes
0answers
16 views
SQL Server - Optimize window functions w/out repeating code?
I have one table on which I want to perform some calculations. The table stores employee points that are awarded for performing various tasks. The relevant structure is as follows (table has about ...
1
vote
1answer
35 views
Schema for SQL database of guitars
I just want to ask you guys if the database design that i made for my asp.net website is correct or not. So basically, I created a database for the brands of my guitar products, which is like this:
...
1
vote
1answer
52 views
Delete duplicate entries with lower IDs
I have a function that deletes duplicate entries. The highest ID is kept and the older ones are removed.
function:
...
3
votes
1answer
85 views
Optimising performance of long running a sql query
The code below takes 5-6 hours for a years worth of data (850,000 records). It takes much less time when running a single month.
To start with is there anything glaringly obvious within this script ...
2
votes
2answers
51 views
Find occurrences of strings in view B in column from table A
I'm trying to find the occurrences of strings in view B in a string in table A. However, the performance is horrendous (takes about 17 minutes to run) and I was wondering if anyone had any suggestions ...
3
votes
0answers
31 views
SqlServer database reflection and autogenerating NLog.xml
This is my first attempt at using T4 templates and I thought I generate the NLog.xml with it by getting the tables and the columns from the database to ...
1
vote
1answer
30 views
Extract Students with All Enrolments in the Past
I am trying to extract the students where all of their enrolments are in the Past.
Some students can have more than one Enrolment and one of its End Date is in the future while the other one is in ...
3
votes
1answer
48 views
Kill All Connections For Domain Account Users
I have an application that has hundreds of users. Every now and again we need all users out of the application to run apply a fix on the data in DB (edit tables, causing triggers to be disabled during ...
4
votes
2answers
37 views
Query that shows similar columns between different tables on a database
In my work, I often have to take a naive look at tables and then never see them again, and it can be difficult to see what lines up where for large databases with many columns. I wrote this, and while ...
2
votes
0answers
44 views
Stored procedure to describe the reason that a particular employee is unsuitable for a particular task
I used the following Stack Overflow questions as references for writing this code:
Select columns from result set of stored procedure
What is the equivalent of String.Join on TSQL?
The idea behind ...
0
votes
1answer
22 views
Fetch a department's total, Its percentage of earnings for that site and its percentage of earnings across the company
I am trying to use the data from one table to produce the result set
| Dept | TotalValue | PercentageOfSite | PercentageOfCompany |
Any improvements or advice on the below would be helpful.
...
5
votes
2answers
59 views
Query to get distinct items at the top of results and duplicates following
I need to query the table below and list the distinct items at the top of my result set and the duplicates will follow, the order of the duplicates does not matter.
I am working with Sql Server 2016 ...
3
votes
1answer
650 views
Using T-SQL SEQUENCE with Entity Framework 6
After reading many posts about how to handle sequences in EF 6 (see below) I found a way that was working for me. I'm using Code First and want to include the sequence from scratch, so without using ...
2
votes
1answer
33 views
Second-sealed-bid auction web app
I am working on a second-sealed-bid auction web app. I have an Auction table to keep auction items on record, a Bid table to ...
3
votes
1answer
48 views
T-SQL UDF to replace replace base URLs
I have a problem where I need to replace every base part of URLs in a body of text. However, I only want to replace it in specific URLs. Only URLs that go to "resource.aspx" that have the "source" ...
3
votes
1answer
57 views
Replacing names in text fields with aliases to help de-identify data
I have a table with over 1,000,000 records. I need to replace any names in the text fields with aliases to help de-identify the data. For this example, let's assume the table is ...
3
votes
1answer
46 views
Acquiring earnings information
The first part of the query grabs Policy's Premiums, Effective and ...
0
votes
0answers
18 views
Rescheduling long production tasks to the next day
The code below is used for a table with production tasks. It moves tasks to the next day where the sum of hours required for actions belonging to the same production order is more than 8.
The code ...
2
votes
0answers
40 views
Generating email with BCP
This code iterates through the table @Table1 to get the Purchaser, PurchaserID, ...
3
votes
1answer
42 views
3
votes
1answer
113 views
Complex report generator using huge SQL Query
As the title say, I have a huge SQL Query which was built over the time (as new requests came, fast workarounds were demanded).More, there were three DB Admins who contributed to this query, and I'm ...
2
votes
2answers
53 views
Rolling totals in a view
I have a view that performs some rolling totals. In this instance, it must be in a view. The issue is that it takes about 10 minutes to run, returning 2700 records. There should be a faster way, but I ...
2
votes
1answer
127 views
Generating INSERT query from parameters
I use NLog everywhere and I really don't like writing the inserts everytime. You need to write the same variable three times: two for the insert and one for the parameter for example:
...
1
vote
1answer
32 views
Correlated subquery slow
I have a table that looks like this
Directory nvarchar(max)
Extension nvarchar(10)
Length bigint
I also have another table that provides the type of file (lookup table)
extension nvarchar(10)
...
6
votes
1answer
51 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
127 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
94 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
23 views
4
votes
2answers
53 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
24 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
46 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
38 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
110 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
84 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
68 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
82 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
60 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
550 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 ...
3
votes
2answers
155 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
59 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
64 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
87 views
4
votes
2answers
65 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
417 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
168 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
346 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
114 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
89 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
97 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 ...
