I’m writing a C# class library in which one of the features is the ability to create an empty data table that matches the schema of any existing table. For example, this: The above code works, but it has a glaring security vulnerability: SQL injection. My first instinct is to parameterize the query like…
Tag: sql-server
Decreasing Cumulative Sum That When Equal to or Less Than Zero Change Another Value
Below we have two tables, one of purchase orders and the other of sales orders. What I’m trying to do is assign each sales order to a purchase order, with free stock. Which I can do with the following query: Table 1 – Incoming Purchase Orders: number item shipDate qty usedQty freeQty 12 Toy 2021-1…
Find names based on max number dynamically in SQL
I have a data set like this: Table A: What’s the maximum number of Place on which a Partner has spent money and who are these Partners? Display only the Partners reaching this max number I tried this: But I feel like its not the right logic. What am I missing? Answer There’s really no need for you…
Get date today, if no data – get data from yesterday (dynamic)
I have such an issue. I have a report that is built around today’s date. We see data only from today. On Hand units from today. However, we have many import issues and sometimes import fails and there is no data in this report. I was wondering if there may be a way to make it dynamic like to use
How to sum up and other calculations by the other columns in the same table?
id amount 1 20 2 20 3 20 4 30 5 100 6 20 7 30 8 100 I would like to create three more columns which will be calculated by the amount column. Expected output: id amount openamt debamt credamt closeamt 1 20 340 140 200 400 2 20 340 140 200 400 3 20 340 140 200 400
How to query cumulative sum period by month
I have an issue with table source like this: Available month in table is only as is from column month, no April, May, June etc. I need to create a view to create “Year to Date” table as shown here: I need to view all months from Year 2020 January – December with value based on table source. …
Search by best values for every minute
The problem I’m having is that I have a table of data that has a new row added every second (imagine the structure {id, timestamp(datetime), value}). I would like to do a single query for MSSQL to go through the table and output only the number of objects that have the top 2 values asc for each minute (…
T-SQL Logic (Where exists one column but not the other)
I need a bit of logical help in writing a query. Here is the set up. I need a query that returns all records from main_table where the id of main_table matches the id of sub_table, but seq of main_table does not match seq of sub_table. Expected result is id seq A1 3 A2 2 A2 3 My attempt What
Divide the sum of grades using pivot operator
Output: Expected output: Answer Each student may have two marks in each subject? Make sure of that first of all Try to use the average AVG(Grades)
Does Adding Indexes speed up String Wildcard % searches?
We are conducting a wildcard search on a database table with column string. Does creating a non-clustered index on columns help with wildcard searches? Will this improve performance? Proposed Index: for this query Currently using Microsoft SQL Server 2019. Answer Creating a normal index will not help(*), but …