Skip to main content
Share Your Experience: Take the 2024 Developer Survey

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
1 answer
22 views

Filter filtered bike prices twice without repeating yourself

dbfiddle Goal Without repetition in the code, I want to filter BikeValuationList to only give the BikePriceId of the latest ...
J. Mini's user avatar
  • 127
0 votes
0 answers
14 views

Calendar Table in Power BI

I've been reusing calendar tables in various formats for a few years now. I'd like to know best practices and if there are ways to improve them. Any suggestions for style, code, etc. are all welcome. ...
aduguid's user avatar
  • 490
3 votes
2 answers
100 views

Calculate the count of top performers in SQL

I have a transaction table let's call it Offers Table. Please don't read too much into the business but let's say a customer wants to buy a car and the customer searches variety of websites but at the ...
Cody's user avatar
  • 31
2 votes
1 answer
61 views

SEDE query to count questions, views and unanswered for a set of tags

This query selects the number of SO questions, the number of views and the number of unanswered questions for each tag (the list of the tags is the user input). It works fine when it works, but it ...
Yulia V's user avatar
  • 635
2 votes
1 answer
70 views

Find All Recent Winners and Calculate an Encoding of What They Won

Context I was proud of this code for a little while, but the repetition wounds me. I know that the function name sucks, but the name and interface to this function are not under my control. ...
J. Mini's user avatar
  • 127
2 votes
2 answers
86 views

Find All Recent Winners and Calculate an Encoding

Context I was proud of this code for a little while, but the repetition wounds me. I know that the function name sucks, but the name and interface to this function are not under my control. ...
J. Mini's user avatar
  • 127
1 vote
1 answer
128 views

What is the percentage of customers who increase their closing balance by more than 5%?

The Question What is the percentage of customers who increase their closing balance by more than 5%? Source Code The database and all details can be found here. ERD My Proposed Solution ...
Mike's user avatar
  • 113
0 votes
1 answer
115 views

Is there a more concise way to write this Procedure

I have this stored Procedure that is passed in the data below that will building the SQL select that will get EXEC. The data that can be passed in can me null so I have three ...
Jefferson's user avatar
  • 353
1 vote
1 answer
71 views

Is there a more concise way to write this query in SQL Server?

I'm playing with data from a Time Use survey as a beginner SQL user. Gender is represented as 1's (Male) and 2's (Female), and the age of each participant is listed. I would like to divide them into ...
Erik's user avatar
  • 303
0 votes
1 answer
157 views

Count how many active and inactive users are in a group/unit/department/team

Given the following example data: id username group unit department team status 1 user1 g1 u1 d1 t1 active 2 user2 g1 u1 d1 t2 active 3 user3 g1 u1 d1 t3 inactive 4 user4 g3 u6 d12 t30 active 5 ...
pileup's user avatar
  • 421
-2 votes
1 answer
55 views

Optimizing a TRANSACT SQL statement [closed]

I'm in need to optimize the following TRANSACT SQL statement : ...
Simone Spagna's user avatar
3 votes
0 answers
47 views

SQL query to pivot table into dynamically growing set of columns

I am working on SQL Server I have sample data like this in a table. CustId Bank city rating date rnk 1 Deutsche Delhi 5 10/10/2022 1 1 BOA Pune 6 10/10/2022 2 1 UBS Mumbai 7 10/10/2022 3 1 SBI ...
user3580341's user avatar
0 votes
1 answer
58 views

Nested table in query

I have transaction tables called MaterialRequest and MaterialRequestDetails. What I want to check my query and review that it's ...
Basit Sarguroh's user avatar
5 votes
0 answers
57 views

Message-free flow-oriented logger

Today, I've got a small FlowLogger for you to review. The idea is not to log pure messages, but focus on the flow of the app that might contain such items as: <...
t3chb0t's user avatar
  • 44.3k
0 votes
1 answer
51 views

Find dogs who stopped being good boys without ever being good boys - Was HAVING the wrong approach?

The problem I have solved is as follows: Consider a table of dogs, DOGGIES, that records on each row the ID of a doggy, one ...
J. Mini's user avatar
  • 127
-2 votes
1 answer
52 views

T-SQL Query Performance [closed]

The query below already has indexes based its execution plan but it is still under performant with larger datasets. Are the additional null checks needed and why? Do you see anything that can be done ...
Rick B.'s user avatar
  • 135
