ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,496 through 9,510 (of 10,143 total)

RE: Query help urgent

samsql, could you help us by showing exactly how your output should look, in tabular format?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 3, 2008 at 5:11 am

#880383
RE: 'N' number of usage - Tally Table

Fuzzy dedupe:
[font="Courier New"]-- Self fuzzy match
SELECT t.VendorID, r.VendorID AS Dupe, COUNT(*) AS TokenCount
FROM FuzzyInputFile r
INNER JOIN [Numbers] n ON n.number < LEN(r.VendorName)
INNER JOIN FuzzyInputFile t ON t.VendorID <> r.VendorID...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 1, 2008 at 5:56 am

#879409
RE: Finding Table is empty or not without using count() function

karthikeyan (10/1/2008)
Chris,
Thanks a lot for your prompt reply with example. It is working fine now.
Can you tell me why you used BIT instead of INT ? Becuase both of them...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 1, 2008 at 5:39 am

#879404
RE: Finding Table is empty or not without using count() function

[font="Courier New"]DROP TABLE #Temp
CREATE TABLE #Temp (SomeColumn INT)
DECLARE @HasRowsINT INT, @HasRowsBIT BIT
SELECT @HasRowsINT = COUNT(*) FROM (SELECT TOP 1 * FROM #Temp) d
SELECT @HasRowsBIT = COUNT(*) FROM (SELECT TOP 1...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 1, 2008 at 5:17 am

#879395
RE: Finding Table is empty or not without using count() function

Ian Scarlett (10/1/2008)
IF EXISTS (select * from Emp) print 'exists'.
This only needs to verify that a row exists in the table, and should only do a single I/O, regardless of...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 1, 2008 at 4:51 am

#879389
RE: Finding Table is empty or not without using count() function

Karthik, if I run your slight modification of my code as displayed in your post:
DECLARE @HasRows INT
SELECT @HasRows = COUNT(*) FROM (SELECT TOP 1 * FROM INVOICES_Monthly) d
SELECT @HasRows
Then the...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 1, 2008 at 4:43 am

#879385
RE: Finding Table is empty or not without using count() function

You don't have to COUNT() all of the rows...
DECLARE @HasRows BIT
SELECT @HasRows = COUNT(*) FROM (SELECT TOP 1 * FROM YourTable) d
SELECT @HasRows
Cheers
ChrisM
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 1, 2008 at 4:30 am

#879381
RE: Error on StoredProcedure

Hi Suresh
You're very nearly there, just a couple of small changes...
[font="Courier New"]DECLARE @UserId BIGINT
SELECT @UserId = UserId
FROM DAMS_Tbl_UsersLogin
WHERE UserName = @UserName
INSERT INTO  DAMS_Tbl_RegisteredUsers
   (UserId,FirstName,LastName,DateOfBirth ,StreetAddress1 ,StreetAddress2 ,
       City,State ,CountryId...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

September 30, 2008 at 3:16 am

#878724
RE: CONVERT from minutes to hours

Philip Horan (9/29/2008)
Chris poor explanation on my part. If the user enters 8 in the hours field then enters 15 in the minutes field this is recorded as 75:00000.
Phil.
Many thanks...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

September 30, 2008 at 12:56 am

#878689
RE: Query puzzler

Are they spaces? Or are they non-printing characters?
Try returning the ascii value of
LEFT(REVERSE(yourstring), 1)
and
SUBSTRING(REVERSE(yourstring), 2, 1)
Cheers
ChrisM
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

September 29, 2008 at 10:29 am

#878506
RE: SQL Case Update Statement Error Message

Hi Roger:
Update Billed
set bill_amount =
Case When product_Code like '%0000' then STATE_DUES
When product_code like 'CHAPT/%' and product_code not like '%0000' Then Local_dues
Else '9999'
End
where billed.ID = '123456'
Cheers
ChrisM
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

September 29, 2008 at 10:25 am

#878504
RE: select 1 days worth of data every day

You're welcome maui, many thanks for the feedback.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

September 29, 2008 at 10:21 am

#878500
RE: select 1 day's worth of data every day using SSIS

Crosspost, see http://www.sqlservercentral.com/Forums/Topic577820-147-1.aspx
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

September 29, 2008 at 10:07 am

#878493
RE: select 1 days worth of data every day

This might help:
[font="Courier New"]DECLARE @Today DATETIME
SET @Today = DATEADD(dd,DATEDIFF(dd, 0, GETDATE()), 0) -- today with no time component
-- Sanity check:
SELECT @Today, DATEADD(dd, 1, @Today) AS Tomorrow_1, DATEADD(dd,1+DATEDIFF(dd, 0, GETDATE()), 0)...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

September 29, 2008 at 10:03 am

#878491
RE: Re: Short-circuiting a query

Spooky, Grant. Very spooky 😎
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

September 29, 2008 at 9:07 am

#878447

Viewing 15 posts - 9,496 through 9,510 (of 10,143 total)