grouping over multiple datasets

I have two datasets on a report. Both have a common field - State. I'd like to be able to have data from each dataset on a page relevant to the common state (e.g. I want data from both datasets on every page, but only data for that state). Note that I can not join the information from the two datasets, but they do share the same grouping field.
So far, I'm not having any luck getting this to work. Can anyone tell me if what I'm trying to do is possible and shed some light if it is?

You either need to combine these two data sets in the query (UNION with a filter will probably work) or use subreports. Data regions to not support multiple 1:manys.

grouping over multiple datasets

I have two datasets on a report. Both have a common field - State. I'd like to be able to have data from each dataset on a page relevant to the common state (e.g. I want data from both datasets on every page, but only data for that state). Note that I can not join the information from the two datasets, but they do share the same grouping field.
So far, I'm not having any luck getting this to work. Can anyone tell me if what I'm trying to do is possible and shed some light if it is?

You either need to combine these two data sets in the query (UNION with a filter will probably work) or use subreports. Data regions to not support multiple 1:manys.

Grouping common functionality in multiple stored procedures

Hi i have always used views in my code to group common functionality in my sql expressions and then i can simply call these views in my data access layer by saing:

SqlCommand cmd = new SqlCommand("SELECT * FROM vw_Documents WHERE CategoryID = @.CategoryID", cn);

However my view has become so complicated that i had to convert it to a stored procedure called sp_Documents. The problem now though is that is that i wish to do queries against the data returned but i can't simply say:

SqlCommand cmd = new SqlCommand("SELECT * FROM sp_Documents WHERE CategoryID = @.CategoryID", cn);

The only way i can see to do it is to create a stored procedure for every single senario i have passing in the appropriate values as parameters. This seems a pretty messy solution to me because i would have repeated logic in all my stored procedures. Therefore i was wondering if there's a simpler way for me to do this or am i just being lazy :).

Appreciate if someone could help,

Oops i found the solution straight after i posted. User defined functions. Never realized you could return more than one value with a function in sql server. If there is a better solution please let me know but this seems to tick all the boxes.

Edit: I have discovered that this is not going to work for me since my stored procedure produces different columns (based on values passed in) and it appears that the Multi-statement Table-Value User-Defined Function requires you to specify the structure you will be outputting.

|||

>SqlCommand cmd = new SqlCommand("SELECT * FROM vw_Documents WHERE CategoryID = @.CategoryID", cn);

It is preferable to select just the columns you require.

>However my view has become so complicated that i had to convert itto a stored procedure called sp_Documents.
>The problem now though isthat is that i wish to do queries against the data returned but I can'tsimply say:
>SqlCommand cmd = new SqlCommand("SELECT * FROM sp_Documents WHERE CategoryID = @.CategoryID", cn);
>The only way i can see to do it is to create a stored procedure forevery single scenario i have passing in the appropriate values asparameters
It is tempting to code complicated IF ... SELECT ... ELSE SELECT ..., however it is generally best to a code one stored procedure for each permutation as then the query engine can optimise each variation. There are some situations where serial scanning of a table is an acceptable perfomance hit and it is possible to use the COALESCE trick to search any combination of 1 to N columns for specific value. For example if table FRED has non-null columns A through D and the sp has args &A to &D and for simplicity the allowed values are non-zero integer then:
IF &A = 0 SET &A = NULL
IF &B = 0 SET &B = NULL
IF &C = 0 SET &C = NULL
IF &D = 0 SET &D = NULL
SELECT A, B, C, D FROM FRED
WHERE COALESCE(&A, A) = A
AND COALESCE(&B, B) = B AND COALESCE(&C, C) = C AND COALESCE(&D, D) = D

If say &A is the only non-zero parameter then the effect select simplifies to SELECT A, B, C, D FROM FRED WHERE &A = A, as COALESCE selects the first non-null value.

sql

group Number for millions of records.

