Questions tagged [t-sql]

Transact-SQL (TSQL) is the extended SQL dialect used in Microsoft SQL Server and Sybase. Please also tag with either [sql-server] or [sybase].

Filter by
Sorted by
Tagged with
1
vote
1answer
160 views

Report which connects to SQL Server via ODBC

I've created a report in Excel which connects to SQL Server via ODBC and I wrote some VBA to allow parameters to be passed in optionally. At this point everything works as expected but I'm curious to ...
3
votes
1answer
76 views

Creating SQL Indexes on Large Tables

I currently have a table with about 29 million records. The table currently has no indexes. However, I would like to build a clustered composite index built on three columns. When I attempt to add ...
8
votes
3answers
588 views

SQL trigger to log when employee records are updated

I am learning TSQL (SQL in general for that matter), and I wrote this trigger that inserts logs about updated employees into the tblEmployeeAudit table. When I was ...
1
vote
2answers
56 views

Stored procedure for processed orders in SQL Server

I am struggling with creating a Stored Procedure in SQL Server for getting sales statistics. I have managed to get the information I need out, but the query is a mess and I have probably messed up the ...
2
votes
1answer
54 views

Trigger Updating another table - Baseball Database

This is my first time writing T-SQL so I'd like to get some criticism on how I wrote this trigger. I'm building a Pitch-Tracking App that will use a relational database to track Pitches, At-Bats, ...
-1
votes
3answers
74 views

Insert to table from a temporary table [closed]

I created a procedure involving dynamically collecting from various projects (databases) some records into a temporary table and from that temporary table I am inserting into a table. With a ...
1
vote
1answer
403 views

Alternative to XML PATH

This inserts into 3 tables really quickly. My SELECT at the bottom appears to take an age, I assumed it would be fairly quick but is not. Is the XML path the right ...
3
votes
1answer
111 views

Speed up SQL query

I have a query that I need help with speeding up. Right now it takes long enough that it often times out. I do know that the part of the query that times out is part of an ...
1
vote
1answer
59 views

Posts that probably need editing

I wrote this Data Explorer query (reproduced below) some time ago that helped identify posts containing potential spelling/punctuation errors and earned me a Copy Editor badge on Anime.SE. ...
3
votes
1answer
567 views

Small DSL: Converting JSON to MSSQL query

Scenario Once upon a time, there was this human (ahem, me, ahem) who tried (and succeeded) to build a small program which would convert JSON to a MSSQL query using Python 3.x. How does it work? The ...
2
votes
2answers
82 views

SQL Server Stored Procedure quality

I'm beginner in programming and I have recently written quite an advanced stored procedure. Based on parameters with which I call it, it returns data and groups it, but that is not the most important ...
1
vote
0answers
42 views

Check status of all child generations of medical claim [closed]

When a medical claim is replaced, it gets a new number. The older version is supposed to get cancelled out, but this doesn't always happen successfully yet at my workplace. I wrote this to try to aid ...
1
vote
1answer
46 views

Remove table variable from query

I am running this query, and to get the calculation that I need, I use a table value parameter to house some of the data. Scanning roughly 50K rows to create the table value parameter then performing ...
0
votes
1answer
45 views

Concatenating results in a comma separated list

I am trying to list all of the employees linked to a certain 'Interaction' I want the employees listed on one line with comma's separating them, I have the following query which works but is very slow....
3
votes
2answers
56 views

FInd the correct Casing description from process tracking - Scalar Function

It works as intended, I just wonder if there is any stylistic things I could improve. It already runs well fast enough, although I'd be open to speed increases. This SQL Server 2008 Scalar Function ...
3
votes
1answer
81 views

Calculating employee points

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
217 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
71 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: ...
2
votes
1answer
109 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
0answers
32 views

Executing test cases when executing the SQL script [closed]

While executing a SQL script in production like sensitive database, usually I do the following ...
3
votes
1answer
70 views

Storing user permissions to hierarchically structured data in SQL Server

I'm developing a system that supports creating accounts and multiple users within every account that can each access a certain subset of data of their "parent" account. The data is stored in a SQL ...
2
votes
2answers
58 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
1answer
181 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 ...
2
votes
1answer
40 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
68 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 ...
5
votes
2answers
43 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 ...
6
votes
1answer
68 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 ...
1
vote
1answer
27 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. ...
6
votes
2answers
69 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 ...
5
votes
1answer
7k 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
145 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
126 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
337 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 ...
4
votes
2answers
58 views

Acquiring earnings information

The first part of the query grabs Policy's Premiums, Effective and ...
3
votes
1answer
100 views

Generating email with BCP

This code iterates through the table @Table1 to get the Purchaser, PurchaserID, ...
3
votes
1answer
52 views

Create new columns depending on the columns in a table

I have code in the this example: ...
3
votes
1answer
153 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
57 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
744 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
78 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) ...
7
votes
1answer
55 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
153 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. ...
6
votes
1answer
173 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
32 views

Optimize query from xml column [closed]

So I have a query like this: ...
5
votes
2answers
62 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 ...
6
votes
1answer
54 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
63 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
46 views

Queries within transaction [closed]

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? ...
9
votes
1answer
117 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 ...
3
votes
2answers
105 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 ...