Questions tagged [t-sql]

Transact-SQL (TSQL) is the extended SQL dialect used in Microsoft SQL Server and Sybase. Please also tag with either [sql-server] or [sybase].

Filter by
Sorted by
Tagged with
5
votes
2answers
73 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, ...
3
votes
2answers
153 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 ...
3
votes
1answer
55 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. ...
1
vote
1answer
22 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 ...
3
votes
1answer
63 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 ...
11
votes
1answer
183 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
votes
2answers
196 views

Build WHERE clause for search conditions [closed]

Here is a set of classes that are used to build where clause for SQL Server and Oracle for different field types e.g. text, <...
1
vote
2answers
65 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
0answers
33 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
1answer
78 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. ...
0
votes
0answers
47 views

Parties and Messages in SQL e-Commerce db design

I'm posting a follow-up question based on my first question. My goal is to, as an exercise for myself, create a complex (in my opinion) e-commerce application. The explanation of the application can ...
3
votes
1answer
32 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 ...
4
votes
1answer
181 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. ...
4
votes
1answer
225 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 ...
1
vote
1answer
54 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 ...
1
vote
1answer
34 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 (...
1
vote
1answer
78 views

Initialize various commands for the SqlDataAdapter

This is my code for my SqlDataAdapter which is similar to the code example given in the docs: ...
3
votes
2answers
103 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 ...
2
votes
1answer
50 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 ...
6
votes
1answer
105 views

Retrieving the IDs of entries to be deleted from a table

Business logic It would take us too far to explain the whole business case behind this code, so I'll try to be as succinct as possible. There are Persons, and ...
4
votes
1answer
43 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 ...
2
votes
1answer
59 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 ...
3
votes
1answer
133 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 ...
0
votes
1answer
49 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: ...
3
votes
1answer
51 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 ...
3
votes
1answer
290 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 ...
1
vote
1answer
92 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 ...
1
vote
0answers
165 views

Stored procedure that adds row-level auditing to a table

I'm looking for any suggestions on how to improve my T-SQL for this procedure. It works okay, but I'd like to get advice to improve it from more experienced SQL developers. This procedure adds 4 ...
1
vote
1answer
100 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 ...
4
votes
1answer
101 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/...
6
votes
2answers
102 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 ...
0
votes
1answer
53 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 ...
7
votes
0answers
137 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
0answers
44 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 ...
4
votes
2answers
94 views

Simple locale loader

I have a class that loads locales from a database. I'm not sure if locale is the right word. It's basically loading texts for a game, generic sayings like alerts and error messages. I'm also not sure ...
3
votes
1answer
70 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! ...
0
votes
2answers
379 views

Merge table stored procedure

I wrote a stored procedure that will do a merge of two tables: ...
3
votes
2answers
83 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: ...
4
votes
1answer
31 views

Parse numbers from text based on relative position to certain substrings

The input is a product code. I want to pull out the "percentage" portion of that code. For example, if the code contains the substring BIO, I want all the numbers that follow it until I hit another ...
2
votes
1answer
59 views

SQL Query to check if 100+ (or Dynamic) columns in table is null

Let assume a table has more than 100 of columns & 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 ...
0
votes
1answer
62 views

Query to show each sites stock transfer history between all other sites

The below query is used to return a dataset for a SSRS report. It works fine and is quick enough but I am looking for any improvements that could be made. The client has asked to see all transfers ...
1
vote
2answers
55 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 ~...
9
votes
1answer
119 views

The seasoned Zombie sniper: a query to find unanswered questions with specific tags

The query (on SEDE) This query builds upon the previous and includes the suggestions by rofl as well as the now mentioned activity index. ...
11
votes
1answer
92 views

The sniper: a Zombie searcher for specific tags

Sometimes, it's not enough to know what the easy prey is. You also need to know whether you will be able to hunt it down. What good is an almost turned haskell zombie if you cannot read the functional ...
14
votes
1answer
1k views

A wounded Zombie finder, or: how to find the almost dead undead?

The Zombies are on their way to eat our statistics. Fortunately, we have a way to get rid of them: our votes. However, we need to find them first. The /unanswered section is too large and contains ...
2
votes
3answers
75 views

search for text across multiple tables

I've got the following query. ...
0
votes
1answer
317 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], ...
2
votes
2answers
112 views

T-SQL simplify repeated query unions for each day of the week

I've created the T-SQL code below to get the next nearest datetime value during the next 7 days. The ScheduleDetail table schema is: ...
1
vote
1answer
48 views

Update Trigger (Comparing Descriptive string), as well as checking for valid updates

I have an audit table EpisodesHistory. One of the fields that gets copied is eDescription. I expect ...
1
vote
1answer
211 views

Ontario Health Card mod 10 validation

An example of the business rules are here. Does this code implement those rules correctly? Note that you will have to derive the rules from the example. ...