tsql

Easy SQL query But getting confused

I am facing a problem for writing an sql query which should be easy I guess but I am not able to concentrate on that query. Hence taking some help from this website. Problem: I have an table "Request" which has following columns - CreatedOn: When I create new request, the CreatedOn is filled up with current datetime LastModifiedOn: Wh...

How to update column with incremental value using tsql?

I wanna update first and last name of one table with incremental value. For example:- ID FirstName 1 Demo_01 2. Demo_02 3. Demo_03 4. And so on.... This table has 1000s of records. But for demo purposes we do not want to share real Name. So Please help how to update First Name with "Demo_ + Incremental value by one? ...

T-SQL query help - select unique grouping?

My table has 3 columns: RecordId Value InsertDate Each RecordId has multiple entries in the table. In fact the table gets updated several times a day. How do I write a t-sql query to select all the latest rows (based on InsertDate) for each unique record? My t-sql skills are non-existent. Thanks in advance ...

TSQL, counting pairs of values in a table.

Given a table in the format of ID Forename Surname 1 John Doe 2 Jane Doe 3 Bob Smith 4 John Doe How would you go about getting the output Forename Surname Count John Doe 2 Jane Doe 1 Bob Smith 1 For a single column I would just use count, but am unsure ...

SSAS - Where do you start using it?

Hi Folks, I'm in the middle of designing a SSAS db. I get the theory and the use of this stuff. Here's the thing, I've got a logging database that logs interesting order statuses which I would like to measure time to complete. I've got these tables (not implemented), to measure status times time_dimension user_dimension status_dimens...

SQL: Need to return specific dates for sku's

Here is what i have: UPDATE webdev.tempdb.dbo.MediaResource SET Date_Min_Enabled = PRAH.DateCreated FROM FeedBack.dbo.Part_Resource_Associations_History as PRAH JOIN WebDev.DataWareHouse.dbo.Inventory as I on I.SKU = PRAH.PartNumber JOIN FeedBack.dbo.Part_Resource_Associations as PRA on PRA.PartNumber = I.SKU This only return...

Is there a better way to write this query?

So let's say I'm trying to get a list of all my users who belong to a certain user group. Easy: SELECT * FROM users, usergroups WHERE usergroups.user_group_id = 1 NOW, let's say I want to get a list of all users who belong to a certain user group AND who have an email that ends in .edu. SELECT * FROM users, usergroups WHERE use...

SQL - field concatenation, based on variable

Hi, I have a need to build a string from Last Name, First Name, Middle Initial according to the following rules: If the Last Name is unique, just return the Last Name If the Last Name isn't unique, but the first letter of the First Name is unique, return Last Name + first letter of First Name If the Last Name and first letter of the F...

finding the start day (Monday) of the current week.

Looking for a SQL query/queries that would determine the start day (Monday) of the current week. Example: If today is -> then the start of the week is Sat Oct 09, 2010 -> Start of the week is Monday Oct 04, 2010 Sun Oct 10, 2010 -> Start of the week is Monday Oct 04, 2010 Mon Oct 11, 2010 -> Start of the week is Monday Oct 11, 2010 Tu...

tsql : is it possible to do nested case statements in a select?

how do i incorporate a nested if statement in a select clause of a sql query? I know to use case when condition then X else y end but how do you do a nested one in the same fashion for each record in a record set. if x.boy is not null then x.boy else if x.girl is not null then x.girl else if x.dog is not null then x.dog e...

Creating a SQL Server trigger to transition from a natural key to a surrogate key

Backstory At work where we're planning on deprecating a Natural Key column in one of our primary tables. The project consists of 100+ applications that link to this table/column; 400+ stored procedures that reference this column directly; and a vast array of common tables between these applications that also reference this column. The ...

Update all SQL NULL values in multiple columns using Column level WHERE clause?

We have a database with a bunch of wide tables (40-80 columns each) and just found a bug that introduced NULL values into about 500 of the records. The NULL values can appear in any of the columns (all are integer columns, see image below) but these NULL values are causing issues with one of our reporting systems that cannot be changed e...

Can ObjectDataSource use table-valued parameters

If an ASP.NET web page uses an ObjectDataSource, can you configure it to use a stored procedure that uses table-value parameters? User-defined type: CREATE TYPE [dbo].[integer_list_tbltype] AS TABLE ( [n] [int] NOT NULL, PRIMARY KEY CLUSTERED ) Stored procedure: CREATE PROCEDURE [dbo].[GeneralReport] @intList integer_list_tblty...

Help with duplicates in output

I'm in need of some help with getting my duplicates from showing more than once in my output. SELECT accountNumber AS 'Member Number', OD.orderdetails AS 'iNum', FirstName AS 'First Name', LastName AS 'Last Name', HGP.email AS 'Email', points AS 'Points -->', '$' + CONVERT(varchar(50),(CONVERT(int,Points) * .1)) AS...

DATEPART as a parameter

I'm guessing this is not possible since the engine doesn't like it, but is there a way (barring dynamic SQL) to pass in a DATEPART as a parameter to a procedure? ...

Stored procedure hangs seemingly without explanation

Hi, we have a stored procedure that ran fine until 10 minutes ago and then it just hangs after you call it. Observations: Copying the code into a query window yields the query result in 1 second SP takes > 2.5 minutes until I cancel it Activity Monitor shows it's not being blocked by anything, it's just doing a SELECT. Running sp_rec...

T-Sql - Pivot Error

I have the following code in a T-Sql query, I am getting the following error message and I am not really sure what is causing the error. I am writing the Pivot statement to be dynamic b/c I do not know the columns that will be returned. Error Message: Msg 8156, Level 16, State 1, Line 9 The column 'Title - Endorsement Fee / END8' was s...

What is the most efficient way in T-SQL to compare answer strings to answer keys for scoring an exam

These exams typically have about 120 questions. Currently, they strings are compared to the keys and a value of 1 or 0 assigned. When complete, total the 1's for a raw score. Are there any T-SQL functions like intersect or diff or something all together different that would handle this process as quickly as possible for 100,000 examinee...

TSQL - SET vs. SELECT when assigning variables?

What are the differences between SET vs. SELECT statement when assigning variables in T-SQL? ...

How do I count how many of these columns have a value in SQL?

Assume the following table: ID | Item1 | Item2 | Item3 | Item4 | Item5 ------------------------------------------ A | NULL | NULL | YES | YES | NULL B | NULL | NULL | NULL | YES | NULL C | NULL | NULL | NULL | NULL | NULL I want to return the following data set: ID | Count ------------ A | 2 B | 1 C | 0 Bas...