Grouping several items in one group

Hi Everyone,
I am new to reporting services and I am trying to create groups which
contains more then one code .
Table
Name, Code, Amount
paper 1101 £10
Pens 1102 £5
Shoes 2512 £20
Clothes 3455 £5
I want to put code 1101 and 1102 as group 1 with total, 2512 and 3455 as
group 2 with total.
At the moment I can only seem to group each one individually.
Please help.
John
--
John HoYou question is more of a SQL problem, and there is more than one way
to solve your problem.
SELECT 'GRP1' as groupcode, amount from paper where code =3D 1101
UNION
SELECT 'GRP1' as groupcode, amount from pens where code =3D 1102
UNION
SELECT 'GRP2' as groupcode, amount from shoes where code =3D 2512
UNION
SELECT 'GRP2' as groupcode, amount from clothes where code =3D 3455
save the above query to a View object. When you open the view, you'll
see this:
<pre>
groupcode | amount
GRP1 | =A310
GRP1 | =A35
GRP2 | =A320
GRP2 | =A35
</pre>
Now you can group & sum on your view for your report. I'm sure there
are more elegant solutions (perhaps using StoredProcs), but this is
dirty and quick...heh.
On Apr 7, 11:05 am, Learner <Lear...@.discussions.microsoft.com> wrote:
> Hi Everyone,
> I am new to reporting services and I am trying to create groups which
> contains more then one code .
> Table
> Name, Code, Amount
> paper 1101 =A310
> Pens 1102 =A35
> Shoes 2512 =A320
> Clothes 3455 =A35
> I want to put code 1101 and 1102 as group 1 with total, 2512 and 3455 as
> group 2 with total.
> At the moment I can only seem to group each one individually.
> Please help.
> John
> --
> John Ho

Grouping on ISNULL SP

I use the folliowing SP in a report in which i group by categorydescription:

SELECT ISNULL(categorydescription,'No category indicator code') as categorydescription, AccountMV, AccountFeeLY, ISNULL(company,'other') from snapsraw
where branchstate = @.state and (monthend = @.date)

problem is it doesnt show the isnull value for categorydescription in the group table, its just blank?

nevermind, doesntshow up in VS, but shows up on websql

Grouping numbers

I have a table which lists player names, teams played for and the
years they played there and my code looks like this

SELECT AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
AlsoPlayedFor.TeamName, Min([AlsoPlayedFor].[Year]) & "-" &
Max([AlsoPlayedFor].[Year]) AS [Year]
FROM AlsoPlayedFor
GROUP BY AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
AlsoPlayedFor.TeamName;

which takes the Min year and the Max Year and displays it like "Year-
Year"

But lets say for example the player played for 5 years so it 1990,
1991, 1992, 1993, 1995

It would display as 1990-1995 but I want it to display as 1990-1993,
1995, is this possiable? Also I need it to gothe other wayso if the
years are 1990, 1992, 1993, 1994, 1995 I want that to display as 1990,
1992-1995.

PLEASE HELPChris (chrislabs12@.gmail.com) writes:

Quote:

Originally Posted by

I have a table which lists player names, teams played for and the
years they played there and my code looks like this
>
SELECT AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
AlsoPlayedFor.TeamName, Min([AlsoPlayedFor].[Year]) & "-" &
Max([AlsoPlayedFor].[Year]) AS [Year]
FROM AlsoPlayedFor
GROUP BY AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
AlsoPlayedFor.TeamName;
>
which takes the Min year and the Max Year and displays it like "Year-
Year"
>
But lets say for example the player played for 5 years so it 1990,
1991, 1992, 1993, 1995
>
It would display as 1990-1995 but I want it to display as 1990-1993,
1995, is this possiable? Also I need it to gothe other wayso if the
years are 1990, 1992, 1993, 1994, 1995 I want that to display as 1990,
1992-1995.

I could suggest a query which in SQL 2005 at least give you a comma-
separated list of the years. Collapsing adjacent years into ranges appears
to make things a lot more complicated.

However, the query you posted has syntax which is not legal in SQL Server,
but has a touch of Access, a product of which I have no experience.

Could you clarify which product and which version of that product you
are using? If you are using Access, I recommend that you try an Access
newsgroup instead.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

What you posted implies a serious design error about history tables.
What you are askimg for is a violaiton of 1NF and the principle that
display is done in the front end and never the backend in a tiered
architecture. And finally the syntax you posted is not valid.

Want to try again?|||Erland Sommarskog wrote:

Quote:

Originally Posted by

>
>
I could suggest a query which in SQL 2005 at least give you a comma-
separated list of the years. Collapsing adjacent years into ranges appears
to make things a lot more complicated.

Here is a recursive solution that will do the job when
MAXRECURSION is no greater than the number of separate
years for one individual player. Some of the complication
is to get around limitations in what a recursive query
can contain (no GROUP BY, for example). The idea is
slippery, but not quite as messy as it looks.

CREATE TABLE T (
Pid INT,
yr INT,
primary key (Pid,yr)
)
go

INSERT T (Pid,yr) VALUES(1,1)
INSERT T (Pid,yr) VALUES(1,4)
INSERT T (Pid,yr) VALUES(1,3)
INSERT T (Pid,yr) VALUES(1,5)
INSERT T (Pid,yr) VALUES(1,6)
INSERT T (Pid,yr) VALUES(1,9)
INSERT T (Pid,yr) VALUES(1,10)
INSERT T (Pid,yr) VALUES(2,29)
INSERT T (Pid,yr) VALUES(2,30)
INSERT T (Pid,yr) VALUES(2,31)
INSERT T (Pid,yr) VALUES(2,9)
INSERT T (Pid,yr) VALUES(2,130)
INSERT T (Pid,yr) VALUES(2,131)
INSERT T (Pid,yr) VALUES(2,132)
go

