Grabbing the Top Nth Records based on another table per group

Hi All,

I am trying to perform a SQl query that I have never attempted before.

I have a table with records such that would look like this

GroupField1 GroupField2 Field1 Amount

AAA BBB Field1Data 23.00

AAA BBB Feld2Data 24.00

AAA BBB Field3Data 8.00

BBB CCC Field1Data 25.00

BBB CCC Field2 Data 23.00

DDD BBH Field1Data 232.00

I have a second table that is like

GroupField1 GroupField2 NumberOfRows

AAA BBB 2

BBB CCC 1

DDD BBH 1

What I am trying to do is create a query that gets data from table 1 based on how many rows table 2 says to get.

I think the query would be something like

Select Top(select number of rows from table 2 where Table1.GroupField1 = Table2.GroupField1 and Table1.GroupField2 = Table2.GroupField2) from Table1

Anybody oput there have any expertise in this area?

Thank you,

Lynn

Wrong forum. Try posting on http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

Grabbing first record rather than the record I am trying to find.

I tried checking to see if the point at which the reader was, that if it was the record I am looking for to go ahead and add the table data to a label. But for some reason it's only taking the first record in the database and not the one I thought I was at.

[CODE] public void UpdateMaleHistLbl()
{
SqlConnection conn = new SqlConnection("Server=localhost\\SqlExpress;Database=MyFamTree;" + "Integrated Security=True");
SqlCommand comm = new SqlCommand("SELECT * FROM FatherHistTable, MotherHistTable, UsersTable WHERE UsersTable.UserName = @.usrnmeLbl ", conn);
comm.Parameters.AddWithValue("@.usrnmeLbl", usrnmeLbl.Text);
conn.Open();
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
string usr = reader["username"].ToString();
usr = usr.TrimEnd();
string pss = reader["password"].ToString();
pss = pss.TrimEnd();
if (usrnmeLbl.Text == usr)
{
if (hiddenpassLbl.Text == pss)
{
maleHistLbl.Text = reader["GG_Grandfather"] + " > ";
maleHistLbl.Text += reader["G_Grandfather"] + " > ";
maleHistLbl.Text += reader["Grandfather"] + " > ";
maleHistLbl.Text += reader["Father"] + " > ";
maleHistLbl.Text += reader["Son"] + " > ";
maleHistLbl.Text += reader["Grandson"] + " > ";
maleHistLbl.Text += reader["G_Grandson"] + " > ";
maleHistLbl.Text += reader["GG_Grandson"] + "<br /><br />";
}
}
break; //exit out of the loop since user found
}
reader.Close();
conn.Close();
}
}[/CODE]

Thanks in advanceIt was the break statement taking me out too early.sql

Grabbing DATENAME from date only returns January

why is the below only giving me January?

CAST(DATENAME(Month, datepart(month, ph.systemmonth)) AS varchar(15))

What did you expect ?

-Jens Suessmeyer.

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

I solved it:

Results:

0000002 MA 0000002 25 5.00 January 1 2005
0000002 MA 0000002 25 20.00 January 1 2005
0000002 MA 0000002 25 43.00 January 1 2005
0000002 MA 0000002 25 1264.20 January 6 2005
0000002 MA 0000002 25 1344.47 January 6 2005
0000002 MA 0000002 25 210.23 January 10 2005
0000002 MA 0000002 25 211.25 January 10 2005

Expected Results:

0000002 MA 0000002 25 5.00 January 1 2005
0000002 MA 0000002 25 20.00 January 1 2005
0000002 MA 0000002 25 43.00 January 1 2005
0000002 MA 0000002 25 1264.20 June 6 2005
0000002 MA 0000002 25 1344.47 June 6 2005
0000002 MA 0000002 25 210.23 October 10 2005
0000002 MA 0000002 25 211.25 October 10 2005

Resolution:

CAST(DATENAME(Month, '2006/' + Convert(varchar(2), ph.systemmonth)) + '/1') AS varchar(15)) as SystemMonth

for a real date field (which systemmonth was not in this case...since it only contained the month) you can do this:

CAST(DATENAME(Month, '2006/' + Convert(varchar(2), datepart(mm,getdate())) + '/1') AS varchar(15)) as SystemMonth,

Grabbing CURRENT Parameter Data

