Can anyone suggest a good approach to this problem?
I need to match transactions from two tables on several columns. When
I have a match, I need to set a match flag and a match number in each
table. There can be duplicate transactions in either table. If I have
two transactions in one table that match three in the other, I want to
set the match flag and match number on two transactions in each table
and leave the third one blank.
Results:
table 1
--
col A col B match_flag match_number
1 2 Y 1
1 2 Y 2
5 5 Y 3
5 5
table 2
--
col A col B match_flag match_number
1 2 Y 1
1 2 Y 2
1 2
5 5 Y 3>> Can anyone suggest a good approach to this problem?
Please post your table structures & expected results along with clear
explanation of what you are trying to do. For details, refer to :
www.aspfaq.com/5006
Anith|||I have two temp tables loaded with the transactions I want to match
with the other table. They need to match on mid, tran_date, amount,
and card. When there's a match, I want to set the match_flag to 'AM'
and set the match_number to a unique number given to each matching
transaction. It's possible that there can be duplicate transactions,
as in the samples below, where there isn't a corresponding match in the
other table. If there are three in one table and two in the other that
match, I want to set the match_flag and match_number on two from each
table and leave the third from one table null to match on a possible
future load. I'll be matching on several thousands of transactions.
Let me know if you need more information. Thanks.
create table #setl (
mid char(6) not null,
load_number int not null,
detail_number int not null,
batch_number int not null,
tran_date datetime not null,
amount decimal(8,2) not null,
card char(4) not null,
match_flag char(4) null,
match_number int null )
Data:
--
mid load_number detail_number batch_number
543684 23712 1 877
543684 23712 2 877
543684 23712 3 877
tran_date amount card match_flag match_number
2005-09-30 .01 4444 null null
2005-09-30 .01 4444 null null
2005-09-30 .01 4444 null null
create table #debit (
mid char(6) not null,
load_number int not null,
detail_number int not null,
tran_date datetime not null,
amount decimal(8,2) not null,
card char(4) not null,
match_flag char(4) null,
match_number int null )
Data:
--
mid load_number batch_number
543684 658 1
543684 658 1
tran_date amount card match_flag match_number
2005-09-30 .01 4444 null null
2005-09-30 .01 4444 null null
desired results:
#setl
--
mid load_number detail_number batch_number
543684 23712 1 877
543684 23712 2 877
543684 23712 3 877
tran_date amount card match_flag match_number
2005-09-30 .01 4444 AM 1
2005-09-30 .01 4444 AM 2
2005-09-30 .01 4444 null null
#debit
--
mid load_number batch_number
543684 658 1
543684 658 1
tran_date amount card match_flag match_number
2005-09-30 .01 4444 AM 1
2005-09-30 .01 4444 AM 2|||First all, in databases, duplicates nullify logic. In other words, without a
column or set of column to uniquely identify a row in a table, it is
impossible to logically manipulate the data in those tables.
Given the data in your tables, it is impossible to tell if the duplicate
rows represent actually duplicated transactions or simply erroneous entries.
So rather than trying to work with meaningless data, you should consider
eliminating the duplicates in the first place. For starters refer to:
http://support.microsoft.com/kb/139444/en-us
If you are looking for a short term workaround to please your boss, write up
a cursor to loop through the rows and assign the match_flag and match_number
values. However, such a solution will do no good since you will still be
left with redundant data with no keys and constraints.
Anith
Showing posts with label match. Show all posts
Showing posts with label match. Show all posts
Saturday, February 25, 2012
matching saved searches to newly inserted record
Hello SQL wizards,
I'm trying to match saved searches to a newly inserted "job", and send
an email for matching searches. get about 20 job postings a day, and
have about 150k saved searches. want to do this as quickly as possible.
Please advise...
here's what I need to do:
1. job s
ers create saved searches, with criteria such as location
and some keywords
2. job is posted by a employer and inserted in job table
3. find all saved searches that match the newly inserted job
4. send emails to job sers with matching searches
right now, i'm doing the following:
1. using insert trigger on job table
2. put matching searches into a cursor (except by keyword search as I
can't figure out how to match by keyword using full text index all in
one statement)
select savedSearchId,...from savedSearches where (location='' OR
location=@.JobLocation) AND (duration='' or duration=@.jobDuration)...
3. loop throught cursor, doing
if(savedSearch has keywords)
select count(*) from jobtable where jobid=@.newlyInsertedJobId and
CONTAINS(*, keywords)
4. send email if matches keywords
this takes a while. there are about 150k saved searches. filtering on
non keywords returns about 3000 records to the cursor. the CONTAINS
search takes a long time.
Questions:
1. possible to do an asynchronous insert using ADO.net 1.1?
2. should i find the matching saved searches, put them in a table, and
do the keyword search/email later? if so, how?
3. how would you do it differently?
4. how to send email? xpsendmail or external component?
Thanks in advance!
Neilfound some problems myself:
1. full text index doesn't contain the new posting as it was just
inserted. should i do an incremental catalog population on insert?
2. contains() returns all rows that match the keywords, and THEN it's
filtered by jobid, so that's why it's slow...
any advice would be greatly appreciated.|||(neilmcguigan@.gmail.com) writes:
> 1. possible to do an asynchronous insert using ADO.net 1.1?
No and yes. There is no such thing as an asynchrounous insert, but
in your INSERT trigger just write a row to an alert table, and have
a job to run from SQL Agent (or scheduled by your own app) once a minute
or how often you see fit, to check for new entries.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||For instant propagation of changes to the FTI, you should use change trackin
g
and background propagation.
Look up sp_fulltext_table in Books Online.
ML|||Yes, both are true for SQL Server 2000...
For #1 you should enabled "Change Tracking" with "Update Index in
Background". The first initial setting of the CT with UIiB will
automatically run either a Full or Incremental population depending upon a
timestamp column in the table and if the FT Catalog is already populated.
For #2 you may want to use more sophisticated filtering with pre- and post-
processing as I once worked with a client in Europe who had a similar
requirement, except they were using FTS with a custom new clipping service
where the newspaper publishers were the employers and the newspaper reader
was the job ser. If you're interested, I may be able to put you in touch
with them. Feel free to email me directly if you want.
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<neilmcguigan@.gmail.com> wrote in message
news:1131222002.331389.73460@.g49g2000cwa.googlegroups.com...
> found some problems myself:
> 1. full text index doesn't contain the new posting as it was just
> inserted. should i do an incremental catalog population on insert?
> 2. contains() returns all rows that match the keywords, and THEN it's
> filtered by jobid, so that's why it's slow...
> any advice would be greatly appreciated.
>
I'm trying to match saved searches to a newly inserted "job", and send
an email for matching searches. get about 20 job postings a day, and
have about 150k saved searches. want to do this as quickly as possible.
Please advise...
here's what I need to do:
1. job s
ers create saved searches, with criteria such as locationand some keywords
2. job is posted by a employer and inserted in job table
3. find all saved searches that match the newly inserted job
4. send emails to job s
ers with matching searchesright now, i'm doing the following:
1. using insert trigger on job table
2. put matching searches into a cursor (except by keyword search as I
can't figure out how to match by keyword using full text index all in
one statement)
select savedSearchId,...from savedSearches where (location='' OR
location=@.JobLocation) AND (duration='' or duration=@.jobDuration)...
3. loop throught cursor, doing
if(savedSearch has keywords)
select count(*) from jobtable where jobid=@.newlyInsertedJobId and
CONTAINS(*, keywords)
4. send email if matches keywords
this takes a while. there are about 150k saved searches. filtering on
non keywords returns about 3000 records to the cursor. the CONTAINS
search takes a long time.
Questions:
1. possible to do an asynchronous insert using ADO.net 1.1?
2. should i find the matching saved searches, put them in a table, and
do the keyword search/email later? if so, how?
3. how would you do it differently?
4. how to send email? xpsendmail or external component?
Thanks in advance!
Neilfound some problems myself:
1. full text index doesn't contain the new posting as it was just
inserted. should i do an incremental catalog population on insert?
2. contains() returns all rows that match the keywords, and THEN it's
filtered by jobid, so that's why it's slow...
any advice would be greatly appreciated.|||(neilmcguigan@.gmail.com) writes:
> 1. possible to do an asynchronous insert using ADO.net 1.1?
No and yes. There is no such thing as an asynchrounous insert, but
in your INSERT trigger just write a row to an alert table, and have
a job to run from SQL Agent (or scheduled by your own app) once a minute
or how often you see fit, to check for new entries.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||For instant propagation of changes to the FTI, you should use change trackin
g
and background propagation.
Look up sp_fulltext_table in Books Online.
ML|||Yes, both are true for SQL Server 2000...
For #1 you should enabled "Change Tracking" with "Update Index in
Background". The first initial setting of the CT with UIiB will
automatically run either a Full or Incremental population depending upon a
timestamp column in the table and if the FT Catalog is already populated.
For #2 you may want to use more sophisticated filtering with pre- and post-
processing as I once worked with a client in Europe who had a similar
requirement, except they were using FTS with a custom new clipping service
where the newspaper publishers were the employers and the newspaper reader
was the job s
er. If you're interested, I may be able to put you in touchwith them. Feel free to email me directly if you want.
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<neilmcguigan@.gmail.com> wrote in message
news:1131222002.331389.73460@.g49g2000cwa.googlegroups.com...
> found some problems myself:
> 1. full text index doesn't contain the new posting as it was just
> inserted. should i do an incremental catalog population on insert?
> 2. contains() returns all rows that match the keywords, and THEN it's
> filtered by jobid, so that's why it's slow...
> any advice would be greatly appreciated.
>
Matching on from a list
HI,
say I have a list from an sql statement (results list)
this list contains 10 items
In another table, in one particular column - there is a match for one of these items from the initial list.
SO... this may be the list
_____________________
itemnumber
1
2
3
4
5
6
7
8
9
10
----------
in the other table there is a match...
but just for one item on that list.
____________________
othertablefield
11
13
14
3 <-- match
99
78
---------
How do I find that match with my sql statement?SELECT [Othertablefield] FROM Table2 WHERE [Othertablefield] NOT IN (SELECT [itemnumber] FROM Table1)
HTH
Matching Names
Hi All,
I have a table with two columns that I want to match but am unsure of
how to.
The Columns are called "User_Name" and Managed_By" the user_name is
entered as "Fred Flintstone" while the Managed_By is entered are
"Flintstone, Fred".
To the human eye you can see that they are the same person but how can
i do that match in SQL?
I am using a SQL 2000 server
Here are 5 rows of data that I am trying to match from my table, there
are other columns in the table such as Row_Date, Acc_No
What I want to do is bring back all of the rows where the managed_by is
equal to the user_name
Thanks
Mark
Sample Data>>>>>>>>>>>>>>>>>>>>>>>
Managed_By User_Name
Ward, Kimberley Kimberley Ward
Pinder, Louise Rachel Brooks
Services, Credit Rob Mackey
Hatfield, Rebecca Joanne Fixter
Hatfield, Rebecca Rebecca HatfieldTry,
use northwind
go
declare @.t table (
Managed_By varchar(50),
[User_Name] varchar(50)
)
insert into @.t values('Ward, Kimberley', 'Kimberley Ward')
insert into @.t values('Pinder, Louise', 'Rachel Brooks')
insert into @.t values('Services, Credit', 'Rob Mackey')
insert into @.t values('Hatfield, Rebecca', 'Joanne Fixter')
insert into @.t values('Hatfield, Rebecca', 'Rebecca Hatfield')
select
*
from
@.t as a
where
[User_Name] = parsename(replace(Managed_By, ', ', '.'), 1) + ' ' +
parsename(replace(Managed_By, ', ', '.'), 2)
go
AMB
"Sh0t2bts" wrote:
> Hi All,
> I have a table with two columns that I want to match but am unsure of
> how to.
> The Columns are called "User_Name" and Managed_By" the user_name is
> entered as "Fred Flintstone" while the Managed_By is entered are
> "Flintstone, Fred".
> To the human eye you can see that they are the same person but how can
> i do that match in SQL?
> I am using a SQL 2000 server
> Here are 5 rows of data that I am trying to match from my table, there
> are other columns in the table such as Row_Date, Acc_No
> What I want to do is bring back all of the rows where the managed_by is
> equal to the user_name
> Thanks
> Mark
> Sample Data>>>>>>>>>>>>>>>>>>>>>>>
> Managed_By User_Name
> Ward, Kimberley Kimberley Ward
> Pinder, Louise Rachel Brooks
> Services, Credit Rob Mackey
> Hatfield, Rebecca Joanne Fixter
> Hatfield, Rebecca Rebecca Hatfield
>
I have a table with two columns that I want to match but am unsure of
how to.
The Columns are called "User_Name" and Managed_By" the user_name is
entered as "Fred Flintstone" while the Managed_By is entered are
"Flintstone, Fred".
To the human eye you can see that they are the same person but how can
i do that match in SQL?
I am using a SQL 2000 server
Here are 5 rows of data that I am trying to match from my table, there
are other columns in the table such as Row_Date, Acc_No
What I want to do is bring back all of the rows where the managed_by is
equal to the user_name
Thanks
Mark
Sample Data>>>>>>>>>>>>>>>>>>>>>>>
Managed_By User_Name
Ward, Kimberley Kimberley Ward
Pinder, Louise Rachel Brooks
Services, Credit Rob Mackey
Hatfield, Rebecca Joanne Fixter
Hatfield, Rebecca Rebecca HatfieldTry,
use northwind
go
declare @.t table (
Managed_By varchar(50),
[User_Name] varchar(50)
)
insert into @.t values('Ward, Kimberley', 'Kimberley Ward')
insert into @.t values('Pinder, Louise', 'Rachel Brooks')
insert into @.t values('Services, Credit', 'Rob Mackey')
insert into @.t values('Hatfield, Rebecca', 'Joanne Fixter')
insert into @.t values('Hatfield, Rebecca', 'Rebecca Hatfield')
select
*
from
@.t as a
where
[User_Name] = parsename(replace(Managed_By, ', ', '.'), 1) + ' ' +
parsename(replace(Managed_By, ', ', '.'), 2)
go
AMB
"Sh0t2bts" wrote:
> Hi All,
> I have a table with two columns that I want to match but am unsure of
> how to.
> The Columns are called "User_Name" and Managed_By" the user_name is
> entered as "Fred Flintstone" while the Managed_By is entered are
> "Flintstone, Fred".
> To the human eye you can see that they are the same person but how can
> i do that match in SQL?
> I am using a SQL 2000 server
> Here are 5 rows of data that I am trying to match from my table, there
> are other columns in the table such as Row_Date, Acc_No
> What I want to do is bring back all of the rows where the managed_by is
> equal to the user_name
> Thanks
> Mark
> Sample Data>>>>>>>>>>>>>>>>>>>>>>>
> Managed_By User_Name
> Ward, Kimberley Kimberley Ward
> Pinder, Louise Rachel Brooks
> Services, Credit Rob Mackey
> Hatfield, Rebecca Joanne Fixter
> Hatfield, Rebecca Rebecca Hatfield
>
Matching Collation and Sort
I am attempting to match the following collation and sort. I cannot seem to
make the right choices to prevent collation conflicts in my stored
procedures. Can ANYONE please give me some insight on what options to select
in order to match this in SQL 2000 Service pack 3? The server that produced
the info below (by running sp_helpsort) is a SQL 2000 SP3 server as well.
The closest I've been able to come at this point is to match everything
except the accent-sensative selection.
THANKS IN ADVANCE!
Skip
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
1252 for non-Unicode DataYou can run select serverproperty('Collation') to get the server collation
and select databasepropertyex('dbname','Collation') to get the database
collation. Have you moved a database from one server to another with a
different collation ?
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Skip B" <skip@.theborlands.com> wrote in message
news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>I am attempting to match the following collation and sort. I cannot seem to
>make the right choices to prevent collation conflicts in my stored
>procedures. Can ANYONE please give me some insight on what options to
>select in order to match this in SQL 2000 Service pack 3? The server that
>produced the info below (by running sp_helpsort) is a SQL 2000 SP3 server
>as well.
> The closest I've been able to come at this point is to match everything
> except the accent-sensative selection.
> THANKS IN ADVANCE!
> Skip
> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
> 1252 for non-Unicode Data
>|||Thanks Jasper. My issue is not identifying the collation. The issue is
matching the collation on a new sql box to which I will be moving the
database to. Notice the vagaries in the collation properties at the bottom
of my original note. There is no predefined collation in SQL that matches
this one nor can you get it right by using the collation designer. I know,
I've rebuilt the master on that thing numerous times now. The closest I've
been able to come is this:
Current Production Server:
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
1252 for non-Unicode Data
New (Soon to be I hope) Production Server
Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page
1252 for non-Unicode Data
Notice the accent-sensitivity and the SQL Server Sort order differences.
Also, when you use the collation designer there is no way to specify the SQL
Server Sort order either.
This is the problem I'm trying to address...Skip
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
> You can run select serverproperty('Collation') to get the server collation
> and select databasepropertyex('dbname','Collation') to get the database
> collation. Have you moved a database from one server to another with a
> different collation ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Skip B" <skip@.theborlands.com> wrote in message
> news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>>I am attempting to match the following collation and sort. I cannot seem
>>to make the right choices to prevent collation conflicts in my stored
>>procedures. Can ANYONE please give me some insight on what options to
>>select in order to match this in SQL 2000 Service pack 3? The server that
>>produced the info below (by running sp_helpsort) is a SQL 2000 SP3 server
>>as well.
>> The closest I've been able to come at this point is to match everything
>> except the accent-sensative selection.
>> THANKS IN ADVANCE!
>> Skip
>> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
>> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
>> 1252 for non-Unicode Data
>>
>|||I just installed a named instance and the output of sp_helpsort matches your
current production server and the collation I chose was
SQL_Latin1_General_CP1_CI_AS (the default sql collation during install). The
descriptive collation description during install was (under the SQL
Collations bit) "Dictionary order,case-insensitive,for use with the 1252
Character set"
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Skip B" <skip@.theborlands.com> wrote in message
news:eWw1aLcUFHA.2444@.TK2MSFTNGP10.phx.gbl...
>
> Thanks Jasper. My issue is not identifying the collation. The issue is
> matching the collation on a new sql box to which I will be moving the
> database to. Notice the vagaries in the collation properties at the bottom
> of my original note. There is no predefined collation in SQL that matches
> this one nor can you get it right by using the collation designer. I know,
> I've rebuilt the master on that thing numerous times now. The closest I've
> been able to come is this:
>
> Current Production Server:
> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
> 1252 for non-Unicode Data
> New (Soon to be I hope) Production Server
> Latin1-General, case-insensitive, accent-insensitive,
> kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page
> 1252 for non-Unicode Data
> Notice the accent-sensitivity and the SQL Server Sort order differences.
> Also, when you use the collation designer there is no way to specify the
> SQL Server Sort order either.
> This is the problem I'm trying to address...Skip
>
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
>> You can run select serverproperty('Collation') to get the server
>> collation and select databasepropertyex('dbname','Collation') to get the
>> database collation. Have you moved a database from one server to another
>> with a different collation ?
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Skip B" <skip@.theborlands.com> wrote in message
>> news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>>I am attempting to match the following collation and sort. I cannot seem
>>to make the right choices to prevent collation conflicts in my stored
>>procedures. Can ANYONE please give me some insight on what options to
>>select in order to match this in SQL 2000 Service pack 3? The server that
>>produced the info below (by running sp_helpsort) is a SQL 2000 SP3 server
>>as well.
>> The closest I've been able to come at this point is to match everything
>> except the accent-sensative selection.
>> THANKS IN ADVANCE!
>> Skip
>> Latin1-General, case-insensitive, accent-sensitive,
>> kanatype-insensitive, width-insensitive for Unicode Data, SQL Server
>> Sort Order 52 on Code Page 1252 for non-Unicode Data
>>
>>
>|||Great. I'm rebuilding the master now. I'll let you know shortly. Thanks!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OKFG0WcUFHA.628@.TK2MSFTNGP09.phx.gbl...
>I just installed a named instance and the output of sp_helpsort matches
>your current production server and the collation I chose was
>SQL_Latin1_General_CP1_CI_AS (the default sql collation during install).
>The descriptive collation description during install was (under the SQL
>Collations bit) "Dictionary order,case-insensitive,for use with the 1252
>Character set"
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Skip B" <skip@.theborlands.com> wrote in message
> news:eWw1aLcUFHA.2444@.TK2MSFTNGP10.phx.gbl...
>>
>> Thanks Jasper. My issue is not identifying the collation. The issue is
>> matching the collation on a new sql box to which I will be moving the
>> database to. Notice the vagaries in the collation properties at the
>> bottom of my original note. There is no predefined collation in SQL that
>> matches this one nor can you get it right by using the collation
>> designer. I know, I've rebuilt the master on that thing numerous times
>> now. The closest I've been able to come is this:
>>
>> Current Production Server:
>> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
>> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
>> 1252 for non-Unicode Data
>> New (Soon to be I hope) Production Server
>> Latin1-General, case-insensitive, accent-insensitive,
>> kanatype-insensitive,
>> width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page
>> 1252 for non-Unicode Data
>> Notice the accent-sensitivity and the SQL Server Sort order differences.
>> Also, when you use the collation designer there is no way to specify the
>> SQL Server Sort order either.
>> This is the problem I'm trying to address...Skip
>>
>>
>> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
>> news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
>> You can run select serverproperty('Collation') to get the server
>> collation and select databasepropertyex('dbname','Collation') to get the
>> database collation. Have you moved a database from one server to another
>> with a different collation ?
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Skip B" <skip@.theborlands.com> wrote in message
>> news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>>I am attempting to match the following collation and sort. I cannot seem
>>to make the right choices to prevent collation conflicts in my stored
>>procedures. Can ANYONE please give me some insight on what options to
>>select in order to match this in SQL 2000 Service pack 3? The server
>>that produced the info below (by running sp_helpsort) is a SQL 2000 SP3
>>server as well.
>> The closest I've been able to come at this point is to match everything
>> except the accent-sensative selection.
>> THANKS IN ADVANCE!
>> Skip
>> Latin1-General, case-insensitive, accent-sensitive,
>> kanatype-insensitive, width-insensitive for Unicode Data, SQL Server
>> Sort Order 52 on Code Page 1252 for non-Unicode Data
>>
>>
>>
>|||Done deal. Thanks for your help, Jasper...Skip
"Skip B" <skip@.theborlands.com> wrote in message
news:unFmOjcUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Great. I'm rebuilding the master now. I'll let you know shortly. Thanks!
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OKFG0WcUFHA.628@.TK2MSFTNGP09.phx.gbl...
>>I just installed a named instance and the output of sp_helpsort matches
>>your current production server and the collation I chose was
>>SQL_Latin1_General_CP1_CI_AS (the default sql collation during install).
>>The descriptive collation description during install was (under the SQL
>>Collations bit) "Dictionary order,case-insensitive,for use with the 1252
>>Character set"
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Skip B" <skip@.theborlands.com> wrote in message
>> news:eWw1aLcUFHA.2444@.TK2MSFTNGP10.phx.gbl...
>>
>> Thanks Jasper. My issue is not identifying the collation. The issue is
>> matching the collation on a new sql box to which I will be moving the
>> database to. Notice the vagaries in the collation properties at the
>> bottom of my original note. There is no predefined collation in SQL that
>> matches this one nor can you get it right by using the collation
>> designer. I know, I've rebuilt the master on that thing numerous times
>> now. The closest I've been able to come is this:
>>
>> Current Production Server:
>> Latin1-General, case-insensitive, accent-sensitive,
>> kanatype-insensitive,
>> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code
>> Page
>> 1252 for non-Unicode Data
>> New (Soon to be I hope) Production Server
>> Latin1-General, case-insensitive, accent-insensitive,
>> kanatype-insensitive,
>> width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code
>> Page
>> 1252 for non-Unicode Data
>> Notice the accent-sensitivity and the SQL Server Sort order differences.
>> Also, when you use the collation designer there is no way to specify the
>> SQL Server Sort order either.
>> This is the problem I'm trying to address...Skip
>>
>>
>> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
>> news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
>> You can run select serverproperty('Collation') to get the server
>> collation and select databasepropertyex('dbname','Collation') to get
>> the database collation. Have you moved a database from one server to
>> another with a different collation ?
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Skip B" <skip@.theborlands.com> wrote in message
>> news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>>I am attempting to match the following collation and sort. I cannot
>>seem to make the right choices to prevent collation conflicts in my
>>stored procedures. Can ANYONE please give me some insight on what
>>options to select in order to match this in SQL 2000 Service pack 3?
>>The server that produced the info below (by running sp_helpsort) is a
>>SQL 2000 SP3 server as well.
>> The closest I've been able to come at this point is to match
>> everything except the accent-sensative selection.
>> THANKS IN ADVANCE!
>> Skip
>> Latin1-General, case-insensitive, accent-sensitive,
>> kanatype-insensitive, width-insensitive for Unicode Data, SQL Server
>> Sort Order 52 on Code Page 1252 for non-Unicode Data
>>
>>
>>
>>
>
make the right choices to prevent collation conflicts in my stored
procedures. Can ANYONE please give me some insight on what options to select
in order to match this in SQL 2000 Service pack 3? The server that produced
the info below (by running sp_helpsort) is a SQL 2000 SP3 server as well.
The closest I've been able to come at this point is to match everything
except the accent-sensative selection.
THANKS IN ADVANCE!
Skip
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
1252 for non-Unicode DataYou can run select serverproperty('Collation') to get the server collation
and select databasepropertyex('dbname','Collation') to get the database
collation. Have you moved a database from one server to another with a
different collation ?
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Skip B" <skip@.theborlands.com> wrote in message
news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>I am attempting to match the following collation and sort. I cannot seem to
>make the right choices to prevent collation conflicts in my stored
>procedures. Can ANYONE please give me some insight on what options to
>select in order to match this in SQL 2000 Service pack 3? The server that
>produced the info below (by running sp_helpsort) is a SQL 2000 SP3 server
>as well.
> The closest I've been able to come at this point is to match everything
> except the accent-sensative selection.
> THANKS IN ADVANCE!
> Skip
> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
> 1252 for non-Unicode Data
>|||Thanks Jasper. My issue is not identifying the collation. The issue is
matching the collation on a new sql box to which I will be moving the
database to. Notice the vagaries in the collation properties at the bottom
of my original note. There is no predefined collation in SQL that matches
this one nor can you get it right by using the collation designer. I know,
I've rebuilt the master on that thing numerous times now. The closest I've
been able to come is this:
Current Production Server:
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
1252 for non-Unicode Data
New (Soon to be I hope) Production Server
Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page
1252 for non-Unicode Data
Notice the accent-sensitivity and the SQL Server Sort order differences.
Also, when you use the collation designer there is no way to specify the SQL
Server Sort order either.
This is the problem I'm trying to address...Skip
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
> You can run select serverproperty('Collation') to get the server collation
> and select databasepropertyex('dbname','Collation') to get the database
> collation. Have you moved a database from one server to another with a
> different collation ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Skip B" <skip@.theborlands.com> wrote in message
> news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>>I am attempting to match the following collation and sort. I cannot seem
>>to make the right choices to prevent collation conflicts in my stored
>>procedures. Can ANYONE please give me some insight on what options to
>>select in order to match this in SQL 2000 Service pack 3? The server that
>>produced the info below (by running sp_helpsort) is a SQL 2000 SP3 server
>>as well.
>> The closest I've been able to come at this point is to match everything
>> except the accent-sensative selection.
>> THANKS IN ADVANCE!
>> Skip
>> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
>> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
>> 1252 for non-Unicode Data
>>
>|||I just installed a named instance and the output of sp_helpsort matches your
current production server and the collation I chose was
SQL_Latin1_General_CP1_CI_AS (the default sql collation during install). The
descriptive collation description during install was (under the SQL
Collations bit) "Dictionary order,case-insensitive,for use with the 1252
Character set"
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Skip B" <skip@.theborlands.com> wrote in message
news:eWw1aLcUFHA.2444@.TK2MSFTNGP10.phx.gbl...
>
> Thanks Jasper. My issue is not identifying the collation. The issue is
> matching the collation on a new sql box to which I will be moving the
> database to. Notice the vagaries in the collation properties at the bottom
> of my original note. There is no predefined collation in SQL that matches
> this one nor can you get it right by using the collation designer. I know,
> I've rebuilt the master on that thing numerous times now. The closest I've
> been able to come is this:
>
> Current Production Server:
> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
> 1252 for non-Unicode Data
> New (Soon to be I hope) Production Server
> Latin1-General, case-insensitive, accent-insensitive,
> kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page
> 1252 for non-Unicode Data
> Notice the accent-sensitivity and the SQL Server Sort order differences.
> Also, when you use the collation designer there is no way to specify the
> SQL Server Sort order either.
> This is the problem I'm trying to address...Skip
>
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
>> You can run select serverproperty('Collation') to get the server
>> collation and select databasepropertyex('dbname','Collation') to get the
>> database collation. Have you moved a database from one server to another
>> with a different collation ?
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Skip B" <skip@.theborlands.com> wrote in message
>> news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>>I am attempting to match the following collation and sort. I cannot seem
>>to make the right choices to prevent collation conflicts in my stored
>>procedures. Can ANYONE please give me some insight on what options to
>>select in order to match this in SQL 2000 Service pack 3? The server that
>>produced the info below (by running sp_helpsort) is a SQL 2000 SP3 server
>>as well.
>> The closest I've been able to come at this point is to match everything
>> except the accent-sensative selection.
>> THANKS IN ADVANCE!
>> Skip
>> Latin1-General, case-insensitive, accent-sensitive,
>> kanatype-insensitive, width-insensitive for Unicode Data, SQL Server
>> Sort Order 52 on Code Page 1252 for non-Unicode Data
>>
>>
>|||Great. I'm rebuilding the master now. I'll let you know shortly. Thanks!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OKFG0WcUFHA.628@.TK2MSFTNGP09.phx.gbl...
>I just installed a named instance and the output of sp_helpsort matches
>your current production server and the collation I chose was
>SQL_Latin1_General_CP1_CI_AS (the default sql collation during install).
>The descriptive collation description during install was (under the SQL
>Collations bit) "Dictionary order,case-insensitive,for use with the 1252
>Character set"
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Skip B" <skip@.theborlands.com> wrote in message
> news:eWw1aLcUFHA.2444@.TK2MSFTNGP10.phx.gbl...
>>
>> Thanks Jasper. My issue is not identifying the collation. The issue is
>> matching the collation on a new sql box to which I will be moving the
>> database to. Notice the vagaries in the collation properties at the
>> bottom of my original note. There is no predefined collation in SQL that
>> matches this one nor can you get it right by using the collation
>> designer. I know, I've rebuilt the master on that thing numerous times
>> now. The closest I've been able to come is this:
>>
>> Current Production Server:
>> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
>> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
>> 1252 for non-Unicode Data
>> New (Soon to be I hope) Production Server
>> Latin1-General, case-insensitive, accent-insensitive,
>> kanatype-insensitive,
>> width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page
>> 1252 for non-Unicode Data
>> Notice the accent-sensitivity and the SQL Server Sort order differences.
>> Also, when you use the collation designer there is no way to specify the
>> SQL Server Sort order either.
>> This is the problem I'm trying to address...Skip
>>
>>
>> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
>> news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
>> You can run select serverproperty('Collation') to get the server
>> collation and select databasepropertyex('dbname','Collation') to get the
>> database collation. Have you moved a database from one server to another
>> with a different collation ?
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Skip B" <skip@.theborlands.com> wrote in message
>> news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>>I am attempting to match the following collation and sort. I cannot seem
>>to make the right choices to prevent collation conflicts in my stored
>>procedures. Can ANYONE please give me some insight on what options to
>>select in order to match this in SQL 2000 Service pack 3? The server
>>that produced the info below (by running sp_helpsort) is a SQL 2000 SP3
>>server as well.
>> The closest I've been able to come at this point is to match everything
>> except the accent-sensative selection.
>> THANKS IN ADVANCE!
>> Skip
>> Latin1-General, case-insensitive, accent-sensitive,
>> kanatype-insensitive, width-insensitive for Unicode Data, SQL Server
>> Sort Order 52 on Code Page 1252 for non-Unicode Data
>>
>>
>>
>|||Done deal. Thanks for your help, Jasper...Skip
"Skip B" <skip@.theborlands.com> wrote in message
news:unFmOjcUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Great. I'm rebuilding the master now. I'll let you know shortly. Thanks!
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OKFG0WcUFHA.628@.TK2MSFTNGP09.phx.gbl...
>>I just installed a named instance and the output of sp_helpsort matches
>>your current production server and the collation I chose was
>>SQL_Latin1_General_CP1_CI_AS (the default sql collation during install).
>>The descriptive collation description during install was (under the SQL
>>Collations bit) "Dictionary order,case-insensitive,for use with the 1252
>>Character set"
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Skip B" <skip@.theborlands.com> wrote in message
>> news:eWw1aLcUFHA.2444@.TK2MSFTNGP10.phx.gbl...
>>
>> Thanks Jasper. My issue is not identifying the collation. The issue is
>> matching the collation on a new sql box to which I will be moving the
>> database to. Notice the vagaries in the collation properties at the
>> bottom of my original note. There is no predefined collation in SQL that
>> matches this one nor can you get it right by using the collation
>> designer. I know, I've rebuilt the master on that thing numerous times
>> now. The closest I've been able to come is this:
>>
>> Current Production Server:
>> Latin1-General, case-insensitive, accent-sensitive,
>> kanatype-insensitive,
>> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code
>> Page
>> 1252 for non-Unicode Data
>> New (Soon to be I hope) Production Server
>> Latin1-General, case-insensitive, accent-insensitive,
>> kanatype-insensitive,
>> width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code
>> Page
>> 1252 for non-Unicode Data
>> Notice the accent-sensitivity and the SQL Server Sort order differences.
>> Also, when you use the collation designer there is no way to specify the
>> SQL Server Sort order either.
>> This is the problem I'm trying to address...Skip
>>
>>
>> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
>> news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
>> You can run select serverproperty('Collation') to get the server
>> collation and select databasepropertyex('dbname','Collation') to get
>> the database collation. Have you moved a database from one server to
>> another with a different collation ?
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Skip B" <skip@.theborlands.com> wrote in message
>> news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>>I am attempting to match the following collation and sort. I cannot
>>seem to make the right choices to prevent collation conflicts in my
>>stored procedures. Can ANYONE please give me some insight on what
>>options to select in order to match this in SQL 2000 Service pack 3?
>>The server that produced the info below (by running sp_helpsort) is a
>>SQL 2000 SP3 server as well.
>> The closest I've been able to come at this point is to match
>> everything except the accent-sensative selection.
>> THANKS IN ADVANCE!
>> Skip
>> Latin1-General, case-insensitive, accent-sensitive,
>> kanatype-insensitive, width-insensitive for Unicode Data, SQL Server
>> Sort Order 52 on Code Page 1252 for non-Unicode Data
>>
>>
>>
>>
>
Matching Collation and Sort
I am attempting to match the following collation and sort. I cannot seem to
make the right choices to prevent collation conflicts in my stored
procedures. Can ANYONE please give me some insight on what options to select
in order to match this in SQL 2000 Service pack 3? The server that produced
the info below (by running sp_helpsort) is a SQL 2000 SP3 server as well.
The closest I've been able to come at this point is to match everything
except the accent-sensative selection.
THANKS IN ADVANCE!
Skip
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
1252 for non-Unicode Data
You can run select serverproperty('Collation') to get the server collation
and select databasepropertyex('dbname','Collation') to get the database
collation. Have you moved a database from one server to another with a
different collation ?
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Skip B" <skip@.theborlands.com> wrote in message
news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>I am attempting to match the following collation and sort. I cannot seem to
>make the right choices to prevent collation conflicts in my stored
>procedures. Can ANYONE please give me some insight on what options to
>select in order to match this in SQL 2000 Service pack 3? The server that
>produced the info below (by running sp_helpsort) is a SQL 2000 SP3 server
>as well.
> The closest I've been able to come at this point is to match everything
> except the accent-sensative selection.
> THANKS IN ADVANCE!
> Skip
> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
> 1252 for non-Unicode Data
>
|||Thanks Jasper. My issue is not identifying the collation. The issue is
matching the collation on a new sql box to which I will be moving the
database to. Notice the vagaries in the collation properties at the bottom
of my original note. There is no predefined collation in SQL that matches
this one nor can you get it right by using the collation designer. I know,
I've rebuilt the master on that thing numerous times now. The closest I've
been able to come is this:
Current Production Server:
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
1252 for non-Unicode Data
New (Soon to be I hope) Production Server
Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page
1252 for non-Unicode Data
Notice the accent-sensitivity and the SQL Server Sort order differences.
Also, when you use the collation designer there is no way to specify the SQL
Server Sort order either.
This is the problem I'm trying to address...Skip
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
> You can run select serverproperty('Collation') to get the server collation
> and select databasepropertyex('dbname','Collation') to get the database
> collation. Have you moved a database from one server to another with a
> different collation ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Skip B" <skip@.theborlands.com> wrote in message
> news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>
|||I just installed a named instance and the output of sp_helpsort matches your
current production server and the collation I chose was
SQL_Latin1_General_CP1_CI_AS (the default sql collation during install). The
descriptive collation description during install was (under the SQL
Collations bit) "Dictionary order,case-insensitive,for use with the 1252
Character set"
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Skip B" <skip@.theborlands.com> wrote in message
news:eWw1aLcUFHA.2444@.TK2MSFTNGP10.phx.gbl...
>
> Thanks Jasper. My issue is not identifying the collation. The issue is
> matching the collation on a new sql box to which I will be moving the
> database to. Notice the vagaries in the collation properties at the bottom
> of my original note. There is no predefined collation in SQL that matches
> this one nor can you get it right by using the collation designer. I know,
> I've rebuilt the master on that thing numerous times now. The closest I've
> been able to come is this:
>
> Current Production Server:
> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
> 1252 for non-Unicode Data
> New (Soon to be I hope) Production Server
> Latin1-General, case-insensitive, accent-insensitive,
> kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page
> 1252 for non-Unicode Data
> Notice the accent-sensitivity and the SQL Server Sort order differences.
> Also, when you use the collation designer there is no way to specify the
> SQL Server Sort order either.
> This is the problem I'm trying to address...Skip
>
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
>
|||Great. I'm rebuilding the master now. I'll let you know shortly. Thanks!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OKFG0WcUFHA.628@.TK2MSFTNGP09.phx.gbl...
>I just installed a named instance and the output of sp_helpsort matches
>your current production server and the collation I chose was
>SQL_Latin1_General_CP1_CI_AS (the default sql collation during install).
>The descriptive collation description during install was (under the SQL
>Collations bit) "Dictionary order,case-insensitive,for use with the 1252
>Character set"
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Skip B" <skip@.theborlands.com> wrote in message
> news:eWw1aLcUFHA.2444@.TK2MSFTNGP10.phx.gbl...
>
|||Done deal. Thanks for your help, Jasper...Skip
"Skip B" <skip@.theborlands.com> wrote in message
news:unFmOjcUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Great. I'm rebuilding the master now. I'll let you know shortly. Thanks!
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OKFG0WcUFHA.628@.TK2MSFTNGP09.phx.gbl...
>
make the right choices to prevent collation conflicts in my stored
procedures. Can ANYONE please give me some insight on what options to select
in order to match this in SQL 2000 Service pack 3? The server that produced
the info below (by running sp_helpsort) is a SQL 2000 SP3 server as well.
The closest I've been able to come at this point is to match everything
except the accent-sensative selection.
THANKS IN ADVANCE!
Skip
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
1252 for non-Unicode Data
You can run select serverproperty('Collation') to get the server collation
and select databasepropertyex('dbname','Collation') to get the database
collation. Have you moved a database from one server to another with a
different collation ?
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Skip B" <skip@.theborlands.com> wrote in message
news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>I am attempting to match the following collation and sort. I cannot seem to
>make the right choices to prevent collation conflicts in my stored
>procedures. Can ANYONE please give me some insight on what options to
>select in order to match this in SQL 2000 Service pack 3? The server that
>produced the info below (by running sp_helpsort) is a SQL 2000 SP3 server
>as well.
> The closest I've been able to come at this point is to match everything
> except the accent-sensative selection.
> THANKS IN ADVANCE!
> Skip
> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
> 1252 for non-Unicode Data
>
|||Thanks Jasper. My issue is not identifying the collation. The issue is
matching the collation on a new sql box to which I will be moving the
database to. Notice the vagaries in the collation properties at the bottom
of my original note. There is no predefined collation in SQL that matches
this one nor can you get it right by using the collation designer. I know,
I've rebuilt the master on that thing numerous times now. The closest I've
been able to come is this:
Current Production Server:
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
1252 for non-Unicode Data
New (Soon to be I hope) Production Server
Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page
1252 for non-Unicode Data
Notice the accent-sensitivity and the SQL Server Sort order differences.
Also, when you use the collation designer there is no way to specify the SQL
Server Sort order either.
This is the problem I'm trying to address...Skip
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
> You can run select serverproperty('Collation') to get the server collation
> and select databasepropertyex('dbname','Collation') to get the database
> collation. Have you moved a database from one server to another with a
> different collation ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Skip B" <skip@.theborlands.com> wrote in message
> news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>
|||I just installed a named instance and the output of sp_helpsort matches your
current production server and the collation I chose was
SQL_Latin1_General_CP1_CI_AS (the default sql collation during install). The
descriptive collation description during install was (under the SQL
Collations bit) "Dictionary order,case-insensitive,for use with the 1252
Character set"
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Skip B" <skip@.theborlands.com> wrote in message
news:eWw1aLcUFHA.2444@.TK2MSFTNGP10.phx.gbl...
>
> Thanks Jasper. My issue is not identifying the collation. The issue is
> matching the collation on a new sql box to which I will be moving the
> database to. Notice the vagaries in the collation properties at the bottom
> of my original note. There is no predefined collation in SQL that matches
> this one nor can you get it right by using the collation designer. I know,
> I've rebuilt the master on that thing numerous times now. The closest I've
> been able to come is this:
>
> Current Production Server:
> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
> 1252 for non-Unicode Data
> New (Soon to be I hope) Production Server
> Latin1-General, case-insensitive, accent-insensitive,
> kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page
> 1252 for non-Unicode Data
> Notice the accent-sensitivity and the SQL Server Sort order differences.
> Also, when you use the collation designer there is no way to specify the
> SQL Server Sort order either.
> This is the problem I'm trying to address...Skip
>
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
>
|||Great. I'm rebuilding the master now. I'll let you know shortly. Thanks!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OKFG0WcUFHA.628@.TK2MSFTNGP09.phx.gbl...
>I just installed a named instance and the output of sp_helpsort matches
>your current production server and the collation I chose was
>SQL_Latin1_General_CP1_CI_AS (the default sql collation during install).
>The descriptive collation description during install was (under the SQL
>Collations bit) "Dictionary order,case-insensitive,for use with the 1252
>Character set"
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Skip B" <skip@.theborlands.com> wrote in message
> news:eWw1aLcUFHA.2444@.TK2MSFTNGP10.phx.gbl...
>
|||Done deal. Thanks for your help, Jasper...Skip
"Skip B" <skip@.theborlands.com> wrote in message
news:unFmOjcUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Great. I'm rebuilding the master now. I'll let you know shortly. Thanks!
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OKFG0WcUFHA.628@.TK2MSFTNGP09.phx.gbl...
>
Matching Collation and Sort
I am attempting to match the following collation and sort. I cannot seem to
make the right choices to prevent collation conflicts in my stored
procedures. Can ANYONE please give me some insight on what options to select
in order to match this in SQL 2000 Service pack 3? The server that produced
the info below (by running sp_helpsort) is a SQL 2000 SP3 server as well.
The closest I've been able to come at this point is to match everything
except the accent-sensative selection.
THANKS IN ADVANCE!
Skip
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
1252 for non-Unicode DataYou can run select serverproperty('Collation') to get the server collation
and select databasepropertyex('dbname','Collation')
to get the database
collation. Have you moved a database from one server to another with a
different collation ?
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Skip B" <skip@.theborlands.com> wrote in message
news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>I am attempting to match the following collation and sort. I cannot seem to
>make the right choices to prevent collation conflicts in my stored
>procedures. Can ANYONE please give me some insight on what options to
>select in order to match this in SQL 2000 Service pack 3? The server that
>produced the info below (by running sp_helpsort) is a SQL 2000 SP3 server
>as well.
> The closest I've been able to come at this point is to match everything
> except the accent-sensative selection.
> THANKS IN ADVANCE!
> Skip
> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
> 1252 for non-Unicode Data
>|||Thanks Jasper. My issue is not identifying the collation. The issue is
matching the collation on a new sql box to which I will be moving the
database to. Notice the vagaries in the collation properties at the bottom
of my original note. There is no predefined collation in SQL that matches
this one nor can you get it right by using the collation designer. I know,
I've rebuilt the master on that thing numerous times now. The closest I've
been able to come is this:
Current Production Server:
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
1252 for non-Unicode Data
New (Soon to be I hope) Production Server
Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page
1252 for non-Unicode Data
Notice the accent-sensitivity and the SQL Server Sort order differences.
Also, when you use the collation designer there is no way to specify the SQL
Server Sort order either.
This is the problem I'm trying to address...Skip
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
> You can run select serverproperty('Collation') to get the server collation
> and select databasepropertyex('dbname','Collation')
to get the database
> collation. Have you moved a database from one server to another with a
> different collation ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Skip B" <skip@.theborlands.com> wrote in message
> news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>|||I just installed a named instance and the output of sp_helpsort matches your
current production server and the collation I chose was
SQL_Latin1_General_CP1_CI_AS (the default sql collation during install). The
descriptive collation description during install was (under the SQL
Collations bit) "Dictionary order,case-insensitive,for use with the 1252
Character set"
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Skip B" <skip@.theborlands.com> wrote in message
news:eWw1aLcUFHA.2444@.TK2MSFTNGP10.phx.gbl...
>
> Thanks Jasper. My issue is not identifying the collation. The issue is
> matching the collation on a new sql box to which I will be moving the
> database to. Notice the vagaries in the collation properties at the bottom
> of my original note. There is no predefined collation in SQL that matches
> this one nor can you get it right by using the collation designer. I know,
> I've rebuilt the master on that thing numerous times now. The closest I've
> been able to come is this:
>
> Current Production Server:
> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
> 1252 for non-Unicode Data
> New (Soon to be I hope) Production Server
> Latin1-General, case-insensitive, accent-insensitive,
> kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page
> 1252 for non-Unicode Data
> Notice the accent-sensitivity and the SQL Server Sort order differences.
> Also, when you use the collation designer there is no way to specify the
> SQL Server Sort order either.
> This is the problem I'm trying to address...Skip
>
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
>|||Great. I'm rebuilding the master now. I'll let you know shortly. Thanks!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OKFG0WcUFHA.628@.TK2MSFTNGP09.phx.gbl...
>I just installed a named instance and the output of sp_helpsort matches
>your current production server and the collation I chose was
>SQL_Latin1_General_CP1_CI_AS (the default sql collation during install).
>The descriptive collation description during install was (under the SQL
>Collations bit) "Dictionary order,case-insensitive,for use with the 1252
>Character set"
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Skip B" <skip@.theborlands.com> wrote in message
> news:eWw1aLcUFHA.2444@.TK2MSFTNGP10.phx.gbl...
>|||Done deal. Thanks for your help, Jasper...Skip
"Skip B" <skip@.theborlands.com> wrote in message
news:unFmOjcUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Great. I'm rebuilding the master now. I'll let you know shortly. Thanks!
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OKFG0WcUFHA.628@.TK2MSFTNGP09.phx.gbl...
>
make the right choices to prevent collation conflicts in my stored
procedures. Can ANYONE please give me some insight on what options to select
in order to match this in SQL 2000 Service pack 3? The server that produced
the info below (by running sp_helpsort) is a SQL 2000 SP3 server as well.
The closest I've been able to come at this point is to match everything
except the accent-sensative selection.
THANKS IN ADVANCE!
Skip
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
1252 for non-Unicode DataYou can run select serverproperty('Collation') to get the server collation
and select databasepropertyex('dbname','Collation')
to get the database
collation. Have you moved a database from one server to another with a
different collation ?
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Skip B" <skip@.theborlands.com> wrote in message
news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>I am attempting to match the following collation and sort. I cannot seem to
>make the right choices to prevent collation conflicts in my stored
>procedures. Can ANYONE please give me some insight on what options to
>select in order to match this in SQL 2000 Service pack 3? The server that
>produced the info below (by running sp_helpsort) is a SQL 2000 SP3 server
>as well.
> The closest I've been able to come at this point is to match everything
> except the accent-sensative selection.
> THANKS IN ADVANCE!
> Skip
> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
> 1252 for non-Unicode Data
>|||Thanks Jasper. My issue is not identifying the collation. The issue is
matching the collation on a new sql box to which I will be moving the
database to. Notice the vagaries in the collation properties at the bottom
of my original note. There is no predefined collation in SQL that matches
this one nor can you get it right by using the collation designer. I know,
I've rebuilt the master on that thing numerous times now. The closest I've
been able to come is this:
Current Production Server:
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
1252 for non-Unicode Data
New (Soon to be I hope) Production Server
Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page
1252 for non-Unicode Data
Notice the accent-sensitivity and the SQL Server Sort order differences.
Also, when you use the collation designer there is no way to specify the SQL
Server Sort order either.
This is the problem I'm trying to address...Skip
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
> You can run select serverproperty('Collation') to get the server collation
> and select databasepropertyex('dbname','Collation')
to get the database
> collation. Have you moved a database from one server to another with a
> different collation ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Skip B" <skip@.theborlands.com> wrote in message
> news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>|||I just installed a named instance and the output of sp_helpsort matches your
current production server and the collation I chose was
SQL_Latin1_General_CP1_CI_AS (the default sql collation during install). The
descriptive collation description during install was (under the SQL
Collations bit) "Dictionary order,case-insensitive,for use with the 1252
Character set"
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Skip B" <skip@.theborlands.com> wrote in message
news:eWw1aLcUFHA.2444@.TK2MSFTNGP10.phx.gbl...
>
> Thanks Jasper. My issue is not identifying the collation. The issue is
> matching the collation on a new sql box to which I will be moving the
> database to. Notice the vagaries in the collation properties at the bottom
> of my original note. There is no predefined collation in SQL that matches
> this one nor can you get it right by using the collation designer. I know,
> I've rebuilt the master on that thing numerous times now. The closest I've
> been able to come is this:
>
> Current Production Server:
> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
> 1252 for non-Unicode Data
> New (Soon to be I hope) Production Server
> Latin1-General, case-insensitive, accent-insensitive,
> kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page
> 1252 for non-Unicode Data
> Notice the accent-sensitivity and the SQL Server Sort order differences.
> Also, when you use the collation designer there is no way to specify the
> SQL Server Sort order either.
> This is the problem I'm trying to address...Skip
>
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
>|||Great. I'm rebuilding the master now. I'll let you know shortly. Thanks!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OKFG0WcUFHA.628@.TK2MSFTNGP09.phx.gbl...
>I just installed a named instance and the output of sp_helpsort matches
>your current production server and the collation I chose was
>SQL_Latin1_General_CP1_CI_AS (the default sql collation during install).
>The descriptive collation description during install was (under the SQL
>Collations bit) "Dictionary order,case-insensitive,for use with the 1252
>Character set"
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Skip B" <skip@.theborlands.com> wrote in message
> news:eWw1aLcUFHA.2444@.TK2MSFTNGP10.phx.gbl...
>|||Done deal. Thanks for your help, Jasper...Skip
"Skip B" <skip@.theborlands.com> wrote in message
news:unFmOjcUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Great. I'm rebuilding the master now. I'll let you know shortly. Thanks!
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OKFG0WcUFHA.628@.TK2MSFTNGP09.phx.gbl...
>
Match timed events
/*
Anyone?
An Object is. An object has an owner.
Events happen to an object at given times
I would like to list out all events for an object
and show who was the owner of it at that time.
However my mind is a blank!
My legacy data structure looks something like this
*/
CREATE TABLE EVENT (
EVENTID Int IDENTITY(1,1) PRIMARY KEY,
OBJECTID CHAR(4),
EVENTDATE CHAR(8)
)
CREATE TABLE OBJECTOWNER (
OBJECTID CHAR(4) NOT NULL,
OWNERID CHAR(4) NOT NULL,
OWNERFROM CHAR(8) NOT NULL
)
ALTER TABLE [OBJECTOWNER] WITH NOCHECK ADD
CONSTRAINT [PK_OBJECTOWNER] PRIMARY KEY CLUSTERED
(
[OBJECTID],
[OWNERID],
[OWNERFROM]
)
INSERT INTO EVENT (OBJECTID, EVENTDATE) VALUES ('1234', '19920101')
INSERT INTO EVENT (OBJECTID, EVENTDATE) VALUES ('1234', '20030601')
INSERT INTO EVENT (OBJECTID, EVENTDATE) VALUES ('1234', '20060122')
INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
'A111', '19801201')
INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
'A692', '19921201')
INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
'B386', '20011201')
INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
'F279', '20041201')
INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
'F111', '20060310')
/*I presume I need the MAX OWNERFROM value that less then or equal to the
EventDate, along the lines of*/
SELECT TOP 1 MAX(OwnerFrom) As 'Most Recent owner date', OwnerID AS 'Most
recent Owner' from ObjectOwner
WHERE ObjectID = '1234' AND OwnerFrom <= '19971207' group by OwnerID
/*Which gives the owner of the object for a given date,
and I am sure I need to join in a select returning the max <=...
I could do it by inserting into a temp table and updating
but I though there must be a more elegant solution
The listing I'd like to retrive is
EVENTID, OBJECTID, EVENTDATE, OWNERID,
EVENTID OBJECTID EVENTDATE OWNERID
-- -- -- --
1 1234 19920101 A111
2 1234 20050601 B386
3 1234 20060122 F279
Any help would be greatly appreciated.
Cheers!
Simon
*/Different ways...
One with correlated subqueries in SELECT clause :
SELECT EVENTID, OBJECTID, EVENTDATE,
(SELECT OWNERID
FROM OBJECTOWNER O
WHERE O.OBJECTID = E.OBJECTID
AND OWNERFROM = (SELECT MAX(OWNERFROM)
FROM OBJECTOWNER O2
WHERE OWNERFROM <= E.EVENTDATE
AND O2.OBJECTID = E.OBJECTID)) AS
OWNERID_AT_TIME_EVENT
FROM EVENT E
A +
Simon a crit :
> /*
> Anyone?
> An Object is. An object has an owner.
> Events happen to an object at given times
> I would like to list out all events for an object
> and show who was the owner of it at that time.
> However my mind is a blank!
> My legacy data structure looks something like this
> */
>
> CREATE TABLE EVENT (
> EVENTID Int IDENTITY(1,1) PRIMARY KEY,
> OBJECTID CHAR(4),
> EVENTDATE CHAR(8)
> )
> CREATE TABLE OBJECTOWNER (
> OBJECTID CHAR(4) NOT NULL,
> OWNERID CHAR(4) NOT NULL,
> OWNERFROM CHAR(8) NOT NULL
> )
> ALTER TABLE [OBJECTOWNER] WITH NOCHECK ADD
> CONSTRAINT [PK_OBJECTOWNER] PRIMARY KEY CLUSTERED
> (
> [OBJECTID],
> [OWNERID],
> [OWNERFROM]
> )
> INSERT INTO EVENT (OBJECTID, EVENTDATE) VALUES ('1234', '19920101')
> INSERT INTO EVENT (OBJECTID, EVENTDATE) VALUES ('1234', '20030601')
> INSERT INTO EVENT (OBJECTID, EVENTDATE) VALUES ('1234', '20060122')
> INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
> 'A111', '19801201')
> INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
> 'A692', '19921201')
> INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
> 'B386', '20011201')
> INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
> 'F279', '20041201')
> INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
> 'F111', '20060310')
>
> /*I presume I need the MAX OWNERFROM value that less then or equal to the
> EventDate, along the lines of*/
> SELECT TOP 1 MAX(OwnerFrom) As 'Most Recent owner date', OwnerID AS 'Most
> recent Owner' from ObjectOwner
> WHERE ObjectID = '1234' AND OwnerFrom <= '19971207' group by OwnerID
> /*Which gives the owner of the object for a given date,
> and I am sure I need to join in a select returning the max <=...
> I could do it by inserting into a temp table and updating
> but I though there must be a more elegant solution
>
> The listing I'd like to retrive is
> EVENTID, OBJECTID, EVENTDATE, OWNERID,
> EVENTID OBJECTID EVENTDATE OWNERID
> -- -- -- --
> 1 1234 19920101 A111
> 2 1234 20050601 B386
> 3 1234 20060122 F279
> Any help would be greatly appreciated.
> Cheers!
> Simon
> */
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||SQLpro [MVP] wrote:
> Different ways...
> One with correlated subqueries in SELECT clause :
>
> SELECT EVENTID, OBJECTID, EVENTDATE,
> (SELECT OWNERID
> FROM OBJECTOWNER O
> WHERE O.OBJECTID = E.OBJECTID
> AND OWNERFROM = (SELECT MAX(OWNERFROM)
> FROM OBJECTOWNER O2
> WHERE OWNERFROM <= E.EVENTDATE
> AND O2.OBJECTID = E.OBJECTID)) AS
> OWNERID_AT_TIME_EVENT
> FROM EVENT E
> A +
Much appreciated, that is exactly what I needed. I ahd just the (SELECT
MAX(OWNERFROM)
> FROM OBJECTOWNER O2
> WHERE OWNERFROM <= E.EVENTDATE
> AND O2.OBJECTID = E.OBJECTID))
JOINed as was getting nowhere fast.
Cheers!
Anyone?
An Object is. An object has an owner.
Events happen to an object at given times
I would like to list out all events for an object
and show who was the owner of it at that time.
However my mind is a blank!
My legacy data structure looks something like this
*/
CREATE TABLE EVENT (
EVENTID Int IDENTITY(1,1) PRIMARY KEY,
OBJECTID CHAR(4),
EVENTDATE CHAR(8)
)
CREATE TABLE OBJECTOWNER (
OBJECTID CHAR(4) NOT NULL,
OWNERID CHAR(4) NOT NULL,
OWNERFROM CHAR(8) NOT NULL
)
ALTER TABLE [OBJECTOWNER] WITH NOCHECK ADD
CONSTRAINT [PK_OBJECTOWNER] PRIMARY KEY CLUSTERED
(
[OBJECTID],
[OWNERID],
[OWNERFROM]
)
INSERT INTO EVENT (OBJECTID, EVENTDATE) VALUES ('1234', '19920101')
INSERT INTO EVENT (OBJECTID, EVENTDATE) VALUES ('1234', '20030601')
INSERT INTO EVENT (OBJECTID, EVENTDATE) VALUES ('1234', '20060122')
INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
'A111', '19801201')
INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
'A692', '19921201')
INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
'B386', '20011201')
INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
'F279', '20041201')
INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
'F111', '20060310')
/*I presume I need the MAX OWNERFROM value that less then or equal to the
EventDate, along the lines of*/
SELECT TOP 1 MAX(OwnerFrom) As 'Most Recent owner date', OwnerID AS 'Most
recent Owner' from ObjectOwner
WHERE ObjectID = '1234' AND OwnerFrom <= '19971207' group by OwnerID
/*Which gives the owner of the object for a given date,
and I am sure I need to join in a select returning the max <=...
I could do it by inserting into a temp table and updating
but I though there must be a more elegant solution
The listing I'd like to retrive is
EVENTID, OBJECTID, EVENTDATE, OWNERID,
EVENTID OBJECTID EVENTDATE OWNERID
-- -- -- --
1 1234 19920101 A111
2 1234 20050601 B386
3 1234 20060122 F279
Any help would be greatly appreciated.
Cheers!
Simon
*/Different ways...
One with correlated subqueries in SELECT clause :
SELECT EVENTID, OBJECTID, EVENTDATE,
(SELECT OWNERID
FROM OBJECTOWNER O
WHERE O.OBJECTID = E.OBJECTID
AND OWNERFROM = (SELECT MAX(OWNERFROM)
FROM OBJECTOWNER O2
WHERE OWNERFROM <= E.EVENTDATE
AND O2.OBJECTID = E.OBJECTID)) AS
OWNERID_AT_TIME_EVENT
FROM EVENT E
A +
Simon a crit :
> /*
> Anyone?
> An Object is. An object has an owner.
> Events happen to an object at given times
> I would like to list out all events for an object
> and show who was the owner of it at that time.
> However my mind is a blank!
> My legacy data structure looks something like this
> */
>
> CREATE TABLE EVENT (
> EVENTID Int IDENTITY(1,1) PRIMARY KEY,
> OBJECTID CHAR(4),
> EVENTDATE CHAR(8)
> )
> CREATE TABLE OBJECTOWNER (
> OBJECTID CHAR(4) NOT NULL,
> OWNERID CHAR(4) NOT NULL,
> OWNERFROM CHAR(8) NOT NULL
> )
> ALTER TABLE [OBJECTOWNER] WITH NOCHECK ADD
> CONSTRAINT [PK_OBJECTOWNER] PRIMARY KEY CLUSTERED
> (
> [OBJECTID],
> [OWNERID],
> [OWNERFROM]
> )
> INSERT INTO EVENT (OBJECTID, EVENTDATE) VALUES ('1234', '19920101')
> INSERT INTO EVENT (OBJECTID, EVENTDATE) VALUES ('1234', '20030601')
> INSERT INTO EVENT (OBJECTID, EVENTDATE) VALUES ('1234', '20060122')
> INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
> 'A111', '19801201')
> INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
> 'A692', '19921201')
> INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
> 'B386', '20011201')
> INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
> 'F279', '20041201')
> INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
> 'F111', '20060310')
>
> /*I presume I need the MAX OWNERFROM value that less then or equal to the
> EventDate, along the lines of*/
> SELECT TOP 1 MAX(OwnerFrom) As 'Most Recent owner date', OwnerID AS 'Most
> recent Owner' from ObjectOwner
> WHERE ObjectID = '1234' AND OwnerFrom <= '19971207' group by OwnerID
> /*Which gives the owner of the object for a given date,
> and I am sure I need to join in a select returning the max <=...
> I could do it by inserting into a temp table and updating
> but I though there must be a more elegant solution
>
> The listing I'd like to retrive is
> EVENTID, OBJECTID, EVENTDATE, OWNERID,
> EVENTID OBJECTID EVENTDATE OWNERID
> -- -- -- --
> 1 1234 19920101 A111
> 2 1234 20050601 B386
> 3 1234 20060122 F279
> Any help would be greatly appreciated.
> Cheers!
> Simon
> */
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||SQLpro [MVP] wrote:
> Different ways...
> One with correlated subqueries in SELECT clause :
>
> SELECT EVENTID, OBJECTID, EVENTDATE,
> (SELECT OWNERID
> FROM OBJECTOWNER O
> WHERE O.OBJECTID = E.OBJECTID
> AND OWNERFROM = (SELECT MAX(OWNERFROM)
> FROM OBJECTOWNER O2
> WHERE OWNERFROM <= E.EVENTDATE
> AND O2.OBJECTID = E.OBJECTID)) AS
> OWNERID_AT_TIME_EVENT
> FROM EVENT E
> A +
Much appreciated, that is exactly what I needed. I ahd just the (SELECT
MAX(OWNERFROM)
> FROM OBJECTOWNER O2
> WHERE OWNERFROM <= E.EVENTDATE
> AND O2.OBJECTID = E.OBJECTID))
JOINed as was getting nowhere fast.
Cheers!
Match numbers
I have a problem using the sql builder since i am trying to match a list of 5 numbers in TAble A with other 5 numbers in Table B
All i want to know is when there will be a match it will indicate in the report.
Example code of what i tried:
SELECT DBA.*,DBB.*
FROM (DBA INNER JOIN
DBB ON DBA.[ 1] = DBB.[ 1] OR DBA.[ 1] = DBB.[ 2] OR DBA.[ 1] =DBB.[ 3] OR
DBA.[ 1] = DBB.[ 4] OR DBA.[ 1] = DBB.[ 5] OR DBA.[ 2] = DBB.[ 1] OR
DBA.[ 2] = DBB.[ 1] OR DBA.[ 2] = DBB.[ 2] OR DBA.[ 2] = DBB.[ 3] OR
DBA.[ 2] = DBB.[ 4] OR DBA.[ 2] = DBB.[ 5] OR DBA.[ 3] = DBB.[ 1] OR
DBA.[ 3] = DBB.[ 2] OR DBA.[ 3] = DBB.[ 3] OR DBA.[ 3] = DBB.[ 4] OR
DBA.[ 3] = DBB.[ 5] OR DBA.[ 4] = DBB.[ 1] OR DBA.[ 4] = DBB.[ 2] OR
DBA.[ 4] = DBB.[ 3] OR DBA.[ 4] = DBB.[ 4] OR DBA.[ 4] = DBB.[ 5] OR
DBA.[ 5] = DBB.[ 1] OR DBA.[ 5] = DBB.[ 2] OR DBA.[ 5] = DBB.[ 3] OR
DBA.[ 5] = DBB.[ 4] OR DBA.[ 5] = DBB.[ 5])
I did like this and the result was not like i want since i had errors in the report and there where tables with the same ID number.
the error was :
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
Then i turned EnforceConstraints = False
When i turned it to false it worked but the results where not in the same line as i would like them to be they where seperated with the ID and Date
Example
Date ID Numbers
| 11/16/2006 08:15:20 PM | 6 | 13 | 26 | 29 | 42 | 56 | ||
| 11/16/2006 08:18:20 PM | 6 | 13 | 26 | 29 | 42 | 56 |
Any help please?
Thanks
have u tried not to use SELECT * but select only the values you need seperatly and then group the result?|||Thanks for your reply.
I tried tried to remove date and ID number but still the same the only differnce it will not give me the error of constraints.
Let me give u an example what i have in the database
ID 1 2 3 4 5
date 1 2 3 4 5
What I need is that ID6 and ID1 will display in the same line
The result of my query is this:
Thanks
based on your example, try
SELECT DISTINCT DBA.*
...
WIth DISTINCT it worked from the SQL but when i try to access from VB2005 Reportviewer i cannot see the DBB since i only manage to see what i selected DBA.
Is there a way how i can add more than one DATASET in a table ?
I can see others DATASETS in the expression menu but if i try to use those the expression will be
=Sum(Fields!1.Value, "DBB")
Would it be possible that i add also DBB in the FIELDS Menu and use multiple datasets?
At the moment i can see only Fields(DBA)
Thanks in advance
|||
I believe you can't. You can try using multiple tables though.
In terms of the problem you are trying to solve, I am thoroughly confused. Any chance you could go back clarify what you are trying to achieve. Can you explain the colour coding in your previous posts?
|||What I am trying to achieve is very simple I thought.
The color coding means that there is a match from list A to List B with 1 or all 5 numbers.
I also would like to know if there is a match with 1 number, 2 number etc.
The Red code means that there was a match
Example :
Date ID Numbers
| 11/16/2006 08:15:20 PM | 6 | 13 | 26 | 29 | 42 | 56 |
|
| 11/16/2006 08:18:20 PM | 6 | 13 | 26 | 29 | 42 | 56 |
|
Means that the first 13, and 26 = from date
11/16/2006 8:15:20 PM | 10 | 13 | 17 | 22 | 26 |
And the second row 29,42,56 = from date
11/16/2006 8:18:20 PM | 13 | 26 | 29 | 42 | 56 |
What I really need is that there will not be 2 rows and the date is not important so I can remove it, it is there just for reference.
Thanks
|||To help me understand this, the closest thing I can compare this is a lottery. Let's think of DBA as user selected numbers and DBB as the draw dates and results.
You would like for every set of numbers in DBA to be compared with the draw results in DBB and any hits to be highlighted in red.
The results in your example above show me which set of numbers had a hit on which date and the highlight shows which number from that set was a match. If you condense these onto 1 row and remove the date then you will only know which of that set of numbers has ever had a hit. Is this what you need. Where else do you need to display data from DBB and why?
It feels like you are not showing the full example of what you are trying to do in your report.
|||Yes this is like a lottery project for school.
I do not have nothing to show more 
You would like for every set of numbers in DBA to be compared with the draw results in DBB and any hits to be highlighted in re
Yes correct thats what I need.
In this Report i would like to display ID number . no1 , no2 , no 3 , no 4, no 5 which had a hit and if possible, if all 5 numbers where guessed i will write a winner.
Thanks once again.
|||Any ideas please how can I do
Thanks
|||Bear with me. I'm on a project. I'll try it in a dummy database and create the RDL and post it when I'm done.|||Thanks for your reply I will wait for your solution|||I'm just playing around at the moment. Here's some output from a matrix report I knocked up. I know it's not exactly what you asked for but tell me what you think anyway. I haven't got the "Winner" text showing yet but I'll need to revert to using a table for that. Basically my thinking is that for every draw you want to see how your chosen numbers did. The assumption here is that the same numbers were played for each draw.
1
2
3
4
5
1
18
23
47
84
90
1
10
15
16
17
19
2
25
36
42
58
69
3
90
47
84
18
23
4
18
23
47
84
90
5
40
50
60
70
80
6
13
26
29
42
56
7
61
62
63
64
65
2
10/16/2006 08:10:20 PM
70
71
72
73
74
1
10
15
16
17
19
2
25
36
42
58
69
3
90
47
84
18
23
4
18
23
47
84
90
5
40
50
60
70
80
6
13
26
29
42
56
7
61
62
63
64
65
3
11/16/2006 08:15:20 PM
10
13
17
22
26
1
10
15
16
17
19
2
25
36
42
58
69
3
90
47
84
18
23
4
18
23
47
84
90
5
40
50
60
70
80
6
13
26
29
42
56
7
61
62
63
64
65
4
11/16/2006 08:18:20 PM
18
19
29
42
56
1
10
15
16
17
19
2
25
36
42
58
69
3
90
47
84
18
23
4
18
23
47
84
90
5
40
50
60
70
80
6
13
26
29
42
56
7
61
62
63
64
65
|||
Hi ,
Thanks once again for your reply,
Yes that is near what I need but the report must not repeat the tickets because imagine what will happen with 1000 tickets.
For example in your report with the 13
11/16/2006 08:15:20 PM
10
13
17
22
6
13
26
29
16
42
56
11/16/2006 08:18:20 PM
18
19
29
42
56
6
13
26
29
42
56
The 13 did not remain RED from the prevous date and thats want i do not know how to do.
To get my winner i need to have 5 Macthing numbers in RED either from the same date or number by number from each lottery of the end of the week, who is playing will not have an expiry date, the ticket will expire only if there will be a winner.
Thanks
|||Ok so this is more like bingo where the numbers accumulate.
Anyway, below is the solution.
My tables have slightly renamed columns as follows
A_ID A_N1 A_N2 A_N3 A_N4 A_N5
B_ID B_date B_N1 B_N2 B_N3 B_N4 B_N5
I also added an ID column to DBB and created a primary key on both the ID columns in the tables
Then I created a couple of views for your tables to unpivot the numbered columns as follows:
CREATE VIEW [dbo].[UDBA] AS
SELECT A_ID
, A_INDEX = CAST(RIGHT(A_INDEX, 1) AS INT)
, A_VALUE
FROM DBA
UNPIVOT
(
A_VALUE
FOR A_INDEX IN (A_N1, A_N2, A_N3, A_N4, A_N5)
) U
CREATE VIEW [dbo].[UDBB] AS
SELECT B_ID
, B_date
, B_INDEX = CAST(RIGHT(B_INDEX,1) AS INT)
, B_VALUE
FROM DBB
UNPIVOT
(
B_VALUE
FOR B_INDEX IN (B_N1, B_N2, B_N3, B_N4, B_N5)
) U
Then with the views in place I use a common table expression in the report query:
WITH lotteryCTE (id, col, val, hit)
AS
(
-- Bring back all the results
SELECT id = A_ID
, col = CAST(A_INDEX as varchar)
, val = CAST(A_VALUE AS VARCHAR)
, hit = MAX(CASE WHEN results.B_ID IS NULL THEN 0 ELSE 1 END)
FROM UDBA picks
LEFT JOIN UDBB results
ON picks.A_VALUE IN (results.B_VALUE)
GROUP BY A_ID
, A_INDEX
, A_VALUE
)
-- Bring back all the values from the CTE. If it was a hit then highlight in red
SELECT id
, col
, val
, is_red = hit
FROM lotteryCTE
UNION ALL
-- Append the rows for the winner column only for picks where the numbers of hits is equal to the number of values chosen
-- Winner cells always red
SELECT id
, col = 'Win'
, val = 'Winner'
, is_red = 1
FROM lotteryCTE
GROUP BY id
HAVING SUM(hit) = COUNT(DISTINCT col)
ORDER BY id
, col
The above query would not need to change if the number of values chosen or drawn changes.
For the report I then use 2 separate datasets one using the above the query, the other simply retrieving the contents of the DBB table. On the report I use a table to display DBB and a matrix to display the results as follows:
Date
1
2
3
4
5
9/16/2006 08:10:20 PM
18
23
47
84
90
10/16/2006 08:10:20 PM
70
71
72
73
74
11/16/2006 08:15:20 PM
10
13
17
22
26
11/16/2006 08:18:20 PM
18
19
29
42
56
1
2
3
4
5
Win
1
10
15
16
17
19
2
25
36
42
58
69
3
90
47
84
18
23
Winner
4
18
23
47
84
90
Winner
5
40
50
60
70
80
6
13
26
29
42
56
Winner
7
61
62
63
64
65
The following is the RDL used for the report:
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="ds_lottery">
<DataSourceReference>ds_lottery</DataSourceReference>
<rd:DataSourceID>a2cbdf77-dd94-4883-b0e2-1538ed72012c</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>2.5cm</BottomMargin>
<RightMargin>2.5cm</RightMargin>
<PageWidth>21cm</PageWidth>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>21cm</InteractiveWidth>
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ColumnSpacing>1cm</ColumnSpacing>
<ReportItems>
<Textbox Name="textbox6">
<Left>0.25cm</Left>
<Top>3cm</Top>
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>3</ZIndex>
<Width>2.53968cm</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.63492cm</Height>
<Value>Results</Value>
</Textbox>
<Textbox Name="textbox2">
<Left>0.25cm</Left>
<Top>0.25cm</Top>
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>2</ZIndex>
<Width>2.53968cm</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.63492cm</Height>
<Value>Draws</Value>
</Textbox>
<Table Name="table1">
<Left>0.25cm</Left>
<DataSetName>dst_picks</DataSetName>
<Top>1.25cm</Top>
<ZIndex>1</ZIndex>
<Width>9.30291cm</Width>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="B_date">
<rd:DefaultName>B_date</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>1pt</Bottom>
<Left>1pt</Left>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Bottom>Black</Bottom>
<Left>Black</Left>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!B_date.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="B_N1_1">
<rd:DefaultName>B_N1_1</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>1pt</Bottom>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!B_N1.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="B_N2">
<rd:DefaultName>B_N2</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>1pt</Bottom>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!B_N2.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="B_N3">
<rd:DefaultName>B_N3</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>1pt</Bottom>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!B_N3.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="B_N4">
<rd:DefaultName>B_N4</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>1pt</Bottom>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!B_N4.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="B_N5">
<rd:DefaultName>B_N5</rd:DefaultName>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>1pt</Bottom>
<Right>1pt</Right>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Bottom>Black</Bottom>
<Right>Black</Right>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!B_N5.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Details>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>11</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Top>1pt</Top>
<Left>1pt</Left>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Top>Black</Top>
<Left>Black</Left>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Date</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>10</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Top>1pt</Top>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Top>Black</Top>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>1</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<ZIndex>9</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Top>1pt</Top>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Top>Black</Top>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>2</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<rd:DefaultName>textbox11</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Top>1pt</Top>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Top>Black</Top>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>3</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox14">
<rd:DefaultName>textbox14</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Top>1pt</Top>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Top>Black</Top>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>4</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox17">
<rd:DefaultName>textbox17</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Top>1pt</Top>
<Right>1pt</Right>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Top>Black</Top>
<Right>Black</Right>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>5</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>4.25cm</Width>
</TableColumn>
<TableColumn>
<Width>1cm</Width>
</TableColumn>
<TableColumn>
<Width>1cm</Width>
</TableColumn>
<TableColumn>
<Width>1cm</Width>
</TableColumn>
<TableColumn>
<Width>1.02646cm</Width>
</TableColumn>
<TableColumn>
<Width>1.02646cm</Width>
</TableColumn>
</TableColumns>
<Height>1.26984cm</Height>
</Table>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1.5cm</Width>
</MatrixColumn>
</MatrixColumns>
<Left>0.25cm</Left>
<RowGroupings>
<RowGrouping>
<Width>0.75cm</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="A_ID">
<rd:DefaultName>A_ID</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!id.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_A_ID">
<GroupExpressions>
<GroupExpression>=Fields!id.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="A_INDEX">
<rd:DefaultName>A_INDEX</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Top>Solid</Top>
<Left>Solid</Left>
<Right>Solid</Right>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!col.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_A_INDEX">
<GroupExpressions>
<GroupExpression>=Fields!col.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.63492cm</Height>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>dst_results</DataSetName>
<Top>4cm</Top>
<Width>2.25cm</Width>
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<MatrixRows>
<MatrixRow>
<Height>0.63492cm</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="A_VALUE">
<rd:DefaultName>A_VALUE</rd:DefaultName>
<Style>
<BorderStyle>
<Bottom>Solid</Bottom>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<Color>=iif(Fields!is_red.Value = 1, "Red", "Black")</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Max(Fields!val.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>5.26984cm</Height>
</Body>
<rd:ReportID>b64d7ed0-c87b-4f36-8cb8-3727005d5e2f</rd:ReportID>
<LeftMargin>2.5cm</LeftMargin>
<DataSets>
<DataSet Name="dst_results">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>WITH lotteryCTE (id, col, val, hit)
AS
(
-- Bring back all the results
SELECT id = A_ID
, col = CAST(A_INDEX as varchar)
, val = CAST(A_VALUE AS VARCHAR)
, hit = MAX(CASE WHEN results.B_ID IS NULL THEN 0 ELSE 1 END)
FROM UDBA picks
LEFT JOIN UDBB results
ON picks.A_VALUE IN (results.B_VALUE)
GROUP BY A_ID
, A_INDEX
, A_VALUE
)
-- Bring back all the values from the CTE. If it was a match then highlight in red
SELECT id
, col
, val
, is_red = hit
FROM lotteryCTE
UNION ALL
-- Append the rows for the winner column only for picks where the numbers of hits is equal to the number of values chosen
-- Winner cells always red
SELECT id
, col = 'Win'
, val = 'Winner'
, is_red = 1
FROM lotteryCTE
GROUP BY id
HAVING SUM(hit) = COUNT(DISTINCT col)
ORDER BY id
, col</CommandText>
<DataSourceName>ds_lottery</DataSourceName>
</Query>
<Fields>
<Field Name="id">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>id</DataField>
</Field>
<Field Name="col">
<rd:TypeName>System.String</rd:TypeName>
<DataField>col</DataField>
</Field>
<Field Name="val">
<rd:TypeName>System.String</rd:TypeName>
<DataField>val</DataField>
</Field>
<Field Name="is_red">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>is_red</DataField>
</Field>
</Fields>
</DataSet>
<DataSet Name="dst_picks">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select * from DBB</CommandText>
<DataSourceName>ds_lottery</DataSourceName>
</Query>
<Fields>
<Field Name="B_ID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>B_ID</DataField>
</Field>
<Field Name="B_date">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>B_date</DataField>
</Field>
<Field Name="B_N1">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>B_N1</DataField>
</Field>
<Field Name="B_N2">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>B_N2</DataField>
</Field>
<Field Name="B_N3">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>B_N3</DataField>
</Field>
<Field Name="B_N4">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>B_N4</DataField>
</Field>
<Field Name="B_N5">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>B_N5</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>28.02778cm</Width>
<InteractiveHeight>29.7cm</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>2.5cm</TopMargin>
<PageHeight>29.7cm</PageHeight>
</Report>
Hope this gives you what you need.
Adam.
Subscribe to:
Posts (Atom)