with Mins(iter,Pid,lastwrite,lastfound,rowYr,yrs) as (
select
0,
Pid,
min(yr),
min(yr),
min(yr),
cast(min(yr) as varchar(max))
from T
group by Pid
union all
select
Mins.iter+1,
Mins.Pid,
case when min(T.yr) over (partition by Mins.Pid) = Mins.lastfound + 1
--and Mins.rightest < Mins.upto
then Mins.lastwrite else min(T.yr) over (partition by Mins.Pid) end,
min(T.yr) over (partition by Mins.Pid),
T.yr,
Mins.yrs
+ case when min(T.yr) over (partition by Mins.Pid) Mins.lastfound + 1
then case when Mins.lastfound Mins.lastwrite
then rtrim(Mins.lastfound) else '' end
+ ',' + rtrim(min(T.yr) over (partition by Mins.Pid))
else case when Mins.lastfound = Mins.lastwrite
then '-' else '' end
end
from Mins join T
on Mins.Pid = T.Pid
and Mins.lastfound < T.yr
and Mins.rowYr = Mins.lastfound
), AllSteps(Pid,yrs,lastwrite,lastfound,rk) as (
select distinct Pid, yrs,lastwrite,lastfound,
rank() over (partition by Pid order by iter desc)
from Mins
)
select
Pid,lastwrite,
yrs + case when lastwrite < lastfound then rtrim(lastfound) else ','+rtrim(lastfound) end
from AllSteps
where rk = 1

go

-- Steve Kass
-- Drew University
-- http://www.stevekass.com
-- 95508D54-0B01-431B-8B58-880146787216|||Correction: The final SELECT should be

select
Pid,lastwrite,
yrs + case when lastwrite < lastfound then rtrim(lastfound) else '' end
from AllSteps
where rk = 1

The version I posted lists the last year twice, if it is not
part of a preceding range of years.

SK

Steve Kass wrote:

Quote:

Originally Posted by

Erland Sommarskog wrote:
>

Quote:

Originally Posted by

>
>
I could suggest a query which in SQL 2005 at least give you a comma-
separated list of the years. Collapsing adjacent years into ranges

appears

Quote:

Originally Posted by

to make things a lot more complicated.

>
Here is a recursive solution that will do the job when
MAXRECURSION is no greater than the number of separate
years for one individual player. Some of the complication
is to get around limitations in what a recursive query
can contain (no GROUP BY, for example). The idea is
slippery, but not quite as messy as it looks.
>

<snip>

Quote:

Originally Posted by

select
Pid,lastwrite,
yrs + case when lastwrite < lastfound then rtrim(lastfound) else
','+rtrim(lastfound) end
from AllSteps
where rk = 1
>
go
>
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
-- 95508D54-0B01-431B-8B58-880146787216
>
>

|||Here is a guess at what you should have used for DDL if you had fgiven
us specs.

