All Questions
26 questions
1
vote
1
answer
83
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
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
7
votes
0
answers
164
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 ...
3
votes
2
answers
101
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
3
votes
1
answer
141
views
SQL query to check if 100+ (or dynamic) columns in table are null
Let’s assume a table has more than 100 columns and I want to display all the rows when all of its columns (Col1, Col2, …, ColN) are nulls except for the column(s) I mentioned (PK).
This is just a ...
- 133
1
vote
2
answers
75
views
Displaying a single record based on DocID
I have a table with ~900,000 records. I need to display a single record based on DocID using this query. The record is the latest record that got modified by another program.
This piece of query run ~...
- 143
2
votes
3
answers
100
views
0
votes
1
answer
1k
views
T-SQL query outer apply with nested subqueries
I have the following 3 tables, and I don't have the option to modify them.
[Logs] table with [log_type], ...
- 177
-1
votes
3
answers
95
views
Insert to table from a temporary table [closed]
I created a procedure involving dynamically collecting from various projects (databases) some records into a temporary table and from that temporary table I am inserting into a table. With a ...
- 157
1
vote
1
answer
62
views
Remove table variable from query
I am running this query, and to get the calculation that I need, I use a table value parameter to house some of the data. Scanning roughly 50K rows to create the table value parameter then performing ...
2
votes
1
answer
115
views
Optimising performance of long running a sql query
The code below takes 5-6 hours for a years worth of data (850,000 records). It takes much less time when running a single month.
To start with is there anything glaringly obvious within this script ...
- 149
3
votes
1
answer
128
views
Storing user permissions to hierarchically structured data in SQL Server
I'm developing a system that supports creating accounts and multiple users within every account that can each access a certain subset of data of their "parent" account.
The data is stored in a SQL ...
- 275
4
votes
2
answers
67
views
Acquiring earnings information
The first part of the query grabs Policy's Premiums, Effective and ...
- 41
1
vote
1
answer
95
views
Correlated subquery slow
I have a table that looks like this
Directory nvarchar(max)
Extension nvarchar(10)
Length bigint
I also have another table that provides the type of file (lookup table)
extension nvarchar(10)
...
- 189
2
votes
1
answer
73
views
Demographics information for EDI report
I am going through some of my companies EDI reports to see if I can make them faster. The query below runs in less than one second in our AWS RDS environment, but it takes ~55 seconds on a blade in ...
- 207