Distrib agent question

From BOL
CommitBatchThreshold 1000 The number of replication commands to be
issued to the Subscriber before a COMMIT statement is issued.
So if the distrib agent report 1 transaction with 100,000 commands are
replicated, i guess the above CommitBatchThreshold of 1000 does not apply..
right ? It appears that it commits only after the 100,000 commands make it
to the subscriber . Am I right ? Can i commit on the subscriber 1000
commands at a time ?
Not really, one commit will be called. One commit will occur in increments
of 1000, so one commit will be called from 1 to 1000 transactions. For 1001
to 2000 there will be 2, 2000>3000 3, etc.
The commit is done on a per transaction basis as opposed to a per command
basis.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%232IwOPmYFHA.2076@.TK2MSFTNGP15.phx.gbl...
> From BOL
> CommitBatchThreshold 1000 The number of replication commands to be
> issued to the Subscriber before a COMMIT statement is issued.
>
> So if the distrib agent report 1 transaction with 100,000 commands are
> replicated, i guess the above CommitBatchThreshold of 1000 does not
> apply..
> right ? It appears that it commits only after the 100,000 commands make it
> to the subscriber . Am I right ? Can i commit on the subscriber 1000
> commands at a time ?
>
|||I have 1 transaction with 100,000 commands..
Do you mean it will commit 1000 commands at a time..
Im a lil confused as your message indicates transactions and not commands
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O4Y1LzRZFHA.2768@.tk2msftngp13.phx.gbl...
> Not really, one commit will be called. One commit will occur in increments
> of 1000, so one commit will be called from 1 to 1000 transactions. For
1001[vbcol=seagreen]
> to 2000 there will be 2, 2000>3000 3, etc.
> The commit is done on a per transaction basis as opposed to a per command
> basis.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%232IwOPmYFHA.2076@.TK2MSFTNGP15.phx.gbl...
it
>
|||The way I read it: one commit, no matter how many commands per transaction.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uaN5P6SZFHA.1368@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> I have 1 transaction with 100,000 commands..
> Do you mean it will commit 1000 commands at a time..
> Im a lil confused as your message indicates transactions and not commands
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:O4Y1LzRZFHA.2768@.tk2msftngp13.phx.gbl...
increments[vbcol=seagreen]
> 1001
command[vbcol=seagreen]
be[vbcol=seagreen]
are[vbcol=seagreen]
make
> it
>

distinct select statement of households

I'm trying to match households based on last name, house no., street
and apt no. and select only 1 record so that I get something like 'the
DOE Family' when all criteria match. I still want to keep Frank Black
results.
Here is how the data is structured:
+--+--+--+--+
| Last Name | First Name | Initial | House No | Street | Apt |
+--+--+--+--+
| DOE | JOHN | | 100 | MAIN STREET | 3 |
| DOE | JANE | | 100 | MAIN STREET | 3 |
| BLACK | FRANK | M | 104 | CHESTNUT DR | |
I initially tried doing a subselect statement but I'm still not
getting just one result for 'Doe':
SELECT DISTINCT
[Last_Name],[First_Name],[Initial],
CONVERT(INT, [House_Number]) AS 'House',[Street_Name],[Apt_Number]
FROM address_tbl T
WHERE
freq >= 3 AND
Street_Name = (
SELECT DISTINCT TOP 1 Street_Name FROM address_tbl T1 WHERE
T.House_Number = T1.House_Number AND
T.Street_Name = T1.Street_Name AND
T.Apt_Number = T1.Apt_Number AND
T.Zip_Code = T1.Zip_Code
)
ORDER BY Street_Name, House
What am I doing wrong?
How do you define what First Name/Middle Initial to select?
Try:
SELECT Last_Name,
MAX(First_Name) AS First_Name,
MAX(Initial) AS Initial,
CONVERT(INT, House_Number) AS House,
Street_Name,
Apt_Number
FROM address_tbl T
WHERE freq >= 3
GROUP BY Last_Name, House_Number, Street_Name, Apt_Number
ORDER BY Street_Name, House
HTH,
Plamen Ratchev
http://www.SQLStudio.com