CREATE TABLE PlayerHistory
(player_id INTEGER NOT NULL
REFERENCES Pleyers(player_id)
team_name CHAR(15) NOT NULL
REFERENCES Teams(team_name),
start_year INTEGER NOT NULL
CHECK(start_year BETWEEN 1950 AND 9999),
end_year INTEGER
CHECK(start_year BETWEEN 1950 AND 9999),
CHECK(start_year <= end_year),
PRIMARY KEY ((player_id , team_name ,start_year)
);

A null end_year means the player is still with that team. You use a
VIEW with WHERE end_year IS NULL to get the current situation; you do
not put it in a separate table. What you seem to have is a table in
which an attribute (temproal duration) is split over several rows.

See how simple basic RDBMS design can save you from complex kludges?
Here is a guess at what you should have used for DDL if you had fgiven
us specs.

CREATE TABLE PlayerHistory
(player_id INTEGER NOT NULL
REFERENCES Pleyers(player_id)
team_name CHAR(15) NOT NULL
REFERENCES Teams(team_name),
start_year INTEGER NOT NULL
CHECK(start_year BETWEEN 1950 AND 9999),
end_year INTEGER
CHECK(start_year BETWEEN 1950 AND 9999),
CHECK(start_year <= end_year),
PRIMARY KEY ((player_id , team_name ,start_year)
);

A null end_year means the player is still with that team. You use a
VIEW with WHERE end_year IS NULL to get the current situation; you do
not put it in a separate table. What you seem to have is a table in
which an attribute (temproal duration) is split over several rows.

See how simple basic RDBMS design can save you from complex kludges?

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

grouping by months

Hello everyone

starting from this example table:

code qnt date

aaa 1 21/01/2006
abc 2 24/01/2006
aaa 3 27/01/2006
asd 1 11/03/2006
wde 2 16/03/2006
aaa 1 18/03/2006

I'd like to select records grouping by months and adding quantities (qnt column) for similar codes on each moth period.

Result expected is:

code qnt month

aaa 4 01

abc 2 01

asd 1 03

wde 2 03

aaa 1 03

Is there an efficient way to do this?

Any example?

Thanks a lot

WHat aout:

SELECT code,SUM(qnt),MONTH(date)
FROM SomeTable
GROUP BY code,MONTH(Date)

HTH, Jens Suessmeyer.

|||

Gee!

I didn't realize it was so obvious..

Thanks!

|||

Would ne nice if you could mark the topic as solved that it doesn��t appear on my (any other) watch lists anymore, thanks.

-Jens-

|||

Select Sum(Qty), DatePart(m, date) from Table group by DatePart(m, date)

That should get you started.

edit: Sorry, didn't realize someone had helped.

Grouping by Distinct

Hi,
I have 2 columns of data, one has an Agent code and the other has
information about the Agent. There are duplicates of the Agent code in the
1st column, but different info in the second. For example:
Col 1 Col 2
Agent 1 Data 1
Agent 1 Data 2
Agent 1 Data 3
Agent 2 Data 4
Agent 2 Data 5
Is there a way to only show the Agent once without duplicating it? I dont
want to sum or count anything, I just want to show the data like this:
Agent 1 Data 1
Data 2
Data 3
Agent 2 Data 4
Data 5
Does anyone know if this grouping is possible?
Thanks,
What you describe is more like a report than a query
result, but you can produce reports in SQL. One way
to do it is like this:
select
Col1, Col2
from (
select
Col1 as hidden1, Col1,
min(Col2) as hidden2, min(Col2) as Col2
from T
group by Col1
union all
select
Col1, '', Col2, space(2) + Col2
from T
where Col2 <> (
select min(Col2) from T as Tm
where Tm.Col1 = T.Col1
)
) T
order by hidden1, hidden2
-- Steve Kass
-- Drew University
PML wrote:

>Hi,
>I have 2 columns of data, one has an Agent code and the other has
>information about the Agent. There are duplicates of the Agent code in the
>1st column, but different info in the second. For example:
>Col 1 Col 2
>Agent 1 Data 1
>Agent 1 Data 2
>Agent 1 Data 3
>Agent 2 Data 4
>Agent 2 Data 5
>Is there a way to only show the Agent once without duplicating it? I dont
>want to sum or count anything, I just want to show the data like this:
>Agent 1 Data 1
> Data 2
> Data 3
>Agent 2 Data 4
> Data 5
>Does anyone know if this grouping is possible?
>Thanks,
>
>

Grouping and Custom Code

Hello everyone,

I've got an issue where I want to sum the group values and not the details, the reason is because I am hiding duplicate records. Here's how my Layout is setup.

TH

GH1 (hidden)

GH2 (hidden)

Det (hidden)

GF2 =Code.AddValue(Fields!Quantity.Value * Fieds!Cost.Value)

GF1 =Code.ShowAndResetSubTotal()

TF =Code.GrandTotal

I have the following in my Code window.

Dim Public SubTotal as Decimal

Dim Public GrandTotal as Decimal

Function ShowAndResetSubTotal() as Decimal

ShowAndResetSubTotal = SubTotal

SubTotal = 0

End Function

Function AddValue(newValue as decimal) as Decimal

SubTotal += newValue

GrandTotal += newValue

AddValue = newValue

End Function

This gives me incorrect results and I can't figure out why. Here's how it shows on my report:

Part Number Quantity Cost Regular Subtotal Method Using Custom Code Part 1 4,000 1.49 $5,947.20 Customer 1 $11,894.40 $0.00 Part 2 10 1.01 $10.07 Customer 2 $50.34 $5,947.20 Part 3 1 0.44 $0.44 Part 4 6,050 0.25 $1,530.41 Part 5 0 1.25 $0.00 Part 6 0 1.23 $0.00 Customer 3 $42,851.86 $10.07 Part 7 16,250 0.24 $3,922.59 Customer 4 $19,612.94 $1,530.85 Part 8 17,250 0.38 $6,544.82 Part 9 27,225 0.20 $5,380.20 Customer 5 $66,891.69 $3,922.59 Grand Total $141,301.23 $0.00

The issues brought up from the duplicates is shown in the "Regular Subtotal Method" column (there are 2 detail records for Customer 1-Part 1, which is why it is doubled). I can't use a distinct on the SQL query because there are other fields (not shown) on the report that are different.

As you can see, the GF1 (Customer #) shows the subtotal from the previous group, and the Table Footer (Grand Total) shows 0. Why is this?

Jarret

Hi Jarret,

The reason for seeing 0 (I think) Is that after a group ends, Reporting Services basically creates a new instance of your custom code and therefore any saved values get cleared.

I am not sure how your GF1 shows a value though... I could be wrong, but this is the experience I have had...

Regards,
Neil

|||The way I approached it...

I ordered my duplicate values... or some way of identifying that the value was not needed, and if the previous item = that item then do not add it to the total... Then for each footer call the same code and passing in the same values.

So each footer will be identical ... passing in the value and some other way of identifying if the value is unique...

Hope this helps...

Regards,
Neil

Group-based authorization in Forms Authentication

Hi,
I am using the Forms Authentication Sample to authenticate users in RS. I am
trying to find a sample code to check setup groups and assign access on
Folders to groups instead of users.
Please help.
Cheers
SaiSearch the below URL for 'forms authentication groups'. I found quite a few
results:
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs
--
Adrian M.
MCP
"Sai Vootla" <Sai@.discussions.microsoft.com> wrote in message
news:86332D5C-5A49-4F29-9ED0-B2ABBEDFBF6E@.microsoft.com...
> Hi,
> I am using the Forms Authentication Sample to authenticate users in RS. I
> am
> trying to find a sample code to check setup groups and assign access on
> Folders to groups instead of users.
> Please help.
> Cheers
> Saisql

Group Totals on Last page - How?

I have a report that groups by dept #, job code and earnings code.

9999 Administration

033 Secretary

200 Regular Pay 44.00 1000.00

300 Sick Pay 8.00 25.00

400 Overtime 3.00 75.00

8888 Janoitorial

055 Janitor

200 Regular Pay 24.00 800.00

300 Sick Pay 4.00 15.00

400 Overtime 1.00 45.00

On the last page of my report I want to sum the earnings totals by earnings number. For Example:

Totals

200 Regular Pay 68.00 1800.00

300 Sick Pay 12.00 40.00

400 Overtime 4.00 120.00

Can this be done?

Create a new table at the bottom of the report, group by earnings code, and sum the numbers.|||

Hi Jim,

another way to do this, is to use the table footer for the sums.

In the table footer, put the following expression

=Sum(IIF(Fields!earnings_code.value = 200, Fields!totals, cint(0))) this should be for regular pay and just replace the earning code for
the rest.

Then select the table, go to the properties, and there is an option called "Print footer rows on last page" and check that.

That should do it, hope that helps!

Bernard

|||Thanks for the help. I really appreciate it|||

Can you mark the answers that were helpful?

Thanks.

Group Header Repeating issue

Hi,

I have a group header and I have a invisible textbox which initializes some variable in the custom code (written using C#). The Group Header has been set to repeat on each page but whenever it repeats, the variable is not being initialized. It is initialized only when a new section of the same group starts. Please let me know if this is a drawback with Microsoft reporting services and if there is any workaround.

Thanks,

Shyam

Hi Shyam

I'm don't know if the hidden textbox is in the group header,
in the case that is is not:

Try putting the Initialization code into a hidden column in the groupHeader,
it should solve your problem.

Gerhard Davids

|||

The group header has just one rectangle and many textboxes inside it. One of the textboxes is hidden which is used to initialize the variable in custom code. I tried to put the same in another new column which is hidden but it did not work. But in crystal reports, the initialization code is invoked everytime a group header is displayed either in a new page as a new group.

Thanks,

Shyam

|||

I've Tried this out and couldnt get it working.

This is a sticky Problem, maby see if it is not possible to get it into the Page header instead.
It may be that the header isn't redone on every page simply re-displayd,
Where as the page header i think is different.

Gerhard

Group by statement problem

I am using the T-SQL code below to pull patient information. The code returns 86 rows, however, there are only 9 distinct account numbers. Why is the group by statement not grouping these together to only display the 9 distinct accounts and associated data?

select
srm.episodes.episode_type as Visit_Type,
srm.episodes.account_number as Account_Number,
srm.episodes.medrec_no as MRN,
dbo.PtMstr.PatientFullName,
left(srm.episodes.admission_date,11) as Admit_Date,
left(srm.episodes.episode_date,11) as Disch_Date,
dbo.PtMstr.Cases as Cases,
dbo.PtMstr.TotCharges,
srm.cdmab_base_info.abst_cmp_status as Abtract_Comp_Status,
srm.cdmab_base_info.adm_dx_adt as Admitting_Dx
,srm.event_types.event_type_code
from srm.cdmab_base_info inner join
srm.episodes on srm.episodes.episode_key = srm.cdmab_base_info.episode_key
inner join srm.event_history on srm.event_history.item_key = srm.episodes.episode_key
inner join srm.event_types on srm.event_types.event_type_key = srm.event_history.event_type_key
inner join dbo.PtMstr on dbo.PtMstr.AccountNumber = srm.episodes.account_number
where srm.cdmab_base_info.abst_cmp_status <> 'Y'
and srm.episodes.episode_date is not null
and srm.event_types.event_type_code <> 'ACOD'
AND srm.EPISODES.EPISODE_DATE Between @.StartDate and @.EndDate
AND srm.EPISODES.EPISODE_TYPE IN(@.VisitTypeCode)
Group By srm.episodes.account_number,
dbo.PtMstr.TotCharges,
srm.episodes.episode_type,
srm.episodes.medrec_no,
dbo.PtMstr.PatientFullName,
srm.episodes.admission_date,
srm.episodes.episode_date,
dbo.PtMstr.Cases,
srm.cdmab_base_info.abst_cmp_status,
srm.cdmab_base_info.adm_dx_adt,
srm.event_types.event_type_code

Use the following query..

select

srm.episodes.episode_type as Visit_Type,

srm.episodes.account_number as Account_Number,

srm.episodes.medrec_no as MRN,

dbo.PtMstr.PatientFullName,

left(srm.episodes.admission_date,11) as Admit_Date,

left(srm.episodes.episode_date,11) as Disch_Date,

dbo.PtMstr.Cases as Cases,

dbo.PtMstr.TotCharges,

srm.cdmab_base_info.abst_cmp_status as Abtract_Comp_Status,

srm.cdmab_base_info.adm_dx_adt as Admitting_Dx

,srm.event_types.event_type_code

from

srm.cdmab_base_info

inner join srm.episodes on srm.episodes.episode_key = srm.cdmab_base_info.episode_key

inner join srm.event_history on srm.event_history.item_key = srm.episodes.episode_key

inner join srm.event_types on srm.event_types.event_type_key = srm.event_history.event_type_key

inner join dbo.PtMstr on dbo.PtMstr.AccountNumber = srm.episodes.account_number

where

srm.cdmab_base_info.abst_cmp_status <> 'Y'

and srm.episodes.episode_date is not null

and srm.event_types.event_type_code <> 'ACOD'

AND srm.EPISODES.EPISODE_DATE Between @.StartDate and @.EndDate

AND srm.EPISODES.EPISODE_TYPE IN(@.VisitTypeCode)

Group By

srm.episodes.account_number,

dbo.PtMstr.TotCharges,

srm.episodes.episode_type,

srm.episodes.medrec_no,

dbo.PtMstr.PatientFullName,

left(srm.episodes.admission_date,11) as Admit_Date,

left(srm.episodes.episode_date,11) as Disch_Date,

dbo.PtMstr.Cases,

srm.cdmab_base_info.abst_cmp_status,

srm.cdmab_base_info.adm_dx_adt,

srm.event_types.event_type_code

|||

I had to remove the AS portion of the group by clause to get the code to work , however, it still returns 86 rows versus the expected 9 distinct rows.

|||

How you know there is only 9 distinct record. You only the get the number of rows as per the following query..& i didn't understand your requirement on your query(there is no group by funcations used).

select Distinct

srm.episodes.episode_type as Visit_Type,

srm.episodes.account_number as Account_Number,

srm.episodes.medrec_no as MRN,

dbo.PtMstr.PatientFullName,

left(srm.episodes.admission_date,11) as Admit_Date,

left(srm.episodes.episode_date,11) as Disch_Date,

dbo.PtMstr.Cases as Cases,

dbo.PtMstr.TotCharges,

srm.cdmab_base_info.abst_cmp_status as Abtract_Comp_Status,

srm.cdmab_base_info.adm_dx_adt as Admitting_Dx

,srm.event_types.event_type_code

from

srm.cdmab_base_info

inner join srm.episodes on srm.episodes.episode_key = srm.cdmab_base_info.episode_key

inner join srm.event_history on srm.event_history.item_key = srm.episodes.episode_key

inner join srm.event_types on srm.event_types.event_type_key = srm.event_history.event_type_key

inner join dbo.PtMstr on dbo.PtMstr.AccountNumber = srm.episodes.account_number

where

srm.cdmab_base_info.abst_cmp_status <> 'Y'

and srm.episodes.episode_date is not null

and srm.event_types.event_type_code <> 'ACOD'

AND srm.EPISODES.EPISODE_DATE Between @.StartDate and @.EndDate

AND srm.EPISODES.EPISODE_TYPE IN(@.VisitTypeCode)

|||

I appreciate you help. I ordered the data by account number and saw there were 9 distinct account numbers. I also noticed the srm.event_types.event_type_code field should not have been in this query; once I removed it, the code returned the expected 9 rows of data using either of the examples you provided. Thanks again for your assistance.

|||

You are grouping several additional columns after the account number.

If you just want the nine accounts listed, you'll need to just group on that column.

Then you can apply aggregates to get sums, etc. of the other data you desire.

GROUP BY problems...

Take a look at the following SELECT command:

Code Snippet

SELECT aagfakt.kndnr1,
aagfaktpos.artnr1,
CONVERT (char, aagfaktpos.faktdatum, 104) AS [Datum],
SUM (CASE
WHEN aagfaktpos.storno = 1
THEN aagfaktpos.bestellmenge * -1
ELSE aagfaktpos.bestellmenge
END) AS absatz
FROM aagfaktpos
INNER JOIN aagfakt
ON aagfaktpos.lfdfaktnr = aagfakt.lfdfaktnr
GROUP BY aagfaktpos.artnr1,
kndnr1,
aagfaktpos.faktdatum
ORDER BY kndnr1 ASC;

The data doesn't group, I don't know why... any help would be appreciated!

My suggestion:

Code Snippet

SELECT aagfakt.kndnr1,
aagfaktpos.artnr1,
CONVERT (char, aagfaktpos.faktdatum, 104) AS [Datum],
SUM (CASE
WHEN aagfaktpos.storno = 1
THEN aagfaktpos.bestellmenge * -1
ELSE aagfaktpos.bestellmenge
END) AS absatz
FROM aagfaktpos
INNER JOIN aagfakt
ON aagfaktpos.lfdfaktnr = aagfakt.lfdfaktnr
GROUP BY aagfaktpos.artnr1,
aagfakt.kndnr1,
CONVERT (char, aagfaktpos.faktdatum, 104)
ORDER BY aagfakt.kndnr1 ASC;

Group by on a concatenate field

Select TOP 100 CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) as CODE, Date_Issued, sum(Amount)
from dbo.Enterprise_Credits_Import_90_days
where CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) <> ' 0' and Service_Prefix like 'F'
Group by ?

How can I group by the field that I selected as CODE?

you have to repeat the expression:

Select TOP 100 CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) as CODE, Date_Issued, sum(Amount)
from dbo.Enterprise_Credits_Import_90_days
where CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) <> ' 0' and Service_Prefix like 'F'
Group by CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code), Date_Issued

