In SQL 2005 I am grouping on all available posts by regional office, listed by region,office,vacancyID. I then display a total per office on how many people started in a particular vacancyID by doing a Count(VacancyStartID). In the same group row with the Count(VacancyStartID) I need to display SUM(VacancyID). However at present this doe...
Hi,
I want to insert a row number in a records like counting rows in a specific number of range. example output:
RowNumber ID Name
1 20 a
2 21 b
3 22 c
1 23 d
2 24 e
3 25 f
1 26 g
2 27 h
3 28 i
1 29 j
2 30 k
I rather ...
I'm trying to perform calculations in T-SQL, but am having some problems with it.
Here is what I'm trying to do:
DECLARE @CNT money
SELECT @CNT = 0
Select Amount,
case
when Service like 'pay_in' then SET @CNT = @CNT + Amount
when Service like 'pay_out' then SET @CNT= @CNT - Amount
end
from Pa...
I have an on update trigger in SQL Server 2008. I only need to perform the trigger action if certain columns have been modified. Thus I'd like to check what has changed.
T-SQL offers an "if update( columnName )" construct. However, if many rows have been updated and only a single one of them has the particular column value changed "if...
Hi,
I'm having problems with Full-Text Search on SQL Server 2005. In my setup, I am indexing a single column, let's call it Content. The indexing is done in the neutral culture since the column might contain text in different languages. The fulltext index is created as follows:
CREATE FULLTEXT INDEX
ON [dbo].[Table1]([Content])
KEY IN...
Hello,
I'm trying to create a view that displays events with start and end time. This view should be gathered from an existing table that only has an event time field and event type field.
So the current EventTable looks like this:
EventTime | BooleanField
------------------------------
1/1/2010 6:00AM 1
1/2/2010 6:00AM ...
Hello, I'm transferring data from one database to another (upgrading a system) and have a quick question.
In the source database (the one currently in use), I have a column that stores urls using absolute paths. In my new database, I am using relative paths. How can I trim out the absolute portion of the paths on the transfer (I'm using...
Consider an order. An order will have one or more line items. Each line item is for a particular product.
Given a filter table with a couple of products, how would I get the order id's that had at least all of the products listed in the second table?
table Orders(
OrderId int
)
table LineItems (
OrderId int,
LineItemId int...
I'm refactoring some older SQL, which is struggling after 4 years and 1.7m rows of data. Is there a way to improve the following MS SQL Query:
SELECT ServiceGetDayRange_1.[Display Start Date],
SUM (CASE WHEN Calls.line_date BETWEEN [Start Date] AND [End Date] THEN 1 ELSE 0 END) AS PerDayCount
FROM dbo.ServiceGetDayRange(GET...
Hi Guys
I've had a hunt around for something similar to this but can't find anything.
I have a query that provides the number of transactions that have occurred each day and need to group by year, month, week BUT of course some months span multiple week numbers, eg. Sept. & Oct. 2009.
Take for example week 39 last year (September & Oct...
I've noticed that we have a lot of indexes that begin with a certain column and that column has low cardinality (i.e. Company and 99% of records belong to the 'live' company).
Therefore these indexes are next to useless (from what I've read) as they are not providing a means of segregating the data in the table to be found quickly.
So,...
I'm working on integrating some data from a 3rd-Party system into one of my applications (legacy ASP Classic-based web application/SQL 2000) - they have made some poor decisions (IMHO) when it comes to their approach and data structure, though perhaps we might get a chance to refactor at some point... but until then, I have to work with ...
Hi all. A while back I had to come up with a way to clean up all indexes and user-created statistics from some tables in a SQL Server 2005 database. After a few attempts it worked, but now I gotta have it working in SQL Server 2000 databases as well. For SQL Server 2005, I used
SELECT Name FROM sys.stats
WHERE object_id = object_id(@t...
I have a database which was upgraded from 2000 to 2005. Is there any issue using sp_dbcmptlevel to change compatibility level to 90. Will this cause any issues with old queries or stored procedures
...
Consider the following table:
People
FirstName nvarchar(50)
LastName nvarchar(50)
Let's assume for the moment that this table has a full-text index on it for both columns.
Let's suppose that I wanted to find all of the people named "John Smith" in this table. The following query seems like a perfectly rational way to accomplish thi...
Hello,
When I select from SQL Server, I want to get a date, but omit the millisecond value, and I want it to be as a date type. So if I have a value 1/1/2009 1:23:11.923, I want to omit the millisecond but retain the date type, so that it will be the value 1/1/2009 1:23:11.000 (I know you really can't omit the millisecond value with a ...
From what I gather about the IN expression, this should work:
DECLARE @list varchar(255)
SET @list = '''Item1'',''Item2'''
SELECT
*
FROM
Table
WHERE
Item IN (@list)
And it should select those items in @list. The items exist in the table. If I execute the query separately for Item1 and Item2 (Item = Item1, then Item = Item2)...
In oracle, I can issue a DROP TABLE ... cascade constraints and it won't complain about FKs, etc.
Is there an equivalent in T-SQL?
...
Hello I want to generate a Unique Random number with out using the follow statement :
Convert(int, (CHECKSUM(NEWID()))*100000) AS [ITEM]
Cause when I use joins clauses on "from" it generates double registers by using NEWID()
Im using SQL Server 2000
*PD : When I use Rand() it probably repeat on probability 1 of 100000000 but this ...
I am trying to determine what indexes are no longer used in my Database. I have had great luck using the following query:
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
i.Type_Desc as [Index Type],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES
F...