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
4
votes
2answers
1k views

Creating Sequential IDs

I'm using SQL Server 2014 with Visual Studio 2013. When I add a new record, identity column (seed 1, increment 1) "ID" increases by 1, and record added to the database. My table consist of two rows: ...
4
votes
1answer
46 views

Checking that data has been created in multiple tables

I have 4 tables that I want to check for data. When there are Rows in all the tables, I want to print a message for success. When there aren't any rows in any table I print an error (when 1 or more ...
3
votes
1answer
74 views

Perform SELECT INTO Statement and add Primary Key afterwards

How can I imrpove the performance of this T-SQL statement? ...
8
votes
1answer
2k views

Generate random records for table in SQL

I'm doing a project in ms sql and I have to fill table with random records. Here is a definition of table: ...
0
votes
1answer
550 views

EF Code First Seed Method for States/Countries Part I

I am working to complete this even further, but here's what I've got thus far... (See my other post for the definition.) ...
2
votes
2answers
3k views

Haversine formula in SQL

This is an implementation of the Haversine formula in Microsoft Transact SQL. How can I simplify the function? ...
3
votes
1answer
88 views

Is the usage of SQL Cursor appropriate here?

I'm looking for following kind of feedback: Firstly, is the use of a cursor an overkill here? Is there a simpler way to do what I'm doing in SQL? Could the script go terribly wrong? I have limited ...
8
votes
2answers
193 views

What are my highest activity streaks?

I have written the following query to figure out activity streaks on a per-user basis. I find it... Ugly... And would love to improve it! Limitations Those are explained as commented text at the ...
24
votes
6answers
679 views

Winterbash 2014 “Red Shirt” Estimation

Some fellow code reviewers (hi @Janos!) have been inquiring about a SEDE query to allow to check progress of the Red Shirt "hat" progression. Try it here! Background Red Shirt cast 5 ...
32
votes
7answers
260k views

Select first value if exists, otherwise select default value

Given an instance of SQL Server, imagine there's a table named Configuration, which has three columns: ID, ...
14
votes
2answers
107 views

Bling Accounting - Badge summaries

A question was raised in chat (hi @malachi) about how you can count the number of badges of a given metal (bronze, silver, gold). This can be hard to do because the medal details are not stored in the ...
3
votes
2answers
3k views

Running validation procedures on a table

I am working on a project that has to parse through a text file and perform validations on certain fields in the file. Some of the validations are basic (i.e. data types), and other's are a little ...
3
votes
3answers
108 views

Cluster date time values

I came up with this TSQL for SQL Server 2008: ...
12
votes
4answers
10k views

LW, MTD, STD, YTD Sales… all in line and looking pretty

That's Last Week, Month-to-Date, Season-to-Date, and Year-to-Date sales. I have a table-valued function for each one, that takes a date and returns all CalendarDate...
3
votes
2answers
48 views

Extract last record inserted for each key identifier

I have a table with this schema ID, int primary key PathKey, string not null InsertDate, datetime not null Value, int not null In the table there are a medium-...
6
votes
1answer
61 views

Saving and updating records

I have made two SQL statements as I was unable to figure out how to solve my problem. One is called Product_GetID and the other ...
7
votes
2answers
164 views

Follow up to Top Active Answerers on Stack Exchange site

This is a follow up to Top Active Answerers on a Stack Exchange site Actual SEDE Query --> Top Active Answerers on site Do I have a good understanding of the RANK ...
3
votes
2answers
294 views

Top Active Answerers on a Stack Exchange site

I created this query back in January to find the top active answerers on Code Review and thought that it might be a good idea to get other opinions on my SQL coding using a database that we can all ...
3
votes
3answers
1k 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 value'...
3
votes
1answer
187 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 ...
3
votes
1answer
64 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
1answer
218 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
2answers
174 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? ...
7
votes
3answers
2k 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 ...
7
votes
3answers
1k views

Sorting three Decimal values

I have been given three values: @lLength, @lWidth, @lHeight. I need to take these ...
6
votes
2answers
206 views

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 ...
7
votes
1answer
864 views

Count and group rows for six months prior

I've written the following query for SQL server: ...
6
votes
1answer
94 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 ...
7
votes
3answers
195 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 C#...
7
votes
1answer
59 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 ...
22
votes
1answer
395 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 ...
13
votes
3answers
200 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, ...
2
votes
1answer
178 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
219 views

Computing the week for a certain date

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
2k views

Maintaining a total balance of debit/credit sales transactions

The overall 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
83 views

Store procedure timing out sometimes

Can someone please find out why this store procedure is timing out? ...
5
votes
2answers
3k 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 ...
4
votes
1answer
197 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 ...
2
votes
2answers
136 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
139 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 ...
-1
votes
1answer
653 views

Using xp_cmdshell

Am I doing it fine?? Its only for setup not executed repeatedly ...
0
votes
1answer
2k 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
2answers
3k views

SQL query to obtain totals and subtotals

The query works fine but I'm just intrigued to know if there is a cleaner solution. The data are results from a survey and I'm obtaining the counts per question within a zone and an overall zone count....
3
votes
1answer
237 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 ...
7
votes
1answer
8k 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 ...
14
votes
1answer
9k 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 ...
2
votes
1answer
145 views

Statistics updating SQL Query

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
193 views

Returning the title of a record

I want to return the title of a record if one exists or the word 'invalid' if not: ...
5
votes
1answer
106 views

Adding a new record to the beginning of the approval chain from the delegation chain

Table Structure ApprovalOrder int EntityCode varchar CostCentre varchar DelegationCode varchar ProjectCode varchar RoleGroup varchar Position varchar ...
3
votes
4answers
2k 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, ...