|||Or if you're interested in readability you could make it:

select top 100 code, date_issued, sum(amount)
from
(select CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) as CODE, Date_Issued, Amount, Service_Prefix from
dbo.Enterprise_Credits_Import_90_days) c
where code <> '0'
and Service_Prefix like 'F'
group by code, date_issued

But... why do you add an empty string in between? And do you mean LIKE 'F%', rather than LIKE 'F' ?

Don't underestimate the power of table expressions. The optimiser knows what you mean (so there's very little difference to performance), and you can easily make something far more readable that way. You could also have used a CTE, like this:

with c as (select CONVERT(VarChar(10),Service_Prefix)+ '' +

CONVERT(VarChar(10),Service_Code) as CODE, Date_Issued, Amount,

Service_Prefix from

dbo.Enterprise_Credits_Import_90_days)
select top 100 code, date_issued, sum(amount)

from c
where code <> '0'

and Service_Prefix like 'F'

group by code, date_issued

Rob|||

Rob Farley wrote:

Or if you're interested in readability you could make it:

select top 100 code, date_issued, sum(amount)
from
(select CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) as CODE, Date_Issued, Amount, Service_Prefix from
dbo.Enterprise_Credits_Import_90_days) c
where code <> '0'
and Service_Prefix like 'F'
group by code, date_issued