3 votes
2 answers
149 views

Alter authorization on all non-system SQL Server databases

I am not very experienced with T-SQL and feel this can be done better. The script changes ownership of all "user" (non-system) databases to sa. Usage of a ...
Igor Soloydenko's user avatar
2 votes
2 answers
66 views

Complex reporting query suggestion needed for different approach as per latest Tsql format

We have recently migrated from SQL Server 2008 R2 to SQL Server 2017 web edition. I am not a SQL developer but enthuses to learn. Since many of our scrips are of old SQL format, I am searching for ...
Aditya Sawant's user avatar
1 vote
0 answers
25 views

Re-organizing indices in case of fragmentation is T-SQL

In order to keep the fragmentation ratio of indices low, you should re-organize the indices from time to time on MSSQL. Microsoft describes in the link below how you can check the fragmentation of ...
gil.fernandes's user avatar
4 votes
1 answer
193 views

Basic schemas for database and tables creation, bulk import from .csv and basic queries

The following SQL code serves the purpose of assessing basic SQL Server literacy. I am creating database and tables schemas, inserting data from .csv files. Constructing 8 simple queries as answers to ...
I. Я. Newb's user avatar
0 votes
1 answer
368 views

SQL Query to perform a "reverse exclusion" on a LEFT JOIN with a select from the left side table

I have a set of tables that I want to execute a LEFT JOIN on and bring back "excluded" rows. In addition, I would like the left table's ID included as ...
akousmata's user avatar
  • 101
3 votes
2 answers
128 views

all appointment based on interval distance

Currently with a given Date and an Interval in Day, I can calculate all of the appointment in the future. for example if you are ...
Masoud R's user avatar
3 votes
0 answers
35 views

SEDE query calculating user reputation averages

I've created the following SEDE query which calculates the reputation averages of users on a particular site. It also optionally excludes "new" users (users with rep of 1 or 101 so note 100% ...
TheLethalCoder's user avatar
1 vote
1 answer
55 views

MSSQL database structure and stored procedure performance

I build database called MessageBoard and created StoredProcedure GetHomeView that return data for HomeView. I am completely new ...
user221124's user avatar
5 votes
2 answers
759 views

Query with multiple foreign keys to the same table

I have a primary table, REP_ASSIST.ES_LOG with multiple columns that are foreign keys to another table, ...
Zephyr's user avatar
  • 217
4 votes
2 answers
188 views

Function to get current value or default from a string of two values

I have SQL scripts that I want to be executable directly in the editor (like DataGrip or SSMS) and also in automated integration tests. However, in each use-case they require different environment and ...
t3chb0t's user avatar
  • 44.3k
3 votes
1 answer
90 views

SQL Sub-query with running total optimization

The codes below get the rows of data until it reaches the total running quantity limit. Notice that the SQL uses sub-query. ...
h3n's user avatar
  • 133
1 vote
1 answer
29 views

T-SQL Getting Sequential events with first even criteria

I have a query I am writing where I want output if a person has some service provided, then I want all the services they had provided after that and I don't want the individual returned if that is the ...
MCP_infiltrator's user avatar
3 votes
1 answer
1k views

Daily SQL job to delete records older than 24 hours

Below is a delete job to delete 1M+ records daily and takes 13 hours to complete and sometimes more than that. I need to optimize this. The table tblcalldatastore is being inserted 24*7 through a ...
Andy's user avatar
  • 43
12 votes
1 answer
288 views

Safe dynamic SQL for generic search

Prompted by discussion about SQL injection, I wanted to put a proof of concept forward to get feedback about whether this is in fact safe and protected against SQL injection or other malicious use. ...
this's user avatar
  • 2,029
2 votes
2 answers
342 views

Build WHERE clause for search conditions [closed]

Here is a set of classes that are used to build where clause for SQL Server and Oracle for different field types e.g. text, <...
Aashish Upadhyay's user avatar
1 vote
2 answers
93 views

Combining two queries in one

What I want is getting all names (first, middle, last) of the users along with each of their total hours of work which will be calculated by the SQL statement: ...
Gene Adrian San Luis's user avatar
1 vote
0 answers
90 views

Select splat in a subquery [closed]

Obviously, 'select *' is never a good idea. HOWEVER, I have taken a job with an org that allowed this cancer to spread. They have huge queries, using ...
ARLibertarian's user avatar
1 vote
1 answer
95 views

