Hopefully this is a better explanation of what I tried to explain earlier.
Below is an example of a dataset I build as a #wkfile. It consists of a
count/identity, Company, AccountNo, and Title. I’m trying to loop thru wi
th
SQL to pull out one occurrence of Company Name and AccountNo, into a 2nd
dataset. When there are more than one occurrence, the priority is first -
CEO’ OR like ‘%CEO%’, second - ‘CFO’ or like ‘%CFO%’, and if n
either of them
then take the first title.
id Company AccountNo Title
1 national Publishers A2053145611 CEO
2 (i)STRUCTURE, Inc A5051045506 CFO
3 (i)STRUCTURE, Inc 99091268226 Chief Executive Officer
4 (i)STRUCTURE, Inc A3013151569 Finance Director/CFO
5 (i)STRUCTURE, Inc 99091970756 President
6 (i)STRUCTURE, Inc 99091204766 President/CEO
7 (i)STRUCTURE, Inc 99091268239 Vice President
8 (i)STRUCTURE, Inc A5051045492 VP Finance & Service Management
9 (i)STRUCTURE, Inc A5051045541 VP Infrastructure Operations
10 (i)STRUCTURE, Inc A5051045455 VP Marketing
11 (i)STRUCTURE, Inc A5051045467 VP Sales
12 Andel Amba A3013151463 Finance Director/CFO
13 @.Global Inc A1022852020 President
14 @.plan, Inc. 99113038170 Chairman/CEO
15 @.plan, Inc. 99113038390 President/COO
16 @.plan, Inc. A0080460716 Vice President/CFO/CAO
17 @.radical.media, Inc. A4102749756 Chairman & CEO
18 @.radical.media, Inc. A4102749802 COO
19 @.radical.media, Inc. A4102749784 EVP
20 @.Road, Inc. A4071235030 CFOYou don't need to loop. It would have been nice if you supplied exact DDL
and real INSERT statements. Here's an untested solution:
select
o.*
from
MyTable
where
o.id in
(
select top 1
i.id
from
MyTable i
where
i.Company = o.Company
order by
case
when i.Title like '%CEO%' then 1
when i.Title like '%CFO%' then 2
else 3
end
, o.id
)
It's not clear what you mean by the "first" title, so I used id as the
tiebreaker.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Logger" <Logger@.discussions.microsoft.com> wrote in message
news:635B49E6-0714-46FA-997D-B44B4F1F0085@.microsoft.com...
Hopefully this is a better explanation of what I tried to explain earlier.
Below is an example of a dataset I build as a #wkfile. It consists of a
count/identity, Company, AccountNo, and Title. I’m trying to loop thru wi
th
SQL to pull out one occurrence of Company Name and AccountNo, into a 2nd
dataset. When there are more than one occurrence, the priority is first -
CEO’ OR like ‘%CEO%’, second - ‘CFO’ or like ‘%CFO%’, and if n
either of them
then take the first title.
id Company AccountNo Title
1 national Publishers A2053145611 CEO
2 (i)STRUCTURE, Inc A5051045506 CFO
3 (i)STRUCTURE, Inc 99091268226 Chief Executive Officer
4 (i)STRUCTURE, Inc A3013151569 Finance Director/CFO
5 (i)STRUCTURE, Inc 99091970756 President
6 (i)STRUCTURE, Inc 99091204766 President/CEO
7 (i)STRUCTURE, Inc 99091268239 Vice President
8 (i)STRUCTURE, Inc A5051045492 VP Finance & Service Management
9 (i)STRUCTURE, Inc A5051045541 VP Infrastructure Operations
10 (i)STRUCTURE, Inc A5051045455 VP Marketing
11 (i)STRUCTURE, Inc A5051045467 VP Sales
12 Andel Amba A3013151463 Finance Director/CFO
13 @.Global Inc A1022852020 President
14 @.plan, Inc. 99113038170 Chairman/CEO
15 @.plan, Inc. 99113038390 President/COO
16 @.plan, Inc. A0080460716 Vice President/CFO/CAO
17 @.radical.media, Inc. A4102749756 Chairman & CEO
18 @.radical.media, Inc. A4102749802 COO
19 @.radical.media, Inc. A4102749784 EVP
20 @.Road, Inc. A4071235030 CFO
Showing posts with label below. Show all posts
Showing posts with label below. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
Looping Query
I know I have done this before but for the life of me I can remember how'
When I run the query below it loops and loops, how do I make it appear just
once - missing something but brain freeze has taken over aaaggghhh...
select [T_GIFTCARDS].[M_CARDNUMBER], [T_GIFTCARDS].[M_SERIALNO],
[transaction].[M_GIFTCARDNO], [transaction].[req_login_time]
from [T_GIFTCARDS], [transaction]
where req_login_time between '27 September,2004' and '28 September, 2004'
your help much appreciatedWhat do you mean by "The Query loops" ? A query doesn=B4t loop around on
its own, actually it doesn=B4t loop at all.|||same information in rows 1-84 shows up in query analyzer then row 85 shows
row 1 again then so on until row 829227262525......or until system stops
"Jens" wrote:
> What do you mean by "The Query loops" ? A query doesn′t loop around on
> its own, actually it doesn′t loop at all.
>|||examnotes (Ivo@.discussions.microsoft.com) writes:
> I know I have done this before but for the life of me I can remember how'
> When I run the query below it loops and loops, how do I make it appear
> just once - missing something but brain freeze has taken over
> aaaggghhh...
> select [T_GIFTCARDS].[M_CARDNUMBER], [T_GIFTCARDS].[M_SERIALNO],
> [transaction].[M_GIFTCARDNO], [transaction].[req_login_time]
> from [T_GIFTCARDS], [transaction]
> where req_login_time between '27 September,2004' and '28 September, 2004'
You have two tables in your WHERE clause, but there is no condition for
joining them, which means that you get call possible combination of
rows. If there are 1000 rows in T_GIFTCARDS, and there are a million
rows in transactions for the given time range, the query will produce
one milliard rows.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog skrev:
> You have two tables in your WHERE clause, but there is no condition for
> joining them, which means that you get call possible combination of
> rows. If there are 1000 rows in T_GIFTCARDS, and there are a million
> rows in transactions for the given time range, the query will produce
> one milliard rows.
>
That would be one 'billion' in english, or are there even differences
between british and american english? (Not to mention Erland's swedish
english ;) )
/impslayer, aka Birger Johansson|||impslayer (impslayer@.hotmail.com) writes:
> That would be one 'billion' in english, or are there even differences
> between british and american english?
See http://www.m-w.com/dictionary/milliard.
However, both in British and American English, they capitalise "British",
"American" and "English". :-)
But maybe we should table this discussion before it goes too far. (Now,
let's see how many how get *that* one!)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog skrev:
> impslayer (impslayer@.hotmail.com) writes:
> See http://www.m-w.com/dictionary/milliard.
>
When will I ever (EVER!) learn to check stuff out before I make a
post?
Had no idea 'milliard' existed in English (sic!), and was hoping I'd
clarify that you meant 'billion'... Definitely no cheap shot, I promise
:)
/impslayer, aka NoBrainPoster|||impslayer (impslayer@.hotmail.com) writes:
> When will I ever (EVER!) learn to check stuff out before I make a
> post?
> Had no idea 'milliard' existed in English (sic!), and was hoping I'd
> clarify that you meant 'billion'... Definitely no cheap shot, I promise
>:)
Rest assured that if I ever say "billion", I mean a real big billion, and
not the wimpy billions they have over there!
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
When I run the query below it loops and loops, how do I make it appear just
once - missing something but brain freeze has taken over aaaggghhh...
select [T_GIFTCARDS].[M_CARDNUMBER], [T_GIFTCARDS].[M_SERIALNO],
[transaction].[M_GIFTCARDNO], [transaction].[req_login_time]
from [T_GIFTCARDS], [transaction]
where req_login_time between '27 September,2004' and '28 September, 2004'
your help much appreciatedWhat do you mean by "The Query loops" ? A query doesn=B4t loop around on
its own, actually it doesn=B4t loop at all.|||same information in rows 1-84 shows up in query analyzer then row 85 shows
row 1 again then so on until row 829227262525......or until system stops
"Jens" wrote:
> What do you mean by "The Query loops" ? A query doesn′t loop around on
> its own, actually it doesn′t loop at all.
>|||examnotes (Ivo@.discussions.microsoft.com) writes:
> I know I have done this before but for the life of me I can remember how'
> When I run the query below it loops and loops, how do I make it appear
> just once - missing something but brain freeze has taken over
> aaaggghhh...
> select [T_GIFTCARDS].[M_CARDNUMBER], [T_GIFTCARDS].[M_SERIALNO],
> [transaction].[M_GIFTCARDNO], [transaction].[req_login_time]
> from [T_GIFTCARDS], [transaction]
> where req_login_time between '27 September,2004' and '28 September, 2004'
You have two tables in your WHERE clause, but there is no condition for
joining them, which means that you get call possible combination of
rows. If there are 1000 rows in T_GIFTCARDS, and there are a million
rows in transactions for the given time range, the query will produce
one milliard rows.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog skrev:
> You have two tables in your WHERE clause, but there is no condition for
> joining them, which means that you get call possible combination of
> rows. If there are 1000 rows in T_GIFTCARDS, and there are a million
> rows in transactions for the given time range, the query will produce
> one milliard rows.
>
That would be one 'billion' in english, or are there even differences
between british and american english? (Not to mention Erland's swedish
english ;) )
/impslayer, aka Birger Johansson|||impslayer (impslayer@.hotmail.com) writes:
> That would be one 'billion' in english, or are there even differences
> between british and american english?
See http://www.m-w.com/dictionary/milliard.
However, both in British and American English, they capitalise "British",
"American" and "English". :-)
But maybe we should table this discussion before it goes too far. (Now,
let's see how many how get *that* one!)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog skrev:
> impslayer (impslayer@.hotmail.com) writes:
> See http://www.m-w.com/dictionary/milliard.
>
When will I ever (EVER!) learn to check stuff out before I make a
post?
Had no idea 'milliard' existed in English (sic!), and was hoping I'd
clarify that you meant 'billion'... Definitely no cheap shot, I promise
:)
/impslayer, aka NoBrainPoster|||impslayer (impslayer@.hotmail.com) writes:
> When will I ever (EVER!) learn to check stuff out before I make a
> post?
> Had no idea 'milliard' existed in English (sic!), and was hoping I'd
> clarify that you meant 'billion'... Definitely no cheap shot, I promise
>:)
Rest assured that if I ever say "billion", I mean a real big billion, and
not the wimpy billions they have over there!
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Monday, March 19, 2012
lookup transformation validation error
below is the error message:
TITLE: Package Validation Error
Package Validation Error
ADDITIONAL INFORMATION:
Error at Data Flow ACH Validate File and Header Info [Lookup ACH Batch Number [506]]: input column "ID" (571) and reference column named "BANKBATCHNBR" have incompatible data types.
"Lookup ACH Batch Number" is the name of the lookup transformation. input column "ID" has a string data type. reference column "BANKBATCHNBR" has a varchar(50) data type. can someone please tell me how i should go about debugging this validation error? thanks.
What is the length of the input column? I think (but may be wrong) that the lengths have to be the same.
There is a property of the LOOKUP transform that contains an XML snippet. I *think* that snippet contains the metadata of the lookup cache. I'm afraid I can't be absolutely certain about these things (and I can't remember the name of the property) because I don't have a BIDS instance to hand.
-Jamie
lookup transformation validation error
below is the error message:
TITLE: Package Validation Error
Package Validation Error
ADDITIONAL INFORMATION:
Error at Data Flow ACH Validate File and Header Info [Lookup ACH Batch Number [506]]: input column "ID" (571) and reference column named "BANKBATCHNBR" have incompatible data types.
"Lookup ACH Batch Number" is the name of the lookup transformation. input column "ID" has a string data type. reference column "BANKBATCHNBR" has a varchar(50) data type. can someone please tell me how i should go about debugging this validation error? thanks.
What is the length of the input column? I think (but may be wrong) that the lengths have to be the same.
There is a property of the LOOKUP transform that contains an XML snippet. I *think* that snippet contains the metadata of the lookup cache. I'm afraid I can't be absolutely certain about these things (and I can't remember the name of the property) because I don't have a BIDS instance to hand.
-Jamie
Friday, March 9, 2012
looking to tune query...
If anyone is able to provide advice for tuning the below query in sql
server, it is much appreciated. In addition any index suggestions are
also appreciated as I have access to the tables. Thank you.
server, it is much appreciated. In addition any index suggestions are
also appreciated as I have access to the tables. Thank you.
select a.id, isnull(b.advisement_satisfaction_yes, 0) as
advisement_satisfaction_yes,
isnull(c.advisement_satisfaction_no, 0) as advisement_satisfaction_no,
case
when isnull(b.advisement_satisfaction_yes, 0) >
isnull(c.advisement_satisfaction_no, 0) then 'YES'
when isnull(b.advisement_satisfaction_yes, 0) <
isnull(c.advisement_satisfaction_no, 0) then 'NO'
when isnull(b.advisement_satisfaction_yes, 0) =
isnull(c.advisement_satisfaction_no, 0) then 'TIE'
end as Satisfied_With_Advisement
from a
left Join
(select id, count(answer_text) as Advisement_Satisfaction_yes from a
where question = 'The level of Academic Advisement I received from the
University staff during this course was appropriate.'
and answer_text = 'yes'
GROUP BY id) b
on a.id = b.id
left join
(select id, count(answer_text) as Advisement_Satisfaction_NO from a
where question = 'The level of Academic Advisement I received from the
University staff during this course was appropriate.'
and answer_text = 'NO'
GROUP BY id) c
on a.id = b.id
where question = 'The level of Academic Advisement I received from the
University staff during this course was appropriate.'The above query should provide the same results as your query with a
DISTINCT clause, but is shorter and should perform better:
SELECT id, Advisement_Satisfaction_yes, Advisement_Satisfaction_NO,
CASE
WHEN Advisement_Satisfaction_yes>Advisement_Satisfaction_NO
THEN 'YES'
WHEN Advisement_Satisfaction_yes<Advisement_Satisfaction_NO
THEN 'NO'
ELSE 'TIE'
END AS Satisfied_With_Advisement
FROM (
SELECT id,
SUM(CASE WHEN answer_text='yes' THEN 1 ELSE 0 END)
AS Advisement_Satisfaction_yes,
SUM(CASE WHEN answer_text='NO' THEN 1 ELSE 0 END)
AS Advisement_Satisfaction_NO
FROM a GROUP BY id
) x
Razvan|||AMAZING...THANK YOU SO MUCH!
Razvan Socol wrote:
Quote:
Originally Posted by
The above query should provide the same results as your query with a
DISTINCT clause, but is shorter and should perform better:
>
SELECT id, Advisement_Satisfaction_yes, Advisement_Satisfaction_NO,
CASE
WHEN Advisement_Satisfaction_yes>Advisement_Satisfaction_NO
THEN 'YES'
WHEN Advisement_Satisfaction_yes<Advisement_Satisfaction_NO
THEN 'NO'
ELSE 'TIE'
END AS Satisfied_With_Advisement
FROM (
SELECT id,
SUM(CASE WHEN answer_text='yes' THEN 1 ELSE 0 END)
AS Advisement_Satisfaction_yes,
SUM(CASE WHEN answer_text='NO' THEN 1 ELSE 0 END)
AS Advisement_Satisfaction_NO
FROM a GROUP BY id
) x
>
Razvan
Subscribe to:
Posts (Atom)