SQL Server does not allow grouping by column alias names - does it? Other RDB engines allow this, and even ordinal grouping (group by 1,2, ...) notations but I thought SQL Server required you to repeat the expressions.

|||But this isn't actually grouping by a column alias. It's grouping by a field in a table expression. It would be no different if you created a view which had those fields in it - then you wouldn't see a problem using the view's fields to group by...

By wrapping the fields up in a table expression, you can easily circumnavigate the restrictions on SQL Server to repeat those expressions.

Rob|||right - I should have looked more closely at your original query - I didn't see the inline view before...|||:) So has it helped?|||

Im going to test it today. Ill let you know if I get it to work.

Thanks for alll the help !

|||

I tried the first statement and was able to make it work. Thanks for the info.

The second query gave me this error

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'.

Also due to the fact that the database has around 6 million records the group by's seem to make the query take a while to run. Is there any way to speed up the query?

Thanks again for your help

|||The second one requires SQL2005 - you'll get an error in SQL2000.

Could you get away with grouping by service_prefix and service_code separately, and then only concatenating them in the final select? That way, you could put a useful index on those two fields (plus date_issued, and amount), and it should run much more nicely. A covering index which includes all the fields you're interested in will mean that it doesn't even need to look at the table, because all the info will exist in the index.

Like this:

Select TOP 100 CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) as CODE, Date_Issued, sum(Amount)
from dbo.Enterprise_Credits_Import_90_days
where Service_Code <> ' 0' and Service_Prefix like 'F%'
group by service_code, service_prefix, date_issued

And you have an index on service_code, service_prefix, date_issued, amount

By changing the where clause to filter on the service_code rather than the concatenated field, that will help performance too, because it can then use the index more effectively. If you need to cater for where the 0 can be either in the prefix or in the code, you could always do an extra check... but try to avoid grouping or filtering on calculated fields, which makes it harder for the system to use indexes.

Rob|||

I think I see what you are saying here. The group by on both non concatenated fields should produce the same results as one group by by using the indexes more effectively. Ill try this and let you know. Prob be tommorow. Although in order to do a group by I thought you had to have the the group by fields in the select statement?