T-SQL query Choose name of clients, concluded more than one loan agreement in 2010

QUESTION: Choose the name of clients, concluded more than one loan agreement in 2010, as the numbers and dates of issue of these contracts. Sort by name of the client and date of issue of the loan. ...
Pavel Kononenko's user avatar
3 votes
1 answer
43 views

T-SQL Query remove duplicates except for original row from the table

Question: There is an Exam_Merge table into which to import records from a similarly structured table in another database. In this case, the Exam_Merge table contains the values of the primary key ID ...
Pavel Kononenko's user avatar
4 votes
1 answer
235 views

SQL query to count loans issued during each week

Calculate the number of loans issued by the Regional offices for each business week in the period from 38 to 40 business week of 2011 inclusive. Sort by name of the regional unit, business week. ...
Pavel Kononenko's user avatar
4 votes
1 answer
1k views

SQL e-commerce database design

I want as an exercise to create an e-commerce application from bottom up. My main goal here is to gain a lot of knowledge. I have experience in web development, but never have been there from the ...
JeremyVm's user avatar
  • 143
1 vote
1 answer
71 views

Query to generate payroll based on attendance for a month

I have written a query to generate payroll based on attendance for a month, for about 4000 employees. It takes quite a long time to execute. Basically, what I am doing is to joining two different ...
Parvez's user avatar
  • 23
1 vote
1 answer
636 views

Insert multiple rows without using CURSOR SQL Server 2014

I have a fairly complex query which insert multiple rows into a table from multiple tables. Its works fine but I really don't want to use cursor as some blog says cursor took long time for execution (...
Parvez's user avatar
  • 23
1 vote
1 answer
115 views

Initialize various commands for the SqlDataAdapter

This is my code for my SqlDataAdapter which is similar to the code example given in the docs: ...
asathkum's user avatar
  • 155
3 votes
2 answers
747 views

SQL Query - records within the last year

Bit of a complicated one for me. I have a database full of hundreds of thousands of records, many of which are duplicated. I need to get all records within the last year but making sure every ...
Matthew Stott's user avatar
1 vote
1 answer
1k views

Determining daylight-saving time in Central European Time

(We are on SQL Server 2008). The following constellation: Our applications passes date&time around (in the most horrible way possible). We want to simplify this. Instead of a culture-specific ...
Quandary's user avatar
  • 159
7 votes
1 answer
318 views

Retrieving the IDs of entries to be deleted from a table

Business logic It would take us too far to explain the whole business case behind this code, so I'll try to be as succinct as possible. There are Persons, and ...
BCdotWEB's user avatar
  • 11.2k
4 votes
1 answer
99 views

Get local datetime from UTC time

One of the app is inserting date in UTC format (column CreateDate). When reporting from this table, I used a difference in hours between the inbuilt ...
Ashish Gupta's user avatar
2 votes
1 answer
404 views

Validating region/state/province codes for different countries

I have a large table in which I store region/state/province codes (two letters) from different countries. I use these region codes further downstream for multiple process. One of the steps I do is a ...
VK_217's user avatar
  • 141
3 votes
1 answer
150 views

SQL SP creating certificates for shipping

We ship large spools of copper wire and pallets of copper rod that have to meet specific chemical and mechanical requirements, this is what must be put on the certifications. I created an SP which ...
Mike's user avatar
  • 185
0 votes
1 answer
57 views

Updating 3 columns conditionally on a database table

I need to run this script to update the value of 3 columns based on certain conditions. Right now, this is what I have: ...
João Pinto's user avatar
3 votes
1 answer
81 views

Loop Through bitarray to retrieve subsets of that bitarray

Objective: I want to loop through a bitarray and store subsets of that bitarray in a table. Context: I have a bitarray with 48 elements where each element represent one hour. I want to look back 24 ...
RMSPereira's user avatar
3 votes
1 answer
1k views

Inserting data into SQL-Server table dynamically

I know I can do all this via SSMA quite quickly, but I wanted to see if I could do it efficiently in PowerShell myself as a little project. Basically, the entire script takes tables from an Access ...
Owain Esau's user avatar
1 vote
1 answer
108 views

SQL Stored Procedure to export specified data

I'm currently using a stored procedure(sproc) to export to Excel. The sproc is being passed ...
Jaskier's user avatar
  • 111

1
2 3 4 5