distinct select statement of households

I'm trying to match households based on last name, house no., street
and apt no. and select only 1 record so that I get something like 'the
DOE Family' when all criteria match. I still want to keep Frank Black
results.
Here is how the data is structured:
+--+--+--+--+
| Last Name | First Name | Initial | House No | Street | Apt |
+--+--+--+--+
| DOE | JOHN | | 100 | MAIN STREET | 3 |
| DOE | JANE | | 100 | MAIN STREET | 3 |
| BLACK | FRANK | M | 104 | CHESTNUT DR | |
I initially tried doing a subselect statement but I'm still not
getting just one result for 'Doe':
SELECT DISTINCT
[Last_Name],[First_Name],[Initial],
CONVERT(INT, [House_Number]) AS 'House',[Street_Name],[Apt_Number]
FROM address_tbl T
WHERE
freq >= 3 AND
Street_Name = (
SELECT DISTINCT TOP 1 Street_Name FROM address_tbl T1 WHERE
T.House_Number = T1.House_Number AND
T.Street_Name = T1.Street_Name AND
T.Apt_Number = T1.Apt_Number AND
T.Zip_Code = T1.Zip_Code
)
ORDER BY Street_Name, House
What am I doing wrong?How do you define what First Name/Middle Initial to select?
Try:
SELECT Last_Name,
MAX(First_Name) AS First_Name,
MAX(Initial) AS Initial,
CONVERT(INT, House_Number) AS House,
Street_Name,
Apt_Number
FROM address_tbl T
WHERE freq >= 3
GROUP BY Last_Name, House_Number, Street_Name, Apt_Number
ORDER BY Street_Name, House
HTH,
Plamen Ratchev
http://www.SQLStudio.com

DISTINCT question

Hello all!

I understand that if you place a DISTINCT in a SELECT statement, it will return only unique values of that row, depending how many coumns you have. So look at this.

Code Snippet

SELECT DISTINCT Active_Orders.First_Name, Active_Orders.Last_Name, Active_Orders.Account_Number, Active_Orders.Service_Date_Time, Active_Orders.Stat,

Order_Status.Status, Active_Orders.Order_ID, Active_Orders.Age, Active_Orders.DOB, Active_Orders.Rm_Desc, Active_Orders.Check_Out,

Active_Orders.Remarks, Locations.Loct_Desc, Active_Orders.FilePath, Active_Orders.Misc, Active_Orders.File_Date_Time

FROM Active_Orders INNER JOIN

Order_Status ON Active_Orders.Status_ID = Order_Status.Status_ID INNER JOIN

Locations ON Active_Orders.Location_ID = Locations.Location_ID

WHERE (Order_Status.Status = 'InProcess') OR

(Order_Status.Status = 'Pending') OR

(Order_Status.Status = 'OnHold') OR

(Order_Status.Status = 'C1') OR

(Order_Status.Status = 'C2') OR

(Order_Status.Status = 'C3') OR

(Order_Status.Status = 'C4') OR

(Order_Status.Status = 'C5') OR

(Order_Status.Status = 'C6') OR

(Order_Status.Status = 'C7')

ORDER BY Active_Orders.Stat DESC, Order_Status.Status DESC

I need to only return rows with unique Active_Orders.Account_Number. The way it is now, even if I have the same Account_Number, it will return both, because according the statment above, it still is a unique record despite the fact the account number is the same. The Order_ID is diffrent, Service_Date_time is different, etc.

So, How can I return all the fields above, but eliminate account numbers are the same?

Thanks!!

Rudy

Your query currently returns distinct rows.

You want it to return fewer rows than it does--specifically--only one row for each Account_Number. The question then is "Which rows do you want?" Unfortunately, there is no "any one but I don't care which one" aggregate in SQL, so you have to give a precise condition that can be evaluated in SQL to describe for a given account number, which row you want to see out of the many you're currently getting.

You might end up with something like (if your rule "the one with the latest File_Date_Time" value)

with YourQuery as (

rank() over (partition by Account_Number order by File_Date_Time desc) as rk,

<the rest of your current query>

)

select * from YourQuery as Q1

