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 ...
2
votes
2answers
115 views

T-SQL simplify repeated query unions for each day of the week

I've created the T-SQL code below to get the next nearest datetime value during the next 7 days. The ScheduleDetail table schema is: ...
0
votes
1answer
467 views

T-SQL query outer apply with nested subqueries

I have the following 3 tables, and I don't have the option to modify them. [Logs] table with [log_type], ...
6
votes
2answers
103 views

Find how much reputation a user had on a given date

I saw this question on MSE and went ahead and wrote a solution to it: https://meta.stackexchange.com/questions/313561/determining-users-reputation-as-of-particular-date This calculates the ...
1
vote
1answer
122 views

Calculate NPS in a stored procedure

We collect customer surveys and one of the questions is the dreaded "How likely are you to recommend our service?" question from which we calculate a Net Promoter Score. (I hate this calculation, but ...
4
votes
1answer
101 views

Retrieving a product's sold quantity

I've written a view to retrieve each product's sold quantity and four week sales average by Branch/Route/...
0
votes
2answers
707 views

Merge table stored procedure

I wrote a stored procedure that will do a merge of two tables: ...
4
votes
2answers
95 views

Simple locale loader

I have a class that loads locales from a database. I'm not sure if locale is the right word. It's basically loading texts for a game, generic sayings like alerts and error messages. I'm also not sure ...
4
votes
0answers
45 views

Insert a page definition in the right order

I have a table SlideList that defines all available slides that can be put into a PowerPoint presentation. I have a table ...
-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 ...
3
votes
2answers
83 views

SQL Server - Iterate, aggregate, and insert

I'm trying to find an efficient way to aggregate data for reporting. Let's say I need to aggregate the following data in 5-second intervals: ...
4
votes
1answer
32 views

Parse numbers from text based on relative position to certain substrings

The input is a product code. I want to pull out the "percentage" portion of that code. For example, if the code contains the substring BIO, I want all the numbers that follow it until I hit another ...
2
votes
1answer
60 views

SQL Query to check if 100+ (or Dynamic) columns in table is null

Let assume a table has more than 100 of columns & I want to display all the rows when all of its columns (Col1, Col2, ..., ColN) are nulls except for the column(s) I mentioned (PK). This is just ...
1
vote
2answers
55 views

Displaying a single record based on DocID

I have a table with ~900,000 records. I need to display a single record based on DocID using this query. The record is the latest record that got modified by another program. This piece of query run ~...
6
votes
2answers
466 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. ...
11
votes
1answer
93 views

The sniper: a Zombie searcher for specific tags

Sometimes, it's not enough to know what the easy prey is. You also need to know whether you will be able to hunt it down. What good is an almost turned haskell zombie if you cannot read the functional ...
14
votes
1answer
1k views

A wounded Zombie finder, or: how to find the almost dead undead?

The Zombies are on their way to eat our statistics. Fortunately, we have a way to get rid of them: our votes. However, we need to find them first. The /unanswered section is too large and contains ...
2
votes
3answers
76 views

search for text across multiple tables

I've got the following query. ...
6
votes
1answer
176 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
1answer
50 views

Update Trigger (Comparing Descriptive string), as well as checking for valid updates

I have an audit table EpisodesHistory. One of the fields that gets copied is eDescription. I expect ...
1
vote
1answer
256 views

Ontario Health Card mod 10 validation

An example of the business rules are here. Does this code implement those rules correctly? Note that you will have to derive the rules from the example. ...
33
votes
7answers
266k 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, ...
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....
8
votes
3answers
592 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
57 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 ...
1
vote
1answer
410 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
112 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
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 ...
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
612 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
85 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 ...
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 ...
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 ...
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
224 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
73 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: ...
7
votes
3answers
196 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 C#...
2
votes
2answers
63 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 ...
5
votes
1answer
8k 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 ...
5
votes
2answers
45 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 ...
3
votes
1answer
70 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 ...
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 ...
3
votes
1answer
380 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 ...
2
votes
1answer
148 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 ...
2
votes
1answer
748 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
votes
2answers
154 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. ...
13
votes
3answers
200 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, ...
2
votes
3answers
113 views

Query for combining results of same query running across multiple databases

I wrote a query to run the same query across multiple databases and combine the results. While it seems plenty quick I was wondering if there is a better way to do this. ...