Showing posts with label parameters. Show all posts
Showing posts with label parameters. Show all posts

Monday, March 12, 2012

I thought I posted this one but dont see it. Problem with querying a query

It is my understanding that Views cannot have parameters. Also that
stored procedures can not be queried. My problem is this:

I want to select the rows that match a certain parameter.

From that I want to select the most current 20 rows (there is a date
field).

From that I want to select the lowest 10 rows based on a numeric
field.

Finally I want that to be input to a report and some calculations.

What this basically is the selection for USGA Golf Handicap Index. It
is the most current 20 rounds of golf by a golfer, then the best 10 of
those 20 and then finally the calculation.

Any help would be appreciated.Could be something like this. Here it is in SQL92:

SELECT S.employee_id, S.date_col, S.numeric_col
FROM Something AS S
LEFT JOIN Something AS T
ON S.employee_id = 1
AND T.employee_id = 1
AND (S.date_col < T.date_col
OR (s.date_col = T.date_col
AND S.numeric_col < T.numeric_col))
GROUP BY S.employee_id, S.date_col, S.numeric_col
HAVING COUNT(T.employee_id)<10 ;

Or, if you don't mind using the proprietary TOP modifier in SQL Server:

SELECT TOP 10 WITH TIES
employee_id, date_col, numeric_col
FROM Something
WHERE employee_id = 1
ORDER BY date_col DESC, numeric_col DESC ;

--
David Portas
SQL Server MVP
--|||Do some research on "inline user defined functions". These are essentially
views with parameters.

"Jim R" <JimReid@.comcast.net> wrote in message
news:7eb6df4b.0407151031.3b92ccd2@.posting.google.c om...
> It is my understanding that Views cannot have parameters. Also that
> stored procedures can not be queried. My problem is this:
> I want to select the rows that match a certain parameter.
> From that I want to select the most current 20 rows (there is a date
> field).
> From that I want to select the lowest 10 rows based on a numeric
> field.
> Finally I want that to be input to a report and some calculations.
> What this basically is the selection for USGA Golf Handicap Index. It
> is the most current 20 rounds of golf by a golfer, then the best 10 of
> those 20 and then finally the calculation.
> Any help would be appreciated.|||Thank you. My brother who is a dba in CA with Oracle was trying to
explain this to me but I just couldn't get it. You example allowed me
to do it both ways. I don't mind using the Top 10 with TIES but never
heard of it before.

Again thanks, makes for cleaner than temporary tables.

Jim

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Friday, March 9, 2012

I need to startup a windows application with parameters passed from SRS

Is there any way to start up a windows application and pass parameters from a
report. I know its a long shot but I am sure somebody knows how to do this.
--
Gene Hunter
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1Sure.
Write some custom code in an assembly that you link to your report. Then in
your report call a function on the assembly to run your app. Since this is
.NET code, you have access to the Process object which you can use to start
the process.
"gene hunter via SQLMonster.com" wrote:
> Is there any way to start up a windows application and pass parameters from a
> report. I know its a long shot but I am sure somebody knows how to do this.
> --
> Gene Hunter
>
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1
>

I need to put ‘ExecuteNonQuery’ in this Command ?


Dim CmdCertAut AS New SqlCommand("sel_CertAuto", strConnection)
CmdCertAut.CommandType = CommandType.StoredProcedure

CmdCertAut.Parameters.Add(New SqlParameter("@.Product_id", SqlDbType.smallint, 2, "Product_id"))
CmdCertAut.Parameters("@.Product_id").Value = product.SelectedItem.Value

CmdCertAut.Parameters.Add(New SqlParameter("@.Region_id", SqlDbType.smallint, 2, "Region_id"))
CmdCertAut.Parameters("@.Region_id").Value = reg.SelectedItem.Value

strConnection.open()
CmdCertAut.ExecuteNonQuery

Dim Certif_Aut As SqlDataReader = CmdCertAut.ExecuteReader(CommandBehavior.CloseConnection)

certAut.DataSource = Certif_Aut
certAut.DataBind()

Dim Cert_Aut As New ListItem
Cert_Aut.Text = "(Select)"
Cert_Aut.Value = "0"
certAut.Items.Insert(0, Cert_Aut)
certAut.SelectedIndex = 0

what are you trying to do ? bind the results of the stored proc to a datagrid ?|||Yes|||Then.. I need tho put it?