where rk = 1

If you aren't using SQL Server 2005, it's harder to write, but can still be done. In that case, you express this:

select <columns>

from <wherever>

where <whatever> as T1

and NOT EXISTS (

select *

from <same wherever> as T2

where <same whatever>

and T2.account_number = T1.account_number

and T2.File_Date_Time > T1.File_Date_Time

)

Steve Kass

Drew University

http://www.stevekass.com

|||

Just Try a Group statement instead

like this

SELECT a.* FROM mytable AS a

WHERE a.Account_Number IN(

SELECT B.Account_Number FROM (

SELECT Account_Number , count(Account_Number ) AS ACC

FROM mytable GROUP BY Account_Number

) AS B

WHERE B.ACC=1

)

So You'll get only the rows of The Account_numbers represented once in the table

I hope this will help

Best regards

Raimund

|||

Thank you guys for your suggestion!

Steve, I am using SQL 2005. I like the idea of the rule with the time. I'm actually going to have to put that in place anyway. If an order has the same account number within 2 minutes of the FILE_TIME, then only show one account number.

So do I put "with YourQuery as (

rank() over (partition by Account_Number order by File_Date_Time desc) as rk,

this on the very top of my procedure?

Then put in my query in,

then put in "select * from YourQuery as Q1

where rk = 1"

Sorry, I'm just sure of the order of this.

Thanks!

Rudy

DISTINCT question

Hello all!

I understand that if you place a DISTINCT in a SELECT statement, it will return only unique values of that row, depending how many coumns you have. So look at this.

Code Snippet

SELECT DISTINCT Active_Orders.First_Name, Active_Orders.Last_Name, Active_Orders.Account_Number, Active_Orders.Service_Date_Time, Active_Orders.Stat,

Order_Status.Status, Active_Orders.Order_ID, Active_Orders.Age, Active_Orders.DOB, Active_Orders.Rm_Desc, Active_Orders.Check_Out,

Active_Orders.Remarks, Locations.Loct_Desc, Active_Orders.FilePath, Active_Orders.Misc, Active_Orders.File_Date_Time

FROM Active_Orders INNER JOIN

Order_Status ON Active_Orders.Status_ID = Order_Status.Status_ID INNER JOIN

Locations ON Active_Orders.Location_ID = Locations.Location_ID

WHERE (Order_Status.Status = 'InProcess') OR

(Order_Status.Status = 'Pending') OR

(Order_Status.Status = 'OnHold') OR

(Order_Status.Status = 'C1') OR

(Order_Status.Status = 'C2') OR

(Order_Status.Status = 'C3') OR

(Order_Status.Status = 'C4') OR

(Order_Status.Status = 'C5') OR

(Order_Status.Status = 'C6') OR

(Order_Status.Status = 'C7')

ORDER BY Active_Orders.Stat DESC, Order_Status.Status DESC

I need to only return rows with unique Active_Orders.Account_Number. The way it is now, even if I have the same Account_Number, it will return both, because according the statment above, it still is a unique record despite the fact the account number is the same. The Order_ID is diffrent, Service_Date_time is different, etc.

So, How can I return all the fields above, but eliminate account numbers are the same?

Thanks!!

Rudy

Your query currently returns distinct rows.

You want it to return fewer rows than it does--specifically--only one row for each Account_Number. The question then is "Which rows do you want?" Unfortunately, there is no "any one but I don't care which one" aggregate in SQL, so you have to give a precise condition that can be evaluated in SQL to describe for a given account number, which row you want to see out of the many you're currently getting.

You might end up with something like (if your rule "the one with the latest File_Date_Time" value)

with YourQuery as (

rank() over (partition by Account_Number order by File_Date_Time desc) as rk,

<the rest of your current query>

)

select * from YourQuery as Q1

where rk = 1

If you aren't using SQL Server 2005, it's harder to write, but can still be done. In that case, you express this:

select <columns>

from <wherever>

where <whatever> as T1

and NOT EXISTS (

select *

from <same wherever> as T2

where <same whatever>

and T2.account_number = T1.account_number

and T2.File_Date_Time > T1.File_Date_Time

)