I am using the following query which I found from a fragment of code
by itzik ben-gan to assign a common group id for group of records in my
case which have similar SSN and first Name and Last Name. if the SSN is
the same it should also check the first name and last name of the
record. Because records have more than three AKA names; I need to check
all the possibilities of first name last name combination to verify the
records are the same.
This code works fine and can assign group numbers for all the rows.
I am trying this code on a database of 65,000 rows. It's taking around
20 minute to complete. but I'll have to run the same code on
800,000,000 rows.
It will take years to finish.
Even if the query is optimized to run in 1 second for 65,000 rows, it
will take more than 4 hours to run on the 800,000,000 row. This where I
realized I am in the "wrong jungle".
1. is there any other feasible and faster way to do this? Very
important issue.
2. While assigning group number, it doesn't give sequential numbers. It
skips some of the numbers( group Number 1,2 5,9...) I am just curious
about this(
not very important issue)
SELECT c1.fname, c1.lname, c1.ssn , c3.tu_id,
(SELECT 1 + count(*)
FROM distFLS AS c2
WHERE c2.ssn < c1.ssn
or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
substring(c1.fname,1,1) or substring(c2.lname,1,1) =
substring(c1.lname,1,1)
or substring(c2.fname,1,1) =
substring(c1.lname,1,1) or substring(c2.lname,1,1) =
substring(c1.fname,1,1))
)) AS grp_num
into tmp_FLS
FROM distFLS AS c1
JOIN tu_people_data AS c3
ON (c1.ssn = c3.ssn and
c1.fname = c3.fname and
c1.lname= c3.lname)
GO
distinct firstname, lastname and SSN table from the tu_people_data.
I created this table to increase the query performance.
CREATE TABLE [distFLS] (
[fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[ssn] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [TU_People_Data] (
[tu_id] [bigint] NOT NULL ,
[count_id] [int] NOT NULL ,
[fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[ssn] [int] NULL ,
CONSTRAINT [PK_tu_bulk_people] PRIMARY KEY CLUSTERED
(
[tu_id],
[count_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
sample data
there is a column count_id after the tu_id and before fname(tu_id and
count_id are primary keys)
tu_id fname lname SSN
156078480 KRISINA WALSH 999999000
156078480 KRISTINA GIERER 999999000
156078480 KRISTINA WALSH 999999000
151257883 J SOTO 999999111
151257883 JOSE LARIOS 999999111
151257883 JOSE SOTO 999999111
151257883 L SOTO 999999111
136312525 ELADIO GARCIA 999999222
136312525 ELADIO NAVA 999999222
136312525 ELADIO NAVAGARCIA 999999222
136312525 GARCIA NAVA 999999222
149180940 DARREN SAUERWINE 999999333
149180940 DARREN SUAERWIN 999999333I am assuming that SSN means U.S. Social Security Number. If this is not
the case, ignore the rest of this post.
If SSN is the same you should be able to assume the records are in fact the
same people (this is the rule, but due to human error there have been
exceptions). For the rare exceptions identify them after the fact. Odds
are you won't come accross a true duplicate SSN, although if your data is
innaccurate it is more likely.
If you can go this route, simply assign the same group to anyone with the
same SSN, it will make it much simpler and much, much, much faster. If you
have to do it with all the name comparisons, at least remove the following
subquery and replace it with a different method for assigning a unique
GRP_NUM. That extra full table lookup for every row is what is causing your
performance problems.
(SELECT 1 + COUNT(*)
FROM DISTFLS AS C2
WHERE C2.SSN < C1.SSN
OR (C2.SSN = C1.SSN
AND ( SUBSTRING(C2.FNAME,1,1) = SUBSTRING(C1.FNAME,1,1)
OR SUBSTRING(C2.FNAME,1,1) = SUBSTRING(C1.LNAME,1,1)
OR SUBSTRING(C2.LNAME,1,1) = SUBSTRING(C1.LNAME,1,1)
OR SUBSTRING(C2.LNAME,1,1) = SUBSTRING(C1.FNAME,1,1)
)
)
) AS GRP_NUM
<sql.greg@.gmail.com> wrote in message
news:1141742507.871093.35900@.i40g2000cwc.googlegroups.com...
> I am using the following query which I found from a fragment of code
> by itzik ben-gan to assign a common group id for group of records in my
> case which have similar SSN and first Name and Last Name. if the SSN is
> the same it should also check the first name and last name of the
> record. Because records have more than three AKA names; I need to check
> all the possibilities of first name last name combination to verify the
> records are the same.
> This code works fine and can assign group numbers for all the rows.
> I am trying this code on a database of 65,000 rows. It's taking around
> 20 minute to complete. but I'll have to run the same code on
> 800,000,000 rows.
> It will take years to finish.
> Even if the query is optimized to run in 1 second for 65,000 rows, it
> will take more than 4 hours to run on the 800,000,000 row. This where I
> realized I am in the "wrong jungle".
> 1. is there any other feasible and faster way to do this? Very
> important issue.
> 2. While assigning group number, it doesn't give sequential numbers. It
> skips some of the numbers( group Number 1,2 5,9...) I am just curious
> about this(
> not very important issue)
> SELECT c1.fname, c1.lname, c1.ssn , c3.tu_id,
> (SELECT 1 + count(*)
> FROM distFLS AS c2
> WHERE c2.ssn < c1.ssn
> or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
> substring(c1.fname,1,1) or substring(c2.lname,1,1) =
> substring(c1.lname,1,1)
> or substring(c2.fname,1,1) =
> substring(c1.lname,1,1) or substring(c2.lname,1,1) =
> substring(c1.fname,1,1))
> )) AS grp_num
> into tmp_FLS
> FROM distFLS AS c1
> JOIN tu_people_data AS c3
> ON (c1.ssn = c3.ssn and
> c1.fname = c3.fname and
> c1.lname= c3.lname)
> GO
> distinct firstname, lastname and SSN table from the tu_people_data.
> I created this table to increase the query performance.
> CREATE TABLE [distFLS] (
> [fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
> [lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
> [ssn] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [TU_People_Data] (
> [tu_id] [bigint] NOT NULL ,
> [count_id] [int] NOT NULL ,
> [fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
> [lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
> [ssn] [int] NULL ,
> CONSTRAINT [PK_tu_bulk_people] PRIMARY KEY CLUSTERED
> (
> [tu_id],
> [count_id]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> sample data
> there is a column count_id after the tu_id and before fname(tu_id and
> count_id are primary keys)
> tu_id fname lname SSN
> 156078480 KRISINA WALSH 999999000
> 156078480 KRISTINA GIERER 999999000
> 156078480 KRISTINA WALSH 999999000
> 151257883 J SOTO 999999111
> 151257883 JOSE LARIOS 999999111
> 151257883 JOSE SOTO 999999111
> 151257883 L SOTO 999999111
> 136312525 ELADIO GARCIA 999999222
> 136312525 ELADIO NAVA 999999222
> 136312525 ELADIO NAVAGARCIA 999999222
> 136312525 GARCIA NAVA 999999222
> 149180940 DARREN SAUERWINE 999999333
> 149180940 DARREN SUAERWIN 999999333
>|||A while back, I ran a query against a customer account table, and found over
1000 unique SSN was used with a different date of birth. Those SSNs with the
highest frequency were obviously fake or just stubbs (ex: 111111111), but
there were some apparently valid numbers that were duplicated several
hundred times by different account holders.
"They were shocked to learn that 18 of the 19 terrorists possessed either
state-issued or counterfeit driver's licenses or ID cards and all 19 had
obtained Social Security numbers (SSNs) - some real, some fake."
http://www.cis.org/articles/2002/back1202.html
"Linda Trevino, who lives in a Chicago suburb, applied for a job last year
at a local Target department store, and was denied. The reason? She already
worked there -- or rather, her Social Security number already worked there."
http://www.msnbc.msn.com/id/6814673/
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23drgo7fQGHA.5552@.TK2MSFTNGP10.phx.gbl...
>I am assuming that SSN means U.S. Social Security Number. If this is not
> the case, ignore the rest of this post.
> If SSN is the same you should be able to assume the records are in fact
> the
> same people (this is the rule, but due to human error there have been
> exceptions). For the rare exceptions identify them after the fact. Odds
> are you won't come accross a true duplicate SSN, although if your data is
> innaccurate it is more likely.
> If you can go this route, simply assign the same group to anyone with the
> same SSN, it will make it much simpler and much, much, much faster. If
> you
> have to do it with all the name comparisons, at least remove the following
> subquery and replace it with a different method for assigning a unique
> GRP_NUM. That extra full table lookup for every row is what is causing
> your
> performance problems.
> (SELECT 1 + COUNT(*)
> FROM DISTFLS AS C2
> WHERE C2.SSN < C1.SSN
> OR (C2.SSN = C1.SSN
> AND ( SUBSTRING(C2.FNAME,1,1) = SUBSTRING(C1.FNAME,1,1)
> OR SUBSTRING(C2.FNAME,1,1) = SUBSTRING(C1.LNAME,1,1)
> OR SUBSTRING(C2.LNAME,1,1) = SUBSTRING(C1.LNAME,1,1)
> OR SUBSTRING(C2.LNAME,1,1) = SUBSTRING(C1.FNAME,1,1)
> )
> )
> ) AS GRP_NUM
>
> <sql.greg@.gmail.com> wrote in message
> news:1141742507.871093.35900@.i40g2000cwc.googlegroups.com...
>

Group by, ignore common words

When using GROUP BY to sort the results of a select statement, is it
possible to have it ignore certain common words. i.e "The", "A".
For instance, say you were sorting a list of book titles; you would
want "The Promise of Happiness" to be near "Pride and Prejudice", not
"Theatre Talk: Voices of Irish Theatre Practitioners".
Thanks.A) GROUP BY is not used for sorting. Use ORDER BY for sorting. GROUP BY
can use other algorithms than sorting for what it does (grouping) so it
can't be trusted to always return a sorted order.
B) Not easily... Can you store 'The Promise of Happiness" as "Promise of
Happiness, The" ?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"never-aways" <nospam@.nospam.com> wrote in message
news:rbtr21l5lnfu33brj4it87rornnf9ubfcl@.
4ax.com...
> When using GROUP BY to sort the results of a select statement, is it
> possible to have it ignore certain common words. i.e "The", "A".
> For instance, say you were sorting a list of book titles; you would
> want "The Promise of Happiness" to be near "Pride and Prejudice", not
> "Theatre Talk: Voices of Irish Theatre Practitioners".
> Thanks.|||What about creating a user defined function that returns the "stripped"
value? You could order by on that.|||What about using this in the order by
SELECT
BookTitle
FROM
BookTitles
ORDER BY
REPLACE(SUBSTRING(BookTitle, 1, 4), 'The ', '') +
SUBSTRING(BookTitle, 5, LEN(BookTitle))
It should work, as long as there aren't any other characters except a space
after "the".
Simon Worth
"never-aways" <nospam@.nospam.com> wrote in message
news:rbtr21l5lnfu33brj4it87rornnf9ubfcl@.
4ax.com...
> When using GROUP BY to sort the results of a select statement, is it
> possible to have it ignore certain common words. i.e "The", "A".
> For instance, say you were sorting a list of book titles; you would
> want "The Promise of Happiness" to be near "Pride and Prejudice", not
> "Theatre Talk: Voices of Irish Theatre Practitioners".
> Thanks.