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

learn more… | top users | synonyms

3
votes
1answer
33 views

Basic T-SQL to compare two same length strings

I've come up with the following T-SQL to compare similar same length strings. Example usage would be: OCR returns a value which is expected to be in the database. PATINDEX is used to check the ...
3
votes
1answer
52 views

Query for combining two SELECTs to get active users and 1 inactive user

What this query does: Gets multiple users with the first select in the union; those are the active users. But when a user is ...
2
votes
1answer
20 views

Optimizing sales report stored procedure

I need to optimize the following stored proc. Please let me know of any techniques or modifications that I can make to optimize this piece of code. The procedure is for a report that needs to run ...
4
votes
3answers
49 views

Retrieve Father and Child structure in a Database Table

I have a table with this schema: CREATE TABLE [td].[MyTable] ( [ID] [int] NOT NULL ,[FatherID] [int] NULL ) (Note: I have excluded all the columns not ...
4
votes
1answer
67 views

Unduplicated counts over various dimensions

I'm working with some data on college students, which has a unique key of SSN, Campus, ...
5
votes
1answer
46 views

SQL query with dynamic unpivot+pivot for cross product

In this Data Explorer query I am trying to do the following: For each tag: Compute sum of answer scores in this tag ("S") Compute count of answers in this tag ("A") For each tag class (Bronze, ...
-2
votes
1answer
27 views

In T-SQL when creating a table with an identity column what data type should be used? [closed]

In T-SQL, when creating a table with an identity column, what data type should be used? Should the data type be numeric, bigint or a different type altogether? For example: ...
19
votes
6answers
2k views

Average time to first answer

Just trying out my T-SQL foo. Wrote a script to get the average time (in seconds) to first answer for a specified language. ...
4
votes
2answers
120 views

Delete from multiple tables using temp table variable

I'm previewing the data I'm about to delete, but this SQL looks a little redundant. Is there a better way to write this? ...
6
votes
3answers
83 views

SQL Stored Procedure Get Distinct and Update

The idea is to show how many times a user (by EmployeeID) is in the TblTableList and then update the ...
6
votes
2answers
95 views

Put it in a bucket

You're running a jeans company. Your system is collecting detailed orders' data, but it's a bit archaic and it's storing the number of units ordered per size in a delimited string with 20 "fields", ...
6
votes
3answers
106 views

Sorting three Decimal values

I have been given three values. @lLength, @lWidth, @lHeight. I need to take these ...
5
votes
1answer
79 views

Dynamically filtering, sorting, and paging using a stored procedure

Is there a better way (I'm pretty sure there is) to create a stored procedure to do dynamic paging, filtering and sorting than the following code? ...
5
votes
2answers
62 views

Better way of calculating a time in a time zone from multiple dates in SQL?

I'm trying to calculate what time a certain time in a time zone is today, so I can schedule something to happen at that time in that time zone. I've got a table with what I have termed the Nominal ...
6
votes
4answers
112 views

SEDE-Query for Rep-Trivia

The code should be relatively self-explanatory. I was wondering how much rep the Top Voters on sites did "generate" on a site, in comparison to how much rep they "own". Therefore I created a little ...
6
votes
1answer
112 views

Count and group rows for six months prior

I've written the following query for SQL server: ...
4
votes
1answer
44 views

SEDE query to find users with exactly one post in a particular tag

I'm doing some research for a meta post on Gardening.SE (where I'm a pro tempore moderator). I'm using the Stack Exchange Data Explorer (SEDE) to find information about first time users who post ...
6
votes
2answers
92 views

Inefficient query actually more efficient?

I have two queries; one runs significantly faster than the other, but the faster one is the one that I would have bet money on to be less efficient. These are both being executed through a front end ...
6
votes
1answer
50 views

Aggregating Conditional Sums

I have recently forked/rewritten a SEDE query (here) that aims at figuring out where a site stands in terms of avid users and distribution of reputation scores, compared to a specific target (number ...
7
votes
3answers
145 views

Return the top 5 of each kind of record

I have this currently working code, but it's extremely slow. I'm talking almost an hour to execute, if it executes at all (our server isn't all that great to start with). Is there a better way to ...
14
votes
1answer
192 views

Calculating Lost Reputation

A discussion arose not long ago on the 2nd Monitor about how much reputation has been lost due to the reputation caps. There are a number of queries on the SEDE which try to address this: A Users ...
11
votes
3answers
160 views

SEDE Top Sponsors

I wanted to see the site's top sponsors - users that have paid bounties on questions that they didn't own. I started off with a bounty-related existing query, selected the details into a subquery, ...
4
votes
3answers
84 views

Remove duplication in SELECT statement

Say I have the following SQL: SELECT amount, amount*.1, (amount*1)+3, ((amount*1)+3)/2, (((amount*1)+3)/2)+37 FROM table Instead of ...
3
votes
1answer
109 views

Query too slow - Optimization

I am having an issue with the following query returning results a bit too slow and I suspect I am missing something basic. My initial guess is the 'CASE' statement is taking too long to process its ...
3
votes
2answers
126 views

SQL query clustered with repeated function calls

I have the following SQL query that computes for every date the week it falls in (a week begins on Sunday and ends on Saturday): ...
1
vote
2answers
125 views

Review single step approach, rather than multiple temp tables

Overall the intent is to consolidate individual sales transactions to total debits/credits summing up the totals by year and month--retaining a running balance from start to end. This code uses ...
1
vote
2answers
57 views

Store Procedure Timing out sometimes?

Can someone please review my this Store Procedure which is timing out? ...
4
votes
2answers
577 views

Selecting the number of working days minus weekend days and UK bank holidays

I am fairly new to coding TSQL script and am looking for some second view on my script. The goal here is to to pull in some data and amongst that data to show a field with a counting the number of ...
1
vote
1answer
249 views

DRY cursors: preventing T-SQL's FETCH statement from being repeated

Please assume I've exhaustively tried to come up with a set-based solution to my T-SQL problem, and that I need to use a cursor. The following is the typical1 boilerplate for a T-SQL cursor: ...
3
votes
1answer
128 views

SQL Server 'Execute As'/Revert pattern in a 'Try/Catch' Block

I wish to ensure I am using the "best" pattern when using an Execute As/Revert from within a Try/Catch block on SQL Server 2012. The below code "seems" to behave correctly... Am I missing anything or ...
1
vote
2answers
111 views

How can I make this WHERE clause more readable?

Without changing the semantics and performance, how can I make this where clause more readable? ...
2
votes
1answer
120 views

How can I improve the following stored procedure?

I have created the following stored procedure which duplicates a record in a table and also all its related records in other tables however since I am a newbie to SQL I would appreciate it if someone ...
3
votes
1answer
3k views

Returning Key Values from Stored Procedures

I wonder which is better practice when I need to return the primary key value of a newly inserted record from a SQL stored procedure. Consider the following implementations: As Return Value ...
-1
votes
1answer
295 views

Using xp_cmdshell

Am I doing it fine?? Its only for setup not executed repeatedly ...
1
vote
0answers
145 views

T-SQL BFS center node of undirected graph [closed]

I am trying to answer this question by using a breadth-first search from each node of the graph represented by the tables mapSolarSystems and ...
0
votes
1answer
580 views

Daily, Weekly, Monthly Individual Tech time in task Repoert

I'm trying to create daily, monthly and weekly SQL Query report to our services time we spent int task and total billing time just want to see if I'm on right track ...
2
votes
1answer
658 views

Sql query to obtain totals and subtotals

I'm interested in knowing if there's a better/cleaner/more efficient way, to obtain totals and subtotals within a query, than my solution below. The query works fine but I'm just intrigued to know if ...
3
votes
1answer
197 views

Is it necessary to replace this cursor in SQL Server 2005?

The Problem I have a cursor that I am trying to replace (perhaps unnecessarily) in an attempt to clean up a stored procedure. Essentially what it is doing is counting each note for each member in a ...
6
votes
1answer
2k views

Splitting an address into fields in SQL

I have the following code to split an address string in T-SQL. Excepting the unit number, which is already in its own field, it would affect too much of the application to split the address into ...
3
votes
2answers
193 views

Any way to speed up this UPDATE?

SQL: ...
1
vote
1answer
199 views

Generic PHP function for making a struct out of multiple resultsets from a stored procedure call

The stored procedures must return their first resultset in the form: ( [index] int, [id] varchar(50) ) where ...
0
votes
1answer
3k views

Data Structure for Categories and SubCategories

I have this relationship: Many Objects to One Category One Category to Many SubCategories I am looking for the best way to store this in my Relational Database? Here were some of the ways I was ...
3
votes
1answer
137 views

Getting rows from several tables where one table doesn't include the rows from the next tables

I have some entity that is spread across three tables. I have to get in a single result the following values: All the values that are in the first table but not on the second nor the third. All ...
6
votes
1answer
3k views

Conditional Create: must be the only statement in the batch

I only want to create this SQL function if the dependent Assembly exists. I can do it using dynamic SQL, but it seems messy and I lose syntax checking (in management studio). This function's ...
1
vote
1answer
128 views

List of students with their classname in current year

I have 3 tables: students and classes and years. In a forth table called ...
2
votes
1answer
122 views

Refactor my simple SQL Statement

I have the following SQL statement that I think could be improved in areas (I believe there may be a way to use where over having, but I'm not sure how and I'm sure there's a way to reference the last ...
4
votes
1answer
153 views

output parameter and exists condition

I want to return title of a record if exists or the word 'invalid' if not. ...
1
vote
1answer
2k views

Query to get number of related records in 2 child tables

I have a category table (brands) and 2 other tables (Pens and Pencils) that have parent-child relation with this table through a ...
2
votes
1answer
87 views

Critique my SQL

Table Structure ApprovalOrder int EntityCode varchar CostCentre varchar DelegationCode varchar ProjectCode varchar RoleGroup varchar Position varchar ...
3
votes
4answers
375 views

Shifting records in SQL Database while sorting with algorithm

I have a table in a SQL Server database, which holds information of some images, and the relevant gallery of them. The columns are like: ImageId, ...