Thanks for your help.

|||You don't need to have the group by fields in the select statement, you just can't use fields that aren't either aggregated or one of the grouped fields.

But you can certainly select a concatenation of two of the grouped fields - definitely no problem there.

Rob|||

I would be careful about grouping on such a value as this:

Group by CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code), Date_Issued

Instead of doing all of this conversion stuff in the bowels of the query do it either:

1. In the user interface and write the query as:

Select TOP 100 Service_Prefix, Service_Code, Date_Issued, sum(Amount)
from dbo.Enterprise_Credits_Import_90_days
where Service_Code<> '0'
and Service_Prefix = 'F'
Group by Service_Prefix, Service_Code, Date_Issued

2. If you cannot use the UI, then do the conversion in an aggregate. There will be little performance hit because there will only be a single row in every case:

Select TOP 100 Max(CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code)) as CODE,
Date_Issued, sum(Amount)
from dbo.Enterprise_Credits_Import_90_days
where Service_Code<> '0'
and Service_Prefix = 'F'
Group by Service_Prefix, Service_Code, Date_Issued

ANY code appearing in an expression in a where or group by clause (and having, join-on criteria, etc) can cause performance issues that cannot be solved with indexes, like the criteria:

CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) <> ' 0'

Will not perform well because you don't have an index on this expression, and the optimizer would have to figure it out over and over instead of a simple probe into an index.

|||Yes... this is the point I was trying to make.

One thing to do it as an intellectual exercise about what you can do with T-SQL, but as soon as you're talking about performance, then you need to consider the fact that you really don't want to use the results of functions for filters/groups/sorts.

Rob|||Not disagreeing with you...Just adding my 2 cents worth to clarify/back you up :)

GROUP BY DATEPART issues...

Hi,

I'm trying:

Code Snippet

SELECT SUM(price), DATEPART(month, order_date), DATEPART(year, order_date)

FROM orders

GROUP BY DATEPART(month, order_date), DATEPART(year, order_date)

and, while this executes w/o a problem, I am a bit concerned with my results...

i get a SUM(price) = 2140.21 when running the above SQL for 11/2006

however, when i try:

Code Snippet

SELECT SUM(price)

FROM orders

WHERE order_date BETWEEN '11/01/2006' AND '11/30/2006'

i get a SUM(price) = 1950.45

if i bump the second date up by one day (i.e. '12/01/2006') i get a SUM(price) = 2140.21, the same value as when I used GROUP BY

any help would be greatly appreciated!

Hi,

What is the result of this query

Code Snippet

SELECT SUM(price), month(order_date), year(order_date)

FROM orders

GROUP BY month(order_date), year(order_date)

HAVING month(order_date)=6 AND year(order_date)=2006

If result is 1950.45 then

u try to use like this syntax

else

check your data one by one ..

|||

Thank you for the response.

I have tried

Code Snippet

...

HAVING DATEPART(month, order_date) = 11 AND DATEPART(year, order_date) = 2006

and received 2140.21

|||

Is the order_date always truncated at midnight? Try replacing "SUM(price)" with "COUNT(*)" with your queries. Do you get the same record counts for November 2006?

Thanks,
Bryan Smith

|||

I'm not sure about order_date being truncated at midnight... order_date is a datetime, so i am assuming that up until 11:59:59.99PM on 11/30, the date parts I am after remain the same.

I ran the COUNT(*) as suggested and get 804 using:

Code Snippet

SELECT COUNT(*)

FROM orders

WHERE order_date BETWEEN '11/01/2006' AND '11/30/2006'

and get 838 when using:

Code Snippet

SELECT COUNT(*), DATEPART(month, order_date), DATEPART(year, order_date)

FROM orders

GROUP BY DATEPART(month, order_date), DATEPART(year, order_date)

the number remains 838 when I add:

Code Snippet

HAVING DATEPART(month, order_date) = 11 AND DATEPART(year, order_date) = 2006

and drops to 804 if i add:

Code Snippet

WHERE order_date BETWEEN '11/01/2006' AND '11/30/2006'

i am baffled, but then again, i'm no expert

thanks for the assistance!

|||

Cool! I think we're on the right track here.

Your original code used "WHERE order_date BETWEEN '11/01/2006' and '11/30/2006'". That means orders created between midnight Nov 1 2006 and midnight at the top of Nov 30 2006. You're dropping orders that occurred from 11/30/2006 12:00:00.001 AM to 11/30/2006 11:59:59.997 PM. That's why the BETWEEN statement gives you 804 records while the DATEPART statement gives you 838 records.

(Please note, SQL Server is only accurate to 3 ms when a datetime data type is used. Times of 11:59:59.998 PM and 11:59:59.999 PM are recorded as 12:00:00.000 AM the next day.)

If you re-write your query to use "WHERE order_date BETWEEN '11/01/2006' AND '11/30/2006 11:59:59.997 PM'" you should get 838 records and your SUM should match the one in the DATEPART query.

Good luck,
Bryan

|||

Ahhh... that makes sense...

would it be correct to assume that the GROUP BY DATEPART... query accurately sums up each months data?

thanks again for your help!

|||

It would. It calculates the month of the date without regard for time, so any orders placed at anytime on 11/30/2006 would fall into November.

B.

Group By Count * >1?

Can this be used to prevent the repetition of records displayed in a page?

Code Snippet

SELECT T_ProgramGuests, GuestName
FROM T_ProgramGuests
GROUP BY ProgramID, GuestName
HAVING (COUNT(*) > 1)

I'm trying to prevent names being repeated. I only want the name to show once followed by the next name and so on. But only once.

Does this do what you want?

SELECT GuestName, MAX(ProgramID) AS pid

FROM T_ProgramGuests

GROUP BY GuestName

ORDER BY GuestName ASC

The above prints the last programId/guest name pair in your table. If you want the first, you can replace the max with min.

Hope this helps!

John (MSFT)

Group By Clause Limitation