Steve Kass

Drew University

http://www.stevekass.com

|||

Just Try a Group statement instead

like this

SELECT a.* FROM mytable AS a

WHERE a.Account_Number IN(

SELECT B.Account_Number FROM (

SELECT Account_Number , count(Account_Number ) AS ACC

FROM mytable GROUP BY Account_Number

) AS B

WHERE B.ACC=1

)

So You'll get only the rows of The Account_numbers represented once in the table

I hope this will help

Best regards

Raimund

|||

Thank you guys for your suggestion!

Steve, I am using SQL 2005. I like the idea of the rule with the time. I'm actually going to have to put that in place anyway. If an order has the same account number within 2 minutes of the FILE_TIME, then only show one account number.

So do I put "with YourQuery as (

rank() over (partition by Account_Number order by File_Date_Time desc) as rk,

this on the very top of my procedure?

Then put in my query in,

then put in "select * from YourQuery as Q1

where rk = 1"

Sorry, I'm just sure of the order of this.

Thanks!

Rudy

sql

Distinct insert

I am trying to insert a record id primary at the same time that I am trying
to insert a Distinct Account number.
The insert statement is something kind of like this:
insert into Accounts (AccountsOID,Account)
select Distinct "Rec NO" + 100 as AccountsOID, "LIC TAG" as Account from
AutoExpt
In the AutoExpt table there are multiple occurrences of "LIC TAG" values all
of which need to be put into the Accounts table, however there must be only
one entry for the Lic tag in the accounts table. The Rec No +100 is just to
create a unique primary key for the AccountsOID.
Is there anyway I can get only the Distinct Lic Tag's, but at the same time
get a unique primary key for them.
Thanks,
On Wed, 25 Aug 2004 12:55:03 -0700, Robert wrote:

>I am trying to insert a record id primary at the same time that I am trying
>to insert a Distinct Account number.
>The insert statement is something kind of like this:
>insert into Accounts (AccountsOID,Account)
>select Distinct "Rec NO" + 100 as AccountsOID, "LIC TAG" as Account from
>AutoExpt
>In the AutoExpt table there are multiple occurrences of "LIC TAG" values all
>of which need to be put into the Accounts table, however there must be only
>one entry for the Lic tag in the accounts table. The Rec No +100 is just to
>create a unique primary key for the AccountsOID.
>Is there anyway I can get only the Distinct Lic Tag's, but at the same time
>get a unique primary key for them.
>Thanks,
Hi Robert,
Please provide DDL (CREATE TABLE statements, with constraints), sample
data (as INSERT statements), expected output and a description of the
business problem you're trying to solve.
http://www.aspfaq.com/etiquette.asp?id=5006
http://vyaskn.tripod.com/code.htm#inserts
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Unfortunately I do not have the create table SQL statements
What I am trying to accomplish is importing data from an excel file into a
SQL database. To make it easier to work with I simply straight imported the
excel file into a new table in the SQL database (AutoExpt). Accounts is one
of the tables that I need to import into. I do know that the OID for the
accounts table is not automatically generated, and must be generated manually.
In the AutoExpt table there is a listing of vechiles and what accounts
maintenance should be charged to. I can add the vechiles fine to the
Equipment table (Not listed here because it works just fine). However another
table EquipmentFeature is linked to the Equipment table. It contains things
such as account numbers, and the department that the vechile belongs to.
From what I can tell I can not add records to the Equipment feature table
such as the account number because they do not exist in the Accounts table.
So the solution is to first add all the distinct account numbers into the
accounts table. This also applies to the departments table as well.
I do have a backup of the database so I can fool aroudn with it all I want
without worry of data loss or damage, plus I am working on a dev server so no
actual users are accessing the database. I found that I can add duplicate
records in the accounts table and the departments table, however I have found
that I can't remove some duplicates in the departments table because it
claims that a record needs to exist in the General table first.
My theory is that if I add only the distinct account numbers and departments
I should be fine, and it should stop telling me that "A record must exist in
the accounts table first"
Unfortunately the company that makes the software is not offering much
support on importing the data. So I am left to figure it out myself.
"Hugo Kornelis" wrote:

