select
t.slotmachinebk,
t.gamingdate,
t.freeplaydownloaded,
t.freeplayadjusted,
t.freeplayplayed,
t.freeplayabandoned,
t.freeplaybalance
from (select * from freeplay.egmfreeplay union all select * from Change.EgmFreePlay) t where not exists (select * from testtable where
slotmachinebk = t.slotmachinebk and
auditdate = t.gamingda...
I have to update a field with a value which is returned by a join of 3 tables.
Example:
select
im.itemid
,im.sku as iSku
,gm.SKU as GSKU
,mm.ManufacturerId as ManuId
,mm.ManufacturerName
,im.mf_item_number
,mm.ManufacturerID
from
item_master im, group_master gm, Manufacturer_Master mm
where
im.mf_...
I have a table in SQL Server 2000 that I am trying to query in a specific way. The best way to show this is with example data.
Behold, [Addresses]:
Name Street City State
--------------------------------------------------------
Bob 123 Fake Street Peoria IL
Bob 234 Other...
Update: Problem solved, and staying solved. If you want to see the site in action, visit Tweet08
I've got several queries that act differently in SSMS versus when run inside my .Net application. The SSMS executes fine in under a second. The .Net call times out after 120 seconds (connection default timeout).
I did a SQL Trace (and col...
I've got a very large xml data set that is structured like the following:
<root>
<person>
<personid>HH3269732</personid>
<firstname>John</firstname>
<lastname>Smith</lastname>
<entertime>01/02/2008 10:15</entertime>
<leavetime>01/02/2008 11:45</leavetime>
<entertime>03/01/2008 08:00</e...
Hey StackOverflow,
I work with Service Broker. I need to know which Service Broker entities (message types, contracts, etc.) have been added to the database I am dealing with.
The question refers to MS SQL Server 2005.
Thank you!
...
I have the below table with the below records in it
create table employee
(
EmpId number,
EmpName varchar2(10),
EmpSSN varchar2(11)
);
insert into employee values(1, 'Jack', '555-55-5555');
insert into employee values (2, 'Joe', '555-56-5555');
insert into employee values (3, 'Fred', '555-57-5555');
insert into employee values (4, '...
Suppose I have 2 tables
Table1
ID Path
-- ----
1 PathA
2 PathB
3 PathC
4 PathD
Table2
ID Path Table1ID
-- ---- --------
23 PathA 1
24 PathX 2
25 PathC 3
26 PathZ 4
In the above, PathX should be PathB and PathZ should be PathD
How do I sync all the path values in Table2 with values in Table 1, the tables could be large so woul...
Hello,
I'm trying to code a user defined function under SQL Server 2005 that will increase integer part of alphanumeric value by one. For example, uf_AlphanumericIncrease ('A000299') should return 'A000300'. Here's what I've done so far;
ALTER FUNCTION uf_AlphaNumericIncrement
(
@ID varchar(10)
)
RETURNS VARCHAR(10) AS
BEGIN
DECL...
Is it possible to return an Oracle Ref Cursor to a caller that is in SqlServer T-SQL? When dealing with a normal .Net program there is this Knowledge Base article: http://support.microsoft.com/kb/322160
But is this same type of thing possible from T-SQL?
...
I have a UDF that queries data out of a table. The table, however, needs to be definable as a parameter. For example I can't have:
Select * From [dbo].[TableA]
I need something like:
Select * From [dbo].[@TableName]
The above line doesn't work, and also the UDF prohibits me from setting the query as a string and calling exec(). I can...
In Excel, there's a function called "MAX" that accepts numbers and returns the largest one in the set. Is there a function in T-SQL that duplicates this functionality? I haven't been able to find one, and I've written a UDF that does it for me, but I thought it was worth asking.
Here is the function I've been using:
CREATE FUNCTION dbo...
Given the following XML variable, how can I replace "UNKNOWN" in the StateCode node with "FOO" in TSQL for MS SQL 2005?
declare @xmldata xml
set @xmldata =
'<Collection>
<Plan>
<StateCode>UNKNOWN</StateCode>
<Type>Tubular</Type>
</Plan>
</Collection>'
Unlike a similar question that I found, this is si...
Guys, I am trying to write a stored procedure in T-SQL (SQL Server) that will select records based on a date field, keeping a variance of minutes in mind. Something like this:
CREATE PROCEDURE spGetCustomers(@DateRange DATETIME, @Variance int) AS
-- The next line is where I need help
-- I'm trying to subtract X amount of minutes from th...
Hi folks,
I've got a trigger attached to a table.
ALTER TRIGGER [dbo].[UpdateUniqueSubjectAfterInsertUpdate]
ON [dbo].[Contents]
AFTER INSERT,UPDATE
AS
BEGIN
-- Grab the Id of the row just inserted/updated
DECLARE @Id INT
SELECT @Id = Id
FROM INSERTED
END
Every time a new entry is inserted or modified, I wish to update a si...
Trying to select records that are all for the same customer, but where the address is different.
So I can later let the user choose Bob Yonkers, then choose to update all of Bob's records to a specific address. So I want to show all the available records.
Data Example:
CUSTOMER_NAME, CUSTOMER_ADDRESS
Bob Yonkers , 42 Satellite Cir
...
How can I rewrite this t-sql to avoid using IN - and use joins in stead?
--Members with membership in 2008 but not in 2009, using IN and NOT IN
SELECT * FROM @Members
WHERE MemberId NOT IN
(SELECT MemberId FROM @Memberships WHERE [Year] = 2009) AND
MemberId IN (SELECT MemberId FROM @Memberships WHERE [Year] = 2008 )
I want to fi...
is it possible to get a numeric parameter to my udf
and do stuff according to its type, like:
if type of @p1 is decimal(10,3)
...
else if type of @p1 is decimal(15,3)
...
else if type of @p1 is integer
...
...
Hi,
Where stockView is an indexed view with a full-text index, I receive the error message below. The database is running on a 2008 Express engine in 2005 compatibility mode.
Code:
with stockCte (title, grade, price, weighted)
as
(
select sv.[title] ,
sv.[grade] ,
sv.[price] ,
(case when sv.[issue] = ...
Consider writing an application that requires registration for an entity, and the schema has been defined to require the country, state/prov/county data to be normalized. This is fairly typical stuff here. Naming also is important to reflect. Each country has a different name for this entity:
USA = states
Australia = states + territori...