Code is:
select
case when ItemCode is null then '-'
else ItemCode
End,
case when sum(RecdQty) is null then '-'
else sum(RecdQty)
End
from ItemMaster where ItemCode='V001' group by ItemCode

Problem Statement:
If query is not getting any records for above mentioned condition, then I want zero to be displayed if datatype is int (i.e. for sum(RecdQty) field) and '-' to be diplayed if datatype is varchar (i.e. for ItemCode field).
In this situation, "ItemCode is null" and "sum(RecdQty) is null" conditions are not been utilised.
Is this a limitation of case or group by clause?No, this is not a limitation of SQL at all, it is doing exactly what it is supposed to do. Please see my explanation from the last time you asked this question by clicking here (http://www.dbforums.com/showthread.php?p=6237156#post6237156). If that explanation isn't clear or sufficient, please continue the discussion in that thread instead of starting new threads.

-PatP|||Consider this

select count(*) from master..sysdatabases
where 1=2

will return 0. But

select count(*) from master..sysdatabases
where 1=2
group by status

return no records

Now you want a specific ItemCode. There is no need for the group by

select isnull(min(ItemCode),'-')
,isnull(sum(RecdQty),0)
from ItemMaster
where ItemCode='V001'

Group By Clause Help

Hello the code below shows multiple instances of targets.name "donor type" I could not correclty run the code without including contributions.program. I would like the output to only have 1 value for each donor type. How would I do this or workaround to get it done?

- thanks for your time.

SQL> SELECT targets.name "DONOR TYPE", contribution.program,
2 SUM(contribution.amount) "CONTRIBUTION QTR2"
3 FROM donor, contribution, targets
4 WHERE contribution.cdate >= TO_DATE('04/01/03', 'MM/DD/YY')
5 AND contribution.cdate <= TO_DATE('06/30/03', 'MM/DD/YY')
6 AND donor.donor = contribution.donor
7 AND targets.type = donor.type
8 GROUP BY targets.name, contributions.program;

DONOR TYPE PROGRAM CONTRIBUTION QTR2
------- -------- ------
Corporate Donors Applied Research 100
Foundations Applied Research 175
Individuals Basic Research 50
Corporate Donors International Programs 100
Corporate Donors Teaching Programs 50
Foundations Teaching Programs 50What prevents you from doing this?:

SQL> SELECT targets.name "DONOR TYPE",
2 SUM(contribution.amount) "CONTRIBUTION QTR2"
3 FROM donor, contribution, targets
4 WHERE contribution.cdate >= TO_DATE('04/01/03', 'MM/DD/YY')
5 AND contribution.cdate <= TO_DATE('06/30/03', 'MM/DD/YY')
6 AND donor.donor = contribution.donor
7 AND targets.type = donor.type
8 GROUP BY targets.name;

DONOR TYPE CONTRIBUTION QTR2
------- ------
Corporate Donors 250
Foundations 225
Individuals 50|||How can I get tthe output to look like this?

DONOR TYPE PROGRAM CONTRIBUTION QTR2
------- -------- ------
Corporate Donors Applied Research 100
International Programs 100
Teaching Programs 50

Foundations Applied Research 175
Teaching Programs 50
Individuals Basic Research 50

- thanks for your help|||Oh I see, you mean suppress the output of the repeated value?

In SQL Plus, use:

SQL> BREAK ON "DONOR TYPE"

Also, add "ORDER BY targets.name, contributions.program" after the GROUP BY clause to be sure the ordering is correct (GROUP BY doesn't guarantee the order).|||Great this worked for that table--thanks a bunch. How can I group by contrubuion by member only so there is only one isntance per name and the sum of all rows info and still maintain each of the columns. this can be easily done by removing the target column but I need to display it along with the others belwo. Once put the target column in I must also gruoup by member.qtr1 which produces the multiple row output. How can I work around this to only group by member?

SQL> SELECT contribution.member, member.qtr1 "TARGET",
2 SUM(contribution.amount) "CONT. QTR1",
3 ROUND(SUM(contribution.amount)/member.qtr1,3)*10 "% OF PROJECTION"
4 FROM contribution, member
5 WHERE contribution.cdate >= TO_DATE('01/01/03', 'MM/DD/YY')
6 AND contribution.cdate <= TO_DATE('03/31/03', 'MM/DD/YY')
7 GROUP BY contribution.member, member.qtr1;

MEMBER TARGET CONT. QTR1 % OF PROJECTION
----- ---- ---- -----
Adams 50 175 35
Adams 75 175 23.33
Adams 100 175 17.5
Adams 150 175 11.67
Adams 200 175 8.75
Adams 250 175 7
Baker 50 100 20
Baker 75 100 13.33
Baker 100 100 10
Baker 150 100 6.67
Baker 200 100 5

GROUP BY Bit in Integer

I have a table where each row contain a unique individual and it's scrap code. The scrap code is an integer where each of the 32 bits represent a unique scrap cause and each individual may have more than one scrap cause.

As an example: the indidual may be both too heavy and too high

Lets say these two scrap causes are represented by bit 0 and bit 1.

That again converts to the integer value 1 and 2. If both bits are true the value of the integer for that row/individual is 3. I use a support table that contain each bit (representetd as an integer value) and a description.

ScrapCode(ScrapCode, Description)

with upto 32 rows.

With this it is easy to decode the scrap code for a selected individual into separate causes, but that is for one individual.
Now I would like to create a report that count codes based on the unique scrap codes in the scrap code table. In other words I would like to group by each bit in the integer.

Table(Id, date, ScrapCode)

Select Count(*) from Table

WHERE ScrapCode <> 0

Group By "Bit"

Any suggestions?

can u supply an example of the expected outcome.|||

One way to do it is to take advantage of the POWER function, the bitwise '&' operator and a table of numbers. I mocked up the data with this:

declare @.aTable table
( rid integer,
scrapCode integer
)

insert into @.aTable
select iter,
-1000000000 + 2147483646*dbo.rand()
from small_iterator (nolock)

The definition of the SMALL_ITERATOR and DBO.RAND() objects can be found here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1330536&SiteID=1

I computed the results with this:

Select iter-1 as [Cause],
Count(*) as[Cause Count]
from @.aTable
inner join small_iterator (nolock)
on iter <= 32
WHERE ScrapCode <> 0
and ( iter < 32 and
ScrapCode & Power (2, iter-1) > 0 or
iter = 32 and
ScrapCode < 0
)
group by iter-1
order by iter-1

/*
Cause Cause Count
-- --
0 16333
1 16494
...
30 16470
31 15336
*/

|||

Found it myself after some trial and error:

Code Snippet

SELECT v.ScrapCode, COUNT(p.Snr) AS NoOfScrap

FROM [table] p, [ScrapCodes] v

WHERE p.date > '2007-04-20'

AND p.Scrapcode <> 0

AND p.ScrapCode & v.ScrapCode <> 0

GROUP BY v.ScrapCode

Output:

1 15

2 250

4 5

8 98

512 23

....

Gridview Search

I tried doing a text box search within Gridview. My code are as follows. However, when I clicked on the search button, nothing shown.

Any help would be appreciated. I'm using an ODBC connection to MySql database. Could it be due to the parameters not accepted in MySql?

Protected

Sub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)

SqlDataSource1.SelectCommand =

"SELECT * FROM carrier_list WHERE carrierName LIKE '%' + @.carrierName + '%'"

EndSub

Sub doSearch(ByVal SourceAsObject,ByVal EAs EventArgs)

GridViewCarrierList.DataSourceID ="SqlDataSource1"

GridViewCarrierList.DataBind()

EndSub

HTML CODES (Snippet)<asp:ButtonID="btnSearchCarrier"runat="server"onclick="doSearch"Text="Search"/>

' Gridview
<asp:GridViewID="GridViewCarrierList"runat="server"DataSourceID="SqlDataSource1">

</asp:GridView>

<

asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"SelectCommand="SELECT * FROM carrier_list"></asp:SqlDataSource>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"><SelectParameters><asp:ControlParameterControlID="txtSearchCarrier"Name="carrierName"PropertyName="Text"Type="String"></asp:ControlParameter></SelectParameters>

</asp:SqlDataSource>

It's a syntax error on your SQL statement.

Try this:

SqlDataSource1.SelectCommand = "SELECT *FROM carrier_listWHERE carrierNameLIKE'%" + @.carrierName + "%'"

You had an extra ' after first '% and before last %'

Hope this helps and let me know if it worked.

Jae.

|||

It reverted with a :

"Character is not valid." error.

Line 33: SqlDataSource1.SelectCommand = "SELECT * FROM carrier_list WHERE carrierName LIKE '%" + @.carrierName + "%'"

|||

check what your server is passing to sqldatasource by doing following:

response.write("SELECT * FROM carrier_list WHERE carrierName LIKE '%" + @.carrierName + "%'")

it print out:

SELECT * FROM carrier_list WHERE carrierName LIKE %yourvalue%

Also, note that your value (carrierName) should not contain any single quote or double quote.

Hope this helps.

Jae.

|||

check what your server is passing to sqldatasource by doing following:

response.write("SELECT * FROM carrier_list WHERE carrierName LIKE '%" + @.carrierName + "%'")

it print out:

SELECT * FROM carrier_list WHERE carrierName LIKE '%yourvalue%'

Also, note that your value (carrierName) should not contain any single quote or double quote.

Hope this helps.

Jae.

|||

Hi it still prompts the same error:

response.write(

"SELECT * FROM carrier_list WHERE carrierName'%" + @.carrierName + "%'")|||

i just looked at your code from beginning again.

1. it's VB, so why should you use +? instead of &? (sorry i thought of C#)
2. you're mising LIKE on above statement.
3. you can't LITERALLY pass @.carrierName as value. Your value is txtSearchCarrier.text
4. I don't understand why you have 2 sqldatasource (Delete sqldatasource2 - this will show same effect, read on)

So, let's write it again and clean up a bit:

SqlDataSource.SelectCommand = "select * from carrier_list where carriername like '%" & txtSearchCarrier.text & "%'"

Also, you don't need <controlparameter> tag within "select parameter", try this approach:

HTML CODES (Snippet)

<asp:Textbox id="txtSearchCarrier" runat="server"/>
<asp:Button ID="btnSearchCarrier" runat="server" onclick="doSearch" Text="Search" />

' Gridview
<asp:GridView ID="GridViewCarrierList" runat="server" DataSourceID="SqlDataSource1" />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" />

That's it.

This will show ALL carriers like you had with sqldatasource2, but with only one sqldatasource1 (because it will pass like '%%' whichi will return all)
*** Also, when carrier name is typed into txtSearchCarrier (obviously a textbox), it will return result sets with characters displayed in textbox.
*** Also, you should have your LOAD_PAGE empty.

If you don't want to diplay anything at first, don't bind DataSourceID = "sqldatasource1", but rather do it on btnSearchCarrier_OnClick handler.
Something like this:

sub btnSeachCarrier_OnClick (....)
.... you other code ...
gridViewCarrierList.datasourceid = "sqlDataSouce1"
gridViewCarrierList.databind()
end sub

This way, you only retrieve data when you click "searchcarrier" button.

Hope this helps and if it doesn't send me the aspx page and I will help you with it. (send it to my email,jae.lee@.jaeleeandco.com)

Jae.

|||

just in case, you HAVE to do following:

1. delete PAGE_LOAD

2. add select command to on_click look at below:

sub btnSeachCarrier_OnClick (....)
.... you other code ...
SqlDataSource.SelectCommand = "select * from carrier_list where carriername like '%" & txtSearchCarrier.text & "%'"
gridViewCarrierList.datasourceid = "sqlDataSouce1"
gridViewCarrierList.databind()
end sub

|||

Thanks Jae,

Actually I used

SqlDataSource1.SelectCommand ="SELECT * FROM carrier_list WHERE carrierName LIKE ? '%' ORDER BY carrierName ASC"

instead and it works.

Will try your suggestion too! Thanks!