> On Wed, 25 Aug 2004 12:55:03 -0700, Robert wrote:
>
> Hi Robert,
> Please provide DDL (CREATE TABLE statements, with constraints), sample
> data (as INSERT statements), expected output and a description of the
> business problem you're trying to solve.
> http://www.aspfaq.com/etiquette.asp?id=5006
> http://vyaskn.tripod.com/code.htm#inserts
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 26 Aug 2004 05:55:04 -0700, Robert wrote:

>Unfortunately I do not have the create table SQL statements
(snip)
Hi Robert,
Did you read the links I posted in my message? They contain instructions
on how you can generate the create table statements from the existing
tables (the ewb site shows how it's done with Query Analyzer; there's an
option to generate SQL in Enterprise Manager as well).
I read your description, but I can't figure out what you're trying to do
and what problems you are trying to tackle. I really need the table
structure and sample data to understand your problem.
(BTW, if therre are legal or other issues that prohibit you from posting
the table structure, you may also create a fake table that recreates the
problem).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||I can't post the structure or data because it's against our policies. I can
provide you something that looks similair. I basically need to be able to
insert only the unique account numbers from one table into another table,
however at the same time inserting a unique primary key into the same
destination table.
For example I kinda have something like this:
Primary Key From Table 1, Account Number
1,A
2,A
3,B
4,C
5,B
I need to put that information in a seperate table that is similair to this:
Primary From table2, Account Number
-564564,K
564578,D
7246,S
54567,F
The second table can not have any duplicate account numbers and of course
the Primary key.
What I want to do is select from table1 something looking like this:
Primary Key From Table 1, Account Number
1,A
3,B
4,C
Leaving me only with unique primary keys and account numbers.
The end result for table2 should look something like this:
-564564,K
564578,D
7246,S
54567,F
1,A
3,B
4,C
"Hugo Kornelis" wrote:

> On Thu, 26 Aug 2004 05:55:04 -0700, Robert wrote:
> (snip)
> Hi Robert,
> Did you read the links I posted in my message? They contain instructions
> on how you can generate the create table statements from the existing
> tables (the ewb site shows how it's done with Query Analyzer; there's an
> option to generate SQL in Enterprise Manager as well).
> I read your description, but I can't figure out what you're trying to do
> and what problems you are trying to tackle. I really need the table
> structure and sample data to understand your problem.
> (BTW, if therre are legal or other issues that prohibit you from posting
> the table structure, you may also create a fake table that recreates the
> problem).
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 26 Aug 2004 08:39:12 -0700, Robert wrote:

>I can't post the structure or data because it's against our policies. I can
>provide you something that looks similair. I basically need to be able to
>insert only the unique account numbers from one table into another table,
>however at the same time inserting a unique primary key into the same
>destination table.
>For example I kinda have something like this:
>Primary Key From Table 1, Account Number
>1,A
>2,A
>3,B
>4,C
>5,B
>I need to put that information in a seperate table that is similair to this:
>Primary From table2, Account Number
>-564564,K
>564578,D
>7246,S
>54567,F
>The second table can not have any duplicate account numbers and of course
>the Primary key.
>What I want to do is select from table1 something looking like this:
>Primary Key From Table 1, Account Number
>1,A
>3,B
>4,C
>Leaving me only with unique primary keys and account numbers.
>The end result for table2 should look something like this:
>-564564,K
>564578,D
>7246,S
>54567,F
>1,A
>3,B
>4,C
Hi Robert,
The following is untested as I only test solutions I provide in newsgroups
if I can copy and paste CREATE TABLE and INSERT statements. I like solving
problems and helping people, but I detest typing - I have to do plenty of
typing in my job :-)
But try if this gives you what you need:
INSERT INTO table2 (PrimKeyFromTable2, AccountNumber)
SELECT MIN(PrimKeyFromTable1), AccountNumber
FROM Table1
GROUP BY AccountNumber
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||That did the trick, I should have thought about grouping them.
Hugo... Your a genius!
Thanks,
"Hugo Kornelis" wrote:

