Structured Query Language is a language for interacting with relational databases. Read the tag wiki's guidelines for requesting SQL reviews: 1) Provide context, 2) Include the schema, 3) If asking about performance, include indexes and the output of EXPLAIN SELECT.
0
votes
0answers
12 views
Update Database row on button click [on hold]
I have a php code that gets unread (unread messages has a column called flag that is always = 0) messages from the database and echoes them out. The problem here is I want that when the user clicks on ...
0
votes
0answers
48 views
Api occasionally slows down at runtime, possibly due SQL/connections
I'm currently working on an API that behaves incredibly unpredictable at runtime. Context: We run the same call 100 times without delay in between. A call can take anywhere from ~200 ms to ~5 seconds. ...
4
votes
2answers
43 views
Replacing MySQL's AUTO_INCREMENT with Postgres' SERIAL
I am making changes to a Python script that converts MySQL scripts to PostgreSQL, and I want to replace strings such as id INTEGER NOT NULL AUTO_INCREMENT with <...
5
votes
1answer
47 views
Locking in MySQL stored procedure for deduplication
Multiple upstream servers are sending notifications to a load-balanced django app. If all the upstream servers are working correctly, the app will always receive duplicates of these notifications (...
1
vote
0answers
28 views
Distance between 2 points using Haversine Formula
This is an implementation of the Haversine formula in SQL.
How can I simplify and improve the function?
...
3
votes
1answer
54 views
Java database query logic
I'm making a wrapper for querying the database so that I can more easily control the flow of database management and to make it easier for me to write data beans around my database. This code is ...
3
votes
2answers
33 views
Get the total counted stock take lines for each hour between two dates
I have the following table structure:
Tables
stocktakebinline
stocktakebinlinebatch
A stocktakebinline has a one to many ...
3
votes
3answers
142 views
Splitting a list of numbers into multiple lists
I need split the list I get from the select into multiple list so I can use Parallel.For() to generate the next query in different threads.
I create the main list
...
-3
votes
1answer
66 views
Increase performance of INNER JOIN with subquery [closed]
Is there any way for optimizing the below query without further addition of any INDEX, if possible?
...
5
votes
2answers
51 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 ...
1
vote
0answers
28 views
Temporary procedure to update/insert settings
I have several applications and each of them has its own [Setting] table. They are also in different schemas. In order to address this and to be able to use the ...
3
votes
1answer
85 views
SQL database for a social network
I'm trying to create a mini social network mobile application kind of like Instagram and I'm not sure about my database organisation, it feels like something is missing, especially with the ...
2
votes
1answer
33 views
2
votes
1answer
41 views
Translating code numbers to item descriptions in PostgreSQL query
For each itemId I need to select a value in my PostgreSQL query. I am doing it like this:
...
0
votes
1answer
59 views
Basic SQL aggregate query
I have an employee table, employees may have multiple peripherals. I want to bring back all employees that have a peripheral of type 1 or 2, but that don't have any ...
3
votes
1answer
85 views
Haskell REST API + PostgreSQL
I'm making some small REST API, which I have never done before. I've got some basic stuff working.
Now my job is to add new entities to this, but all my work is reduced to copy-pasting code I have ...
1
vote
1answer
62 views
Data Access Layer (object oriented)
I am using a global class (like a global model) that acts like a sql abstraction layer for my application. I am using it without problems now, but since the application I am creating is getting bigger,...
6
votes
2answers
39 views
Using/reusing the SQL Command for several Inserts in the SQL Transaction
I'm using the code below to save a record in my database. I was thinking that I am creating new instances of the SQL Command object and fear that I might be bloating the system memory and not doing ...
4
votes
1answer
87 views
Updating complex objects
I'm mainly looking at usage of Dapper, but in general, any comments relating SQL -> C# object mapping best practices would be appreciated.
I've got two super simple objects I'm working with:
...
3
votes
3answers
107 views
Saving complex business objects with reflection and recursion
In our application we have complex objects with many nesting levels, each with various properties that are important to the business. We currently serialize and persist these objects along with their ...
-1
votes
2answers
98 views
Update quote statement
I am writing an update statement. I think my SQL statement is opening me up for SQL injections. Is there a better to do this?
...
1
vote
2answers
146 views
SQL Query Builder in PHP
I have made a Query Builder, (not the full version of it yet) but I wanna ask the if there are someone out there, there can improve this code even more. Will be nice to know so I can code the rest in ...
3
votes
0answers
18 views
Planning the tours of home care employees as a stored procedure
We had an assignment to create a database for a fictional nursing service. To get accustomed to stored procedures we should implement one of our own choice. I decided to try my hands at writing a ...
2
votes
0answers
30 views
Generalizing work orders
I am working on designing tables for work orders.
The problem:
There is different work order models (from now on called WOM)
The WOMs share some attributes (Num, Date, Description, ... etc)
The WOMs ...
4
votes
1answer
38 views
JTable for employees in SQL table
I have been studying Java for 4 months and this project is what I came up with. I know I should make more classes and methods for quality but I am still learning. Please help to me if there is any ...
23
votes
5answers
2k views
Extensible code to support different HR rules
Recently, I got challenged to code with following bullet points:
Extensible code to support different annual leave rules for HR departments
Maintainable code to add/change the existing rules ...
4
votes
2answers
77 views
Transform a List of objects to a string of unique values for use in an SQL query
I have a List of Results. My goal is to extract the Result property ...
4
votes
2answers
46 views
Script for updating an Oracle database
I have the following piece of Perl script for updating an Oracle database. It's working perfectly fine, but I want to know how I can simplify this script.
...
5
votes
1answer
152 views
Translating an inputted query to SQL
I'm working on a small RestApi that would be able to:
Interpret different query languages like ...
0
votes
0answers
31 views
Win-Loss Record in SQL Server
I'm building a database on Microsoft SQL server to teach myself SQL. I am a big UFC fan so I'd like to build a database on UFC fighters. I'm building two tables [fight] and [fighter]. The [fighter] ...
2
votes
1answer
31 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 ...
1
vote
0answers
21 views
SQL Server: Lock rows on updating
I have multiple instances of an application (C# console apps) which needs to pick a set of records from a table and mark them (Update AllocatedTo with instance name), so that no other instances will ...
3
votes
2answers
56 views
C# Datagridview to Access database
I feel like there would be a better way to this. Essentially, I am transporting the data from an excel file to a DGV, and then from a DGV to an Access database. Should I cut the middle man or is this ...
1
vote
1answer
26 views
Working out statistics for unique users
The below code is run on a routine rather than say login time but being new to using FETCH NEXT I can't help think maybe there is a trick here to merge at least one of the SET lines.
The end results ...
3
votes
1answer
41 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" ...
2
votes
2answers
67 views
Register page for website
I've been working on a simple forum website as a toy project, to start learning PHP and SQL. I've implemented all of the functionality I want, but I thought I'd run the code by the Code Review ...
0
votes
1answer
76 views
2
votes
0answers
49 views
Complex views are a little slow
I have this view vw_RFQPartVendor that's based on these two views vw_requestVendor and ...
3
votes
1answer
41 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 ...
1
vote
0answers
25 views
SQL Procedure to populate or create tables from views - performance issues
I have written the below to populate/create tables (t_) from all of the corresponding views (v_) in my database. I converted a previous script from using a cursor to improve running speed. Is there ...
1
vote
1answer
34 views
Optimize subquery that searches xml
The following query lists all domains from multiple companies in which a value occurs in an XML set. This is filtered to the most recent occurrence within a provided date range.
On large databases ...
3
votes
1answer
45 views
Acquiring earnings information
The first part of the query grabs Policy's Premiums, Effective and ...
10
votes
2answers
371 views
Who are these lurkers?
Last night in The 2nd Monitor there was a discussion about postless users - I wanted to see how many there are, whether they're one-timers that showed up once, registered and never came back, and ...
1
vote
1answer
29 views
Read uncommited rows and lock row on update
I have 5 (or more) instances of an application running on same or different machines. Each of these application will pass a comma separated string of UserIDs and the app should pick up a unique unused ...
18
votes
1answer
2k views
Where are my downvotes?
I've been thinking my posts aren't that great, and so I've been wanting to improve them.
One sure fire way to do this is to look at all of my downvoted posts.
Not knowing if Stack Exchange allows you ...
4
votes
0answers
27 views
Find unused stored procedures/functions in SQL 2012
I was given the task of finding those unused procs/functions in our SQL 2012 database.
Initially my plan was to use the table sys.dm_exec_procedure_stats. I could set up a job to check the table ...
0
votes
0answers
16 views
Rescheduling long production tasks to the next day
The code below is used for a table with production tasks. It moves tasks to the next day where the sum of hours required for actions belonging to the same production order is more than 8.
The code ...
1
vote
1answer
29 views
Stored procedure for product search with dynamic filters
Below stored procedure implemented for filtering and sorting operation.But number of table column increases below query becomes complex to read/maintain. This is good for small tables.
I want to ...
2
votes
0answers
38 views
Generating email with BCP
This code iterates through the table @Table1 to get the Purchaser, PurchaserID, ...
7
votes
3answers
418 views
Update 12 records in a database table in less than 13 queries
I've got a database table in my application called "periods". The structure of the table is:
Year (Number)
Month (Number)
Period (Text)
The Year column stores years (Say, 2016), Month stores months (...