So it looks like when you add an expression which grabs a parameter value
(Parameters!SRNumber.Value), it grabs the data from when the "View Report"
button was originally pressed. My goal is to allow someone to punch in new
data into a parameter and without having to re-run the report, be able to use
that for an action. Any ideas? Example of what I am attempting to do below.
= "mailto:" + Fields!SOURCE_EMAIL.Value + "?subject=SR #"
+Parameters!SRNumber.Value + " has been assigned to you"...
(SRNumber is a blank parameter which isn't actually used in any of my
queries, but rather just there for this purpose)On Dec 12, 5:25 pm, Peetaah <Peet...@.discussions.microsoft.com> wrote:
> So it looks like when you add an expression which grabs a parameter value
> (Parameters!SRNumber.Value), it grabs the data from when the "View Report"
> button was originally pressed. My goal is to allow someone to punch in new
> data into a parameter and without having to re-run the report, be able to use
> that for an action. Any ideas? Example of what I am attempting to do below.
> = "mailto:" + Fields!SOURCE_EMAIL.Value + "?subject=SR #"
> +Parameters!SRNumber.Value + " has been assigned to you"...
> (SRNumber is a blank parameter which isn't actually used in any of my
> queries, but rather just there for this purpose)
This is a long shot, but you could try using javascript. Normally, the
last value selected before the View Report button is selected is shown
in an expression based on a parameter. Sorry that I could not be of
further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks, that is what I was thinking too-- just hoping there was a secret call
I could make to get the current value :)
"EMartinez" wrote:
> On Dec 12, 5:25 pm, Peetaah <Peet...@.discussions.microsoft.com> wrote:
> > So it looks like when you add an expression which grabs a parameter value
> > (Parameters!SRNumber.Value), it grabs the data from when the "View Report"
> > button was originally pressed. My goal is to allow someone to punch in new
> > data into a parameter and without having to re-run the report, be able to use
> > that for an action. Any ideas? Example of what I am attempting to do below.
> >
> > = "mailto:" + Fields!SOURCE_EMAIL.Value + "?subject=SR #"
> > +Parameters!SRNumber.Value + " has been assigned to you"...
> > (SRNumber is a blank parameter which isn't actually used in any of my
> > queries, but rather just there for this purpose)
>
> This is a long shot, but you could try using javascript. Normally, the
> last value selected before the View Report button is selected is shown
> in an expression based on a parameter. Sorry that I could not be of
> further assistance.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Dec 12, 10:21 pm, Peetaah <Peet...@.discussions.microsoft.com>
wrote:
> Thanks, that is what I was thinking too-- just hoping there was a secret call
> I could make to get the current value :)
> "EMartinez" wrote:
> > On Dec 12, 5:25 pm, Peetaah <Peet...@.discussions.microsoft.com> wrote:
> > > So it looks like when you add an expression which grabs a parameter value
> > > (Parameters!SRNumber.Value), it grabs the data from when the "View Report"
> > > button was originally pressed. My goal is to allow someone to punch in new
> > > data into a parameter and without having to re-run the report, be able to use
> > > that for an action. Any ideas? Example of what I am attempting to do below.
> > > = "mailto:" + Fields!SOURCE_EMAIL.Value + "?subject=SR #"
> > > +Parameters!SRNumber.Value + " has been assigned to you"...
> > > (SRNumber is a blank parameter which isn't actually used in any of my
> > > queries, but rather just there for this purpose)
> > This is a long shot, but you could try using javascript. Normally, the
> > last value selected before the View Report button is selected is shown
> > in an expression based on a parameter. Sorry that I could not be of
> > further assistance.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Sorry that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Grabbing characters from a string

Hello

I want to write a stored procedure (using Enterprise Manager) that can grab
the digits that are inbetween the two dashes (-) in strings like:
123-150-40
1-123-8
32-4215-61

The digits to the left, right and inbetween the dashes could be any length,
so a static "get the 5th, 6th and 7th digit" stored procedure won't work.

Many thanks,

--
Chris Michael
www.INTOmobiles.com
Download 100s of ringtones, wallpapers & logos every month for only 1.50
per weekChris Michael (news@.intomobiles.com) writes:
> I want to write a stored procedure (using Enterprise Manager) that can
> grab the digits that are inbetween the two dashes (-) in strings like:
> 123-150-40
> 1-123-8
> 32-4215-61
> The digits to the left, right and inbetween the dashes could be any
> length, so a static "get the 5th, 6th and 7th digit" stored procedure
> won't work.

And you want the result to be? Do you want:

12315040
11238
32421561

That is, one single number formed? That would be easy with help of
the replace() function.

Or do you want triplets like:

123, 150, 40
1, 123, 8
32, 4215, 61

And in such case, is there always exactly two dashes, or can you have

123-3455-2345-23345-2349-2-23

If you always have two dashes, using a combination of substring(),
patindex(), reverse(), right() and left() might do the trick.

All functions I have mentioned here, are listed in Books Online under
Functions, String Functions.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

grabbing a value from listbox to query database

hello forum,

