All Questions
207
questions
1
vote
1
answer
26
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 ...
- 127
0
votes
0
answers
20
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. ...
- 490
3
votes
2
answers
105
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
71
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. ...
- 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. ...
- 127
1
vote
1
answer
131
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
0
votes
1
answer
116
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 ...
- 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 ...
- 303
0
votes
1
answer
162
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
...
- 421
-2
votes
1
answer
57
views
Optimizing a TRANSACT SQL statement [closed]
I'm in need to optimize the following TRANSACT SQL statement :
...
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
...
- 31
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 ...
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 ...
- 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 ...
- 135
3
votes
2
answers
150
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,490
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 ...
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 ...
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 ...
- 31
5
votes
2
answers
760
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, ...
- 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 ...
- 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. ...
- 133
1
vote
1
answer
30
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 ...
- 157
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 ...
- 43
12
votes
1
answer
291
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. ...
- 2,029
1
vote
2
answers
94
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: ...
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 ...
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.
...
- 193
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
...
- 193
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.
...
- 193
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 ...
- 143
1
vote
1
answer
72
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 ...
- 23
1
vote
1
answer
638
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 (...
- 23
3
votes
2
answers
761
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 ...
- 201
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 ...
- 159
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 ...
- 223
2
votes
1
answer
410
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 ...
- 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 ...
- 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:
...
- 103
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 ...
- 31
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 ...
- 95
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 ...
- 111
1
vote
1
answer
279
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 ...
- 1,290
4
votes
1
answer
143
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/...
- 1,961
6
votes
2
answers
119
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 ...
- 330
0
votes
1
answer
73
views
Filtering query by date built from integers
I am trying to make a stored procedure and pass some parameters for filtering. As soon as filtering is in horizon, code has to be as generic as possible with as few as possible ...
- 286
7
votes
0
answers
155
views
Modeling tabular structure in MSSQL to store data like Excel sheet applying Column inheritance
I've created this database structure to store tabular information (it's a simplification of my real structure but enough to show the point). In it I have Sheets and ...
4
votes
0
answers
55
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,290
3
votes
1
answer
79
views
Simple script to insert data
This is a simple script which inserts data into a couple of tables. I'm very new to SQL so any feeback, of any kind, would be much appreciated!
...
- 341
0
votes
2
answers
4k
views
3
votes
2
answers
98
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:
...
- 133