> On Thu, 26 Aug 2004 08:39:12 -0700, Robert wrote:
>
> Hi Robert,
> The following is untested as I only test solutions I provide in newsgroups
> if I can copy and paste CREATE TABLE and INSERT statements. I like solving
> problems and helping people, but I detest typing - I have to do plenty of
> typing in my job :-)
> But try if this gives you what you need:
> INSERT INTO table2 (PrimKeyFromTable2, AccountNumber)
> SELECT MIN(PrimKeyFromTable1), AccountNumber
> FROM Table1
> GROUP BY AccountNumber
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Distinct in Select Statement

Hey there, is there a way I can use command such as distinct in a select statement to do the following. Lets say I want to do a search of products based off their location and I want to list the companies that will have products in that area. I only want to list the company once, but if I'm searching by products in the area I might come up with 15 results for that company. I have not written the code yet for this, I'm just planning ahead.

I'm programming using VB so I guess I would do something like this.

State = Trim(Request.QueryString("State"))

SelectStatement = "Select * From Products Where State='" & _
State & "'"

This would of course give me hypothetically speaking a list as long as the amount of products in one given area. Is there a way to cut this down and only list the company once? Any help would be greatly appreciated. Thanks in advance.SELECT DISTINCT(CompanyName)
FROM Company
WHERE Product.State = stateVariable
AND Product.CompanyID = Company.CompanyID|||thanks, I'll give that a shot.

Distinct and grouping problem

can someone look at the statement for me, it's not giving me distinct names,

SELECT DISTINCT TOP 10 leaderboard.name, Last(leaderboard.date_cr) AS date_cr, Last(leaderboard.handicap) AS handicap, Max(leaderboard.total) AS total, Last(leaderboard.cpsa) AS cpsa, Last(leaderboard.rbss) AS rbss, Max(leaderboard.visible) AS visible, Last(leaderboard.score) AS score
FROM leaderboard
WHERE (((leaderboard.visible)=True) AND ((leaderboard.rbss)=True))
GROUP BY leaderboard.name, leaderboard.total
ORDER BY Max(leaderboard.total) DESC , Last(leaderboard.date_cr);

if I take the second grouping off, I get distinct names, but the information isn't correct. i.e. the date, handicap, score, is from different record than the total field.

any help much appreciated.

thanks

That is because you are grouping on more than one column. The total column will get you additional values / rows in the Grouping section.

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

thanks for your reply.

So if I take the second grouping out how can I make the information 'line up' correctly?

i.e. have the correct date, handicap, score with the right total. As at the moment they are the wrong values for the Total.

|||That is hard to tell, because you only told that the query "is not working". Depending on your business case, you might want to get the max(total) or not ? That depends on the logic you want to put in the query.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

ok, these are the rules

1. If somebody has played more than once, only the highest score as calculated in one above should be displayed
2. the results should display correct details by total score in descending order
3. In the case where there are two or more equal total scores by different competitors, these should be sorted by date in ascending order (e, i - January 31 before Feb 12)

|||

