Query a Query with multiple results

Hi,
New to .Net and SQL. I have two tables that I have joined together. RentalControl_Main has the rental informationd and an Adjuster ID that links to the ADjuster table and the adjusters name. I am trying to create a report that gives the "Single" adjuster name and the totals for all of their contracts. I have a details report that gives each contract info. for each specific adjusters rentals. However, I want to just list the adjuster once and give all of their totals. In my SQL statement I have all of it written out and just need to knowwhat to do in place of 'Alex Early' that will give me all of the distinct adjusters.
Do I need to code this on the page with a do while loop?
Appreciate any help.


SELECT SUM(dbo.RentalControl_Main.Rate) / COUNT(dbo.RentalControl_Main.Rate) AS AmtAvg, SUM(dbo.RentalControl_Main.DaysBilled)
/ COUNT(dbo.RentalControl_Main.DaysBilled) AS DayAvg, SUM(dbo.RentalControl_Main.Rate * dbo.RentalControl_Main.DaysBilled)
/ COUNT(dbo.RentalControl_Main.Rate) AS TotAvg
FROM dbo.RentalControl_Main INNER JOIN
dbo.RentalControl_Adjuster ON dbo.RentalControl_Main.AdjusterID = dbo.RentalControl_Adjuster.AdjusterID
WHERE (dbo.RentalControl_Adjuster.AdjusterName = 'Alex Early' AND (dbo.RentalControl_Main.DateClose IS NOT NULL) AND
(dbo.RentalControl_Main.AgencyID = '2')I don't mean to sound off-ish, but do you know much about GROUP BY and DISTINCT?

Queries??

I'm confused.

I am creating a interaction application for alton towers available through a website (using asp.net). This website is connected to a database(sql server 2000) containing infomation on different shops, rides, restaurants and facilities at the park.

I have inputted data to my database. The user should be able to do searches such as:
rides (and shops or all) available in a certain area.
rides in a certain area above a certain height rescrictions.
etc

I'm confused. I have used SQL statements before and I know it is possible to retrieve this information from the database, but how should I go about this and how should I be storing this information so that I am able to call it from the front teir.

I have read up on views, stored procedures and triggers and I'm a bit lost. Should I be creating all the possible queries and then store them as a view (or stored procedure) I thought I would just have to write a sql statement but it seems a lot more confusing...

and what about triggers and user defined statements??

PLEASE help, I have a deadline in a week : (

THANK YOUOriginally posted by asbirpam
I have a deadline in a week : (

Not much time for QA, huh...

Use stored procedures

CREATE PROC mySproc99
@.key int
AS
SELECT Col_list* FROM myTable99 WHERE Key = myKey99**
GO

EXEC mySproc99 value
GO

* Supply the columns you need from the table

** Supply the name of the key colmn and the value you need to select...

Good luck...

Come back with more specific info if you need help

Queried default values

Hello!
Iâ'm using reporting services in a web application. I have made my own user
interface (asp.net / c#) it works rather well except handling queried default
values.
Iâ'm using Stored Procedures to get the default values (non-queried default
values are no problem). The available values are selected the same way
without problems. The SP to get the default and valid values are using one of
the report parameters as a parameter and it works fine using the report
manager.
ReportParams = rs.GetReportParameters(ReportPath,null,true,ParamValues,null);
Above returns NULL or empty default values.
Can anyone help me how to handle queried default values?
If anyone is familiar with my problem, please reply with a solution.
Best Regards,
daniel_bI've done this in vb.net, and it works without problems. Are you sure
you are using the web service correctly?
I set the ParamValues parameter to be Nothing (null) if I have no known
parameter values yet, or to be an array of ParameterValues (with
prompt, name and value set) if I already know the value of some of the
parameters. I sometimes know the value of some parameters when I am
dealing with dependant parameters where the list of values for one
parameter is not known until another parameter has its value set.
ParameterList = service.GetReportParameters(ReportName, Nothing,
True, values, Nothing)
Does this help?
Kulgan.

Q: reporthPath

Hello,
I keep my reports c:\Inetpub\wwwroot\MyApp\Reports\ folder, how should I set
my ReportPath for report viewer to see it in my APS.NET application.
Thanks,Are you using RS 2000 or 2005.
If 2005 are you doing local or server mode?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:57D67BC8-7279-46D7-A8DA-CD8E88F6DB49@.microsoft.com...
> Hello,
> I keep my reports c:\Inetpub\wwwroot\MyApp\Reports\ folder, how should I
> set
> my ReportPath for report viewer to see it in my APS.NET application.
> Thanks,
>|||RS 2000. Can I use RS2005 in my SQL Server 2000?
"Bruce L-C [MVP]" wrote:
> Are you using RS 2000 or 2005.
> If 2005 are you doing local or server mode?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:57D67BC8-7279-46D7-A8DA-CD8E88F6DB49@.microsoft.com...
> > Hello,
> >
> > I keep my reports c:\Inetpub\wwwroot\MyApp\Reports\ folder, how should I
> > set
> > my ReportPath for report viewer to see it in my APS.NET application.
> > Thanks,
> >
> >
>
>|||The reason I asked is if you are using 2000 then you have a misconception.
You say that your reports are in a particular folder. That statement makes
no sense with 2000. Report RDL files exist in the development environment
(which is no your web server). Then you deploy to RS. The act of deploying
has RS stored the reports in the database. No rdl files exist on the server.
Then to integrate with your app you use URL integration or soap (web
services). The report viewer is an example on how to do this. I believe it
uses URL integration.
VS 2005 has a webform and winform controls. Highly functional versions that
use web services rather than URL integration. These controls require RS
2005. RS 2005 can use SQL Server 2000 for its object/metadata store but you
still have to have a 2005 license.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:D55A0051-48F4-48FC-899A-506BFF964AAD@.microsoft.com...
> RS 2000. Can I use RS2005 in my SQL Server 2000?
> "Bruce L-C [MVP]" wrote:
>> Are you using RS 2000 or 2005.
>> If 2005 are you doing local or server mode?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:57D67BC8-7279-46D7-A8DA-CD8E88F6DB49@.microsoft.com...
>> > Hello,
>> >
>> > I keep my reports c:\Inetpub\wwwroot\MyApp\Reports\ folder, how should
>> > I
>> > set
>> > my ReportPath for report viewer to see it in my APS.NET application.
>> > Thanks,
>> >
>> >
>>|||Hi Bruce,
Thank you very much for your response. This part is a little bit confusing
for me. I know I can set ServerUrl and ReportPath properties to render the
report.
string serverUrl="http://MyServer/ReportServer";
this.ReportViewer1.ServerUrl=serverUrl;
this.ReportViewer1.ReportPath="/RepName";
This was working. Now I moved all the report under a folder called â'Reportsâ'
at the same level of RepName folder. So I am assuming
string serverUrl="http://MyServer/ReportServer";
this.ReportViewer1.ServerUrl=serverUrl;
this.ReportViewer1.ReportPath="/Reports/RepName";
this should work. How ever I get error rsItemNotFound. What am I missing?
Thanks,
"Bruce L-C [MVP]" wrote:
> The reason I asked is if you are using 2000 then you have a misconception.
> You say that your reports are in a particular folder. That statement makes
> no sense with 2000. Report RDL files exist in the development environment
> (which is no your web server). Then you deploy to RS. The act of deploying
> has RS stored the reports in the database. No rdl files exist on the server.
> Then to integrate with your app you use URL integration or soap (web
> services). The report viewer is an example on how to do this. I believe it
> uses URL integration.
> VS 2005 has a webform and winform controls. Highly functional versions that
> use web services rather than URL integration. These controls require RS
> 2005. RS 2005 can use SQL Server 2000 for its object/metadata store but you
> still have to have a 2005 license.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:D55A0051-48F4-48FC-899A-506BFF964AAD@.microsoft.com...
> > RS 2000. Can I use RS2005 in my SQL Server 2000?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Are you using RS 2000 or 2005.
> >>
> >> If 2005 are you doing local or server mode?
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> >> news:57D67BC8-7279-46D7-A8DA-CD8E88F6DB49@.microsoft.com...
> >> > Hello,
> >> >
> >> > I keep my reports c:\Inetpub\wwwroot\MyApp\Reports\ folder, how should
> >> > I
> >> > set
> >> > my ReportPath for report viewer to see it in my APS.NET application.
> >> > Thanks,
> >> >
> >> >
> >>
> >>
> >>
>
>|||How did you move the reports. The only way I know that works for this is to
deploy into another folder. Did you go through a deploy process?
I think what you need to do is to first get everything organized and check
that it is working with Report Manager first.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:B79A3821-90E3-4C7E-8527-D175E43FC6BA@.microsoft.com...
> Hi Bruce,
> Thank you very much for your response. This part is a little bit confusing
> for me. I know I can set ServerUrl and ReportPath properties to render the
> report.
> string serverUrl="http://MyServer/ReportServer";
> this.ReportViewer1.ServerUrl=serverUrl;
> this.ReportViewer1.ReportPath="/RepName";
> This was working. Now I moved all the report under a folder called
> "Reports"
> at the same level of RepName folder. So I am assuming
> string serverUrl="http://MyServer/ReportServer";
> this.ReportViewer1.ServerUrl=serverUrl;
> this.ReportViewer1.ReportPath="/Reports/RepName";
> this should work. How ever I get error rsItemNotFound. What am I missing?
> Thanks,
>
> "Bruce L-C [MVP]" wrote:
>> The reason I asked is if you are using 2000 then you have a
>> misconception.
>> You say that your reports are in a particular folder. That statement
>> makes
>> no sense with 2000. Report RDL files exist in the development environment
>> (which is no your web server). Then you deploy to RS. The act of
>> deploying
>> has RS stored the reports in the database. No rdl files exist on the
>> server.
>> Then to integrate with your app you use URL integration or soap (web
>> services). The report viewer is an example on how to do this. I believe
>> it
>> uses URL integration.
>> VS 2005 has a webform and winform controls. Highly functional versions
>> that
>> use web services rather than URL integration. These controls require RS
>> 2005. RS 2005 can use SQL Server 2000 for its object/metadata store but
>> you
>> still have to have a 2005 license.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:D55A0051-48F4-48FC-899A-506BFF964AAD@.microsoft.com...
>> > RS 2000. Can I use RS2005 in my SQL Server 2000?
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> Are you using RS 2000 or 2005.
>> >>
>> >> If 2005 are you doing local or server mode?
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> >> news:57D67BC8-7279-46D7-A8DA-CD8E88F6DB49@.microsoft.com...
>> >> > Hello,
>> >> >
>> >> > I keep my reports c:\Inetpub\wwwroot\MyApp\Reports\ folder, how
>> >> > should
>> >> > I
>> >> > set
>> >> > my ReportPath for report viewer to see it in my APS.NET application.
>> >> > Thanks,
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>