Questions tagged [sql-server]
Microsoft's SQL Server is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. It originated from the Sybase SQL Server codebase, which is why both products use the extension of SQL called Transact-SQL (T-SQL).
577 questions
1
vote
1
answer
66
views
Generate Serial number based on column value in sql server
I have table called cash_voucher it has some columns.
I want to generate voucher number based location id.
I mean generates voucher numbers starting from 1 for each location
For example location id is ...
- 141
3
votes
2
answers
82
views
Stored procedure with output parameters in MSSQL
I want a stored procedure that retrieves ID and PID from the invoice table with batchid as input parameter and based on the retrieved PID I want to retrieve sid from the log table.
In total I want to ...
- 31
2
votes
0
answers
75
views
Case Study: Database design help for a small app used to coach tennis players
I have a small SQL server database and VBA program that is used to automate tasks related to tennis students, programs, practices, performances, and student testing.
I'm trying to learn db design and ...
- 21
1
vote
1
answer
50
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 ...
- 137
2
votes
1
answer
166
views
Need assistance in Optimizing the Recursive Query in Pyspark
I am trying to convert a SQL Server stored procedure to Pyspark code.
This is my SQL Server query:
...
- 21
3
votes
1
answer
141
views
Creating an array (temporary table) in SQL and iterating through each row within this table to preform a check on the data in each row
The SQL Server version I am using is SQL Server 2008.
I've got an SQL query/script I've written which is to help simplify some things while our API is being rebuilt.
Essentially, this script allows ...
- 131
3
votes
2
answers
137
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 ...
- 31
2
votes
1
answer
80
views
Understanding how to properly create a stored procedure that only does INSERT from sourcetable to targettable with dynamic sql
Background and use-case
I need to create a stored procedure where the input parameters will be: @sourceTable, @targetTable, @compositeKey. I'm using available fields for the composite key, since I ...
- 21
4
votes
2
answers
168
views
Apply placeholders to an array in a loop for pdo statements for search functions with unknown numbers of keywords
I'm a newbie to PHP and other programming for that matter.
I'm doing this simply to learn. Figured the code could be used to search through a police database or something.
The statements are Microsoft ...
- 41
3
votes
1
answer
111
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. ...
- 137
2
votes
2
answers
144
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. ...
- 137
1
vote
1
answer
174
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
...
- 113
2
votes
1
answer
315
views
Efficient Connection Pooling in ODBC-MSSQL with Rust (odbc-api)?
I want to connect to MSSQL using ODBC (odbc-api) at the beginning of my server application created with hyper.rs.
I want to have a connection pool and obtain a connection within each request, which ...
- 21
0
votes
1
answer
138
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 ...
- 423
1
vote
1
answer
93
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 ...
- 303
1
vote
1
answer
403
views
Employee Attendance structure design
I am designing a solution to store employee day attendance, which can be recorded either as hours or as an interval within a day. The goal is to maintain a monthly attendance list for each employee.
...
- 119
1
vote
3
answers
2k
views
Write SQL Server table to Parquet file
This is a C# console application that creates Parquet file for SQL Server table.
Given a 5GB SQL table, it takes nearly 65 minutes, so for our bigger tables (up to 500GB), it may end up taking days to ...
- 11
0
votes
1
answer
213
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
user5
...
- 461
-2
votes
1
answer
92
views
Optimizing a TRANSACT SQL statement [closed]
I'm in need to optimize the following TRANSACT SQL statement :
...
3
votes
0
answers
62
views
SQL query to pivot table into dynamically growing set of columns [closed]
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
...
- 39
0
votes
1
answer
80
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 ...
1
vote
1
answer
89
views
Select certain row if it is in the date range otherwise take fallback
SQL Server 2017
The table contains duplicate contracts per factory with different From and To dates.
I'd like to select the correct row based on a declared YearMonth parameter.
If this falls in the ...
- 111
1
vote
1
answer
106
views
updated Accounting system data database design
Many thanks for your valuable comments in my previous post.
In my previous link Accounting system data database design
The primary key and foreign key was not assigned i was thinking handle from front ...
3
votes
2
answers
679
views
Accounting system data database design
I have created an accounting database as per my accounting knowledge.
I want to check: Is that database correct which I design for the accounting system?
I have table called Mst_AccountGroup(Asset,...
0
votes
1
answer
79
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 ...
- 137
1
vote
1
answer
119
views
1
vote
1
answer
166
views
Creating database and two tables linked by foreign key
These commands do what I want, but I'm trying to find out the best approach for this. My main areas of concern are: error handling and disposal of resources. In regards to error handling, I'm catching ...
6
votes
0
answers
272
views
dotnet - creating db records in bulks in a background job
UPDATE: I found the namespace Channels, and this does exactly what I want to do, but in a much more stable way. Please don't use my code for anything important! :)
Use case
I have a site that (at peak ...
- 161
-2
votes
1
answer
95
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 ...
- 135
1
vote
1
answer
468
views
Database schema for discussion forum scraping
I'm scraping posts from various discussion forums and storing those posts, along with some metadata, into a relational database (SQL). My actual use case is a bit more complex, but I think I can give ...
- 13
0
votes
2
answers
66
views
Table-Value Function with row-level-locking
I'm implementing row-level-locking on a MS SQL Server. The function I created below works, but even with a very small data set, it takes a significant amount of time to return the results.
If I were ...
- 181
1
vote
1
answer
2k
views
Get result of a scalar-valued SQL function from C#
I do not receive files on holidays; I only receive files on regular days—i.e., non-holidays. I want to skip 2021-09-06 since that's a holiday (Labor Day) and I know I will not receive any files. ...
- 11
-1
votes
1
answer
110
views
IF SELECT statement will break SQL ACID Compliance? [closed]
My QA told me that I should not use IF SELECT before doing an UPDATE statement because it will breaks the atomicity of a ...
- 141
3
votes
2
answers
181
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 ...
- 3,540
2
votes
2
answers
92
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 ...
3
votes
3
answers
149
views
3
votes
2
answers
966
views
PySpark SCD Type 1
I am using PySpark in Azure DataBricks to try to create a SCD Type 1.
I would like to know if this is an efficient way of doing this?
Here is my SQL table:
...
- 31
4
votes
1
answer
234
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 ...
0
votes
1
answer
405
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 ...
- 101
1
vote
2
answers
192
views
Optimizing database access using LINQ [closed]
I have the following method that I am trying to optimize. Currently, the method works great, however, it takes a little over 3 hours to complete. I feel like, using some nifty LINQ joins, it should be ...
- 188
1
vote
1
answer
236
views
Retrieve data from two different rows in a single SQL query?
I've a table with the following data
and I need to find the number of minutes between two jobs (Say A and C).
The following query works but wondering, if there is a simpler way to achieve the same.
<...
- 163
3
votes
2
answers
165
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 ...
- 31
2
votes
0
answers
55
views
Basic login by username logic with SQL Server
My goal is to make a very simple library with the authentications basics: create a user and save its hashed password, change its password, have a method to check if the password is correct for login.
...
user42412
2
votes
1
answer
301
views
How to make this query better (e.g. avoid repeating subquery in a query)?
Stack Exchange provides access to query their MS SQL databases. They have a table Posts where each post is either a question or an answer (see the schema on right ...
- 121
2
votes
1
answer
64
views
Performance stored procedure update multi-coulmn sql
I have a stored procedure the every 5 min run in sqlserver
high cost iO/cpu
do you have any idea to tune this query
...
2
votes
2
answers
166
views
Receive data from SQL and CSV and send it to MySQL database using Python
Details:
I have a code, which does the following:
Get data from CSV and insert it into MySQL table
Get data from SQL Server and insert it into MySQL table
Run this code every 240 seconds - to refresh ...
- 235
2
votes
0
answers
63
views
API Layer ClientId and Security style
Im at the point where I am designing my api security and I was going with a custom attribute I don't want to use third party systems as they cost money.
I was going to use the client id and the ...
- 231
1
vote
2
answers
799
views
Faster/Better Processing for SQL While Loop
I have a list of about 55k Accounts that I am cleaning up. 95% of them are from US/Canada which is what I'm most concerned with. I am going through record by record and will perform various checks ...
- 199
1
vote
1
answer
69
views
Calling a function within stored procedure to derive a value
I am trying to write a sql storeprocedure that basically returns a derived value from an outcome of a function call. The derived field returns the difference in days and if the difference is less ...
- 217
1
vote
1
answer
168
views
Get average post score of TOP 10 tags on StackOverflow using Stack Exchange Data Explorer
I want to use the Stack exchange data explorer in order to display some basic information about the top tags.
The query should print the number of questions and answers, the average number of answers ...
- 129