It would help us better assist you if you could include table DDL, query strategy used so far, sample data in the form of INSERT statements, and an illustration of the desired results. (For help with that refer to: http://www.aspfaq.com/5006 and to http://classicasp.aspfaq.com/general/how-do-i-make-sure-my-asp-question-gets-answered.html )

The less 'set up' work we have to do, the more likely you are going to have folks tackle your problem and help you. Without this effort from you, we are just playing guessing games.

Dissolve based on common value

I am looking for help with a SQL statement to do the following:
Compare each value in [FIELD2] with every other value in [FIELD2]. Then, for all exact matches, perform % calculations on corresponding values in [FIELD3] and [FIELD4] to update [FIELD5]. ex:

[FIELD1] - [FIELD2] - [FIELD3] - [FIELD4] - [FIELD5]
North - China - 20 - 40 - 50.0%
Central - Japan - 30 - 90 - 33.3%
South - China - 10 - 10 - 100.0%
East - Japan - 50 - 200 - 25.0%

The desired output would be like:
Overall China has [100*(20+10)/(40+10)]=60.0% apples
and
Overall Japan has [100*(30+50)/(90+200)]=27.5% apples

...maybe by creating an array for all values in [FIELD2] using PHP for the comparison??

Regards
Accessorselect t1.f1, t1.f2, t1.f3, t1.f4, 100 * ((t1.f3 + t2.f3)/(t1.f4+t2.f4)) As "%"
from table t1
INNER JOIN
table t2 ON
t1.f2 = t2.f2 AND
NOT t1.f1 = t2.f1;

Returns,

F1 F2 F3 F4 %
---- ---- ---- ---- ----
South China 10 10 60
North China 20 40 60
East Japan 50 200 27.5862069
Central Japan 30 90 27.5862069

Displaying SQL Statement as output on report

The requirements of Sarbanes-Oxley are getting downright outrageous.
Everytime we run a report, we need to verify that the SQL statement has not
been modified since it was last verified as accurate. To combat the constant
requests to show the SQL behind the scenes, I would like to show the SQL as
part of the report.
Is this possible and how would it be done?
I am guessing that it would require custom code that captures the SQL in a
variable and then outputs that variable into the report.Could you get away with driving each report by a seperate stored procedure
( this is what we do anyway ).
Tell them the name of the stored procedure for each report.
If they want to go the extra paranoid step :
Write a custom assembly which uses sp_helptext on the stored proc name to
get the text of the storedproc ( a line at a time )
Generate a SHA1 hash of the text ( you might want to ignore whitespace and
non alphanumeric characters )
Write to some custom reportlog file, reportname, who ran, when, on what
machine, the hash of the procedure name
( Of course you need to record somewhere the hash of the stored procedure
when its verified )
You could even throw an exception if hash doesnt match the last verified
hash.
If thats not good enough ( !! ) you could encrypt the hash with a private
key, i.e. give it a full digital signature.
Or you could just generate a hash of the entire db schema !!
> Everytime we run a report, we need to verify that the SQL statement has
> not
> been modified since it was last verified as accurate.|||I would think the best thing to do is to put everything in SP. Then you have
the date the SP was created and can compare that against when it was
verified. If you keep the date of verification in a table you can then run a
report that shows any unverified SP based on the creation date. This seems
to me to be a much better solution. Straight forward and verifiable (which
is usually what you need for Sarbanes-Oxley).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"David" <David@.discussions.microsoft.com> wrote in message
news:4C2307F6-5002-4713-8B92-DC9A468FFEF2@.microsoft.com...
> The requirements of Sarbanes-Oxley are getting downright outrageous.
> Everytime we run a report, we need to verify that the SQL statement has
not
> been modified since it was last verified as accurate. To combat the
constant
> requests to show the SQL behind the scenes, I would like to show the SQL
as
> part of the report.
> Is this possible and how would it be done?
> I am guessing that it would require custom code that captures the SQL in a
> variable and then outputs that variable into the report.

displaying some predefined no. of rows

hi,

i wanted to know if there is any sql statement that enables the query to display say first '10' rows only.

Eg: suppose

SELECT * from Location;

returns 25 rows and i want only 10 rows in that to be displayed.

How can this be done? Help please.

You can use TOP statement like

SELECT TOP 10 * from Location

Is good to include order by for example table identity column if you would like to receive last inserted records

SELECT TOP 10 *
from Location
order by loc_ID desc

Displaying select results on the page

I have the following select statement on my page:

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:prbc_hrConnectionString %>" SelectCommand="SELECT emp_lname +', ' + emp_fname + ' ' + emp_minitial + '.' AS emp_fullname FROM employee WHERE (emp_username = @.emp_username)"> <SelectParameters> <asp:SessionParameter Name="emp_username" SessionField="Username" Type="String" /> </SelectParameters>

I want to say

"Welcome, emp_fullname"

at the top of the page, but can't figure out how to write the results to the page. I am moving to ASP.NET 2.0 from PHP and am banging my head against the wall trying to figure out how to do these little things.

I appreciate any help you can give.

I am assuming that you don't want to use the sql membership/profile system, and want to implement your own. You should read more about those to see if they would be a better fit for you, as both do a lot of things automatically for you already.

However, to solve your immediate problem, since the sqldatasource will return a set of records, you must use a control that knows how to handle multiple records and iterate through them. Those controls would be things like formview, detailsview, datarepeater, datalist, and gridview (I'm sure I missed a few). I'll show you how to use the datarepeater.

Drop the datarepeater on your form. Change the DataSourceID to SqlDatasource1. Switch to the source view, and add an item template with what you want to display for each record (In your case, only 1). So something like this should be in your .aspx file:

<asp:RepeaterID="Repeater1"runat="server"DataSourceID="SqlDatasource1">

<ItemTemplate><%# Eval("Username") %></ItemTemplate>

</asp:Repeater>

You can do the same thing similarly using a datalist. Drop datalist control on your page, assign the datasource. Refresh the schema. Edit item template, which should already contain a couple labels.

With that said, what I would probably do if I needed to display the username like that (Possibly across multiple pages), I would assign another session variable to hold the emp_fullname. Then on any page you wanted to display it, drop a label on it, and programmatically set the label contents during page load like:

label1.text=Session("emp_fullname").

Since the emp_fullname shouldn't change during a session, and is probably used on multiple pages, then you only need to hit the database when they first log in. It's much more efficient than hitting it every time. Although you could enable the sqldatasource's caching to help with that as well, it's still much more work for asp.net to instantiate a sqldatasource, databind it to a data control, etc.

If you still want to use the sqldatasource control (because you'd like to enable caching, etc), then you can also programmatically retrieve the dataset in page_load and set a label as well. I believe the syntax would be similiar to (This might not run/be syntaxically 100% correct):

if not ispostback() then

dim dsv as datasourceview=sqldatasource1.select(datasourcearguements.empty)

label1.text=dsv.ToTable.Rows(0).Column(0)

end if

The .ToTable.Rows(0).Column(0) I'm not really sure about, but you can access it, just probably not exactly that way.

Display return value from sqldatasource in textbox

Hi,

I wonder is there anyway I can put the returned value from SELECT statement from sqldatasource into a textbox.

I have a simple select statement in a sqldatasource:

SELECT X, Y, Z FROM tablexyz

So, I want the value from X, Y, Z to be displayed in 3 different textboxes. How do I do it? I am able to do it using datareader from code-behind, but I just want to know how to make use of the sqldatasource to make the whole things work.

Thanks

You could use either a gridview or a detailveiw to do that, and strip out all of the headers and footers.

I would have thought it would be easier to just use the datareader and some text boxes, I guess it depends on what you are trying to achieve.

HTH

Display quote in data using select statement

Hi,

I want to display data in the below format, please help me how to get this?

Data

'Employee1',Accountant

'Employee2',Programmer

I gave the select statment like this but it is not taking

SELECT '" + employeename + '" + departmentname from empdept

Please help me how to display quotes before and after the employeename.

Thank you

You need two quotes within quotes (thus four if a standalone quote) to display quotes in SQL, not three, hence:

SELECT ''" + employeename + ''" + departmentname from empdept

HTH

For more SQL tips, check out my blog:

Display Page Footer on first page only

Is there anyway to set-up the page footer of a report to print on the first page only?

I was going to try an IIF statement, but realized there is no visibility style property associated with the Page Footer section.

If you have any ideas, let me know.

Thank you,

T.J.

=iif(what you were doing [ie page =1 ], 'your text', '')

blank it out (the textbox or textboxes values) if it's not the first page... but i think if you're doing it on a textbox, you'll have Visibilitiy to work with.

|||

Thank you very much. I didn't even think of hiding the actual text boxes on the page footer.

Talk about not seeing the forest because of the trees.

Display Duplicates Entry from Sql Server

Hello,

I like to ask if anyone knows the sql statement to display all the field of a record from a table only showing the duplicates, and my criteria of duplicate is that a record has two fields with the same data. Thanks in advance.

Regards,

stealthnet

This one would show the field, and count of occurrences when there are more than one occurrence of that field in the table.

SELECT field, count(*) FROM table
GROUP BY field
HAVING (count(*) > 1)

|||

Thanks!