I have a table of 3 columns: AuthorID (id can be repeated) JournalName (name can be repeated) AuthorScore I need a query that gets JournalName and the count of all authors having their maximum score in this journal. Thank you in advance. ...
In a project I'm working on, there is a table of units and then there are several tables that pertain to the units' locations at a given point in time as well as other factors for a given point in time. For example, Unit1 might be at LocationA on 1/1/2009 and have a second location entry that has Unit1 at LocationB on 3/1/2010. Likewis...
Is there some kind of mechanism in SQL Server to allow Enumerated type like functionality? For example, if I have a column Called "UpdateStatus" it usually gets setup with single letter values like so: D X U I This could equate to a lot of things. That leads to confusion. The alternative is to have it be a string column like this:...
In the above FldID 52 = Description and FldID 54 = HistoryDetail For any given date the output should be the last entry for that date. Also the columns need to be become rows. User will provide 2 dates. Say March 2, 2010 and March 3, 2010. So output in above case should be Since Rev 6 does not have an entry for FldID 52...
I would like to take a field and replace all characters that are not between a-z and A-Z with "". Is this possible, and if so, how? ...
I'm writing a script that is supposed to run around a bunch of servers and select a bunch of data out of them, including the local server. The SQL needed to SELECT the data I need is pretty complicated, so I'm writing sort of an ad-hoc view, and using an OPENQUERY statement to get the data, so ultimately I end up looping over a statement...
I've written a query that groups the number of rows per hour, based on a given date range. SELECT CONVERT(VARCHAR(8),TransactionTime,101) + ' ' + CONVERT(VARCHAR(2),TransactionTime,108) as TDate, COUNT(TransactionID) AS TotalHourlyTransactions FROM MyTransactions WITH (NOLOCK) WHERE TransactionTime BETWEEN CAST(@StartDate A...
I've got an implementation for this that uses a super hairy recursive CTE that is really hard to follow/maintain. I was hoping that one of the brains on SO could come up with some more straightforward tSQL approach code to accomplish the following: Table Documents DocID SortOrder PageCount StartPgNum EndPgNum 5 1 ...
Hi!, I need to examine 30 million records (one month worth) of daily ticket validations (Unix datetime) and divy them up into 24 one-hour periods for 211 stations. First I created a view which selects the month I’m looking for (and the equipment type) then creates a Windows Datetime value for each Unix datetime. SELECT TOP (100) PERCE...
Hi all I am trying to pass array parameter to SQL commnd in C# like below, but it does not work. Does anyone meet it before? string sqlCommand = "SELECT * from TableA WHERE Age IN (@Age)"; SqlConnection sqlCon = new SqlConnection(connectString); SqlCommand sqlComm = new SqlCommand(); sqlComm.Connection = sqlCon; sqlComm.CommandType = ...
So I just saw a weird behavior In one script there is something like: begin transaction begin try stuff stuff stuff print 'commit' commit transaction end try begin catch print 'rollback' print error_message() rollback transaction end catch thing is when this script i...
I need to be able to query a SharePoint database for survey results. The type of data I'm having problems with is a "Rating Scale" value. So the data in each table column represents a whole group of sub-questions and their answers. So the following is an example of what is found in ONE column: 1. Our function has defined how Availa...
Hi All, I want to run a sql query on a specific schedule (weekly) and need to capture the output in an excel file as well. Due to business restrictions we cannot use SQL server jobs and we cannot install Business Intelligence tools too. Regards, RP ...
I have some code like this that I use to do a BULK INSERT of a data file into a table, where the data file and table name are variables: DECLARE @sql AS NVARCHAR(1000) SET @sql = 'BULK INSERT ' + @tableName + ' FROM ''' + @filename + ''' WITH (CODEPAGE=''ACP'', FIELDTERMINATOR=''|'')' EXEC (@sql) The works fine for standard tables, b...
I'm currently using a select statement with one column as DATEPART(hh, CallTime) AS Hour and then doing: GROUP BY DATEPART(hh, CallTime) ORDER BY Hour This displays the hours starting at midnight and going through midnight - how would I go about having this go from noon to noon? Thanks! ...
TABLE_A Rev ChangedBy ----------------------------- 1 A 2 B 3 C TABLE_B Rev Words ID ---------------------------- 1 description_1 52 1 history_1 54 2 description_2 52 3 history_2 54 Words column datatype is ntext. TABLE_C ID Name ----------------------------- 52 Description 54 Hi...
Basically I have a table like this: CREATE TABLE Person( PersonID int IDENTITY(1,1) NOT NULL, FirstName nvarchar(512) NOT NULL, LastName nvarchar(512) NULL ) And I need to find the top n results based on a user-query like this: "Joh Smi" The following query returns the results I need (I think). Just not in the relevant ...
I have a table as below Name Priority Date ------------------------- A 2 d1 B 3 d2 How to write a query to achieve the below output ColumnNames d1 d2 -------------------------- Name A B Priority 2 3 Thanks ...
In the following query the start/finish columns are datetime fields. How should I modify this query to get two more columns, one with the min date and one with the max date (of all the 6 datetime fields and all the rows) repeated in each row. Alternatively how could I create a new query returning only these 2 (min/max) dates, for the...
Hello all, First of all, I don't want to use a "join" because that will make my query longer and difficult to read. So what I need to do must be withing the same SELECT statement. My columns in myTable are A, B , C , D , time, ID and H H columnd tells if a record is 'Open' or 'Close', here how my query looks like. SELECT A, B, C, ...