I need to grab astring value from alist box in from a web form,
and pass it to a sql select command statement where that value is equal to
all values in a database table(sql 2000).

example

zip code list box
33154
33254
84578
85475
35454

selected value is 85475

I am putting that value in a string like this:

dim string_zip as string
string_zip = zip_ListBox.text

Question, how do i pass that value to sql stament, i am using this but does not work.

SqlCommand1 =New SqlCommand("SELECT zip FROM table WHERE zip =string_zip", SqlConnection1)

You should use this

SqlCommand1 =New SqlCommand("SELECT zip FROM table WHERE zip ='" &string_zip &"'", SqlConnection1)

Regards

|||

Actually, you should use this:

SqlCommand1=new sqlcommand("SELECT zip FROM table WHERE zip=@.zip",SqlConnection1)
SqlCommand1.parameters.add(new sqlparameter("@.zip",sqldbtype.varchar))
SqlCommand1.parameters("@.zip").value=string_zip

Using the string concatenation method is a good way to get yourself hacked.

|||

Motley wrote:

Actually, you should use this:

SqlCommand1=new sqlcommand("SELECT zip FROM table WHERE zip=@.zip",SqlConnection1)
SqlCommand1.parameters.add(new sqlparameter("@.zip",sqldbtype.varchar))
SqlCommand1.parameters("@.zip").value=string_zip

Using the string concatenation method is a good way to get yourself hacked.

Yes, this is preferred over my solution becuase it is more secure.

Thanks Motley

|||

Thanks for you help, it works, but I encounter another problem.

Problem:

multiple selection from list box is allowed, I am getting a string value from all selected choices like this:

Dim listofstringsAsString
Dim itemAs ListItem

ForEach itemIn listbox.Items
If item.SelectedThen
listofstrings = listofstrings & item.Text & ","
EndIf
Next

so i havelistofstrings = (selectedvalue1,selectedvalue2,selectedvalue3,......)

I need to select all values from a table in database where any of those values corresponds.

NOTE: Values in database can also be in the format of (value1,value2,value3,.....) or just a single (value1,)

|||

Search for messages on the UDF named "Split" one was posted recently.

SELECT *
FROM table
WHERE field IN (SELECT * FROM Split(@.listofstrings))

OR
SELECT *
FROM table
JOIN Split(@.listofstrings) s ON (table.field=s.id)

Grabbing a calculated field for a new calculation

I'm going to try and explain this as best as I can :)
I have calculated fields that has the following expressions - I've
named each textbox hoping that I could grab them in the expression but
I have no idea how':
1. totalMarket: =iif(sum(Fields!Wins.Value) = 0, 0, sum( Fields!
Wins.Value) + iif(sum(Fields!Pending.Value) = 0, 0, sum(Fields!
Pending.Value))) - iif(sum(Fields!Losses.Value) = 0, 0, sum(Fields!
Losses.Value))
2. remainingSales = =iif(Sum(Fields!Pending.Value)=0, 0, Sum(Fields!
Pending.Value))
I need the next text box (marketPercent) to do the following...
totalMarket - remainingSales / totalMarket
I used all the expression above to make it work - but there has to be
an easier way.. I tried using..
=iif(Sum(ReportItem!totalMarket.Value)) ... etc but that syntax did
not work... does anyone know how to grab the name of the textbox in
an expression?
ps - this is not a header or a footer - it's in the actual form
Thanks in advance!
LisaOn Jun 6, 10:38 am, peashoe <peas...@.yahoo.com> wrote:
> I'm going to try and explain this as best as I can :)
> I have calculated fields that has the following expressions - I've
> named each textbox hoping that I could grab them in the expression but
> I have no idea how':
> 1. totalMarket: =iif(sum(Fields!Wins.Value) = 0, 0, sum( Fields!
> Wins.Value) + iif(sum(Fields!Pending.Value) = 0, 0, sum(Fields!
> Pending.Value))) - iif(sum(Fields!Losses.Value) = 0, 0, sum(Fields!
> Losses.Value))
> 2. remainingSales = =iif(Sum(Fields!Pending.Value)=0, 0, Sum(Fields!
> Pending.Value))
> I need the next text box (marketPercent) to do the following...
> totalMarket - remainingSales / totalMarket
> I used all the expression above to make it work - but there has to be
> an easier way.. I tried using..
> =iif(Sum(ReportItem!totalMarket.Value)) ... etc but that syntax did
> not work... does anyone know how to grab the name of the textbox in
> an expression?
> ps - this is not a header or a footer - it's in the actual form
> Thanks in advance!
> Lisa
As far as I know, referencing the textbox control by an alias is not
possible. Referencing the textbox by the expression used is most
likely the only way to reference it. Sorry that I could not be of
greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultantsql