ChrisM@Work

Forum Replies Created

Viewing 15 posts - 8,266 through 8,280 (of 10,143 total)

RE: Query difference: HOw to Avoiding UNION ALL with correlated Query

Bhuvnesh (1/11/2010)
sample data is not possible , as it includes amny tables
yes row count is correct and time taken = 18 secs
but my concerns is UNION ALL ...
^{“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

January 11, 2010 at 3:11 am

#1101679
RE: Query difference: HOw to Avoiding UNION ALL with correlated Query

Bhuvnesh (1/11/2010)
thanks for ur reply but its not working
There's always a danger of this when there's no sample data to test against. Could you provide some details of why it's...
^{“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

January 11, 2010 at 3:01 am

#1101675
RE: Query difference: HOw to Avoiding UNION ALL with correlated Query

No problem. Note - this is UNTESTED.
select
reg_id = ISNULL(dr1.reg_id, dr2.reg_id), -- changed
invitee_id = ISNULL(di1.invitee_id, di2.invitee_id), -- changed
contact_type = isnull(ct.cont_type_name, ''),
item_id = I.item_id,
item_type = i.item_type, ...
^{“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

January 11, 2010 at 2:41 am

#1101667
RE: Select statement with logical operator

Of course...
drop table #test
GO
CREATE TABLE #test (
[order] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[year] [varchar]...
^{“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

January 11, 2010 at 2:28 am

#1101660
RE: Select statement with logical operator

drop table #test
GO
CREATE TABLE #test (
[order] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[year] [varchar] (50)...
^{“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

January 8, 2010 at 10:23 am

#1101045
RE: Select statement with logical operator

Please read the link below in my sig, it will show you how to create tables and load sample data for people to test their solutions to your problem.
^{“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

January 8, 2010 at 9:32 am

#1100987
RE: ABS on datediff but need negative values

Lynn Pettis (1/8/2010)
Sample data? INSERT INTO statements.
Hi Whitlander
without this essential information, I can't test and complete the code I've written and have to leave here soon because trains are...
^{“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

January 8, 2010 at 9:26 am

#1100977
RE: ABS on datediff but need negative values

whitlander (1/8/2010)
thanks for your help guys.
I'm not sure how to re-write the sub query, what functions do I use to return the negative value?
I've got a solution ready to test,...
^{“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

January 8, 2010 at 8:02 am

#1100909
RE: Looking for a set-based solution

Hi Scott
Run this 'ere bit of code, and the answer will come to you in a flash:
SELECT Customer, COUNT(*) AS trxTotal, COUNT(trx) AS trxNonNULL
FROM YourTable
GROUP BY Customer
^{“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

January 8, 2010 at 7:14 am

#1100837
RE: ABS on datediff but need negative values

Lynn Pettis (1/8/2010)
whitlander (1/8/2010)
Example from 'event' table:
IDSource_IDEventType_ID Use_ID Period
34022 ...
^{“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

January 8, 2010 at 7:09 am

#1100834
RE: ABS on datediff but need negative values

whitlander (1/8/2010)
Example from 'event' table:
IDSource_IDEventType_ID Use_ID Period
34022 10 ...
^{“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

January 8, 2010 at 6:57 am

#1100828
RE: ABS on datediff but need negative values

SELECT planned.ID,
planned.Source_ID,
planned.Event_ID,
planned.User_ID,
planned.Period,
(SELECT TOP (1)
ABS(DATEDIFF(dd, Period, planned.Period)) AS Expr1
FROM Event AS actual
WHERE (Source_ID = planned.Source_ID)
AND (EventType_ID IN (5, 6))
ORDER BY Expr1) AS 'KPI (Days Over/Under...
^{“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

January 8, 2010 at 6:50 am

#1100822
RE: split the table

Where are the rules which dictate what earnings match to what deduction?
BASIC Earnings 5000 PF Deduction 2000
How do these two entities relate?
^{“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

January 8, 2010 at 6:28 am

#1100808
RE: sp needed

rpatil22 (1/7/2010)
Guys I will receive a string
'name',a1,b1,c1,a2,b2,c2,a3,b3,c3,a4,b4,c4
i want to make data in table
name a1 b1 c1
name a2 b2 c2
name...
^{“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

January 8, 2010 at 6:25 am

#1100805
RE: Help with a join

Jpotucek (1/5/2010)
Thank you all for your guidance!!!! This worked :
SELECT
a.Contact_info
,b.MEMBER_NUMBER
,c.CLIENT_NUMBER
,a.CONTACT_TYPE_ID
FROM FRATERNAL.CONTACTS a
inner join FRATERNAL.CLIENT_CONTACTS a1 on a.CONTACT_ID = a1.CONTACT_ID
inner join FRATERNAL.MEMBER b on a1.CLIENT_ID = b.CLIENT_ID
inner join FRATERNAL.CLIENT...
^{“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

January 7, 2010 at 8:45 am

#1100269

Viewing 15 posts - 8,266 through 8,280 (of 10,143 total)